جمع آوری آمار برای جداول پارتیشن شده ای که حجم قابل توجهی هم دارند، می تواند بسیار چالش آفرین باشد برای مثال جدولی را در نظر بگیرید که اطلاعات هر سال آن در یک پارتیشن مجزا قرار گرفته و صرفا قرار است اطلاعات سال آخر این جدول بروزرسانی شود.

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

مثال زیر را ببینید:  

create table usef.mytbl  (

id number(6),

time_ date

)

PARTITION BY RANGE (time_)

INTERVAL(NUMTOYMINTERVAL(1, ‘YEAR’))

(PARTITION p_2018 VALUES LESS THAN (TO_date(‘1-1-2018’, ‘DD-MM-YYYY’)),

 PARTITION p_2019 VALUES LESS THAN (TO_date(‘1-1-2019’, ‘DD-MM-YYYY’)),

 PARTITION p_2020 VALUES LESS THAN (TO_date(‘1-1-2020’, ‘DD-MM-YYYY’))

);

SQL> insert into usef.mytbl values(1,TO_date(‘1-1-2018’, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into usef.mytbl values(2,TO_date(‘1-1-2019’, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into usef.mytbl values(3,TO_date(‘1-1-2020’, ‘DD-MM-YYYY’));

1 row inserted

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats(ownname => ‘USEF’,tabname => ‘MYTBL’);

PL/SQL procedure successfully completed

SQL> select table_name,partition_name,to_char(last_analyzed,’YYYY/DD/MM HH24:mi:ss’),num_rows from dba_tab_statistics where owner=’USEF’ and table_name=’MYTBL’;

SQL> exec dbms_stats.gather_table_stats(ownname => ‘USEF’,tabname => ‘MYTBL’);

PL/SQL procedure successfully completed

SQL> select table_name,partition_name,to_char(last_analyzed,’YYYY/DD/MM HH24:mi:ss’) ,num_rows from dba_tab_statistics where owner=’USEF’ and table_name=’MYTBL’;

همانطور که ملاحظه شد، با هر بار اجرای بسته dbms_stat، آمارها در هر دو سطح Partition و Global بروزرسانی خواهند شد حتی اگر تغییری در این جدول ایجاد نشده باشد.

از اوراکل 11g، با کمک خصیصه Incremental می توان این مسئله را کنترل کرد و مانع از جمع آوری مجدد آمار برای پارتیشنهایی شد که در دیتای انها تغییری ایجاد نشده است.

برای استفاده از این قابلیت، باید سه خصیصه INCREMENTAL، GRANULARITY و PUBLISH را به صورت زیر تنظیم نمود:

SQL> begin

  2    dbms_stats.set_table_prefs(‘USEF’,’MYTBL’,‘INCREMENTAL’,’TRUE’);

  3    dbms_stats.set_table_prefs(‘USEF’,’MYTBL’,‘GRANULARITY’,’AUTO’);

  4    dbms_stats.set_table_prefs(‘USEF’,’MYTBL’,‘PUBLISH’,’TRUE’);

  5  end;

  6  /

PL/SQL procedure successfully completed

SQL> SELECT DBMS_STATS.GET_PREFS(‘INCREMENTAL’,USER,’MYTBL’) incremental FROM dual;

INCREMENTAL

————

TRUE

بعد از این تنظیمات، مجددا سناریوی فوق را تکرار می کنیم:

SQL> exec dbms_stats.gather_table_stats(ownname => ‘USEF’,tabname => ‘MYTBL’);

PL/SQL procedure successfully completed

SQL>select table_name,partition_name,to_char(last_analyzed,’YYYY/DD/MM HH24:mi:ss’),num_rows from dba_tab_statistics where owner=’USEF’ and table_name=’MYTBL’;

SQL> exec dbms_stats.gather_table_stats(ownname => ‘USEF’,tabname => ‘MYTBL’);

PL/SQL procedure successfully completed

SQL>select table_name,partition_name,to_char(last_analyzed,’YYYY/DD/MM HH24:mi:ss’),num_rows from dba_tab_statistics where owner=’USEF’ and table_name=’MYTBL’;

همانطور که مشاهده شد، با توجه به انکه تغییری در دیتای جدول mytbl ایجاد نشده، تغییری در امار مربوط به این جدول در سطح Partition هم رخ نداده است. در ادامه قصد داریم رکوردی را که مربوط به پارتیشن p_2020 می باشد را در جدول درج کنیم:

SQL> insert into usef.mytbl values(6,TO_date(‘1-1-2019’, ‘DD-MM-YYYY’));

1 row inserted

SQL> commit;

Commit complete

با درج این اطلاعات، مجددا آمار جدول را بروزرسانی کرده و تاریخ بروزرسانی هر کدام از پارتیشنها را بررسی می کنیم:

SQL> exec dbms_stats.gather_table_stats(ownname => ‘USEF’,tabname => ‘MYTBL’);

PL/SQL procedure successfully completed

SQL>select table_name,partition_name,to_char(last_analyzed,’YYYY/DD/MM HH24:mi:ss’),num_rows from dba_tab_statistics where owner=’USEF’ and table_name=’MYTBL’;

همانطور که می بینید، با درج حتی یک رکورد در پارتیشن p_2020، آمار این پارتیشن و همچنین آمار GLOBAL جدول تغییر کرده است ولی اطلاعات مابقی پارتیشنهای جداول ثابت باقی مانده است.

در ادامه قصد داریم چالش اساسی که در این زمینه مطرح می شود را با کمک قابلیت جدیدی که در اوراکل 12c ارائه شده برطرف کنیم.

همانطور که در قسمت فوق ملاحظه شد، زمانی که از خصیصه incremental برای مدیریت جمع اوری امار مربوط به جداول پارتیشن بندی شده استفاده می کنیم، با هر تغییری(حتی تغییر یک رکورد) در هر کدام از این پارتیشنها، امار ان پارتیشن هم بروز رسانی می شود با توجه به اینکه در طول روز ممکن است در بعضی از پارتیشن های قدیمی جدول اطلاعاتی هر چند ناچیز درج شود، شاید توجیه چندانی برای بروزرسانی امار این دست از پارتیشنها در زمان اجرای بسته dbms_stat وجود نداشته باشد.

فرض کنید اطلاعاتی را در جدول mytbl درج کرده و تعداد رکوردهای هر کدام از پارتیشنهای این جدول را به صورت زیر تغییر داده ایم:

صرفا یک رکورد به جدول mytbl و پارتیشن p_2018 اضافه می کنیم و سپس آمار مربوط به این جدول را بروزرسانی می کنیم:

SQL> insert into usef.mytbl values(50000,TO_date(‘1-1-2017’, ‘DD-MM-YYYY’));

1 row inserted

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats(ownname => ‘USEF’,tabname => ‘MYTBL’);

PL/SQL procedure successfully completed

SQL>select table_name,partition_name,to_char(last_analyzed,’YYYY/DD/MM HH24:mi:ss’),num_rows from dba_tab_statistics where owner=’USEF’ and table_name=’MYTBL’;

همانطور  که می بینید، اطلاعات این پارتیشن بروزرسانی شده است در صورتی که با خبر شدن optimizer از افزایش تعداد رکوردهای پارتیشن p_2018 از 16384 به 16385 چندان در تصمیم گیری اش موثر نخواهد بود.

همانطور که قبلا هم بیان شد، اوراکل در نسخه 12c بهبودی را در این زمینه ایجاد کرد که بر اساس آن می توان بروزرسانی مجدد امار پارتیشنهای جداول را به زمانی منوط کرد که درصد تغییرات در ان پارتیشن، از حد مشخصی بیشتر باشد یا به اصطلاح دقیقتر و فنی تر، عددی را به عنوان stale_percent برای خصیصه incremental می توان تعیین کرد.

برای مثال، با تنظیمات زیر، اگر میزان stale بودن امار پارتیشنهای جدول mytbl به 15 درصد برسد، امار ان جدول بروز رسانی خواهد شد:

SQL> exec DBMS_STATS.SET_TABLE_PREFS (ownname  => ‘USEF’,tabname  => ‘MYTBL’,pname    => ‘stale_percent’, pvalue   => 15);

PL/SQL procedure successfully completed

SQL> exec dbms_stats.set_table_prefs(ownname=>’USEF’, tabname=>’MYTBL’,pname=>’INCREMENTAL_STALENESS’,pvalue=>’USE_STALE_PERCENT’ );

PL/SQL procedure successfully completed

SQL> select dbms_stats.get_prefs(‘INCREMENTAL_STALENESS’,user,’MYTBL’)  from dual;

DBMS_STATS.GET_PREFS(‘INCREMEN

——————————-

USE_STALE_PERCENT

با انجام این تنظیمات، مجددا رکوردی را در نسخه 12c به جدول mytbl اضافه می کنیم:

SQL> insert into usef.mytbl values(50001,TO_date(‘1-1-2017’, ‘DD-MM-YYYY’));

1 row inserted

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats(ownname => ‘USEF’,tabname => ‘MYTBL’);

PL/SQL procedure successfully completed

select table_name,partition_name,to_char(last_analyzed,’YYYY/DD/MM HH24:mi:ss’),num_rows from dba_tab_statistics where owner=’USEF’ and table_name=’MYTBL’;

همانطور که می بینید آمار مربوط به پارتیشن p_2018 رفرش نشده است.

حال قصد داریم حجم قابل توجهی از اطلاعات را در این پارتیشن درج کنیم تا درصد تغییر ان به بیش از 15 درصد برسد:

SQL> insert into usef.mytbl select * from usef.mytbl where to_char(time_,’YYYY’)=2017;

16386 rows inserted

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats(ownname => ‘USEF’,tabname => ‘MYTBL’);

PL/SQL procedure successfully completed

همانطور که ملاحظه شد، با توجه به درج قابل توجهی از اطلاعات در پارتیشن p_2018، آمار مربوط به این پارتیشن هم بروزرسانی شده است.