فیکس کردن plan بعضی از دستورات بعد از ارتقای دیتابیس

بعد از ارتقای دیتابیس به نسخه ای بالاتر، ممکن است زمان اجرای بعضی از پرس و جوها افزایش پیدا کند. این کندی می تواند به تغییراتی که در رفتار optimizer در هر نسخه از اوراکل ایجاد می شود، برگردد.

در جدول زیر اسامی تعدادی از قابلیتهایی که توسط optimizer در نسخه 11g و 12c قابل استفاده است را مشاهده می کنید:

در این متن به دنبال روشی هستیم که تغییر رفتار optimizer، در دو نسخه مختلف اوراکل را برای یک پرس و جوی مشخص نمایش داده و سپس با کمک قابلیت SQL Plan Management، پلن اجرایی ایجاد شده توسط optimizer، در یکی از این نسخه ها را برای پرس و جوی مورد نظر، فیکس کنیم.

در قسمت فوق اسامی تعدادی از قابلیتهای optimizer را مشاهده کردید یکی از این قابلیتها Subquery Unnesting بود که optimizer از آن در عملیات QUERY TRANSFORMATION استفاده می کند.

*** QUERY TRANSFORMATION یکی از مراحل ایجاد execution plan توسط optimizer می باشد که قرار است optimizer در این مرحله، متن پرس و جو را به شکلی تبدیل کند که اجرای ان با سرعت بالاتری انجام شود(البته در صورت امکان).QUERY TRANSFORMATION انواع مختلفی دارد نظیر View merging، Or Expansion، Subquery Unnesting و … در هر نسخه از اوراکل ممکن است بر کمیت و کیفیت انها تغییراتی ایجاد شود.

در ادامه پرس و جویی را می نویسیم تا optimizer بتواند در نسخه 18c، پرس و جوی مورد نظر را با کمک قابلیت Subquery Unnesting، بازنویسی کند. همچنین با کمک پارامتر OPTIMIZER_FEATURES_ENABLE، پلنی را در  نسخه 8i، برای این پرس و جو ایجاد می کنیم و سرانجام از بین planهای ایجاد شده، planای که توسط optimizer اوراکل 8i ایجاد شده را برای این پرس و جو FIX می کنیم.

به عنوان گام اول، اشیای مورد نیاز را در محیط تست ایجاد می کنیم.

SQL> create table usef.table1 as select * from dba_objects p where p.object_id is not null;

Table created.

SQL> alter table usef.TABLE1  add constraint ppk primary key (OBJECT_ID);

Table altered.

SQL> create table usef.table2 as select * from dba_objects p where 1=2;

Table created.

SQL> alter table usef.TABLE2   add constraint FFK foreign key (OBJECT_ID)     references usef.TABLE1 (OBJECT_ID);

Table altered.

SQL> insert into usef.table2 select * from usef.table1;

73097 rows created.

SQL> commit;

Commit complete.

SQL> create index usef.ind2  on usef.table2(OBJECT_ID);

Index created.

SQL>  exec dbms_stats.gather_table_stats(ownname =>’USEF’ ,tabname =>’TABLE1′,cascade => true );

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname =>’USEF’ ,tabname =>’TABLE2′,cascade => true );

PL/SQL procedure successfully completed.

فرض کنید در چنین محیطی، قصد اجرای پرس و جوی زیر را داریم:

select object_name from table1 where object_id in (select object_id from table2);

همانطور که در قسمت فوق ملاحظه کردید، جدول table1 و table2 از طریق ستون object_id با هم در ارتباط  هستند این موضوع می تواند زمینه را برای انجام Subquery Unnesting توسط optimizer فراهم کند(با توجه به subquery موجود در متن پرس و جو).

optimizer_features_enable=’8.0.0′

در صورتی که این پرس و جو با optimizer مربوط به اوراکل نسخه 8.0.0 اجرا شود، execution plan زیر ایجاد خواهد شد:

SQL> alter session set optimizer_features_enable=’8.0.0′;

Session altered.

SQL> select object_name from table1 where object_id in (select object_id from table2);

Plan Hash Value                : 341703768

Predicate Information (identified by operation id):

