oracle dbms_stat与analyze 获取有效的统计信息(2)

最新推荐文章于 2021-08-30 16:58:49 发布

cuiye1492

最新推荐文章于 2021-08-30 16:58:49 发布

阅读量228

点赞数

文章标签：数据库

#收集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;

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#;

可以发现like的没变化

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

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

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.

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#(+);