oracle dbms_stat與analyze 獲取有效的統計資訊(2)

#收集object資訊
dbms_stats基本收集
1.gather_database_stats:收集database的所有物件統計資訊
2.gahter_dictionary_stats:收集資料字典統計資訊,
3.gather_fixed_objects_stats:收集資料字典中固定表統計資訊（x$表，v$fixed_table可查）
4.gather_schema_stats:收集指定schema下的所有物件統計資訊
5.gather_table_stats:收集指定表的統計資訊（9i預設不收集index,10g預設oracle自己決定)
6.gahter_index_stats:收集指定index的統計資訊

輸入時候引數基本的有：
ownname物件的owner,indname,tabname,partname（不指定這個若分割槽表預設收集所有分割槽,default null）
comp_id元件id預設null，處理所有元件
#獲得元件id

 SELECT u.name AS schema_name, r.cid AS comp_id, r.cname AS comp_name
 FROM sys.user$ u,
 (SELECT schema#, cid, cname
 FROM sys.registry$
 WHERE status IN (1,3,5)
 AND namespace = 'SERVER'
 UNION ALL
 SELECT s.schema#, s.cid, cname
 FROM sys.registry$ r, sys.registry$schemas s
 WHERE r.status IN (1,3,5)
 AND r.namespace = 'SERVER'
 AND r.cid = s.cid) r
 WHERE u.user# = r.schema#

7.granularity:指定需要處理的分割槽物件統計級別,取值：all所有物件分割槽與子分割槽（10g），auto收集物件和分割槽統計資訊(子分割槽是range,list的時候才收集),
default(10g廢棄，收集物件和分割槽資訊)，global(只收集物件)，global and paratition(收集物件和分割槽，10g default),paratition(只收集分割槽)，subparatition（只收集子分割槽）

8.****cascade 是否收集 index統計資訊，true,false(9i default)，dbms_stats.auto_cascade(null 10g default oracle決定是否收集index統計資訊)
9.gather_sys 是否收集sys統計資訊，預設false
10.gather_temp：是否收集temp表統計資訊 dbms_stats算個ddl有個隱commit（執行前commit-執行-執行後commit）,所以只能對on commit preserve rows的temp表才有作用
11.option處理方式，選項比較多gathr（處理所有物件），gather auto（自動決定要處理哪些物件，如何處理,忽略除去ownname,objlist,stattab,statid,statown外所有引數）
,gather stale(只收集統計資訊失效物件，如果物件上無統計資訊不算失效) ,gather empty(收集沒有統計資訊的物件),list auto(列出gather auto處理的物件)，list stale(列出gather stale處理的物件)
,list empty(列出gather empty所處理的物件)

12.force 是否覆蓋lock的物件統計資訊(R2)

13.objlist 根據option取值返回被處理物件的列表
14.stattab：備份表名字 ,default null
15.statid:識別符號(主要就是識別stattab中多次統計資訊)
16.statown:stattab的owner
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> set serveroutput on
SQL>  DECLARE
  2   l_objlist dbms_stats.objecttab;
  3   l_index PLS_INTEGER;
  4   BEGIN
  5   dbms_stats.gather_schema_stats(ownname => 'XH',
 objlist => l_objlist);
  6    7   l_index := l_objlist.FIRST;
  8   WHILE l_index IS NOT NULL
  9   LOOP
 10   dbms_output.put(l_objlist(l_index).ownname || '.');
 11   dbms_output.put_line(l_objlist(l_index).objname);
 12   l_index := l_objlist.next(l_index);
 13   END LOOP;
 14   END;
 15  /

SQL> /      
XH.ST
XH.ST2
XH.T1
XH.T3
XH.T4
XH.T5
XH.TT
XH.T_DUAL

PL/SQL procedure successfully completed.

 

 

 

 

 

 

11G收集達到特定條件的 物件統計資訊

 

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> conn xh/a831115;
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
XHT
XHT2
T1
T2
T3

 

