Oracle收集统计信息
2022-04-01 19:53阅读：
Oracle数据库里的统计信息是这样的一组数据：它存储在数据字典里，且从多个维度描述了Oracle数据库里对象的详细信息。CBO会利用这些统计信息来计算目标SQL各种可能的、不同的执行路径的成本，并从中选择一条成本值最小的执行路径来作为目标SQL的执行计划。
Oracle数据库里的统计信息可以分为如下6种类型：
表的统计信息
索引的统计信息
列的统计信息
系统统计信息
数据字典统计信息
内部对象统计信息
表的统计信息用于描述Oracle数据库里表的详细信息，它包含了一些典型的维度，如记录数、表块(表里的数据块)数量、平均行长度等。
索引的统计信息于描述Oracle数据库里索引的详细信息，它包含了一些典型的维度，如索引的层级、叶子块的数量、聚簇因子等。
列的统计信息于描述Oracle数据库里列的详细信息，它包含了一些典型的维度，如列的distinct值的数量、列的NULL值的数量、列的最小值、列的最大值以及直方图等。
系统统计信息于描述Oracle数据库所在的数据库服务器的系统处理能力，它包含了CPU和I/O这两个维度，借助于系统统计信息，Oracle可以更清楚地知道目标数据库服务器的实际处理能力。
数据字典统计信息用于热核Oracle数据库里数据字典基表(如TAB$、IND$等)、数据字典基表上的索引，以及这些数据字典的列的详细信息，描述上述数据字典基表的统计信息与描述普通表、索引、列的统计信息没有本质区别。
内部对象统计信息用于描述Oracle数据库里的一些内部表(如X$系列表)的详细信息，它的维度和普通表的统计信息的维度类似，只不过其表块的数量为0，因为X$系统表实际上只是Oracle自定义的内存结构，并不占用实际的物理存储空间。
1、收集统计信息
在Oracle数据库里，通常有两种方法可以用来收集统计信息：一种是使用ANALYZE命令；另一种是使用DBMS_STATS包。表、索引、列的统计信息和数据字典统计信息用ANALYZE命令或者DBMS_STATS包收集均可，但系统统计信息和系统内部对象统计信息只能使用DBMS_STATS包来收集。
对系统内部表若使用ANALYZE命令来
收集统计信息，会报错ORA-02030
1.1 用ANALYZE命令收集统计信息
从Oracle7开始，ANALYZE命令就可以用来收集表、索引、列的统计信息，以及系统统计信息。
analyze table t2 compute statistics for table;
analyze table t2 compute statistics for columns
object_name,object_id;
analyze index idx_t2 compute statistics;
analyze table t2 delete statistics;
如果想一次性以计算模式收集表T2、表T2的所有列和表T2上的所有索引的统计信息，执行如下的语句就可以了：
analyze table t2 compute statistics;
1.2 用DBMS_STATS包收集统计信息
从Oracle
8.1.5开始，DBMS_STATS包被广泛用于统计信息的收集，用DMBS_STATS包收集统计信息也是Oracle官方推荐的方式。在收集CBO所需
要的统计信息方面，可以简单的将DBMS_STATS包理解成是ANALYZE命令的增加版。
DBMS_STATS包里最常用的就是如下4个存储过程：
GATHER_TABLE_STATS：用于收集目标表、目标表的列和目标表上的索引的统计信息。
GATHER_INDEX_STATS：用于收集指定索引的统计信息。
GATHER_SCHEMA_STATS：用于收集指定schema下所有对象的统计信息。
GATHER_DATABASE_STATS：用于收集全库所有对象的统计信息。
作用:DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息.
DBMS_STATS.GATHER_TABLE_STATS的语法如下:
DBMS_STATS.GATHER_TABLE_STATS ( ownname
VARCHAR2, tabname
VARCHAR2,
partname VARCHAR2,
estimate_percent NUMBER, block_sample
BOOLEAN, method_opt
VARCHAR2, degree
NUMBER, granularity
VARCHAR2, cascade
BOOLEAN, stattab
VARCHAR2, statid
VARCHAR2, statown
VARCHAR2, no_invalidate
BOOLEAN, force
BOOLEAN);
参数说明:
ownname:要分析表的拥有者
tabname:要分析的表名.
partname:分区的名字,只对分区表或分区索引有用.
estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样.
常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.
block_sapmple:是否用块采样代替行采样.
method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:
for all
columns:统计所有列的histograms.
for all indexed
columns:统计所有indexed列的histograms.
for all hidden
columns:统计你看不到列的histograms
for columns SIZE | REPEAT | AUTO |
SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的
histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the
same value which is what we define by 'there is skew in the
data
degree:决定并行度.默认值为null.
granularity:Granularity of statistics to collect ,only pertinent if
the table is partitioned.
cascace:是收集索引的信息.默认为false.
stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.
no_invalidate: Does not invalidate the dependent cursors if set to
TRUE. The procedure invalidates the dependent cursors immediately
if set to FALSE.
force:即使表锁住了也收集统计信息.
eg:
exec
dbms_stats.gather_index_stats(ownname=>'ZX',indname=>'IDX_T2',estimate_percent=>100);
exec
dbms_stats.delete_table_stats(ownname=>'ZX',tabname=>'T2');
如果想一次性以计算模式收集表T2、表T2的所有列及表T2的所有索引的统计信息，执行如下语句就可以了
exec
dbms_stats.gahter_table_stats(ownname=>'SCOTT',tabname=>'T1',cascade=>true,estimate_percent=>100,degree=>4);
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent =>
15);
EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent =>
15);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES',
estimate_percent => 15);
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK',
estimate_percent => 15);
SELECT s.table_name,s.num_rows,s.last_analyzed FROM dba_tables s
where s.owner='用户' and s.table_name='表名' ;
exec DBMS_STATS.gather_table_stats('用户', '表名',
cascade=>TRUE);
如何使用dbms_stats分析统计信息？
--创建统计信息历史保留表
sql> exec dbms_stats.create_stat_table(ownname =>
'scott',stattab => 'stat_table') ;
--导出整个scheme的统计信息
sql> exec dbms_stats.export_schema_stats(ownname =>
'scott',stattab => 'stat_table') ;
--如果发现执行计划走错，删除表的统计信息
SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname
=> 'work_list') ;
--导入表的历史统计信息
sql> exec dbms_stats.import_table_stats(ownname =>
'scott',tabname => 'work_list',stattab => 'stat_table')
;
--如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息
sql> exec dbms_stats.import_schema_stats(ownname =>
'scott',stattab => 'stat_table');
--导入索引的统计信息
SQL> exec dbms_stats.import_index_stats(ownname =>
'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab
=>'stat_table')
--检查是否导入成功
SQL> select table_name,num_rows,a.blocks,a.last_analyzed from
all_tables a where a.table_name='WORK_LIST';
分析数据库（包括所有的用户对象和系统对象）：gather_database_stats
分析用户所有的对象（包括表、索引、簇）：gather_schema_stats
分析表：gather_table_stats
分析索引：gather_index_stats
删除数据库统计信息：delete_database_stats
删除用户方案统计信息：delete_schema_stats
删除表统计信息：delete_table_stats
删除索引统计信息：delete_index_stats
删除列统计信息：delete_column_stats
设置表统计信息：set_table_stats
设置索引统计信息：set_index_stats
设置列统计信息：set_column_stats
可以查看表 DBA_TABLES来查看表是否与被分析过，如：
SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES
1.3 ANALYZE和DBMS_STATS的区别
从上面的演示中可以看出ANALYZE命令和DBMS_STATS包都可以用来收集表、索引和列的统计信息，看起来它们在收集统计信息方面的效果是一模一样的，为什么Oracle会推荐使用DBMS_STATS包来收集统计信息呢？
因为ANALYZE命令和DMBS_STATS包相比，存在如下缺陷：
ANALYZE命令不能正确地收集分区表的统计信息，而DBMS_STATS包却可以。ANALYZE命令只会收集最低层次对象的统计信息，然后推导和汇总出高一级的统计信息，比如对于有子分区的分区表而言，它只会先收集子分区统计信息，然后再汇总，推导出分区或表级的统计信息。有的统计信息是可以从当前对象的下一级对象进行汇总后得到的，比如表的总行数，可以由各分区的行数相加得到。但有的统计信息则不能从下一级对象得到，比如列上的distinct值数量NUM_DISTINCT以及DESNSITY等。
ANALYZE命令不能并行收集统计信息，而DBMS_STATS包却可以。并行收集统计信息对数据量很大的表表而言，是非常有用的特性。对于数据量很大的表，如果不能并行收集统计信息，则意味着如果想精确地收集目标对象的统计信息，那么耗费的时间可能会非常长，这有可能是不能接受的。在Oracle数据库里，DBMS_STATS包收集统计信息可以并行执行，这在一定程度上缓解了对大表的统计信息收集过长所带来的一系列问题。
当然，DBMS_STATS包也不是完美的，它与ANALYZE命令相比，其缺陷在于DBMS_STATS包只能收集与CBO相关的统计信息，而与CBO无关的一些额外信息，比如行迁移/行链接的数量(CHAIN_CNT)、校验表和索引的结构信息等，DBMS_STATS包就无能为力了。而ANALYZE命令可以用来分析和收集上述额外的信息，比如analyze
table xxx list chained rows intoyyy
可以用来分析和收集行迁移/行链接的数量，analyzeindex xxx validate
structure可以用来分析索引的结构。
https://blog.51cto.com/hbxztc/1899646
自从Oracle8.1.5引入dbms_stats包，Experts们便推荐使用dbms_stats取代analyze。
理由如下
1、dbms_stats可以并行分析
2、dbms_stats有自动分析的功能(alter table monitor )
3、analyze 分析统计信息的不准确some times
4、analyze在分析Partition表的时候，有时候会计算出不准确的Global
statistics .
原因是，dbms_stats会实在的去分析表全局统计信息（当指定参数）；而analyze是将表分区（局部）的statistics
汇总计算成表全局statistics ,可能导致误差。
1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。
a) 可以并行进行，对多个用户，多个Table
b) 可以得到整个分区表的数据和单个分区的数据。
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
d) 可以倒出统计信息
e) 可以用户自动收集统计信息
2、DBMS_STATS的缺点
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
***************************************************************
--SOSI.txt（快速便捷显示统计信息的脚本）
set echo off
set scan on
set lines 200
set pages 66
set verify off
set feedback off
set termout off
column uservar new_value Table_Owner noprint
select user uservar from dual;
set termout on
column TABLE_NAME heading 'Tables owned by &Table_Owner' format
a30
select table_name from dba_tables where
owner=upper('&Table_Owner') order by 1
/
undefine table_name
undefine owner
prompt
accept owner prompt 'Please enter Name of Table Owner (Null =
&Table_Owner): '
accept table_name prompt 'Please enter Table Name to show
Statistics for: '
column TABLE_NAME heading 'Table|Name' format a15
column PARTITION_NAME heading 'Partition|Name' format a15
column SUBPARTITION_NAME heading 'SubPartition|Name' format
a15
column NUM_ROWS heading 'Number|of Rows' format
9,999,999,999,990
column BLOCKS heading 'Blocks' format 999,990
column EMPTY_BLOCKS heading 'Empty|Blocks' format 999,999,990
column AVG_SPACE heading 'Average|Space' format 9,990
column CHAIN_CNT heading 'Chain|Count' format 999,990
column AVG_ROW_LEN heading 'Average|Row Len' format 990
column COLUMN_NAME heading 'Column|Name' format a25
column NULLABLE heading Null|able format a4
column NUM_DISTINCT heading 'Distinct|Values' format
999,999,990
column NUM_NULLS heading 'Number|Nulls' format 9,999,990
column NUM_BUCKETS heading 'Number|Buckets' format 990
column DENSITY heading 'Density' format 990
column INDEX_NAME heading 'Index|Name' format a15
column UNIQUENESS heading 'Unique' format a9
column BLEV heading 'B|Tree|Level' format 90
column LEAF_BLOCKS heading 'Leaf|Blks' format 999,999,990
column DISTINCT_KEYS heading 'Distinct|Keys' format
9,999,999,990
column AVG_LEAF_BLOCKS_PER_KEY heading 'Average|Leaf Blocks|Per
Key' format 99,990
column AVG_DATA_BLOCKS_PER_KEY heading 'Average|Data Blocks|Per
Key' format 99,990
column CLUSTERING_FACTOR heading 'Cluster|Factor' format
999,999,990
column COLUMN_POSITION heading 'Col|Pos' format 990
column col heading 'Column|Details' format a24
column COLUMN_LENGTH heading 'Col|Len' format 9,990
column GLOBAL_STATS heading 'Global|Stats' format a6
column USER_STATS heading 'User|Stats' format a6
column SAMPLE_SIZE heading 'Sample|Size' format
9,999,999,999,990
column to_char(t.last_analyzed,'MM-DD-YYYY') heading
'Date|MM-DD-YYYY' format a10
prompt
prompt ***********
prompt Table Level
prompt ***********
prompt
select
TABLE_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from dba_tables t
where
owner = upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
/
select
COLUMN_NAME,
decode(t.DATA_TYPE,
'NUMBER',t.DATA_TYPE||'('||
decode(t.DATA_PRECISION,
null,t.DATA_LENGTH||')',
t.DATA_PRECISION||','||t.DATA_SCALE||')'),
'DATE',t.DATA_TYPE,
'LONG',t.DATA_TYPE,
'LONG RAW',t.DATA_TYPE,
'ROWID',t.DATA_TYPE,
'MLSLABEL',t.DATA_TYPE,
t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
decode(t.nullable,
'N','NOT
NULL',
'n','NOT
NULL',
NULL)
col,
NUM_DISTINCT,
DENSITY,
NUM_BUCKETS,
NUM_NULLS,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from dba_tab_columns t
where
table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
/
select
INDEX_NAME,
UNIQUENESS,
BLEVEL BLev,
LEAF_BLOCKS,
DISTINCT_KEYS,
NUM_ROWS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_indexes t
where
table_name = upper('&Table_name')
and table_owner = upper(nvl('&Owner',user))
/
break on index_name
select
i.INDEX_NAME,
i.COLUMN_NAME,
i.COLUMN_POSITION,
decode(t.DATA_TYPE,
'NUMBER',t.DATA_TYPE||'('||
decode(t.DATA_PRECISION,
null,t.DATA_LENGTH||')',
t.DATA_PRECISION||','||t.DATA_SCALE||')'),
'DATE',t.DATA_TYPE,
'LONG',t.DATA_TYPE,
'LONG RAW',t.DATA_TYPE,
'ROWID',t.DATA_TYPE,
'MLSLABEL',t.DATA_TYPE,
t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
decode(t.nullable,
'N','NOT NULL',
'n','NOT NULL',
NULL) col
from
dba_ind_columns i,
dba_tab_columns t
where
i.table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
and i.table_name = t.table_name
and i.column_name = t.column_name
order by index_name,column_position
/
prompt
prompt ***************
prompt Partition Level
prompt ***************
select
PARTITION_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_tab_partitions t
where
table_owner =
upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
order by partition_position
/
break on partition_name
select
PARTITION_NAME,
COLUMN_NAME,
NUM_DISTINCT,
DENSITY,
NUM_BUCKETS,
NUM_NULLS,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_PART_COL_STATISTICS t
where
table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
/
break on partition_name
select
t.INDEX_NAME,
t.PARTITION_NAME,
t.BLEVEL BLev,
t.LEAF_BLOCKS,
t.DISTINCT_KEYS,
t.NUM_ROWS,
t.AVG_LEAF_BLOCKS_PER_KEY,
t.AVG_DATA_BLOCKS_PER_KEY,
t.CLUSTERING_FACTOR,
t.GLOBAL_STATS,
t.USER_STATS,
t.SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_ind_partitions t,
dba_indexes i
where
i.table_name = upper('&Table_name')
and i.table_owner = upper(nvl('&Owner',user))
and i.owner = t.index_owner
and i.index_name=t.index_name
/
prompt
prompt ***************
prompt SubPartition Level
prompt ***************
select
PARTITION_NAME,
SUBPARTITION_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_tab_subpartitions t
where
table_owner =
upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
order by SUBPARTITION_POSITION
/
break on partition_name
select
p.PARTITION_NAME,
t.SUBPARTITION_NAME,
t.COLUMN_NAME,
t.NUM_DISTINCT,
t.DENSITY,
t.NUM_BUCKETS,
t.NUM_NULLS,
t.GLOBAL_STATS,
t.USER_STATS,
t.SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_SUBPART_COL_STATISTICS t,
dba_tab_subpartitions p
where
t.table_name = upper('&Table_name')
and t.owner = upper(nvl('&Owner',user))
and t.subpartition_name = p.subpartition_name
and t.owner = p.table_owner
and t.table_name=p.table_name
/
break on partition_name
select
t.INDEX_NAME,
t.PARTITION_NAME,
t.SUBPARTITION_NAME,
t.BLEVEL BLev,
t.LEAF_BLOCKS,
t.DISTINCT_KEYS,
t.NUM_ROWS,
t.AVG_LEAF_BLOCKS_PER_KEY,
t.AVG_DATA_BLOCKS_PER_KEY,
t.CLUSTERING_FACTOR,
t.GLOBAL_STATS,
t.USER_STATS,
t.SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_ind_subpartitions t,
dba_indexes i
where
i.table_name = upper('&Table_name')
and i.table_owner = upper(nvl('&Owner',user))
and i.owner = t.index_owner
and i.index_name=t.index_name
/
clear breaks
set echo on
