【TUNE_ORACLE】列出走了低選擇性索引的SQL參考

實驗環境

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