1 – access(“OBJECT_ID”=”OBJECT_ID”)

همانطور که در execution plan مربوط به این دستور می بینید، به صورت کلی optimizer از ایندکس استفاده نمی کند و cost تخمین زده شده برای اجرای این دستور برابر با 806 می باشد.

برای مشاهده جزییات بیشتر، trace مربوط به optimizer که برابر 10053 می باشد را در سطح session فعال می کنیم:

SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER=’Optimizer_Oracle_8i’;

Session altered.

SQL> ALTER SESSION SET EVENTS ‘10053 TRACE NAME CONTEXT FOREVER, LEVEL 1’;

Session altered.

SQL> SELECT p.tracefile FROM   v$session s JOIN v$process p ON s.paddr = p.addr WHERE  s.sid =(SELECT sid FROM v$mystat WHERE ROWNUM = 1);

TRACEFILE

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

/18c/base/diag/rdbms/db18c/db18c/trace/db18c_ora_26578_Optimizer_Oracle_8i.trc

با اجرای پرس و جو در این session خواهیم دید که optimizer این پرس و جو را به صورت دیگری بازنویسی کرده است:

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

SELECT TABLE1.OBJECT_NAME OBJECT_NAME FROM  (SELECT DISTINCT TABLE2.OBJECT_ID OBJECT_ID FROM USEF.TABLE2 TABLE2) VW_NSO_1,USEF.TABLE1 TABLE1

WHERE TABLE1.OBJECT_ID=VW_NSO_1.OBJECT_ID

Predicate Information:

———————-

1 – access(“OBJECT_ID”=”OBJECT_ID”)

 Content of other_xml column

===========================

  db_version     : 18.0.0.0

  parse_schema   : USEF

  plan_hash_full : 1957135188

  plan_hash      : 341703768

  plan_hash_2    : 1957135188

  Outline Data:

  /*+

    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘8.0.0’)

      DB_VERSION(‘18.1.0’)

      OUTLINE_LEAF(@”SEL$683B0107″)

      OUTLINE_LEAF(@”SEL$5DA710D3″)

      UNNEST(@”SEL$2″ UNNEST_INNERJ_DISTINCT_VIEW)

      OUTLINE(@”SEL$2″)

      OUTLINE(@”SEL$1″)

      FULL(@”SEL$5DA710D3″ “TABLE1″@”SEL$1”)

      NO_ACCESS(@”SEL$5DA710D3″ “VW_NSO_1″@”SEL$5DA710D3”)

      LEADING(@”SEL$5DA710D3″ “TABLE1″@”SEL$1” “VW_NSO_1″@”SEL$5DA710D3”)

      USE_HASH(@”SEL$5DA710D3″ “VW_NSO_1″@”SEL$5DA710D3”)

      SWAP_JOIN_INPUTS(@”SEL$5DA710D3″ “VW_NSO_1″@”SEL$5DA710D3”)

      FULL(@”SEL$683B0107″ “TABLE2″@”SEL$2”)

    END_OUTLINE_DATA

  */

نکته: در صورتیکه قصد داریم optimizer برای این دستور QUERY TRANSFORMATION را انجام ندهد، می توانیم از هینت NO_QUERY_TRANSFORMATION استفاده کنیم:

SQL> select /*+ NO_QUERY_TRANSFORMATION */ object_name from table1 where object_id in (select object_id from table2);

Plan Hash Value                : 549993984

optimizer_features_enable=’18.1.0.1′

حال قصد داریم همان پرس و جو را با optimizer feature اوراکل 18c اجرا کنیم:

SQL> set autotrace traceonly explain

SQL> alter session set optimizer_features_enable=’18.1.0.1′;

Session altered.

SQL> select object_name from table1 where object_id in (select object_id from table2);

Plan Hash Value                : 2688474724

Predicate Information (identified by operation id):

  • 1 – access(“OBJECT_ID”=”OBJECT_ID”)

همانطور که می بینید، در این execution plan، به ایندکس ind2 رجوع شده است و cost تخمین زده شده از 806 به 431 کاهش پیدا کرده است. همچنین با فعال کردن trace در سطح session، خواهیم دید که optimizer با کمک قابلیت subquery unnesting متن پرس و جو را به صورت زیر تغییر داده است:

