قبلا در مطلبی توضیح دادیم که اوراکل وضعیت بروز و یا stale بودن آمارهای جدول را بر اساس اطلاعات موجود در جدول $sys.mon_mods_all تعیین می کند(ویوی dba_tab_modifications شکل خواناتری را از این اطلاعات نمایش می دهد.) و بر اساس آن تصمیم می گیرد که آمار چه جداولی را باید بروزرسانی کند.

در این متن قصد داریم بررسی کنیم که اوراکل در زمان جمع آوری آمار، بر چه مبنایی به این نتیجه می رسد تا برای ستونی Histogram تهیه کند؟

اوراکل برای تصمیم گیری در این زمینه از جدول $col_usage استفاده می کند. این جدول برای هر ستونی که در قسمت where clause پرس و جوها استفاده شده، اطلاعاتی را ذخیره می کند(توسط بک گراند پروسس SMON). به عنوان مثال، با اجرای پرس و جوی زیر، اطلاعاتی از ستون color در جدول $col_usage ثبت خواهد شد:

SQL> select  count(*) from TBL_RANG where color=’Red’;

  COUNT(*)

———-

    578682

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed

SQL> select obj#,intcol#,equality_preds from sys.col_usage$ where obj# in(select obj# from sys.obj$ where name =’TBL_RANG’);

      OBJ#    INTCOL# EQUALITY_PREDS

———- ———- ————–

     81734          1              1

همچنین در صورت عدم وجود ستون در شرط کوئری، بدیهی است که اطلاعاتی از آن در جدول $col_usage ثبت نمی شود:

SQL> select  color from TBL_RANG;

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed

SQL> select obj#,intcol#,equality_preds from sys.col_usage$ where obj# in(select obj# from sys.obj$ where name =’TBL_RANG’);

no rows selected

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

SQL> select r.name              owner,

       o.name              table_name,

       c.name              column_name,

       u.equality_preds

  from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r

 where u.obj# = o.obj#

   and u.obj# = c.obj#

   and u.intcol# = c.col#

   and r.user# = o.owner#

   and r.name = ‘USEF’

   and o.name = ‘TBL_RANG’;

OWNER TABLE_NAME  COLUMN_NAME   EQUALITY_PREDS

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

USEF  TBL_RANG  COLOR             1

اگر در زمان اجرای مجدد دستور، شرطی که برای ستون color در نظر گرفته شده بود، تغییر کند، به مقدار EQUALITY_PREDS یک عدد اضافه خواهد شد:

SQL> select  count(*) from TBL_RANG where color=’Blue’;

  COUNT(*)

———-

         6

SQL>@col_usage

OWNER TABLE_NAME  COLUMN_NAME     EQUALITY_PREDS

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

USEF   TBL_RANG  COLOR                2

از طریق تابع REPORT_COL_USAGE از بسته dbms_stats  هم می توان این اطلاعات را بدست آورد:

SQL> select dbms_stats.report_col_usage(‘USEF’,’TBL_RANG’) from dual;

LEGEND:

…….

EQ         : Used in single table EQuality predicate

RANGE      : Used in single table RANGE predicate

LIKE       : Used in single table LIKE predicate

NULL       : Used in single table is (not) NULL predicate

EQ_JOIN    : Used in EQuality JOIN predicate

NONEQ_JOIN : Used in NON EQuality JOIN predicate

FILTER     : Used in single table FILTER predicate

JOIN       : Used in JOIN predicate

GROUP_BY   : Used in GROUP BY expression

#############################################

COLUMN USAGE REPORT FOR USEF.TBL_RANG

1. COLOR : EQ

###############################################

توجه: اطلاعات در بازه زمانی 15 دقیقه یکبار از حافظه به دیسک منتقل و در جدول $col_usage ثبت می شوند و برای ثبت فوری و دستی اطلاعات از حافظه به دیسک، از دستور زیر استفاده می شود:

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

 

بعد از آشنایی مختصر با عملکرد جدول $col_usage، مجددا به مسئله ای که در ابتدای متن مطرح شد بر می گردیم. همانطور که اشاره شد، اطلاعات جدول $col_usage، مبنای جمع آوری هیستوگرام برای ستونها هستند(البته به همراه شروط دیگری چون “عدم یکنواختی فراوانی مقادیر” و “تعداد محدود Distinct Value درستون”).

برای مثال، با توجه به شرایط فوق، در زمان جمع آوری امار، خواهیم دید که اوراکل صرفا برای ستون color هیستوگرام تهیه خواهد کرد چرا که از این ستون اطلاعاتی در جدول $col_usage موجود است البته همانطور که اشاره شد، وجود نام ستون در این جدول، تنها شرط لازم برای ایجاد هیستوگرام نخواهد بود و شرایط دیگری هم در این زمینه اثرگذار هستند:

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

