统计信息的类型
1. 表的统计信息
2. 索引的统计信息
3. 列的统计信息
4. 系统的统计信息
5. 数据字典的统计信息
6. 内部对象的统计信息
统计信息的收集方法
1. ANALYZE 表，索引，列，系统，不能收集数据字典和内部对象
2. DBMS_STATS 都可以收集
ANALYZE 和 DBMS_STATS 的区别
1. ANALYZE 命令不能正确地收集分区表的统计信息，而 DBMS_STATS 可以。
2. ANALYZE 命令不能并行收集统计信息，而 DBMS_STATS 包可以(degree => 4)。
exec dbms_stats.gather_table_stats(ownname => 'TESTZYL', tabname => 'T2', cascade => true, estimate_percent => 100, degree => 4);
3. DBMS_STATS 只能收集与CBO相关的统计信息，而与CBO无关的一些额外信息，比如行迁移/行链接的数量(CHAIN_CNT)、校验表和索引的结构信息等，DBMS_STATS包就无能为力了。
analyze table XXX list chained rows into YYY --分析收集行迁移/行链接的数量
analyze index XXX validate structure --分析索引的结构
测试环境准备
文章中用到的脚本@/home/oracle/sosc.sql 请参考文章 https://blog.csdn.net/u010692693/article/details/103120646
drop table t2 purge;
create table t2 as select * from dba_objects;
create index idx_t2 on t2(object_id);
analyze index idx_t2 delete statistics;
@/home/oracle/sosc.sql
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
T2 NO NO
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
OWNER VARCHAR2(30) NO NO
OBJECT_NAME VARCHAR2(128) NO NO
SUBOBJECT_NAME VARCHAR2(30) NO NO
OBJECT_ID NUMBER(22) NO NO
DATA_OBJECT_ID NUMBER(22) NO NO
OBJECT_TYPE VARCHAR2(19) NO NO
CREATED DATE NO NO
LAST_DDL_TIME DATE NO NO
TIMESTAMP VARCHAR2(19) NO NO
STATUS VARCHAR2(7) NO NO
TEMPORARY VARCHAR2(1) NO NO
GENERATED VARCHAR2(1) NO NO
SECONDARY VARCHAR2(1) NO NO
NAMESPACE NUMBER(22) NO NO
EDITION_NAME VARCHAR2(30) NO NO
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- ------------------ ----------- ----------- ------------ ------ ------ ------------------ ----------
IDX_T2 NONUNIQUE NO NO
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_T2 OBJECT_ID 1 NUMBER(22)
***************
Partition Level
***************
***************
SubPartition Level
***************
SQL> select count(*) from t2;
COUNT(*)
----------
412403只对表t2收集统计信息，以估算模式，采样比例15%
analyze table t2 estimate statistics sample 15 percent for table;
@/home/oracle/sosc.sql
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
T2 419,609 3,727 49 1,699 0 128 NO NO 56,629 11-18-2019
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
OWNER VARCHAR2(30) NO NO
OBJECT_NAME VARCHAR2(128) NO NO
SUBOBJECT_NAME VARCHAR2(30) NO NO
OBJECT_ID NUMBER(22) NO NO
DATA_OBJECT_ID NUMBER(22) NO NO
OBJECT_TYPE VARCHAR2(19) NO NO
CREATED DATE NO NO
LAST_DDL_TIME DATE NO NO
TIMESTAMP VARCHAR2(19) NO NO
STATUS VARCHAR2(7) NO NO
TEMPORARY VARCHAR2(1) NO NO
GENERATED VARCHAR2(1) NO NO
SECONDARY VARCHAR2(1) NO NO
NAMESPACE NUMBER(22) NO NO
EDITION_NAME VARCHAR2(30) NO NO
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- ------------------ ----------- ----------- ------------ ------ ------ ------------------ ----------
IDX_T2 NONUNIQUE NO NO
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_T2 OBJECT_ID 1 NUMBER(22)
***************
Partition Level
***************
***************
SubPartition Level
***************只对表t2收集统计信息，以计算模式
analyze table t2 compute statistics for table;
@/home/oracle/sosc.sql
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
T2 412,403 3,727 49 1,699 0 127 NO NO 412,403 11-18-2019
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
OWNER VARCHAR2(30) NO NO
OBJECT_NAME VARCHAR2(128) NO NO
SUBOBJECT_NAME VARCHAR2(30) NO NO
OBJECT_ID NUMBER(22) NO NO
DATA_OBJECT_ID NUMBER(22) NO NO
OBJECT_TYPE VARCHAR2(19) NO NO
CREATED DATE NO NO
LAST_DDL_TIME DATE NO NO
TIMESTAMP VARCHAR2(19) NO NO
STATUS VARCHAR2(7) NO NO
TEMPORARY VARCHAR2(1) NO NO
GENERATED VARCHAR2(1) NO NO
SECONDARY VARCHAR2(1) NO NO
NAMESPACE NUMBER(22) NO NO
EDITION_NAME VARCHAR2(30) NO NO
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- ------------------ ----------- ----------- ------------ ------ ------ ------------------ ----------
IDX_T2 NONUNIQUE NO NO
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_T2 OBJECT_ID 1 NUMBER(22)
***************
Partition Level
***************
***************
SubPartition Level
***************
对表T2的列OBJECT_NAME和OBJECT_ID以计算模式收集统计信息
analyze table t2 compute statistics for columns object_name,object_id;
@/home/oracle/sosc.sql
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
T2 412,403 3,727 49 1,699 0 127 NO NO 412,403 11-18-2019
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
OWNER VARCHAR2(30) NO NO
OBJECT_NAME VARCHAR2(128) 58,212 0 75 0 NO NO 412,403 11-18-2019
SUBOBJECT_NAME VARCHAR2(30) NO NO
OBJECT_ID NUMBER(22) 412,243 0 75 160 NO NO 412,403 11-18-2019
DATA_OBJECT_ID NUMBER(22) NO NO
OBJECT_TYPE VARCHAR2(19) NO NO
CREATED DATE NO NO
LAST_DDL_TIME DATE NO NO
TIMESTAMP VARCHAR2(19) NO NO
STATUS VARCHAR2(7) NO NO
TEMPORARY VARCHAR2(1) NO NO
GENERATED VARCHAR2(1) NO NO
SECONDARY VARCHAR2(1) NO NO
NAMESPACE NUMBER(22) NO NO
EDITION_NAME VARCHAR2(30) NO NO
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- ------------------ ----------- ----------- ------------ ------ ------ ------------------ ----------
IDX_T2 NONUNIQUE NO NO
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_T2 OBJECT_ID 1 NUMBER(22)
***************
Partition Level
***************
***************
SubPartition Level
***************对同一个对象而言，新执行的 ANALYZE 命令并不会抹掉之前 ANALYZE 的结果。
同时以计算模式对表T2和列OBJECT_NAME和OBJECT_ID收集统计信息
analyze table t2 compute statistics for table for columns object_name,object_id;
@/home/oracle/sosc.sql
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
T2 412,403 3,727 49 1,699 0 127 NO NO 412,403 11-18-2019
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
OWNER VARCHAR2(30) NO NO
OBJECT_NAME VARCHAR2(128) 58,212 0 75 0 NO NO 412,403 11-18-2019
SUBOBJECT_NAME VARCHAR2(30) NO NO
OBJECT_ID NUMBER(22) 412,243 0 75 160 NO NO 412,403 11-18-2019
DATA_OBJECT_ID NUMBER(22) NO NO
OBJECT_TYPE VARCHAR2(19) NO NO
CREATED DATE NO NO
LAST_DDL_TIME DATE NO NO
TIMESTAMP VARCHAR2(19) NO NO
STATUS VARCHAR2(7) NO NO
TEMPORARY VARCHAR2(1) NO NO
GENERATED VARCHAR2(1) NO NO
SECONDARY VARCHAR2(1) NO NO
NAMESPACE NUMBER(22) NO NO
EDITION_NAME VARCHAR2(30) NO NO
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- ------------------ ----------- ----------- ------------ ------ ------ ------------------ ----------
IDX_T2 NONUNIQUE NO NO
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_T2 OBJECT_ID 1 NUMBER(22)
***************
Partition Level
***************
***************
SubPartition Level
***************以计算模式收集索引 IDX_T2 的统计信息
analyze index idx_t2 compute statistics;
@/home/oracle/sosc.sql
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
T2 412,403 3,727 49 1,699 0 127 NO NO 412,403 11-18-2019
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
OWNER VARCHAR2(30) NO NO
OBJECT_NAME VARCHAR2(128) 58,212 0 75 0 NO NO 412,403 11-18-2019
SUBOBJECT_NAME VARCHAR2(30) NO NO
OBJECT_ID NUMBER(22) 412,243 0 75 160 NO NO 412,403 11-18-2019
DATA_OBJECT_ID NUMBER(22) NO NO
OBJECT_TYPE VARCHAR2(19) NO NO
CREATED DATE NO NO
LAST_DDL_TIME DATE NO NO
TIMESTAMP VARCHAR2(19) NO NO
STATUS VARCHAR2(7) NO NO
TEMPORARY VARCHAR2(1) NO NO
GENERATED VARCHAR2(1) NO NO
SECONDARY VARCHAR2(1) NO NO
NAMESPACE NUMBER(22) NO NO
EDITION_NAME VARCHAR2(30) NO NO
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- ------------------ ----------- ----------- ------------ ------ ------ ------------------ ----------
IDX_T2 NONUNIQUE 1 473 412,243 412,243 1 1 15,241 NO NO 412,243 11-18-2019
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_T2 OBJECT_ID 1 NUMBER(22)
***************
Partition Level
***************
***************
SubPartition Level
***************删除表T2、表T2的所有列及表T2的所有索引的统计信息
analyze table t2 delete statistics;
@/home/oracle/sosc.sql
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
T2 NO NO
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
OWNER VARCHAR2(30) NO NO
OBJECT_NAME VARCHAR2(128) NO NO
SUBOBJECT_NAME VARCHAR2(30) NO NO
OBJECT_ID NUMBER(22) NO NO
DATA_OBJECT_ID NUMBER(22) NO NO
OBJECT_TYPE VARCHAR2(19) NO NO
CREATED DATE NO NO
LAST_DDL_TIME DATE NO NO
TIMESTAMP VARCHAR2(19) NO NO
STATUS VARCHAR2(7) NO NO
TEMPORARY VARCHAR2(1) NO NO
GENERATED VARCHAR2(1) NO NO
SECONDARY VARCHAR2(1) NO NO
NAMESPACE NUMBER(22) NO NO
EDITION_NAME VARCHAR2(30) NO NO
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- ------------------ ----------- ----------- ------------ ------ ------ ------------------ ----------
IDX_T2 NONUNIQUE NO NO
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_T2 OBJECT_ID 1 NUMBER(22)
***************
Partition Level
***************
***************
SubPartition Level
***************一次性以计算模式收集表T2、表T2的所有列和表T2上的所有索引的统计信息
analyze table t2 compute statistics;
@/home/oracle/sosc.sql
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
T2 412,403 3,727 49 1,699 0 127 NO NO 412,403 11-18-2019
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
OWNER VARCHAR2(30) 68 0 1 0 NO NO 412,403 11-18-2019
OBJECT_NAME VARCHAR2(128) 58,212 0 1 0 NO NO 412,403 11-18-2019
SUBOBJECT_NAME VARCHAR2(30) 125,035 0 1 67,868 NO NO 412,403 11-18-2019
OBJECT_ID NUMBER(22) 412,243 0 1 160 NO NO 412,403 11-18-2019
DATA_OBJECT_ID NUMBER(22) 384,280 0 1 28,086 NO NO 412,403 11-18-2019
OBJECT_TYPE VARCHAR2(19) 42 0 1 0 NO NO 412,403 11-18-2019
CREATED DATE 55,682 0 1 0 NO NO 412,403 11-18-2019
LAST_DDL_TIME DATE 65,784 0 1 160 NO NO 412,403 11-18-2019
TIMESTAMP VARCHAR2(19) 55,982 0 1 160 NO NO 412,403 11-18-2019
STATUS VARCHAR2(7) 2 1 1 0 NO NO 412,403 11-18-2019
TEMPORARY VARCHAR2(1) 2 1 1 0 NO NO 412,403 11-18-2019
GENERATED VARCHAR2(1) 2 1 1 0 NO NO 412,403 11-18-2019
SECONDARY VARCHAR2(1) 1 1 1 0 NO NO 412,403 11-18-2019
NAMESPACE NUMBER(22) 18 0 1 160 NO NO 412,403 11-18-2019
EDITION_NAME VARCHAR2(30) 0 0 0 412,403 NO NO 11-18-2019
B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- ------------------ ----------- ----------- ------------ ------ ------ ------------------ ----------
IDX_T2 NONUNIQUE 1 473 412,243 412,243 1 1 15,241 NO NO 412,243 11-18-2019
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
IDX_T2 OBJECT_ID 1 NUMBER(22)
***************
Partition Level
***************
***************
SubPartition Level
***************
2040
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
