实验环境
搭建平台：VMware Workstation
OS：RHEL 6.10
Grid&DB：Oracle 11.2.0.4
SQL参考
--******该脚本依赖统计信息！！必须先收集统计信息！！******
注：统计收集方法详见： http://blog.itpub.net/69992972/viewspace-2784605/ --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%,总行数大于5Wselect 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');