METHOD_OPT

————————-

FOR ALL COLUMNS SIZE AUTO

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname =>’TBL_RANG’);

PL/SQL procedure successfully completed

SQL> select  owner,table_name,column_name,histogram from dba_tab_col_statistics p where p.table_name=’TBL_RANG’ and histogram!=’NONE’;

OWNER TABLE_NAME COLUMN_NAME     HISTOGRAM

—– ———- ————— —————

USEF TBL_RANG COLOR           FREQUENCY

توجه: از طریق پارامتر method_opt در پروسیجر gather_table_stats می توان در مورد کیفیت histogram اعمال نظر کرد. مقدار پیش فرض آن برابر با FOR ALL COLUMNS SIZE AUTO می باشد.

در صورتی که ستون دیگری از این جدول را به شرط پرس و جو اضافه کنیم، نتیجه تغییر خواهد کرد:

SQL> select  count(*) from TBL_RANG c where c.status=’VALID’;

  COUNT(*)

———-

    577392

SQL> select  count(*) from TBL_RANG c where c.status=’INVALID’;

  COUNT(*)

———-

      1296

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed

SQL>@col_usage

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname =>’TBL_RANG’);

PL/SQL procedure successfully completed

SQL> select  owner,table_name,column_name,histogram from dba_tab_col_statistics p where p.table_name=’TBL_RANG’ and histogram!=’NONE’;

OWNER TABLE_NAME  COLUMN_NAME     HISTOGRAM

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

usef   TBL_RANG COLOR           FREQUENCY

usef   TBL_RANG STATUS          FREQUENCY

اگر اطلاعات جدول TBL_RANG در $col_usage حذف شود، با بروزرسانی مجدد آمار، هیستوگرام تهیه شده از ستونهای فوق(color و status) هم از بین خواهد رفت:

–حذف اطلاعات موجود در جدول $col_usage از طریق تابع reset_col_usage:

SQL> exec dbms_stats.reset_col_usage(‘USEF’,’TBL_RANG’);

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname =>’TBL_RANG’);

PL/SQL procedure successfully completed

SQL> select  owner,table_name,column_name,histogram from dba_tab_col_statistics p where p.table_name=’TBL_RANG’ and histogram!=’NONE’;

no rows selected

در زمان بروزرسانی آمار باید در نظر داشت که اگر gather stats با Method Option پیش فرض(FOR ALL COLUMNS SIZE AUTO) انجام نشود، ممکن است به اطلاعات جدول $col_usage رجوع نشود که در این حالت ممکن است برای ستونهای ذکر شده هیستوگرامی ایجاد نشود. برای مثال، در حالت زیر، از دو ستون color و status که ایندکسی برای انها ایجاد نشده، هیستوگرامی هم ایجاد نمی شود:

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname =>’TBL_RANG’,method_opt => ‘for all indexed columns size 254’);

 

نکات پایانی:

1: برای غیرفعال کردن column tracking می توان پارامتر مخفی column_tracking_level_ را به مقدار 0 تنظیم کرد(مقدار پیش فرض این پارامتر برابر 1 می باشد.):

SQL> alter system set “_column_tracking_level”=0;

System altered

2: برای غیرفعال کردن جمع آوری histogram می توان از عبارت FOR ALL COLUMNS SIZE 1 استفاده کرد:

SQL> exec dbms_stats.set_global_prefs (‘METHOD_OPT’,’FOR ALL COLUMNS SIZE 1‘);

PL/SQL procedure successfully completed

3: جمع آوری histogram برای همه ستونهای جدول TBL_RANG از طریق عبارت FOR ALL COLUMNS  SIZE 254 قابل انجام است(که البته انجام چنین کاری توصیه نمی شود):

SQL> exec dbms_stats.Gather_table_stats(ownname => ‘USEF’, tabname => ‘TBL_RANG’,method_opt => ‘FOR ALL COLUMNS  SIZE 254‘);

PL/SQL procedure successfully completed

SQL> select  count(*) from dba_tab_col_statistics p where p.table_name=’TBL_RANG’ and histogram!=’NONE’;

  COUNT(*)

———-

        22

4: حذف هیستوگرام برای یک ستون با دستور زیر قابل انجام است:

SQL> exec dbms_stats.Delete_column_stats(ownname => ‘USEF’, tabname => ‘TBL_RANG’,colname => ‘COLOR’, col_stat_type => ‘HISTOGRAM’);

PL/SQL procedure successfully completed