Oracle GATHER_TABLE_STATS的使用

DBMS_STATS包问世以后，ORACLE专家可通过一种简单的方式来为CBO收集统计数据。目前，已经不再推荐你使用老式的分析表和
DBMS_UTILITY方法来生成CBO统计数据。那些古老的方式甚至有可能危及SQL的性能，
因为它们并非总是能够捕捉到有关表和索引的高质量信息。CBO使用对象统计，为所有SQL语句选择最佳的执行计划。
DBMS_STATS能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。

GATHER_TABLE_STATS语法：

DBMS_STATS.GATHER_TABLE_STATS
(
   OWNNAME VARCHAR2,
   TABNAME VARCHAR2,
   PARTNAME VARCHAR2 DEFAULT NULL,
   ESTIMATE_PERCENT NUMBER DEFAULT TO_ESTIMATE_PERCENT_TYPE
                                                (GET_PARAM('ESTIMATE_PERCENT')),
   BLOCK_SAMPLE BOOLEAN DEFAULT FALSE,
   METHOD_OPT VARCHAR2 DEFAULT GET_PARAM('METHOD_OPT'),
   DEGREE NUMBER DEFAULT TO_DEGREE_TYPE(GET_PARAM('DEGREE')),
   GRANULARITY VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   CASCADE BOOLEAN DEFAULT TO_CASCADE_TYPE(GET_PARAM('CASCADE')),
   STATTAB VARCHAR2 DEFAULT NULL,
   STATID VARCHAR2 DEFAULT NULL,
   STATOWN VARCHAR2 DEFAULT NULL,
   NO_INVALIDATE BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE (
                                     GET_PARAM('NO_INVALIDATE')),
   FORCE BOOLEAN DEFAULT FALSE
);



参数说明：

OWNNAME: 要分析表的拥有者。
TABNAME: 要分析的表名。
PARTNAME: 分区的名字,只对分区表或分区索引有用。

ESTIMATE_PERCENT: 采样行的百分比,取值范围[0.000001,100]，使用常DBMS_STATS.AUTO_SAMPLE_SIZE让ORACLE决定适合的采样大小，
                  这也是默认值，可以使用DBMS_STATS.SET_PARAM进行修改默认值。NULL可以让ORACLE采样全部数据

BLOCK_SAMPLE: 是否采用随即块采样代替行随即行采样。

METHOD_OPT: 决定HISTOGRAMS信息是怎样被统计的.METHOD_OPT的取值如下:
            FOR ALL COLUMNS:统计所有列的HISTOGRAMS.
            FOR ALL INDEXED COLUMNS:统计所有INDEXED列的HISTOGRAMS.
            FOR ALL HIDDEN COLUMNS:统计你看不到列的HISTOGRAMS
            FOR COLUMNS <LIST> SIZE <INTEGER> | REPEAT | AUTO |
            INTEGER指的直方图的BUCKETS数量，取值范围为[1,254]。
            REPEAT上次统计过的HISTOGRAMS。
            AUTO:ORACLE根据列数据的分布及相关列的访问量来决定收集直方图的列。
            SKEWONLY:ORACLE 根据列的数据分布来决定哪些列收集直方图
            。
DEGREE: 并行度

GRANULARITY: 收集统计信息的粒度。(只应用于分区表),值包括:
             'ALL':搜集(SUBPARTITION,PARTITION,AND GLOBAL)统计信息。
             'AUTO':基于分区的类型来决定粒度，默认值。
             'DEFAULT':收集GLOBAL和PARTITION LEVEL的统计信息，等同与'GLOBAL AND PARTITION'。
             'GLOBAL':收集全局统计信息
             'GLOBAL AND PARTITION':收集GLOBAL和PARTITION LEVEL统计信息。
             'GPARTITION':收集PARTITION-LEVEL的统计信息。
             'SUBPARTITION':收集SUBPARTITION-LEVEL统计信息
             。
CASCADE: 收集索引的统计信息。是收集索引的信息.默认为FALSE.

STATTAB : 设置存储统计信息的表
STATID : 表的ID
STATOWN ：表的OWNER

NO_INVALIDATE: 若是设置为TRUE,则Oracle不会立马使 dependent Cursors失效,若设置为FALSE,Oracle则会立马使dependent Cursor失效.

FORCE: 即使锁住也要收集表的统计信息。



--关于GRANULARITY参数的一点说明

--默认分区和全局均会收集统计信息


create table t (id int,name varchar2(1000))
partition by range(id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition pmax values less than (maxvalue)
);

exec dbms_stats.gather_table_stats('ADMIN','T');


drop table t purge;


--global_stats含义

For partitioned tables, indicates whether statistics were collected for the table
as a whole (YES) or were estimated from statistics on underlying partitions and subpartitions (NO)

admin@ORCL> select num_rows,blocks,global_stats from user_tables where table_name = 'T';

  NUM_ROWS BLOCKS GLO
---------- ---------- ---

         0 0 YES


