查看索引使用效率

来源：互联网发布：成都加工中心编程招聘编辑：程序博客网时间：2024/04/28 23:55
WITH Q AS (                  SELECT                         S.OWNER                  A_OWNER,                         TABLE_NAME               A_TABLE_NAME,                         INDEX_NAME               A_INDEX_NAME,                         INDEX_TYPE               A_INDEX_TYPE,                         SUM(S.bytes) / 1048576   A_MB                    FROM DBA_SEGMENTS S,                         DBA_INDEXES  I                   WHERE S.OWNER =  '&&1'                     AND I.OWNER =  '&&1'                     AND INDEX_NAME = SEGMENT_NAME    AND INDEX_NAME NOT LIKE 'PK%'                  GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE                 -- HAVING SUM(S.BYTES) > 1048576 * 0.          )          SELECT /*+ NO_QUERY_TRANSFORMATION(S) */                 A_OWNER                                    OWNER,                 A_TABLE_NAME                               TABLE_NAME,                 A_INDEX_NAME                               INDEX_NAME,                 A_INDEX_TYPE                               INDEX_TYPE,                 A_MB                                       MB,                 DECODE (OPTIONS, null, '       -',OPTIONS) INDEX_OPERATION,                 COUNT(OPERATION)                           NR_EXEC           FROM  Q,                 DBA_HIST_SQL_PLAN d           WHERE                 D.OBJECT_OWNER(+)= q.A_OWNER AND                 D.OBJECT_NAME(+) = q.A_INDEX_NAME          GROUP BY                 A_OWNER,                 A_TABLE_NAME,                 A_INDEX_NAME,                 A_INDEX_TYPE,                 A_MB,                 DECODE (OPTIONS, null, '       -',OPTIONS)          ORDER BY                 A_OWNER,                 A_TABLE_NAME,                 A_INDEX_NAME,                 A_INDEX_TYPE,                 A_MB DESC,                 NR_EXEC DESC
0 0