استفاده از قابلیت Automatic SQL Transpiler در اوراکل ۲۳c

مقدمه:

در اوراکل ۲۳c این قابلیت وجود دارد تا برخی توابع با زبان plsql به صورت خود کار(automatic SQL transpiler) به زبان sql تبدیل شوند تا سربار کمتری در زمان اجرای این توابع به پایگاه داده تحمیل شود. در ادامه این قابلیت را مورد بررسی قرار خواهیم داد.

آماده کردن محیط تست:

برای این منظور به اشیایی زیر در سطح پایگاه داده احتیاج داریم. دستورات زیر را در پایگاه داده ۲۳c خود اجرا کنید.

معرفی امکانات Oracle 23c

 

CREATE TABLE test_t1

(

    id      NUMBER,

    col1    NUMBER,

    col2    NUMBER

);

INSERT INTO test_t1 (id, col1, col2)

     VALUES (1, 1, 2),( 2 ,10,20),( 3 ,100,200);

COMMIT;

در ادامه تابعی می سازیم که دو مقدار را باهم جمع کند:

CREATE OR REPLACE FUNCTION add_number (num1 IN NUMBER, num2 IN NUMBER)

    RETURN NUMBER

AS

BEGIN

    RETURN num1 + num2;

END;

/

طرح مشکل:

با استفاده از توابع می توان کد ها رو به  صورت ماژولار نوشت که قابلیت های خوبی به ما می دهد. اما با توجه به این توابع با Plsql  نوشته می شوند در زمان استفاده در sql دچار context switching  و این سربار ایجاد می کند. راهکارهایی در کاهش سربار وجود دارد که اوراکل ۲۳c  قابلیت Automatic SQL Transplier  را ارائه کرده که مورد بررسی قرا می دهیم.

در ادامه کوئری را زیر را اجرا کرده وپلن اجرای آن را مشاهده می کنیم

 

select id, col1, col2

from   test_t1

where  add_number(col1, col2) = 300;

select *

from   dbms_xplan.display_cursor();

Plan hash value: 4120417487

—————————————————————————–

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————–

|   ۰ | SELECT STATEMENT  |         |     ۱ |    ۳۹ |     ۳   (۰)| ۰۰:۰۰:۰۱ |

|*  ۱ |  TABLE ACCESS FULL| TEST_T1 |     ۱ |    ۳۹ |     ۳   (۰)| ۰۰:۰۰:۰۱ |

—————————————————————————–

Predicate Information (identified by operation id):

—————————————————

   ۱ – filter(“ADD_NUMBER”(“COL1″,”COL2”)=300)

Note

—–

   – dynamic statistics used: dynamic sampling (level=2)

 

حالا با کاربر sys وارد می شویم وshared pool را flush  می کنیم

 

alter system flush shared_pool;

 

 

 

درادامه قابلیت automatic SQL transpiler را در سطح session فعال می کنیم و مراحل فوق را دوباره اجرا میکنیم

 

alter session set sql_transpiler = ‘ON’;

select id, col1, col2

from   test_t1

where  add_number(col1, col2) = 300;

select *

from   dbms_xplan.display_cursor();

Plan hash value: 4120417487

—————————————————————————–

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————–

|   ۰ | SELECT STATEMENT  |         |     ۱ |    ۳۹ |     ۳   (۰)| ۰۰:۰۰:۰۱ |

|*  ۱ |  TABLE ACCESS FULL| TEST_T1 |     ۱ |    ۳۹ |     ۳   (۰)| ۰۰:۰۰:۰۱ |

—————————————————————————–

Predicate Information (identified by operation id):

—————————————————

   ۱ – filter(“COL1″+”COL2″=300)

Note

—–

   – dynamic statistics used: dynamic sampling (level=2)

همانطور که مشاهده می کنید در این به جای تابع از دستورsql استفاده شده برای اینکه بهتر متوجه بشیم در این حالت چه اتفاقی می افتد باید عملکرد query transformation را مشاهده کنیم. برای این حالت باید از trace 10053 استفاده شود

 

alter session set sql_transpiler = ‘ON’;

alter session set events ‘10053 trace name context forever’;

select id, col1, col2

from   test_t1

where  add_numbers(col1, col2) = 300;

alter session set events ‘۱۰۰۵۳ trace name context off’;

در ادامه نتیجه را در trace مشاهده می کنیم.

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT “T1″.”ID” “ID”,”T1″.”COL1″ ” COL1″,”T1″.” COL2″ ” COL2″ FROM “SOLTANI”.”TEST_T1″ “T1” WHERE “T1″.” COL1″+”T1″.” COL2″=300

همانطور که مشاهده می کنید تابع حذف شده و از دستور مستقیم sql استفاده شده است.

نکته : در حال حاضر توابعی که در Package ها تعریف شده اند را نمی توان با استفاده automatic SQL transpiler تغییر داد.

 

منبع مستند