统计信息的类型
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 --分析索引的结构
DBMS_STATS 常见的4个存储过程
GATHER_TABLE_STATS 用于收集目标表、目标表的列和目标表上的索引的统计信息
GATHER_INDEX_STATS 用于收集指定索引的统计信息
GATHER_SCHEMA_STATS 用于收集执行 schema 下所有对象的统计信息
GATHER_DATABASE_STATS 用于收集全库所有对象的统计信息
本文实验继承文章https://blog.youkuaiyun.com/u010692693/article/details/103121179
@/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%
exec dbms_stats.gather_table_stats(ownname => 'TESTZYL', tabname => 'T2', estimate_percent => 15, method_opt => 'FOR TABLE', cascade => false);
--method_opt => 'FOR TABLE' 表示只收集表T2的统计信息，这种方法并不适用于所有版本，在10.2.0.4和10.2.0.5，这两个版本里，即使指定了'FOR TABLE'，Oracle除了收集表统计信息之外还会对所有的列收集统计信息。
@/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,980 3,727 0 0 0 124 YES NO 61,947 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收集统计信息，并且以计算模式，参数estimate_percent设置为100或NULL
exec dbms_stats.gather_table_stats(ownname => 'TESTZYL', tabname => 'T2', estimate_percent => 100, method_opt => 'FOR TABLE', cascade => false);
exec dbms_stats.gather_table_stats(ownname => 'TESTZYL', tabname => 'T2', estimate_percent => NULL, method_opt => 'FOR TABLE', cascade => false);
@/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 0 0 0 124 YES 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 以计算模式收集统计信息(不收集直方图)
exec dbms_stats.gather_table_stats(ownname => 'TESTZYL', tabname => 'T2', estimate_percent => 100, method_opt => 'for columns size 1 object_name object_id', cascade => false);
--ANALYZE 命令可以做到只收集列而不收集表的统计信息，DBMS_STATS包做不到这一点
@/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 0 0 0 124 YES 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 1 0 YES NO 412,403 11-18-2019
SUBOBJECT_NAME VARCHAR2(30) NO NO
OBJECT_ID NUMBER(22) 412,243 0 1 160 YES NO 412,243 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 的统计信息
exec dbms_stats.gather_index_stats(ownname => 'TESTZYL', indname => 'IDX_T2', estimate_percent => 100);
@/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 0 0 0 124 YES 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 1 0 YES NO 412,403 11-18-2019
SUBOBJECT_NAME VARCHAR2(30) NO NO
OBJECT_ID NUMBER(22) 412,243 0 1 160 YES NO 412,243 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 YES 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的所有索引的统计信息
exec dbms_stats.delete_table_stats(ownname => 'TESTZYL', tabname => 'T2');
@/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上的所有索引的统计信息
exec dbms_stats.gather_table_stats(ownname => 'TESTZYL', tabname => 'T2', estimate_percent => 100, cascade => true);
@/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 0 0 0 124 YES 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 YES NO 412,403 11-18-2019
OBJECT_NAME VARCHAR2(128) 58,212 0 1 0 YES NO 412,403 11-18-2019
SUBOBJECT_NAME VARCHAR2(30) 125,035 0 1 67,868 YES NO 344,535 11-18-2019
OBJECT_ID NUMBER(22) 412,243 0 1 160 YES NO 412,243 11-18-2019
DATA_OBJECT_ID NUMBER(22) 384,280 0 1 28,086 YES NO 384,317 11-18-2019
OBJECT_TYPE VARCHAR2(19) 42 0 1 0 YES NO 412,403 11-18-2019
CREATED DATE 55,682 0 1 0 YES NO 412,403 11-18-2019
LAST_DDL_TIME DATE 65,784 0 1 160 YES NO 412,243 11-18-2019
TIMESTAMP VARCHAR2(19) 55,982 0 1 160 YES NO 412,243 11-18-2019
STATUS VARCHAR2(7) 2 1 1 0 YES NO 412,403 11-18-2019
TEMPORARY VARCHAR2(1) 2 1 1 0 YES NO 412,403 11-18-2019
GENERATED VARCHAR2(1) 2 1 1 0 YES NO 412,403 11-18-2019
SECONDARY VARCHAR2(1) 1 1 1 0 YES NO 412,403 11-18-2019
NAMESPACE NUMBER(22) 18 0 1 160 YES NO 412,243 11-18-2019
EDITION_NAME VARCHAR2(30) 0 0 0 412,403 YES 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 YES 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
***************
扫一扫
718
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
