در شبانه روز ممکن است دستورات DMLای متعددی بر روی جداول مختلف اجرا شوند، در نظر داشتن این مسئله، ذهن را متوجه چالش مهمی خواهد کرد!

از بین جداولی که بعضا حجم انها به چند صد میلیون می رسد، کدام یک بیشترین تغییرات را به لحاظ حجمی داشته اند؟ و به بیانی بهتر، برای کدام یک از جداول، باید(بهتر است) بروزرسانی امار(gather statistic) انجام شود؟

برای پاسخ به این دسته از سوالات، نیاز است تا نظارتی بر روی جداول صورت پذیرد و تعداد عملیات DMLای که بر روی انها انجام می شود، در جدولی از بانک ثبت شود.

این اتفاق از نسخه های قدیمی اوراکل تا به امروز(نسخه 18c) در حال انجام است و اطلاعات مورد نظر و تعداد تغییرات DMLای مربوط به یک جدول، در جدولی به نام $mon_mods_all ذخیره می شود و با هر بار بروزرسانی آمار، رکورد جدول مربوطه، از این جدول حذف خواهد شد:

SQL> desc mon_mods_all$

 Name                                      Type

 —————————-    ——————-

 OBJ#                                       NUMBER

 INSERTS                                  NUMBER

 UPDATES                                NUMBER

 DELETES                                  NUMBER

 TIMESTAMP                           DATE

 FLAGS                                      NUMBER

 DROP_SEGMENTS                 NUMBER

نکته: برای مشاهده تعداد تغییرات جداول به شکل خواناتر، می توان از ویوی dba_tab_modifications استفاده کرد.

تا قبل از اوراکل نسخه 12c، تغییرات جداول(اطلاعاتی که در جدول $mon_mods_all ذخیره می شوند)، برای مدتی در حافظه باقی می ماندن و در یک بازه زمانی مشخص(در اوراکل 11g هر 15 دقیقه یکبار)، از حافظه به دیسک منتقل می شدند به همین جهت، جدول مربوطه و به تبع ان، ویوی dba_tab_modifications همیشه اطلاعات بروز را نمایش نمی داد.

در چنین حالتی، برای انتقال دستی اطلاعات از حافظه به دیسک، می توان پروسیجر FLUSH_DATABASE_MONITORING_INFO از بسته dbms_stats را اجرا کرد:

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

 PL/SQL procedure successfully completed

در اوراکل 12c، اطلاعات به صورت انلاین در جدول $mon_mods_all قابل مشاهده خواهند بود.

 

در ادامه با ارائه مثالی، شیوه نگهداری تغییرات جداول و همچنین اثر بروزرسانی امار یک جدول بر روی رکوردهای حاضر در جدول mon_mods_all، را مورد بررسی قرار خواهیم داد.

مثال: در ابتدا جدولی را ایجاد کرده و سپس رکوردی را به ان اضافه می کنیم:

SQL>create table usef.mytbl(name varchar2(15));

SQL>insert into usef.mytbl values(‘USEF’);

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

SQL>select p.inserts,p.updates,p.deletes from dba_tab_modifications p where p.table_name=’MYTBL’;

همانطور که می بینید، خروجی دستور نشان می دهد که تنها یکبار بر روی جدول mytbl عملیات insert انجام شده است.حال امار مربوط به این جدول را برورسانی می کنیم:

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

مجددا اطلاعات ویوی dba_tab_modifications را بازبینی می کنیم:

SQL>select p.inserts,p.updates,p.deletes from dba_tab_modifications p where p.table_name=’MYTBL’;

no rows selected

خروجی دستور نشان می دهد که اطلاعات از جدول  $mon_mods_all پاک شده است.

 

در ادامه این متن، به این سوال پاسخ خواهیم داد که اوراکل بعد از چند درصد تغییر در اطلاعات یک جدول، تصمیم می گیرد تا آمارهای آن را بروزرسانی کند؟

به صورت پیش فرض، اگر جدولی بعد از اخرین جمع آوری آمارش، به اندازه 10% تغییر کند(به لحاط دیتا)، آمار جمع آوری شده مربوط به ان جدول، stale تلقی خواهد شد و در زمان اجرای جاب معمولا شبانه(DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS) و یا با اجرای بسته dbms_stats(همراه با یکی از دو پروسیجرGATHER_SCHEMA_STATS  یا GATHER_DATABASE_STATS و با گزینه هایی چون gather auto و یا gather stale)، امار مربوط به این جدول هم بروزرسانی می شود.

در مثال زیر خواهیم دید ایجاد تغییرات در جداول(تا درصدی مشخص)، سبب stale شدن امار انها خواهد شد.

مثال: در ابتدا با کمک دستور زیر، تاریخ اخرین زمان جمع اوری امار و همچنین وضیعت فعلی امار را برای جدول mytbl مشخص می کنیم:

select p.last_analyzed,p.stale_stats from dba_tab_statistics p where p.table_name=’MYTBL’;

