ایجاد SQL Profile به صورت دستی

برای اعمال نظر در مورد execution plan پرس و جویی که اصلاح متن ان امکان پذیر نیست، می توان از sql profile کمک گرفت و از طریق ایجاد ان، هینتهایی را به این پرس و جو اعمال کرد. در ادامه همراه با یک مثال ساده، اثر استفاده از sql profile را بررسی خواهیم کرد.

مثال:دستیابی غیرهمروند به جدول tbl به صورت full table scan، با هزینه 5851 قابل انجام است:

select * from tbl;

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2670366 5415502248 5851 00:01:11
1 . TABLE ACCESS FULL TBL 2670366 5415502248 5851 00:01:11

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

select /*+parallel(9) */* from tbl;

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2670366 5415502248 721 00:00:02
1 . PX COORDINATOR
2 .. PX SEND QC (RANDOM) :TQ10000 2670366 5415502248 721 00:00:02
3 … PX BLOCK ITERATOR 2670366 5415502248 721 00:00:02
4 …. TABLE ACCESS FULL TBL 2670366 5415502248 721 00:00:02

فرض کنید با بررسی همه شرایط، به این نتیجه رسیدیم که این هینت همروندی را به دستور اضافه کنیم؛ اما به دلیل عدم دسترسی به کد، این مسئله برای ما امکان پذیر نیست! چه باید کرد؟

یکی از راهکارهای حل این مسئله، ایجاد sql profile و اعمال هینت همروندی از طریق این پروفایل به دستور می باشد.در ادامه شیوه ایجاد sql profile را برای این دستور مشاهده می کنید.

در ابتدا با مشاهده قسمت OUTLINE DATA در پلن اجرایی این دستور، outline hintای که برای ایجاد پروفایل به ان نیاز است را مشخص می کنیم:

SQL>  explain plan for  select /*+parallel(9)*/* from tbl t;

SQL>  select * from table(dbms_xplan.display(null,null,’ADVANCED’));

  /*+

      BEGIN_OUTLINE_DATA

      FULL(@”SEL$1″ “T”@”SEL$1”)

      OUTLINE_LEAF(@”SEL$1″)

      SHARED(9)

      END_OUTLINE_DATA

  */

هینت همروندی در قسمت outline data با عبارت shared دیده می شود.

سپس با کمک پروسیجر import_sql_profile از بسته dbms_sqltune، پروفایل مورد نظر را ایجاد می کنیم:

begin

 dbms_sqltune.import_sql_profile(

 name => ‘PROFILE_USEF’,

 category => ‘DEFAULT’,

 sql_text => ‘ select * from tbl’,

 profile => sqlprof_attr(‘SHARED(9)’));

end;

نکته: به جای عبارت shared می توان از عبارت parallel هم استفاده کرد همچنین الزامی به تعیین همه outline hintها برای پارامتر profile وجود ندارد.

با ایجاد این sql profile، پلن اجرایی دستور مورد نظر را بررسی می کنیم:

select * from tbl;

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2670366 5415502248 721 00:00:02
1 . PX COORDINATOR
2 .. PX SEND QC (RANDOM) :TQ10000 2670366 5415502248 721 00:00:02
3 … PX BLOCK ITERATOR 2670366 5415502248 721 00:00:02
4 …. TABLE ACCESS FULL TBL 2670366 5415502248 721 00:00:02

Note

– dynamic sampling used for this statement – SQL profile “PROFILE_USEF” used for this statement

همانطور که قابل مشاهده است، بدون استفاده از هیچ هینتی، دستور به صورت همروند اجرا خواهد شد البته با کمک sql profile.

مثالی که ارائه شد، شکل بسیار ساده و تقلیل یافته ای از شیوه ایجاد sql profile بود در صورتی که ایجاد sql profile برای پرس و جوهای طولانی، با پیچیدگی هایی همراه خواهد بود. در ادامه به صورت نمونه، تعدادی از outline hint پرکاربرد را از نظر می گذرانیم:

-دسترسی به جدول TBL به صورت full table scan:

profile=> sqlprof_attr(‘FULL(@SEL$1 TBL@SEL$1)’)

-معادل هینت INDEX(tbl ind1):

profile => sqlprof_attr(‘INDEX(@”SEL$1″ TBL@”SEL$1″ “IND1”)’)

-صرف نظر کردن از تمامی هینتهای استفاده شده در دستور:

profile => sqlprof_attr(‘IGNORE_OPTIM_EMBEDDED_HINTS’)

نکته: پارامتر دیگری در پروسیجر import_sql_profile وجود دارد که در این مثال از ان استفاده نشد، این پارامتر force_match می باشد که در صورت تنظیم ان به مقدار true، رفتاری شبیه به پارامتر cursor_sharing در حالت force  را ایفا خواهد کرد.

پ.ن 1: برای مشاهده لیست پروفایلها، می توان از ویوی dba_sql_profiles استفاده کرد.

پ.ن 2:برای حذف یک sql profile، می توان از پروسیجر drop_sql_profile بهره گرفت:

begin  dbms_sqltune.drop_sql_profile(name => ‘PROFILE_USEF’);end;

ارائه خدمات مشاوره ، پشتیبانی و نصب و راه اندازی پایگاه داده اوراکل در سراسر کشور...................... تلفن: 09128110897 ایمیل:vahidusefzadeh@gmail.com

Comments (6)

  1. سلام.
    برای مواردی که sql statment طولانی تر هست و variable bind دارند بوسیله sql_id امکان انجام این عملیات هست؟

    1. سلام واحترام
      bind variable رو میشه با پارامتر force_match از پروسیجر DBMS_SQLTUNE.IMPORT_SQL_PROFILE هندل کرد.

پاسخ دادن به قادری لغو پاسخ

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *