实验环境
搭建平台：VMware Workstation
OS：RHEL 6.10
Grid&DB：Oracle 11.2.0.4
SQL参考
--******该脚本依赖统计信息！！必须先收集统计信息！！******
注：
--1. 没有收集过直方图，且列出现在where条件中，列的选择性小于1%
select a.owner,
a.table_name,
a.column_name,
b.num_rows,
a.num_distinct,
trunc(num_distinct / num_rows * 100, 2) selectivity,
'Need Gather Histogram' notice
from dba_tab_col_statistics a, dba_tables b
where a.owner = 'TEST'
and a.table_name = 'TAB'
and a.owner = b.owner
and a.table_name = b.table_name
and num_distinct / num_rows < 0.01 --选择性小于1%
and (a.owner, a.table_name, a.column_name) in
(select r.name owner, o.name table_name, c.name column_name
from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
where o.obj# = u.obj#
and c.obj# = u.obj#
and c.col# = u.intcol#
and r.name = 'TEST'
and o.name = 'TAB')
and a.histogram = 'NONE';
--2. 列出现在where条件中，列的选择性小于5%,总行数大于5W
select a.owner,
a.table_name,
a.column_name,
b.num_rows,
a.num_distinct,
trunc(num_distinct / num_rows * 100, 2) selectivity,
'Need Gather Histogram' notice
from dba_tab_col_statistics a, dba_tables b
where a.owner = 'TEST'
and a.table_name = 'TAB'
and a.owner = b.owner
and a.table_name = b.table_name
and num_distinct / num_rows < 0.05 --选择性小于5%
and num_rows > 50000 --总行数大于5万行
and (a.owner, a.table_name, a.column_name) in
(select r.name owner, o.name table_name, c.name column_name
from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
where o.obj# = u.obj#
and c.obj# = u.obj#
and c.col# = u.intcol#
and r.name = 'TEST'
and o.name = 'TAB');