SQL>   DECLARE
  2      l_objlist dbms_stats.objecttab;
  3      l_index PLS_INTEGER;
  4        l_filter dbms_stats.objecttab := dbms_stats.objecttab();
  5       BEGIN
  6  l_filter.extend(1);
  7  l_filter(1).ownname := 'XH';
  8  l_filter(1).objname := 'T%';
  9  dbms_stats.gather_database_stats(obj_filter_list => l_filter,
 10  ptions => 'gather',objlist=> l_objlist);
 11        l_index := l_objlist.FIRST;
 12       WHILE l_index IS NOT NULL
 13       LOOP
 14      dbms_output.put(l_objlist(l_index).ownname || '.');
 15      dbms_output.put_line(l_objlist(l_index).objname);
 16      l_index := l_objlist.next(l_index);
 17      END LOOP;
 18      END;
 19  /
XH.T1
XH.T2
XH.T3

PL/SQL procedure successfully completed.

 

*obj_filter_list:至少在物件符合一個傳遞過來的引數條件才收集(基於11g dbms_stat.objecttab型別)

 

 

 

*estimate_percent是否取樣收集統計資訊(0.000001-100  %，100=null既不使用取樣 ) 9i default為null表示不用取樣，10g為dbms_stats.auto_sample_size oracle自動決定取樣大小（相當於0）

如果想取樣的話一般表建議10%,大表建議(0.1-0.5%)
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> execute dbms_stats.gather_schema_stats('XH',estimate_percent=>0.5);

PL/SQL procedure successfully completed.

 

SQL> select table_name,sample_size,num_rows ,round(sample_size/num_rows*100,1) as "%" from user_tables;

TABLE_NAME                     SAMPLE_SIZE   NUM_ROWS          %
------------------------------ ----------- ---------- ----------
T5                                  205179   41035800         .5
ST                                       2          2        100
ST2                                   4854      50287        9.7
T1                                    5506      10131       54.3
T4                                       1          1        100
T_DUAL                                   1          1        100
TT                                      24         24        100
T3                                    1001       1001        100

8 rows selected.

oracle自己進行了調整 取樣百分比，所以在對db or schema進行取樣的時候選擇對 大表適合的取樣率 oracle 會調整 對小表的取樣（根據cost），所以取大表的取樣率比較好

 

*block_sample:是否採用資料行或資料塊取樣來收集統計資訊（行精確，塊快）建議資料分佈時採用塊，預設false表示不用塊

*degree:slave process數量,可以並行收集,如果表上定義了 並行度 ,這個可以為NULL(default) 這樣就按表上並行度收並行收集(算是並行DDL)
 dbms_stats.default_degree表示 oracle自己決定並行度(另外 一次處理多個物件時候 是順序執行的,所以只在大物件統計資訊時候才用並行,若要並行收集多個只能並行手動執行)

*method_opt null(只收集col上的統計資訊，不收集histogram資訊)
語法：for size
for all columns size 100，使用100個bucket收集每一個列的histogram
for colunms size 100 col1,col2 col3 size 1 ，col1列用100個bucket，2，3，4 用1個bucket（1 其實就是不建立histogram）

9i default for all columns size 1（1 就是不建立 histogram）
10g default for all columns size auto（這表示10g在某些情況下會收集histogram，由此會有一些問題cursor_sharing=simiary unsave bind的問題 管理過的一個4T左右的生產庫中出現過這個嚴重問題）

*建議size為size skewonly or size auto
size auto:只收集非均勻分佈的列的histogram，where 中引用的列也收集，根據col_usage$決定收集哪個列，系統自定buckets

size 取值（1-254 1表示不建立histogram）
size skewonly 只收集非均勻分佈列的histogram，系統自定bucket

 

 

oracle 根據下面資訊決定是否收集列上histogram資訊

10g