admin@ORCL> select partition_name,num_rows,blocks,global_stats from user_tab_partitions;

PARTITION_NAME NUM_ROWS BLOCKS GLO
------------------------------ ---------- ---------- ---

P1 0 0 YES
P2 0 0 YES
P3 0 0 YES
PMAX 0 0 YES

insert into t select object_id,object_name from dba_objects
commit;

--只对partition层面做分析，根据以下的信息看出表级别的统计信息缺失的

exec dbms_stats.gather_table_stats('ADMIN','T',granularity => 'partition');


admin@ORCL> select num_rows,blocks,global_stats from user_tables where table_name = 'T';

  NUM_ROWS BLOCKS GLO
---------- ---------- ---

         0 0 YES

admin@ORCL> select partition_name,num_rows,blocks,global_stats from user_tab_partitions;

PARTITION_NAME NUM_ROWS BLOCKS GLO
------------------------------ ---------- ---------- ---

P1 9708 43 YES
P2 4557 20 YES
P3 0 0 YES
PMAX 1 5 YES


admin@ORCL> select count(*) from T partition(P2);

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

      4557

--单个分区内的信息统计是准确的。

admin@ORCL> select count(*) from T where ID < 10000;


Execution Plan
----------------------------------------------------------

Plan hash value: 2993254470

------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 4 | 13 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 4 | | | | |
| 2 | PARTITION RANGE SINGLE| | 9708 | 38832 | 13 (0)| 00:00:01 | 1 | 1 |
| 3 | TABLE ACCESS FULL | T | 9708 | 38832 | 13 (0)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------------------------------------

--使用全局分区时，因为统计信息缺失，所以ROWS为1.


admin@ORCL> select count(*) from T where ID < 30000;


Execution Plan
----------------------------------------------------------

Plan hash value: 2010034966

--------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 19 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 1 | 13 | 19 (0)| 00:00:01 | 1 | 3 |
| 3 | TABLE ACCESS FULL | T | 1 | 13 | 19 (0)| 00:00:01 | 1 | 3 |
--------------------------------------------------------------------------------------------------


--跨分区的信息是不准确的。所以在收集统计信息时要注意同时收集全局和分区的统计信息

admin@ORCL> select count(*) from T where ID < 10001;


Execution Plan
----------------------------------------------------------

Plan hash value: 2010034966

--------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 19 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 1 | 13 | 19 (0)| 00:00:01 | 1 | 2 |
|* 3 | TABLE ACCESS FULL | T | 1 | 13 | 19 (0)| 00:00:01 | 1 | 2 |
--------------------------------------------------------------------------------------------------



在11g时，当表上已经有全局统计信息时，单独对分区分析，不会更新全局信息。
当表上没有全局统计信息时，分析单独分区时，Oracle会自动把统计信息累积到全局之上。


Oracle 的cardinality是不会为0的，至少是为1.

删除统计信息
cascade_parts => false指不删除子分区的统计信息
exec dbms_stats.delete_table_stats(user,'T',cascade_parts => false);

--删除之后统计信息就没有了。

admin@ORCL> select num_rows,blocks,global_stats from user_tables where table_name = 'T';

  NUM_ROWS BLOCKS GLO
---------- ---------- ---

                      NO

--收集分区之上的统计信息，发现统计信息会合并到全局。也就不会出现上面的那种情况。

admin@ORCL> exec dbms_stats.gather_table_stats('ADMIN','T',granularity => 'partition');

PL/SQL procedure successfully completed.

admin@ORCL> select num_rows,blocks,global_stats from user_tables where table_name = 'T';

  NUM_ROWS BLOCKS GLO
---------- ---------- ---

     14266 68 NO

admin@ORCL> select count(*) from t;

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

     14266


全局和全局信息
在Oracle 11g之后，有个增量统计信息的收集，在以前需要收集整个表，N个分区的统计信息数据，
才能够得到准确的全局统计信息，而Oracle 11g之后，增量收集分区信息来更新全局信息。
此方式适合特大的分区表进行收集统计信息。

--告诉Oracle已增量的方式收集统计信息


exec dbms_stats.set_table_prefs(user,'t','INCREMENTAL','TRUE');

--Oracle 会以增量的方式收集统计信息，并更新全局信息。


admin@ORCL> exec dbms_stats.gather_table_stats('ADMIN','T');

PL/SQL procedure successfully completed.

admin@ORCL> select num_rows,blocks,global_stats from user_tables where table_name = 'T';

  NUM_ROWS BLOCKS GLO
---------- ---------- ---

     14266 68 YES



分区和全局信息

如何设置这个参数
在一个很大的分区表(OLAP)，全局分析代价是非常昂贵的。
OLAP系统下，除了新加入的数据外，旧的数据基本上是没有变化的，全局分析很浪费资源。
对于很大分区表，将granulariy设置为partition(Oracle 10g)或者incremental(oracle 11g)是很有意义的。
对于不大的分区表，可以使用默认设置。