Fix کردن پلن کوئری های فاقد bind variable

برای دستور زیر، دو plan در دیتابیس موجود است:

SQL> select * from mytbl where object_id=9;

SQL> @plan_hash_value

قصد داریم با ایجاد sql profile، پلن شماره 1787877304 را برای پرس و جو 9tfrqw5x3qw8s، فیکس کنیم:

SQL> @coe_xfr_sql_profile.sql 9tfrqw5x3qw8s 1787877304

SQL>BEGIN

2 IF :other_xml IS NULL THEN

3 RAISE_APPLICATION_ERROR(-20101, ‘PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).’);

4 END IF;

5 END;

6 /

Execute coe_xfr_sql_profile_9tfrqw5x3qw8s_1787877304.sql on TARGET system in order to create a custom SQL Profile with plan 1787877304 linked to adjusted sql_text.

SQL>

SQL> @coe_xfr_sql_profile_9tfrqw5x3qw8s_1787877304.sql

SQL> DECLARE

14 wa(q'[select * from mytbl where object_id=9

15 ]’);

16 DBMS_LOB.CLOSE(sql_txt);

17 h := SYS.SQLPROF_ATTR(

18 q'[BEGIN_OUTLINE_DATA]’,

19 q'[IGNORE_OPTIM_EMBEDDED_HINTS]’,

20 q'[OPTIMIZER_FEATURES_ENABLE(‘19.1.0′)]’,

21 q'[DB_VERSION(‘19.1.0′)]’,

22 q'[ALL_ROWS]’,

23 q'[OUTLINE_LEAF(@”SEL$1″)]’,

24 q'[INDEX_RS_ASC(@”SEL$1″ “MYTBL”@”SEL$1” (“MYTBL”.”OBJECT_ID”))]’,

25 q'[BATCH_TABLE_ACCESS_BY_ROWID(@”SEL$1″ “MYTBL”@”SEL$1”)]’,

26 q'[END_OUTLINE_DATA]’);

27 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);

28 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);

29 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

30 sql_text => sql_txt,

31 profile => h,

32 name => ‘coe_9tfrqw5x3qw8s_1787877304’,

33 description => ‘coe 9tfrqw5x3qw8s 1787877304 ‘||:signature||’ ‘||:signaturef||”,

34 category => ‘DEFAULT’,

35 validate => TRUE,

36 replace => TRUE,

37 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );

38 DBMS_LOB.FREETEMPORARY(sql_txt);

39 END;

40 /

PL/SQL procedure successfully completed.

توجه: از اسکریپت coe_xfr_sql_profile.sql می توان برای جابجایی sql profile بین دیتابیسها هم استفاده کرد.

لیست sql profile ایجاد شده را می توان از طریق ویوی dba_sql_profiles مشاهده کرد:

select name,p.sql_text,type,status,force_matching from dba_sql_profiles p;

با sql profile ایجاد شده، پلن شماره 1787877304 برای 9tfrqw5x3qw8s انتخاب خواهد شد:

select * from mytbl where object_id=9;

با توجه به آنکه در پرس و جوی فوق از bind variable استفاده نشده و از سوی دیگر پارامتر cursor_sharing هم در حالت پیش فرضش قرار دارد(cursor_sharing=exact)، بنابرین با تغییر شرط پرس و جو از object_id=9 به object_id=10ء، sql_id هم تغییر خواهد کرد:

SQL> select * from mytbl where object_id=10;

SQL> select sql_text,sql_id,plan_hash_value from v$sql where sql_text like ‘%select * from mytbl where object_id=10%’ and sql_text not like ‘%sql_text%’;

همانطور که می بینید با تغییر sql_id، اوراکل به sql profile ایجاد شده اهمیتی نمی دهد اگر قصد داریم برای همه literalها از sql profile استفاده شود، می توانیم گزینه force_match را در زمان ساخت sql profile به مقدار true تنظیم کنیم:

force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );

SQL> exec DBMS_SQLTUNE.drop_sql_profile(name => ‘coe_9tfrqw5x3qw8s_1787877304’);

PL/SQL procedure successfully completed

SQL> @coe_xfr_sql_profile_9tfrqw5x3qw8s_1787877304.sql

SQL> select name,p.sql_text,type,status,force_matching from dba_sql_profiles p;

با این تغییر، برای همه literalها، از plan شماره 1787877304 استفاده خواهد شد:

SQL> select * from mytbl where object_id=10;

SQL> select * from mytbl where object_id=9;

SQL> select * from mytbl where object_id=11;

تالار های تخصصی...