query optimizer 生成執行計劃時，會檢視where子句中用到的列的使用情況
col_usage$(由smon 來執行維護，資訊來自  shared pool 跟*_tab_modifications一樣(統計資訊時效性,smon維護))
This table allows to monitor the usage of predicates on columns in select statements. It is updated (if _column_tracking_level is set to 1) at intervalls by smon, so it might be a little out of date. Also, dbms_stats will make use of that info when deciding if it needs to create a histogram on a column

create table col_usage$
(
  obj#              number,                                 /* object number */
  intcol#           number,                        /* internal column number */
  equality_preds    number,                           /* equality predicates */
  equijoin_preds    number,                           /* equijoin predicates */
  nonequijoin_preds number,                        /* nonequijoin predicates */
  range_preds       number,                              /* range predicates */
  like_preds        number,                         /* (not) like predicates */
  null_preds        number,                         /* (not) null predicates */
  timestamp         date      /* timestamp of last time this row was change

 

指令碼來自trouble shooting oracle
SQL> col name for a10
SQL> SELECT c.name, cu.timestamp,
  2         cu.equality_preds AS equality, cu.equijoin_preds AS equijoin,
  3         cu.nonequijoin_preds AS noneequijoin, cu.range_preds AS range,
  4         cu.like_preds AS "LIKE", cu.null_preds AS "NULL"
  5  FROM sys.col$ c, sys.col_usage$ cu, sys.obj$ o, sys.user$ u
  6  WHERE c.obj# = cu.obj# (+)
  7  AND c.intcol# = cu.intcol# (+)
  8  AND c.obj# = o.obj#
  9  AND o.owner# = u.user#
 10  AND o.name = 'T1'
 11  AND u.name ='XH'
 12  ORDER BY c.col#;

NAME       TIMESTAMP   EQUALITY   EQUIJOIN NONEEQUIJOIN      RANGE       LIKE
---------- --------- ---------- ---------- ------------ ---------- ----------
      NULL
----------
A          05-JAN-10          2          0            0          2          0
         0

SQL> select count(*) from t1 where a like '%1%';

  COUNT(*)
----------
      3440

SQL> execute dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL>  col name for a10
SQL>  SELECT c.name, cu.timestamp,
  2             cu.equality_preds AS equality, cu.equijoin_preds AS equijoin,
  3             cu.nonequijoin_preds AS noneequijoin, cu.range_preds AS range,
  4             cu.like_preds AS "LIKE", cu.null_preds AS "NULL"
  5      FROM sys.col$ c, sys.col_usage$ cu, sys.obj$ o, sys.user$ u
  6      WHERE c.obj# = cu.obj# (+)
  7      AND c.intcol# = cu.intcol# (+)
  8      AND c.obj# = o.obj#
  9      AND o.owner# = u.user#
 10     AND o.name = 'T1'
 11     AND u.name ='XH'
 12    ORDER BY c.col#;

NAME       TIMESTAMP   EQUALITY   EQUIJOIN NONEEQUIJOIN      RANGE       LIKE
---------- --------- ---------- ---------- ------------ ---------- ----------
      NULL
----------
A          05-JAN-10          2          0            0          2          0
         0

SQL>  col name for a10
SQL>  SELECT c.name, cu.timestamp,
  2             cu.equality_preds AS equality, cu.equijoin_preds AS equijoin,
  3             cu.nonequijoin_preds AS noneequijoin, cu.range_preds AS range,
  4             cu.like_preds AS "LIKE", cu.null_preds AS "NULL"
  5      FROM sys.col$ c, sys.col_usage$ cu, sys.obj$ o, sys.user$ u
  6      WHERE c.obj# = cu.obj# (+)
  7      AND c.intcol# = cu.intcol# (+)
  8      AND c.obj# = o.obj#
  9      AND o.owner# = u.user#
 10     AND o.name = 'T1'
 11     AND u.name ='XH'
 12    ORDER BY c.col#;

NAME       TIMESTAMP   EQUALITY   EQUIJOIN NONEEQUIJOIN      RANGE       LIKE
---------- --------- ---------- ---------- ------------ ---------- ----------
      NULL
----------
A          05-JAN-10          2          0            0          2          0
         0

可以發現like的沒變化

SQL> select count(*) from t1 where a>9999;

  COUNT(*)
----------
         1

SQL>  col name for a10
SQL>  SELECT c.name, cu.timestamp,
  2             cu.equality_preds AS equality, cu.equijoin_preds AS equijoin,
  3             cu.nonequijoin_preds AS noneequijoin, cu.range_preds AS range,
  4             cu.like_preds AS "LIKE", cu.null_preds AS "NULL"
  5      FROM sys.col$ c, sys.col_usage$ cu, sys.obj$ o, sys.user$ u
  6      WHERE c.obj# = cu.obj# (+)
  7      AND c.intcol# = cu.intcol# (+)
  8      AND c.obj# = o.obj#
  9      AND o.owner# = u.user#
 10     AND o.name = 'T1'
 11     AND u.name ='XH'
 12    ORDER BY c.col#;

NAME       TIMESTAMP   EQUALITY   EQUIJOIN NONEEQUIJOIN      RANGE       LIKE
---------- --------- ---------- ---------- ------------ ---------- ----------
      NULL
----------
A          05-JAN-10          2          0            0          2          0
         0

SQL> execute dbms_stats.flush_database_monitoring_info;
oracle自己決定重新整理時間（根據負載壓力效能原因，很可能幾小時後才重新整理），也可以手動執行
These views are populated only for tables with the MONITORING attribute. They are intended for statistics collection over a long period of time. For performance reasons, Oracle may not populate these views until a few hours after the actual modifications occurred.

PL/SQL procedure successfully completed.

SQL>  col name for a10
SQL>  SELECT c.name, cu.timestamp,
  2             cu.equality_preds AS equality, cu.equijoin_preds AS equijoin,
  3             cu.nonequijoin_preds AS noneequijoin, cu.range_preds AS range,
  4             cu.like_preds AS "LIKE", cu.null_preds AS "NULL"
  5      FROM sys.col$ c, sys.col_usage$ cu, sys.obj$ o, sys.user$ u
  6      WHERE c.obj# = cu.obj# (+)
  7      AND c.intcol# = cu.intcol# (+)
  8      AND c.obj# = o.obj#
  9      AND o.owner# = u.user#
 10     AND o.name = 'T1'
 11     AND u.name ='XH'
 12    ORDER BY c.col#;

NAME       TIMESTAMP   EQUALITY   EQUIJOIN NONEEQUIJOIN      RANGE       LIKE
---------- --------- ---------- ---------- ------------ ---------- ----------
      NULL
----------
A          06-JAN-10          2          0            0          3          0
         0

使用這些資訊 決定是否收集列上histogram

......like的沒被記錄,range的記錄加了
#查詢列 在謂詞中使用情況
SELECT /*+  RULE  */
       c.NAME col_name, c.type# col_type, c.CHARSETFORM. col_csf,
       c.default$ col_def, c.null$ col_null, c.property col_prop,
       c.col# col_unum, c.intcol# col_inum, c.obj# col_obj, c.scale col_scale,
       h.bucket_cnt h_bcnt, h.distcnt h_pndv, c.LENGTH col_len,
       cu.TIMESTAMP cu_time, cu.equality_preds cu_ep,
       cu.equijoin_preds cu_ejp, cu.range_preds cu_rp, cu.like_preds cu_lp
  FROM SYS.user$ u,
       SYS.obj$ o,
       SYS.col$ c,
       SYS.col_usage$ cu,
       SYS.hist_head$ h
 WHERE LOWER(u.NAME) = '&owner'
   AND o.owner# = u.user#
   AND o.type# = 2
   AND LOWER(o.NAME)= '&objectname'
   AND o.obj# = c.obj#
   AND c.obj# = cu.obj#(+)
   AND c.intcol# = cu.intcol#(+)
   AND c.obj# = h.obj#(+)
   AND c.intcol# = h.intcol#(+);

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-664239/，如需轉載，請註明出處，否則將追究法律責任。