为了讲解直方图，我收集统计信息的时候是 method_opt => 'for all columns size skewonly'
正式的生产环境中，最好别用allcolumns方式收集直方图，因为all columns 几乎会对所有列都收集直方图信息
method_opt => 'for all columns size skewonly'
drop table p500 purge;
create table p500 as select * from dba_objects;
select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'TEST'
and a.table_name = 'P500';
select owner, table_name name, object_type, stale_stats, last_analyzed
from dba_tab_statistics
where table_name in ('P500')
and owner = 'TEST'
and (stale_stats = 'YES' or last_analyzed is null);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
method_opt => 'for all columns size auto',
auto表示Oracle根据谓词过滤信息(前文讲解直方图的时候提到过的where条件过滤)，自动判断该列是否收集直方图。
一个稳定的系统，不应该让Oracle去自动判断，自动判断很可能就会出事，比如某列不该收集直方图，设置auto过后它自己去收集直方图了，从而导致系统不稳定。
drop table p600 purge;
create table p600 as select * from dba_objects;
select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'TEST'
and a.table_name = 'P600';
method_opt => 'for all columns size auto';
select * from p600 a where a.owner='SYS';
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
tabname => 'P600',
estimate_percent => 100,
method_opt => 'for all columns size auto',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'TEST'
and a.table_name = 'P600';
只有OWNER列被采集了直方图
SQL> set linesie 200
SP2-0158: 未知的 SET 选项 "linesie"
SQL> set linesize 200
SQL> set pagesize 200
SQL> select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'TEST'
and a.table_name = 'P600';
2 3 4 5 6 7 8 9 10 11
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------------------------------------ ---------- ----------- ----------- --------------------------------------------- -----------
OWNER 86978 30 .03 FREQUENCY 30
OBJECT_NAME 86978 52436 60.29 NONE 1
SUBOBJECT_NAME 86978 142 .16 NONE 1
OBJECT_ID 86978 86978 100 NONE 1
DATA_OBJECT_ID 86978 9097 10.46 NONE 1
OBJECT_TYPE 86978 45 .05 NONE 1
CREATED 86978 963 1.11 NONE 1
LAST_DDL_TIME 86978 1065 1.22 NONE 1
TIMESTAMP 86978 1108 1.27 NONE 1
STATUS 86978 1 0 NONE 1
TEMPORARY 86978 2 0 NONE 1
GENERATED 86978 2 0 NONE 1
SECONDARY 86978 2 0 NONE 1
NAMESPACE 86978 21 .02 NONE 1
EDITION_NAME 86978 0 0 NONE 0
已选择15行。
method_opt=> 'for all columns size repeat'
repeat表示以前收集过直方图，现在收集统计信息的时候就收集直方图，如果以前没收集过直方图，现在收集统计信息的时候就不收集。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
tabname => 'P500',
estimate_percent => 100,
method_opt=> 'for all columns size repeat',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
SQL>
SQL>
SQL> select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'TEST'
and a.table_name = 'P600'; 2 3 4 5 6 7 8 9 10 11
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------------------------------------------------------------------ ---------- ----------- ----------- --------------------------------------------- -----------
OWNER 86978 30 .03 FREQUENCY 30
OBJECT_NAME 86978 52436 60.29 NONE 1
SUBOBJECT_NAME 86978 142 .16 NONE 1
OBJECT_ID 86978 86978 100 NONE 1
DATA_OBJECT_ID 86978 9097 10.46 NONE 1
OBJECT_TYPE 86978 45 .05 NONE 1
CREATED 86978 963 1.11 NONE 1
LAST_DDL_TIME 86978 1065 1.22 NONE 1
TIMESTAMP 86978 1108 1.27 NONE 1
STATUS 86978 1 0 NONE 1
TEMPORARY 86978 2 0 NONE 1
GENERATED 86978 2 0 NONE 1
SECONDARY 86978 2 0 NONE 1
NAMESPACE 86978 21 .02 NONE 1
EDITION_NAME 86978 0 0 NONE 0
已选择15行。