实验环境

搭建平台：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;

ITPub博客

【TUNE_ORACLE】列出可以创建组合索引的SQL(回表再过滤选择性高的列)的SQL参考