بعد از ارتقای دیتابیس به نسخه ای بالاتر، ممکن است زمان اجرای بعضی از پرس و جوها افزایش پیدا کند. این کندی می تواند به تغییراتی که در رفتار 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′;
وحید یوسف زاده
ارائه خدمات مشاوره ، پشتیبانی و نصب و راه اندازی پایگاه داده اوراکل در سراسر کشور...................... تلفن: 09128110897 ایمیل:vahidusefzadeh@gmail.com