查看索引使用效率

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