همانطور که می بینید، آمار مربوط به این جدول، در حالت stale قرار ندارد. با کمک بلاک زیر، اطلاعاتی را به این جدول اضافه می کنیم:

SQL>begin

  for i in 1..10 loop

    insert into usef.mytbl values(‘USEF’);

    commit;

  end loop;

end;

دستور زیر هم تایید می کند که ده رکورد به این جدول اضافه شده است:

SQL>select p.inserts,p.updates,p.deletes from dba_tab_modifications p where p.table_name=’MYTBL’;

طبق امار قبلی بانک، این جدول صرفا یک رکورد دارد:

SQL>select p.num_rows from  dba_tables p  where p.table_name=’MYTBL’;

1

با کمک فرمول زیر محاسبه می کنیم که آمار فعلی این جدول، در حالت stale قرار گرفته است یا خیر؟

SQL>select round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as PCT_MODIFIED From dba_tab_modifications m,dba_tables t where m.table_owner=t.owner and m.table_name=t.table_name and m.table_name=’MYTBL’;

1000

علاوه بر این دستور، می توان با رجوع به فیلد stale_stats از ویوی dba_tab_statistics، مشاهده کرد که امار مربوط به این جدول، در حالت stale قرار دارد یا خیر؟

SQL>select p.last_analyzed,p.stale_stats from dba_tab_statistics p where p.table_name=’MYTBL’;

همانطور که می بینید، آمار مربوط به این جدول، در حالت stale قرار دارد.

نکته 1: مقدار پیش فرض STALE_PRECENT با کمک دستور زیر بدست خواهد امد:

SQL>select dbms_stats.get_prefs(‘STALE_PERCENT‘) from dual;

و همچنین می توان با کمک دستور زیر، مقدار STALE_PRECENT را در سطح بانک به 90 درصد تغییر خواهد کرد:

SQL> exec dbms_stats.set_global_prefs(‘STALE_PERCENT’,90);

PL/SQL procedure successfully completed

این پارامتر در سطح جدول هم قابل تنظیم می باشد:

SQL> exec  dbms_stats.set_table_prefs(ownname => ‘USEF’,tabname => ‘MYTBL’,pname => ‘STALE_PERCENT‘,pvalue => 90);

PL/SQL procedure successfully completed

نکته 2: با کمک ویوی DBA_TAB_STAT_PREFS می توان لیست پارامترهایی که برای جداول مختلف، در زمینه جمع اوری امار تنظیم شده است، را مشاهده کرد.

نکته 3: دستور زیر، آمار همه جداول کاربر usef که در حالت stale قرار دارند را بروزرسانی خواهد کرد:

SQL>begin DBMS_STATS.GATHER_SCHEMA_STATS (ownname =>’USEF’,options=>‘GATHER STALE’); end;

در اوراکل 9i، امکان مدیریت دستی monitoring جداول وجود داشت و در صورت صلاحدید، dba می توانست جدولی را به طور کلی از حالت monitoring خارج کند. برای این کار کافی بود تا از دستور زیر استفاده می کرد:

SQL>alter table table_name nomonitoring;

همچنین فعال سازی مجدد monitoring، با کمک دستور زیر امکان پذیر بود:

SQL>alter table table_name monitoring;

از اوراکل 10g، استفاده از این دستورات، عملا کاربردی ندارد و اصطلاحا deprecate شده است و برای اعمال سیاستی در این زمینه، باید از پارامتر statistics_level استفاده کرد. مقدار پیش فرض این پارامتر، به TYPICAL تنظیم شده است:

SQL> show parameter statistics_level

NAME                                 TYPE        VALUE

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

statistics_level                     string      TYPICAL

برای جلوگیری از جمع اوری امار و همچنین مانیتورینگ جداول، باید این پارامتر را به مقدار BASIC، تنظیم کرد:

SQL> alter system set statistics_level=basic;

ORA-32017: failure in updating SPFILE

ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled

همانطور که می بینید، برای تنظیم این پارامتر به مقدار basic، قبلا باید مدیریت خودکار sga را غیرفعال کرد:

SQL> alter system reset sga_target scope=spfile;

System altered

SQL> startup force;

SQL> alter system set statistics_level=basic;

System altered

با این تنظیمات، فیلد monitoring در dba_tables، برای همه جداول به مقدار no تغییر خواهد کرد:

SQL>select count(*),monitoring from  dba_tables group by monitoring;

2299  NO

پس با ایجاد تغییرات در جدول mytbl، تغییراتی در جدول $mon_mods_all ثبت نخواهد شد:

SQL> select p.inserts,p.updates,p.deletes from dba_tab_modifications p where p.table_name=’MYTBL’;

no rows selected

SQL>insert into usef.mytbl values(‘USEF’);

1 row inserted

SQL> begin dbms_stats.FLUSH_DATABASE_MONITORING_INFO(); end;

PL/SQL procedure successfully completed

SQL> select p.inserts,p.updates,p.deletes from dba_tab_modifications p where p.table_name=’MYTBL’;

no rows selected

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

SQL> select owner,object_name,status,last_ddl_time from dba_objects where object_name=’TABLE_NAME’;