我们通常使用下面脚本收集表和索引的统计信息。
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'TAB_OWNER'，tabname => 'TAB_NAME'，estimate_percent => 根据表大小设置，method_opt => 'for all columns size repeat'，no_invalidate => FALSE，degree => 根据表大小，CPU 资源和负载设置，granularity => 'AUTO'，cascade => TRUE);END;
ownname 表示表的拥有者，不区分大小写。
tabname 表示表名字，不区分大小写。
granularity 表示收集统计信息的粒度，该选项只对分区表生效，默认为 AUTO，表示让 Oracle 根据表的分区类型自己判断如何收集分区表的统计信息。对于该选项，我们一般采用 AUTO 方式，也就是数据库默认方式，因此，在后面的脚本中，省略该选项。
estimate_percent 表示采样率，范围是 0.000 001～100。
我们一般对小于 1GB 的表进行 100% 采样，因为表很小，即使 100% 采样速度也比较快。有时候小表有可能数据分布不均衡，如果没有 100% 采样，可能会导致统计信息不准。因此我们建议对小表 100% 采样。
我们一般对表大小在 1GB～5GB 的表采样 50%，对大于 5GB 的表采样 30%。如果表特别大，有几十甚至上百 GB，我们建议应该先对表进行分区，然后分别对每个分区收集统计信息。
一般情况下，为了确保统计信息比较准确，我们建议采样率不要低于 30%。
我们可以使用下面脚本查看表的采样率。
SELECT owner,table_name,num_rows,sample_size,round(sample_size / num_rows * 100) estimate_percentFROM DBA_TAB_STATISTICSWHERE owner='SCOTT' AND table_name='T_STATS';
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCENT--------------- --------------- ---------- ----------- ----------------SCOTT T_STATS 72674 72674 100
从上面查询我们可以看到，对表 T_STATS 是 100% 采样的。现在我们将采样率设置为 30%。
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'T_STATS',estimate_percent => 30,method_opt => 'for all columns size auto',no_invalidate => FALSE,degree => 1,cascade => TRUE);END;
PL/SQL procedure successfully completed.
SELECT owner,table_name,num_rows,sample_size,round(sample_size / num_rows * 100) estimate_percentFROM DBA_TAB_STATISTICSWHERE owner='SCOTT' AND table_name='T_STATS';
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCENT--------------- --------------- ---------- ----------- ----------------SCOTT T_STATS 73067 21920 30
从上面查询我们可以看到采样率为 30%，表的总行数被估算为 73 067，而实际上表的总行数为 72 674。设置采样率 30% 的时候，一共分析了 21 920 条数据，表的总行数等于 round(21 920*100/30)，也就是 73 067。
除非一个表是小表，否则没有必要对一个表 100% 采样。因为表一直都会进行 DML 操作，表中的数据始终是变化的。
method_opt 用于控制收集直方图策略。
method_opt => 'for all columns size 1'表示所有列都不收集直方图，如下所示。
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'T_STATS',estimate_percent => 100,method_opt => 'for all columns size 1',no_invalidate => FALSE,degree => 1,cascade => TRUE);END;
PL/SQL procedure successfully completed.
我们查看直方图信息。
select a.column_name,b.num_rows,a.num_nulls,a.num_distinct Cardinality,round(a.num_distinct / b.num_rows * 100, 2) selectivity,a.histogram,a.num_bucketsfrom dba_tab_col_statistics a, dba_tables bwhere a.owner = b.ownerand a.table_name = b.table_nameand a.owner = 'SCOTT'and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS--------------- ---------- ---------- ----------- ----------- --------------- -------EDITION_NAME 72674 72674 0 0 NONE 0NAMESPACE 72674 1 21 .03 NONE 1SECONDARY 72674 0 2 0 NONE 1GENERATED 72674 0 2 0 NONE 1TEMPORARY 72674 0 2 0 NONE 1STATUS 72674 0 2 0 NONE 1TIMESTAMP 72674 1 1592 2.19 NONE 1LAST_DDL_TIME 72674 1 1521 2.09 NONE 1CREATED 72674 0 1472 2.03 NONE 1OBJECT_TYPE 72674 0 45 .06 NONE 1DATA_OBJECT_ID 72674 64833 7796 10.73 NONE 1OBJECT_ID 72674 1 72673 100 NONE 1SUBOBJECT_NAME 72674 72145 140 .19 NONE 1OBJECT_NAME 72674 0 44333 61 NONE 1OWNER 72674 0 31 .04 NONE 115 rows selected.
从上面查询我们看到，所有列都没有收集直方图。
method_opt => 'for all columns size skewonly'表示对表中所有列收集自动判断是否收集直方图，如下所示。
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'T_STATS',estimate_percent => 100,method_opt => 'for all columns size skewonly',no_invalidate => FALSE,degree => 1,cascade => TRUE);END;
PL/SQL procedure successfully completed.
我们查看直方图信息，如下所示。
select a.column_name,b.num_rows,a.num_nulls,a.num_distinct Cardinality,round(a.num_distinct / b.num_rows * 100, 2) selectivity,a.histogram,a.num_bucketsfrom dba_tab_col_statistics a, dba_tables bwhere a.owner = b.ownerand a.table_name = b.table_nameand a.owner = 'SCOTT'and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS--------------- ---------- ---------- ----------- ----------- --------------- -------EDITION_NAME 72674 72674 0 0 NONE 0NAMESPACE 72674 1 21 .03 FREQUENCY 21SECONDARY 72674 0 2 0 FREQUENCY 2GENERATED 72674 0 2 0 FREQUENCY 2TEMPORARY 72674 0 2 0 FREQUENCY 2STATUS 72674 0 2 0 FREQUENCY 2TIMESTAMP 72674 1 1592 2.19 HEIGHT BALANCED 254LAST_DDL_TIME 72674 1 1521 2.09 HEIGHT BALANCED 254CREATED 72674 0 1472 2.03 HEIGHT BALANCED 254OBJECT_TYPE 72674 0 45 .06 FREQUENCY 45DATA_OBJECT_ID 72674 64833 7796 10.73 HEIGHT BALANCED 254OBJECT_ID 72674 1 72673 100 NONE 1SUBOBJECT_NAME 72674 72145 140 .19 FREQUENCY 140OBJECT_NAME 72674 0 44333 61 HEIGHT BALANCED 254OWNER 72674 0 31 .04 FREQUENCY 3115 rows selected.
从上面查询我们可以看到，除了 OBJECT_ID 列和 EDITION_NAME 列，其余所有列都收集了直方图。因为 EDITION_NAME 列全是 NULL，所以没必要收集直方图。OBJECT_ID 列选择性为 100%，没必要收集直方图。
在实际工作中千万不要使用**method_opt => 'for all columns size skewonly'** 收集直方图信息，因为并不是表中所有的列都会出现在 where 条件中，对没有出现在 where 条件中的列收集直方图没有意义。
method_opt => 'for all columns size auto'表示对出现在 where 条件中的列自动判断是否收集直方图。
现在我们删除表中所有列的直方图。
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'T_STATS',estimate_percent => 100,method_opt => 'for all columns size 1',no_invalidate => FALSE,degree => 1,cascade => TRUE);END;
PL/SQL procedure successfully completed.
我们执行下面 SQL，以便将 owner 列放入 where 条件中。
select count(*) from t_stats where owner='SYS';
COUNT(*)----------30850
接下来我们刷新数据库监控信息。
begindbms_stats.flush_database_monitoring_info;end;
PL/SQL procedure successfully completed.
我们使用method_opt => 'for all columns size auto'方式对表收集统计信息。
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'T_STATS',estimate_percent => 100,method_opt => 'for all columns size auto',no_invalidate => FALSE,degree => 1,cascade => TRUE);END;
PL/SQL procedure successfully completed.
然后我们查看直方图信息。
select a.column_name,b.num_rows,a.num_nulls,a.num_distinct Cardinality,round(a.num_distinct / b.num_rows * 100, 2) selectivity,a.histogram,a.num_bucketsfrom dba_tab_col_statistics a, dba_tables bwhere a.owner = b.ownerand a.table_name = b.table_nameand a.owner = 'SCOTT'and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS--------------- ---------- ---------- ----------- ----------- --------------- -------EDITION_NAME 72674 72674 0 0 NONE 0NAMESPACE 72674 1 21 .03 NONE 1SECONDARY 72674 0 2 0 NONE 1GENERATED 72674 0 2 0 NONE 1TEMPORARY 72674 0 2 0 NONE 1STATUS 72674 0 2 0 NONE 1TIMESTAMP 72674 1 1592 2.19 NONE 1LAST_DDL_TIME 72674 1 1521 2.09 NONE 1CREATED 72674 0 1472 2.03 NONE 1OBJECT_TYPE 72674 0 45 .06 NONE 1DATA_OBJECT_ID 72674 64833 7796 10.73 NONE 1OBJECT_ID 72674 1 72673 100 NONE 1SUBOBJECT_NAME 72674 72145 140 .19 NONE 1OBJECT_NAME 72674 0 44333 61 NONE 1OWNER 72674 0 31 .04 FREQUENCY 3115 rows selected.
从上面查询我们可以看到，Oracle 自动地对 owner 列收集了直方图。
思考，如果将选择性比较高的列放入 where 条件中，会不会自动收集直方图？现在我们将 OBJECT_NAME 列放入 where 条件中。
select count(*) from t_stats where object_name='EMP';
COUNT(*)----------3
然后我们刷新数据库监控信息。
begindbms_stats.flush_database_monitoring_info;end;
PL/SQL procedure successfully completed.
我们收集统计信息。
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'T_STATS',estimate_percent => 100,method_opt => 'for all columns size auto',no_invalidate => FALSE,degree => 1,cascade => TRUE);END;
PL/SQL procedure successfully completed.
我们查看 OBJECT_NAME 列是否收集了直方图。
select a.column_name,b.num_rows,a.num_nulls,a.num_distinct Cardinality,round(a.num_distinct / b.num_rows * 100, 2) selectivity,a.histogram,a.num_bucketsfrom dba_tab_col_statistics a, dba_tables bwhere a.owner = b.ownerand a.table_name = b.table_nameand a.owner = 'SCOTT'and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS--------------- ---------- ---------- ----------- ----------- --------------- -------EDITION_NAME 72674 72674 0 0 NONE 0NAMESPACE 72674 1 21 .03 NONE 1SECONDARY 72674 0 2 0 NONE 1GENERATED 72674 0 2 0 NONE 1TEMPORARY 72674 0 2 0 NONE 1STATUS 72674 0 2 0 NONE 1TIMESTAMP 72674 1 1592 2.19 NONE 1LAST_DDL_TIME 72674 1 1521 2.09 NONE 1CREATED 72674 0 1472 2.03 NONE 1OBJECT_TYPE 72674 0 45 .06 NONE 1DATA_OBJECT_ID 72674 64833 7796 10.73 NONE 1OBJECT_ID 72674 1 72673 100 NONE 1SUBOBJECT_NAME 72674 72145 140 .19 NONE 1OBJECT_NAME 72674 0 44333 61 NONE 1OWNER 72674 0 31 .04 FREQUENCY 3115 rows selected.
从上面查询我们可以看到，OBJECT_NAME 列没有收集直方图。由此可见，使用 AUTO 方式收集直方图很智能。mothod_opt 默认的参数就是 for all columns size auto。
method_opt => 'for all columns size repeat'表示当前有哪些列收集了直方图，现在就对哪些列收集直方图。
当前只对 OWNER 列收集了直方图，现在我们使用 REPEAT 方式收集直方图。
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'T_STATS',estimate_percent => 100,method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 1,cascade => TRUE);END;
PL/SQL procedure successfully completed.
我们查看直方图信息。
select a.column_name,b.num_rows,a.num_nulls,a.num_distinct Cardinality,round(a.num_distinct / b.num_rows * 100, 2) selectivity,a.histogram,a.num_bucketsfrom dba_tab_col_statistics a, dba_tables bwhere a.owner = b.ownerand a.table_name = b.table_nameand a.owner = 'SCOTT'and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS--------------- ---------- ---------- ----------- ----------- --------------- -------EDITION_NAME 72674 72674 0 0 NONE 0NAMESPACE 72674 1 21 .03 NONE 1SECONDARY 72674 0 2 0 NONE 1GENERATED 72674 0 2 0 NONE 1TEMPORARY 72674 0 2 0 NONE 1STATUS 72674 0 2 0 NONE 1TIMESTAMP 72674 1 1592 2.19 NONE 1LAST_DDL_TIME 72674 1 1521 2.09 NONE 1CREATED 72674 0 1472 2.03 NONE 1OBJECT_TYPE 72674 0 45 .06 NONE 1DATA_OBJECT_ID 72674 64833 7796 10.73 NONE 1OBJECT_ID 72674 1 72673 100 NONE 1SUBOBJECT_NAME 72674 72145 140 .19 NONE 1OBJECT_NAME 72674 0 44333 61 NONE 1OWNER 72674 0 31 .04 FREQUENCY 3115 rows selected.
从查询中我们可以看到，使用 REPEAT 方式延续了上次收集直方图的策略。对一个运行稳定的系统，我们应该采用 REPEAT 方式收集直方图。
单独对某一列收集直方图：method_opt => 'for columns 列名 size skewonly'
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'T',estimate_percent => 100,method_opt => 'for columns SYS_STUNA$6DVXJXTP05EH56DTIR0X size skewonly',no_invalidate => FALSE,degree => 1,cascade => TRUE);END;
method_opt => 'for columns object_type size skewonly'表示单独对 OBJECT_TYPE 列收集直方图，对于其余列，如果之前收集过直方图，现在也收集直方图。
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'T_STATS',estimate_percent => 100,method_opt => 'for columns object_type size skewonly',no_invalidate => FALSE,degree => 1,cascade => TRUE);END;
PL/SQL procedure successfully completed.
我们查看直方图信息。
select a.column_name,b.num_rows,a.num_nulls,a.num_distinct Cardinality,round(a.num_distinct / b.num_rows * 100, 2) selectivity,a.histogram,a.num_bucketsfrom dba_tab_col_statistics a, dba_tables bwhere a.owner = b.ownerand a.table_name = b.table_nameand a.owner = 'SCOTT'and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS--------------- ---------- ---------- ----------- ----------- --------------- -------EDITION_NAME 72674 72674 0 0 NONE 0NAMESPACE 72674 1 21 .03 NONE 1SECONDARY 72674 0 2 0 NONE 1GENERATED 72674 0 2 0 NONE 1TEMPORARY 72674 0 2 0 NONE 1STATUS 72674 0 2 0 NONE 1TIMESTAMP 72674 1 1592 2.19 NONE 1LAST_DDL_TIME 72674 1 1521 2.09 NONE 1CREATED 72674 0 1472 2.03 NONE 1OBJECT_TYPE 72674 0 45 .06 FREQUENCY 45DATA_OBJECT_ID 72674 64833 7796 10.73 NONE 1OBJECT_ID 72674 1 72673 100 NONE 1SUBOBJECT_NAME 72674 72145 140 .19 NONE 1OBJECT_NAME 72674 0 44333 61 NONE 1OWNER 72674 0 31 .04 FREQUENCY 3115 rows selected.
从查询中我们可以看到，OBJECT_TYPE 列收集了直方图，因为之前收集过 owner 列直方图，现在也跟着收集了 owner 列的直方图。
在实际工作中，我们需要对列收集直方图就收集直方图，需要删除某列直方图就删除其直方图，当系统趋于稳定之后，使用 REPEAT 方式收集直方图。
no_invalidate 表示共享池中涉及到该表的游标是否立即失效，默认值为 DBMS_STATS. AUTO_INVALIDATE，表示让 Oracle 自己决定是否立即失效。我们建议将 no_invalidate 参数设置为 FALSE，立即失效。因为我们发现有时候 SQL 执行缓慢是因为统计信息过期导致，重新收集了统计信息之后执行计划还是没有更改，原因就在于没有将这个参数设置为 false。
degree 表示收集统计信息的并行度，默认为 NULL。如果表没有设置 degree，收集统计信息的时候后就不开并行；如果表设置了 degree，收集统计信息的时候就按照表的 degree 来开并行。可以查询 DBA_TABLES.degree 来查看表的 degree，一般情况下，表的 degree 都为 1。我们建议可以根据当时系统的负载、系统中 CPU 的个数以及表大小来综合判断设置并行度。
SELECT DEGREE FROM DBA_TABLES WHERE owner = 'SCOTT' AND table_name = 'T_STATS';
cascade 表示在收集表的统计信息的时候，是否级联收集索引的统计信息，默认值为 DBMS_STATS.AUTO_CASCADE，表示让 Oracle 自己判断是否级联收集索引的统计信息。我们一般将其设置为 TRUE，在收集表的统计信息的时候，级联收集索引的统计信息。
若有收获，就点个赞吧
0 人点赞
