此脚本依赖统计信息。

    如果一个索引选择性很低，说明列数据分布不均衡。当 SQL 走了数据分布不均衡列的索引，很容易走错执行计划，此时我们应该检查 SQL 语句中是否有其他过滤条件，如果有其他过滤条件，可以考虑建立组合索引，将选择性高的列作为引导列；如果没有其他过滤条件，应该检查列是否有收集直方图。以下脚本抓出走了低选择性索引的 SQL。

    1. select c.sql_id,
    2.        c.sql_text,
    3.        b.index_name,
    4.        e.table_name,
    5.        trunc(d.num_distinct / e.num_rows * 100, 2) selectivity,
    6.        d.num_distinct,
    7.        e.num_rows
    8.   from v$sql_plan a,
    9.        (select *
    10.           from (select index_owner,
    11.                        index_name,
    12.                        table_owner,
    13.                        table_name,
    14.                        column_name,
    15.                        count(*) over(partition by index_owner, index_name, table_owner, table_name) cnt
    16.                   from dba_ind_columns)
    17.          where cnt = 1) b,
    18.        v$sql c,
    19.        dba_tab_col_statistics d,
    20.        dba_tables e
    21.  where a.object_owner = b.index_owner
    22.    and a.object_name = b.index_name
    23.    and b.index_owner = 'SCOTT'
    24.    and a.access_predicates is not null
    25.    and a.sql_id = c.sql_id
    26.    and a.child_number = c.child_number
    27.    and d.owner = e.owner
    28.    and d.table_name = e.table_name
    29.    and b.table_owner = e.owner
    30.    and b.table_name = e.table_name
    31.    and d.column_name = b.column_name
    32.    and d.table_name = b.table_name
    33.    and d.num_distinct / e.num_rows < 0.1;

    我们在 Scott 账户中执行如下 SQL 并且查看执行计划。

    1. select * from t where owner='SYS';
    1. 23654 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 2480948561
    7. -------------------------------------------------------------------------------------
    8. | Id | Operation                   | Name      | Rows  | Bytes | Cost(%CPU)|Time    |
    9. -------------------------------------------------------------------------------------
    10. |  0 | SELECT STATEMENT            |           |  2346 |   222K|    68  (0)|00:00:01|
    11. |  1 |  TABLE ACCESS BY INDEX ROWID| T         |  2346 |   222K|    68  (0)|00:00:01|
    12. |* 2 |   INDEX RANGE SCAN          | IDX_OWNER |  2346 |       |     6  (0)|00:00:01|
    13. -------------------------------------------------------------------------------------
    15. Predicate Information (identified by operation id):
    16. ---------------------------------------------------
    18.    2 - access("OWNER"='SYS')
    20. Statistics
    21. ----------------------------------------------------------
    22.           1  recursive calls
    23.           0  db block gets
    24.        3819  consistent gets
    25.           0  physical reads
    26.           0  redo size
    27.     2680901  bytes sent via SQL*Net to client
    28.       17756  bytes received via SQL*Net from client
    29.        1578  SQL*Net roundtrips to/from client
    30.           0  sorts (memory)
    31.           0  sorts (disk)
    32.       23654  rows processed

    我们使用脚本将以上 SQL 抓出。

    1. select c.sql_id,
    2.         c.sql_text,
    3.          b.index_name,
    4.         e.table_name,
    5.          trunc(d.num_distinct / e.num_rows * 100, 2) selectivity,
    6.          d.num_distinct,
    7.          e.num_rows
    8.    from v$sql_plan a,
    9.          (select *
    10.             from (select index_owner,
    11.                           index_name,
    12.                          table_owner,
    13.                         table_name,
    14.                          column_name,
    15.                          count(*) over(partition by index_owner, index_name, table_owner, table_name) cnt
    16.                     from dba_ind_columns)
    17.            where cnt = 1) b,
    18.         v$sql c,
    19.          dba_tab_col_statistics d,
    20.          dba_tables e
    21.    where a.object_owner = b.index_owner
    22.      and a.object_name = b.index_name
    23.      and b.index_owner = 'SCOTT'
    24.      and a.access_predicates is not null
    25.      and a.sql_id = c.sql_id
    26.      and a.child_number = c.child_number
    27.      and d.owner = e.owner
    28.      and d.table_name = e.table_name
    29.      and b.table_owner = e.owner
    30.      and b.table_name = e.table_name
    31.      and d.column_name = b.column_name
    32.      and d.table_name = b.table_name
    33.      and d.num_distinct / e.num_rows < 0.1;
    1. SQL_ID      SQL_TEXT        INDEX_NAME  TABLE_NAME SELECTIVITY NUM_DISTINCT  NUM_ROWS
    2. ----------- --------------------------- ---------- ----------- ---- -------- --------
    3. 6gzd8z5vm5k0t select * from t where owner='SYS' IDX_OWNER     T             .04         31   72734