SQL:******* UNPARSED QUERY IS *******

SELECT TABLE1.OBJECT_NAME OBJECT_NAME FROM USEF.TABLE2 TABLE2,USEF.TABLE1 TABLE1

WHERE TABLE1.OBJECT_ID=TABLE2.OBJECT_ID

Content of other_xml column

===========================

  db_version     : 18.0.0.0

  parse_schema   : USEF

  plan_hash_full : 14647104

  plan_hash      : 2688474724

  plan_hash_2    : 14647104

  Outline Data:

  /*+

    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘18.1.0.1’)

      DB_VERSION(‘18.1.0’)

      ALL_ROWS

      OUTLINE_LEAF(@”SEL$5DA710D3″)

      UNNEST(@”SEL$2″)

      OUTLINE(@”SEL$1″)

      OUTLINE(@”SEL$2″)

      FULL(@”SEL$5DA710D3″ “TABLE1″@”SEL$1”)

      INDEX_FFS(@”SEL$5DA710D3″ “TABLE2″@”SEL$2” (“TABLE2″.”OBJECT_ID”))

      LEADING(@”SEL$5DA710D3″ “TABLE1″@”SEL$1” “TABLE2″@”SEL$2”)

      USE_HASH(@”SEL$5DA710D3″ “TABLE2″@”SEL$2”)

      SWAP_JOIN_INPUTS(@”SEL$5DA710D3″ “TABLE2″@”SEL$2”)

    END_OUTLINE_DATA

  */

FIX کردن Execution Plan

همانطور که در قسمت قبلی مشاهده شد، optimizer با تغییر مقدار پارامتر optimizer_features_enable، پلن اجرایی جدیدی را ایجاد کرده است به عبارتی دیگر،  با رجوع به ویوی v$sql خواهیم دید که برای یک sql_id مشخص، دو plan hash value موجود است:

select p.SQL_ID, p.CHILD_NUMBER, p.PLAN_HASH_VALUE  from v$sql p

 where p.SQL_TEXT like ‘%select object_name from table1 where object_id in %’

and p.SQL_TEXT not like ‘%v$sql%’

and p.SQL_TEXT not like ‘%EXPLAIN PLAN SET STATEMENT_ID%’;

نکته: ایجاد چند execution plan برای یک پرس و جو، صرفا به تغییر مقدار پارامتر optimizer_features_enable  محدود نمی شود و ممکن است به دلایل دیگری چون ایجاد ایندکس، بروزرسانی آمار، تغییر در دیگر پارامترهای دیتابیس و … شاهد ایجاد execution plan جدید توسط optimizer باشیم.

در صورتی که بخواهیم پرس و جوی 26pwjpn2d15k2 همیشه با optimizer feature اوراکل 8i اجرا شود، راه ههای مختلقی وجود دارد که نمونه هایی از ان را در قسمت زیر می بینید:

1.تغییر optimizer_features_enable در سطح سیستم: این کار سبب خواهد شد تا امکان استفاده از قابلیتهای جدید optimizer برای دستورات دیگر از بین برود.

2.اضافه کردن hint به دستور

select /*+OPT_PARAM(‘optimizer_features_enable’,’8.0.0′)*/ object_name from table1 where object_id in (select object_id from table2);

در بعضی از applicationها امکان ایجاد هرگونه تغییر وجود ندارد بنابرین، استفاده از این روش در چنین شرایطی امکان پذیر نخواهد بود.

3.ایجاد sql profile به صورت دستی(به مقاله “ایجاد SQL Profile به صورت دستی” رجوع شود)

علاوه بر روشهای فوق، روش دیگری هم در این زمینه وجود دارد که آن هم استفاده از SQL baseline می باشد که در ادامه به نحوه استفاده از ان خواهیم پرداخت.

استفاده از SQL Plan Management برای fix کردن Plan یک پرس و جو

همانطور که بیان شد، با کمک SQL Baseline می توان به optimizer دستور داد تا برای یک پرس و جوی مشخص، تنها از یک execution plan مشخص استفاده کند.

