实验环境

搭建平台：VMware Workstation

OS：RHEL 6.10

Grid&DB：Oracle 11.2.0.4

SQL参考

select c.sql_id,

c.sql_text,

b.index_name,

e.table_name,

trunc(d.num_distinct / e.num_rows * 100, 2) selectivity,

d.num_distinct,

e.num_rows

from v$sql_plan a,

(select *

from (select index_owner,

index_name,

table_owner,

table_name,

column_name,

count(*) over(partition by index_owner, index_name, table_owner, table_name) cnt

from dba_ind_columns)

where cnt = 1) b,

v$sql c,

dba_tab_col_statistics d,

dba_tables e

where a.object_owner = b.index_owner

and a.object_name = b.index_name

and b.index_owner = 'TEST'

and a.access_predicates is not null

and a.sql_id = c.sql_id

and a.child_number = c.child_number

and d.owner = e.owner

and d.table_name = e.table_name

and b.table_owner = e.owner

and b.table_name = e.table_name

and d.column_name = b.column_name

and d.table_name = b.table_name

and d.num_distinct / e.num_rows < 0.1; --找出选择性小于10%的SQL

ITPub博客

【TUNE_ORACLE】列出走了低选择性索引的SQL参考