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)是很有意义的。
对于不大的分区表，可以使用默认设置。
扫一扫
3003
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