در ادامه قصد داریم با کمک قابلیت SQL Plan Management نقشه اجرایی پرس و جوی 26pwjpn2d15k2 را به plan hash value شماره 341703768 فیکس کنیم.

1.در گام اول با کمک اطلاعاتی که از این پرس و جو در library cache وجود دارد، باید SQL plan baseline مربوط به این پرس و جو را در SPM baseline وارد کنیم.

شکل کلی:

var result number ;

exec :result := dbms_spm.load_plans_from_cursor_cache(sql_id => ‘<SQL_ID>’, plan_hash_value => ‘<PLAN_HASH_VALUE’ );

وارد کردن مشخصات پرس و جو در SPM baseline:

SQL> var result number ;

SQL> exec :result := dbms_spm.load_plans_from_cursor_cache(sql_id => ’26pwjpn2d15k2′, plan_hash_value => ‘341703768’ );

PL/SQL procedure successfully completed

result

———

1

SQL> select sql_text,sql_handle,plan_name,enabled,accepted,fixed from dba_sql_plan_baselines;

همانطور که می بینید، plan مورد نظر در SPM Baseline قرار گرفته اما هنوز برای این پرس و جو fix نشده است و ممکن است پرس و جو برای اجرا به سراغ plan دیگری برود.

نکته: اگر از زمان اجرای پرس و جو مدتی گذشته باشد و اطلاعات ان پرس و جو دیگر در library cache موجود نباشد، می توان به سراغ awr snapshot رفت.

2.با کمک تابع alter_sql_plan_baseline از بسته dbms_spm می توانیم plan مورد نظر را برای این دستور fix کنیم:

declare

  plans_loaded pls_integer ;

begin

  plans_loaded := dbms_spm.alter_sql_plan_baseline(

    sql_handle => ‘SQL_509ada5bace74157’,

     plan_name => ‘SQL_PLAN_516qubfqffhar74a78354’,

    attribute_name => ‘fixed’,

    attribute_value => ‘YES’

  ) ;

end ;

/

با رجوع مجدد به ویوی dba_sql_plan_baselines، خواهیم دید که execution plan برای دستور fix شده است:

select sql_text, sql_handle, plan_name, enabled, accepted,FIXED from dba_sql_plan_baselines;

با تنظیمات انجام شده، با هر تغییر در مقدار پارامتر optimizer_features_enable، شاهد یک plan ثابت خواهیم بود و optimizer همیشه پلن شماره 341703768 را انتخاب خواهد کرد:

SQL> startup force;

ORACLE instance started.

Total System Global Area 3925866272 bytes

Fixed Size                  8664864 bytes

Variable Size             989855744 bytes

Database Buffers         2919235584 bytes

Redo Buffers                8110080 bytes

Database mounted.

Database opened.

SQL> conn usef/a

Connected.

SQL> set autotrace traceonly explain

SQL>  alter session set optimizer_features_enable=’18.1.0.1′;

Session altered.

SQL> select object_name from table1 where object_id in (select object_id from table2);

Plan Hash Value : 341703768

Predicate Information (identified by operation id):

1 – access(“OBJECT_ID”=”OBJECT_ID”)

Notes

– SQL plan baseline “SQL_PLAN_516qubfqffhar74a78354” used for this statement

SQL> alter session set optimizer_features_enable=’12.1.0.1′;

Session altered.

SQL> select object_name from table1 where object_id in (select object_id from table2);

Plan Hash Value : 341703768

Predicate Information (identified by operation id):

1 – access(“OBJECT_ID”=”OBJECT_ID”)

Notes

– SQL plan baseline “SQL_PLAN_516qubfqffhar74a78354” used for this statement

با کمک دستور زیر، می توان تعداد دفعات استفاده از execution plan فیکس شده را مشاهده کرد:

select SQL_ID, PLAN_HASH_VALUE ,SQL_PLAN_BASELINE,EXECUTIONS, SQL_TEXT from v$sql p where SQL_PLAN_BASELINE=’SQL_PLAN_516qubfqffhar74a78354′;

ارتباط با نویسنده مطلب:vahidusefzadeh@ کانال تخصصی اوراکل و لینوکس: OracleDB@

پاسخی بگذارید

نشانی ایمیل شما منتشر نخواهد شد.