实验环境
搭建平台：VMware Workstation
OS：RHEL 6.10
Grid&DB：Oracle 11.2.0.4
SQL参考select a.sql_id, a.sql_text, f.table_name, c.size_mb, e.column_name, round(e.num_distinct / f.num_rows * 100, 2) selectivity from v$sql a, v$sql_plan b, (select owner, segment_name, sum(bytes / 1024 / 1024) size_mb from dba_segments group by owner, segment_name) c, dba_tab_col_statistics e, dba_tables f where a.sql_id = b.sql_id and a.child_number = b.child_number and b.object_owner = c.owner and b.object_name = c.segment_name and e.owner = f.owner and e.table_name = f.table_name and b.object_owner = f.owner and b.object_name = f.table_name and instr(b.filter_predicates, e.column_name) > 0 and (e.num_distinct / f.num_rows) > 0.1 --比例要超过10%才算选择性高 and c.owner = 'TEST' and b.operation = 'TABLE ACCESS' and b.options = 'BY INDEX ROWID' -- TABLE ACCESS BY INDEX ROWID and e.owner = 'TEST' order by 4 desc;
