برای دستور زیر، دو 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;