Friday, November 26, 2010

How to see index usage (without "alter index ... monitoring usage")

Recent Oracle online course "Understanding Explain Plans & Index Utilization" by Dan Hotka, reminded me to write some thoughts about index and usage in Oracle databases.

As we all known, some developers/DBAs have bad habit to "tune" SQL's by adding new indexes with or without real need. So any unnecessary index, beside professional aspect, has 2 major weakness:
  1. Adding new index very often change current SQL plans (what, as Murphy says, mostly finish badly!)
  2. Set overhead on I/O and CPU in any DML operation on involved table (maintain index blocks)
Oracle has provided "out of the box" Monitor Index Usage solution, which idea is to activate oracle core process which will log usage of chosen index. Later DBA can query v$object_usage view and see if index is used:
select table_name, index_name, monitoring, used 
 from v$object_usage 
 where table_name = '&&TABLE_NAME' 
order by table_name, index_name; 
This solution has two disadvantages:
  1. Regardless how many times index was used it is recorded just once
  2. There is no other way to see any additional info on index usage
It is known that on big tables which use some index very rare, better option is to create index before execution of that query and later immediately drop it! So finding least number of executions should be really important in index usage analyze.

The solution

To get a solution for this, there is one pre requirement-AWR. AWR (Automatic Workload Repository), which is part of "Tuning and Diagnostic Packs", must be installed, what require additional licensing. AWR is snapshot repository of important database activity, so AWR should able to keep data with retention of minimum of 30 days (46 days are perfect) and those snapshots must be taken in appropriate interval. Here is easy example how to make that (as I do):
    retention => 66240,           -- = 46 Days
    interval  => 15)              -- = 15 Minutes
Now you need to wait 30/46 days and then you can query index usage.

After that you are able to use this script that shows all indexes in schema (defined with first parameter-&&1) which are bigger then value (defined with scripts's second parameter-&&2). Remember that in mine case monitor period is 46 days, what is not so little.
/* ---------------------------------------------------------------------------
 CR/TR#  :
 Purpose : Shows index usage by execution (find problematic indexes)

 Date    : 22.01.2008.
 Author  : Damir Vadas,

 Remarks : run as privileged user
           Must have AWR run because sql joins data from there
           works on 10g >         
           @index_usage SCHEMA MIN_INDEX_SIZE
 Changes (DD.MM.YYYY, Name, CR/TR#):           
          25.11.2010, Damir Vadas
                      added index size as parameter
          30.11.2010, Damir Vadas
                      fixed bug in query
--------------------------------------------------------------------------- */
set linesize 140
set pagesize 160

clear breaks
clear computes

compute sum of NR_EXEC on TABLE_NAME SKIP 2
compute sum of MB on TABLE_NAME SKIP 2

set linesize 140
set pagesize 10000
col OWNER noprint
col TABLE_NAME for a30 heading 'Table name'
col INDEX_NAME for a25 heading 'Index name'
col INDEX_TYPE for a10 heading 'Index type'
col INDEX_OPERATION for a21 Heading 'Index operation'
col NR_EXEC for 9G999G990 heading 'Executions'
col MB for 999G990D90 Heading 'Index|Size MB' justify  right

        WITH Q AS (
                       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
                HAVING SUM(S.BYTES) > 1048576 * &&2
               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
               D.OBJECT_OWNER(+)= q.A_OWNER AND
               D.OBJECT_NAME(+) = q.A_INDEX_NAME 
        GROUP BY
               DECODE (OPTIONS, null, '       -',OPTIONS)
        ORDER BY 
               A_MB DESC, 
               NR_EXEC DESC

PROMPT Showed only indexes in &&1 schema whose size > &&2 MB in period:
select to_char (min(BEGIN_INTERVAL_TIME), 'DD.MM.YYYY')
       || '-' ||
       to_char (max(END_INTERVAL_TIME), 'DD.MM.YYYY')
from dba_hist_snapshot;

Script is run against "SOME_SCHEMA" and query indexes bigger then 100 MB:
SQL> @index_usage SOME_SCHEMA 100
Here is reduced script's output on one of database which I was dealing with. Highlighted are some of indexes with special interest:
Table name                     Index name                Index type     Size MB Index operation       Executions
------------------------------ ------------------------- ---------- ----------- --------------------- ----------
D_DOPRIX                       D_DOP_DAT                 NORMAL          208.00 RANGE SCAN                    24
                               D_DOP_DATUM_NDX           NORMAL          193.00        -                       0
                               D_DOPX_PRIM               NORMAL          168.00 FAST FULL SCAN                 1
                               D_DOPX_R                  NORMAL          168.00 RANGE SCAN                     3
                                                                                FAST FULL SCAN                 1
                               D_DPX_ORGJED              NORMAL          176.00        -                       0
                               D_DPX_VP_RAD              NORMAL          240.00 RANGE SCAN                     1
                               D_PK_DOPRIX               NORMAL          312.00 SKIP SCAN                      1
                                                                                RANGE SCAN                     1
****************************** ************************* ********** -----------                       ----------
sum                                                                    1,945.00                               32

D_OSIGURANJE                   D_OSIG_OO                 NORMAL          208.00 FAST FULL SCAN                 1
                               D_OSIG_OS                 NORMAL          424.00 RANGE SCAN                     4
****************************** ************************* ********** -----------                       ----------
sum                                                                      632.00                                5

G_DNEVNIK                      DK_BRDOK_I                NORMAL          184.00 RANGE SCAN                    99
                               DK_GOD_FK_I               NORMAL          168.00 RANGE SCAN                   137
                                                                                FAST FULL SCAN                 2
                               DK_KON_FK_I               NORMAL          200.00 RANGE SCAN                   627
                                                                                FULL SCAN                     10
                               DK_MTR_FK_I               NORMAL          201.00 RANGE SCAN                   254
                               DK_ORG_FK_I               NORMAL          176.00 RANGE SCAN                   109
                               DK_PDS_FK_I               NORMAL          160.00 RANGE SCAN                    36
                               DK_PK                     NORMAL          185.00 RANGE SCAN (MIN/MAX)           1
                               DK_VRD_FK_I               NORMAL          168.00 RANGE SCAN                     6
****************************** ************************* ********** -----------                       ----------
sum                                                                    1,810.00                            1,281

G_PROMET_PP                    SCP_BROJ_URA              NORMAL          112.00 RANGE SCAN                     2
                               SCP_PK                    NORMAL          104.00 UNIQUE SCAN                  205
                                                                                RANGE SCAN (MIN/MAX)           1
                               SCP_SIFPP_KONTO_I         NORMAL          104.00 RANGE SCAN                   588
****************************** ************************* ********** -----------                       ----------
sum                                                                      424.00                              796

G_PROMET_S                     SP_MT_FK_I                NORMAL          104.00 RANGE SCAN                     1
                               SP_PK                     NORMAL          136.00 RANGE SCAN (MIN/MAX)           1
                               SP_SCP_FK_I               NORMAL          136.00 RANGE SCAN                    34
                                                                                FAST FULL SCAN                 1
****************************** ************************* ********** -----------                       ----------
sum                                                                      512.00                               37

I_DATPBZ_S002                  IDS2_AUT_I                NORMAL          189.00        -                       0
                               IDS2_DATSOC_I             NORMAL          136.00 RANGE SCAN                     1
                                                                                SAMPLE FAST FULL SCAN          1
                               IDS2_DATTRAN_I            NORMAL          152.00 RANGE SCAN                     2
                               IDS2_DATUM_I              NORMAL          152.00 RANGE SCAN                    10
                                                                                FULL SCAN                      1
                               IDS2_IDS1_FK_I            NORMAL          129.00 RANGE SCAN                    16
                               IDS2_PK                   NORMAL          144.00        -                       0
                               IDS2_PZNBR                NORMAL          144.00        -                       0
                               IDS2_VRSTA_PROD_I         NORMAL          113.00 FAST FULL SCAN                 1
****************************** ************************* ********** -----------                       ----------
sum                                                                    1,447.00                               32

NC_BESPLATNI                   NSB_CSC_I                 NORMAL          112.00 RANGE SCAN                    13
                               NSB_DATUM_I               NORMAL          112.00 RANGE SCAN                    26
                               NSB_GRUPA_I               NORMAL          120.00        -                       0
****************************** ************************* ********** -----------                       ----------
sum                                                                      344.00                               39

NC_CASH_UP_DETAIL_T            NCD_CASH_UP_I             NORMAL        9,984.00        -                       0
                               NCD_CASH_UP_IX_REDD       NORMAL        4,288.00        -                       0
                               NCD_PAIEMENT_I            NORMAL        3,200.00        -                       0
****************************** ************************* ********** -----------                       ----------
sum                                                                   17,472.00                                0

NC_CRED_T                      TRC_CONTENU_I             NORMAL          144.00 RANGE SCAN                    29
                                                                                SAMPLE FAST FULL SCAN          1
                               TRC_DATE_I                NORMAL          104.00 RANGE SCAN                     7
                                                                                SAMPLE FAST FULL SCAN          1
****************************** ************************* ********** -----------                       ----------
sum                                                                      496.00                               38

NC_SUBSCRIPTION_SALDO          NC_SUBSCRIPTION_SALDO_IXX NORMAL          128.00 RANGE SCAN                     1
                               NSO_CSC_I                 NORMAL          696.00 RANGE SCAN                     3
                               NSO_DATUM_I               NORMAL          568.00 RANGE SCAN                    11
                               NSO_DATUM2_I              NORMAL          569.00 RANGE SCAN                     5
                               NSO_GRUPA_I               NORMAL          712.00 RANGE SCAN                   136
                                                                                FAST FULL SCAN                 1
                               NSO_IND_I                 NORMAL          368.00 RANGE SCAN                    14
                                                                                FAST FULL SCAN                 3
                               NSO_PAR_I                 NORMAL          488.00 RANGE SCAN                 2,569
                                                                                FAST FULL SCAN                 2
                               NSO_PK                    NORMAL          432.00 UNIQUE SCAN                    5
                               NSO_PROD_I                NORMAL          432.00        -                       0
                               NSO_I                     NORMAL          560.00 FULL SCAN                      1
****************************** ************************* ********** -----------                       ----------
sum                                                                    6,521.00                            2,751

NC_TRANSACTION_ARZ_T           TRA_I                     NORMAL          184.00 FAST FULL SCAN                 1
                                                                                FULL SCAN                      1
                                                                                SAMPLE FAST FULL SCAN          1
                               TRA_ID_PAIEMENT_I         NORMAL          136.00 RANGE SCAN                     1
                                                                                SAMPLE FAST FULL SCAN          1
                               TRA_ID_VOIE_I             NORMAL          128.00 SAMPLE FAST FULL SCAN          1
                               TRA_ISSUER_I              NORMAL          105.00 RANGE SCAN                     3
                                                                                SAMPLE FAST FULL SCAN          1
                               TRA_NUM_TR_I              NORMAL          112.00 RANGE SCAN                    13
                                                                                SAMPLE FAST FULL SCAN          1
                               TRA_RESEAU_I              NORMAL          160.00 RANGE SCAN                    13
                                                                                SAMPLE FAST FULL SCAN          1
                               TR_ARZ_BROJ_CSC_I         NORMAL          145.00 RANGE SCAN                     5
                                                                                SAMPLE FAST FULL SCAN          1
                               TR_ARZ_DATUM_IZLAZA_I     NORMAL          168.00 RANGE SCAN                 1,037
                                                                                FULL SCAN                      4
                                                                                SAMPLE FAST FULL SCAN          1
                               TR_ARZ_DATUM_ULAZA_I      NORMAL          153.00 RANGE SCAN                   447
                                                                                FULL SCAN                      1
                                                                                SAMPLE FAST FULL SCAN          1
                               TR_ARZ_KLASA_I            NORMAL          128.00 SAMPLE FAST FULL SCAN          1
                               TRA_TICK_IDX              NORMAL          128.00 RANGE SCAN                   408
                                                                                SAMPLE FAST FULL SCAN          1
****************************** ************************* ********** -----------                       ----------
sum                                                                    3,343.00                            1,945

NC_TRANSACTION_MODE7_T         TRM7_BROJ_CSC_I           NORMAL          193.00 RANGE SCAN                     2
                                                                                SAMPLE FAST FULL SCAN          1
                               TRM7_CLASSE_I             NORMAL          152.00 SAMPLE FAST FULL SCAN          1
                               TRM7_DATE_I               NORMAL          169.00 RANGE SCAN                     2
                                                                                SAMPLE FAST FULL SCAN          1
                               TRM7_DATE2_I              NORMAL          184.00 RANGE SCAN                    65
                                                                                SAMPLE FAST FULL SCAN          1
                               TRM7_GP_I                 NORMAL          199.00 RANGE SCAN                     1
                                                                                SAMPLE FAST FULL SCAN          1
                               TRM7_LENGTH_I             FUNCTION-B      129.00 SAMPLE FAST FULL SCAN          1
                                                         ASED NORMA

                               TRM7_OBS_MP_I             NORMAL          152.00 RANGE SCAN                     5
                                                                                SAMPLE FAST FULL SCAN          1
                               TRM7_OBS_SEQ_I            NORMAL          145.00 SAMPLE FAST FULL SCAN          1
                               TRM7_PAIEMENT_I           NORMAL          152.00 SAMPLE FAST FULL SCAN          1
                               TRM7_RESEAU_I             NORMAL          193.00 RANGE SCAN                     5
                                                                                SAMPLE FAST FULL SCAN          1
                               TRM7_TAB_CLASSE_I         NORMAL          152.00 SAMPLE FAST FULL SCAN          1
                               TRM7_TAB_PLAZA_I          NORMAL          192.00 SAMPLE FAST FULL SCAN          2
****************************** ************************* ********** -----------                       ----------
sum                                                                    3,102.00                               93

NC_TRANSACTION_OK_T            TROK_DATE_I               NORMAL          728.00 RANGE SCAN                    20
                               TROK_I                    NORMAL          536.00        -                       0
                               TROK_ID_VOIE_I            NORMAL          608.00        -                       0
                               TROK_KLASA_I              NORMAL          632.00        -                       0
                               TROK_OP_DATE_I            NORMAL          400.00        -                       0
                               TROK_PAIEMENT_I           NORMAL          616.00        -                       0
                               TROK_SVE_OK_I             NORMAL        1,280.00 RANGE SCAN                     1
                               TROK_TAB_RESEAU_I         NORMAL          728.00        -                       0
                               TROK_VOIE_I               NORMAL          624.00        -                       0
****************************** ************************* ********** -----------                       ----------
sum                                                                    6,152.00                               21

NC_TRANSACTION_PRIHOD_T        TRPRH_DATUM_I             NORMAL          872.00 RANGE SCAN                    15
                               TRPRH_ISNC_I              NORMAL          830.00        -                       0
                               TRPRH_IZLAZ_I             NORMAL          777.00 FAST FULL SCAN                 1
                               TRPRH_ULAZ_I              NORMAL          785.00        -                       0
****************************** ************************* ********** -----------                       ----------
sum                                                                    3,264.00                               16

NC_TRANSACTION_T               NC_TR_INSERT_DATE_I       NORMAL          936.00 RANGE SCAN                     1
                               RESEAU_GARE_I             NORMAL        1,024.00 RANGE SCAN                   331
                               TAB_RESEAU_I              NORMAL        1,024.00 RANGE SCAN                     7
                               TR_BROJ_CSC_I             NORMAL          360.00 RANGE SCAN                     7
                               TR_DATUM_IZLAZA_I         NORMAL        1,024.00 RANGE SCAN                 1,312
                                                                                FULL SCAN                      2
                               TR_DATUM_ULAZA_I          NORMAL          815.00 RANGE SCAN                    50
                               TR_GRUPA_PROIZVODA_I      NORMAL          608.00        -                       0
                               TR_ID_OBS_MP_I            NORMAL          832.00        -                       0
                               TR_ID_OBS_SEQUENCE_I      NORMAL          640.00        -                       0
                               TR_ID_PAIEMENT_I          NORMAL          768.00 RANGE SCAN                   447
                               TR_MATRICULE_I            NORMAL        1,152.00        -                       0
                               TR_MODE_REGLEMENT_I       NORMAL          896.00 RANGE SCAN                     5
                               TR_MOP_ISSUER_CODE_I      NORMAL          168.00        -                       0
                               TR_RTRIM_I                NORMAL          592.00        -                       0
                               TR_TDATE_I                NORMAL        1,216.00 RANGE SCAN                    73
                               TR_TICKET_I               NORMAL        1,024.00 RANGE SCAN                   436
****************************** ************************* ********** -----------                       ----------
sum                                                                   14,103.00                            2,671

NC_TRANSACTION_ULAZ_T          TRU_DATUM_IZLAZA_I        NORMAL          952.00 RANGE SCAN                   731
                                                                                FAST FULL SCAN                 1
                               TRU_DATUM_ULAZA           NORMAL          600.00        -                       0
                               TRU_GRUPA_PROIZVODA_I     NORMAL          992.00        -                       0
                               TRU_ID_OBS_SEQUENCE_I     NORMAL          688.00        -                       0
                               TRU_IZLAZ_DATE_I          NORMAL          616.00 RANGE SCAN                    20
                                                                                FULL SCAN                      7
                               TRU_MATRICULE_I           NORMAL        1,152.00        -                       0
                               TRU_RTRIM_I               NORMAL          992.00 RANGE SCAN                    15
                               TRU_TDATE_I               NORMAL        1,153.00        -                       0
****************************** ************************* ********** -----------                       ----------
sum                                                                    8,713.00                              774

NC_TRANSACTION_ULAZ_TT         TRUT_I                    NORMAL          774.00 RANGE SCAN                     1
                                                                                FULL SCAN                      1
                               TRUT_GRUPA_PROIZVODA_I    NORMAL          888.00 FULL SCAN                      1
                               TRUT_MATRICULE_I          NORMAL          848.00        -                       0
                               TRUT_RESEAU_GARE_I        NORMAL          784.00 FULL SCAN                      1
                               TRUT_RTRIM_I              NORMAL          888.00 FULL SCAN                      6
****************************** ************************* ********** -----------                       ----------
sum                                                                    4,956.00                               10

P_OPIZNOP                      IOO_OPI_FK_I              NORMAL          104.00 RANGE SCAN                    84
                               IOO_PK                    NORMAL          112.00 RANGE SCAN                    42
                                                                                FAST FULL SCAN                 1
****************************** ************************* ********** -----------                       ----------
sum                                                                      328.00                              127

Z_PLACENO                      PLC_MT_I                  NORMAL          166.56 SAMPLE FAST FULL SCAN          1
                               PLC_OBP_I                 NORMAL          136.00 RANGE SCAN                    13
****************************** ************************* ********** -----------                       ----------
sum                                                                      302.56                               14

154 rows selected.

Showed only indexes in TAB schema whose size > 100 MB in period:

What is interesting here (keep in mind that monitoring period is 46 days!):
  1. NC_CASH_UP_DETAIL_T table was probably a play yard for some testing and player has forget it about that. After public mail announcement, drop all three indexes ASAP. But if you have space leave it-looks like this table is forgotten in your storage!
  2. All indexes with "Executions" value=0 has never been used in monitored period. Reason may be poor quality (against underlying data) or change of CBO plans. These indexes are highly on the list for purge-especially if they are big or they are under table with big use (on other indexes i.e. NSO_PROD_I)!
  3. Big indexes with small number of executions, especially those with just one execution, are really candidate to be purged and created/dropped for each execution
  4. Indexes where FULL SCAN is using mode are candidates for rebuild or checking it's quality*
  5. Tables which have many indexes with small index usage are candidates for relation model redesign
  6. Indexes with huge number of executions should be observed a lot. Rebuild it when usage in next period decrease for more then 15%-25%.

Index quality*

Mentioned index quality was firstly published on Jonathan Lewis blog and Dan has shown it's version:
 * File:        Index_Info.sql
 * Type:        SQL*Plus script
 * Author:      Dan Hotka
 * Date:        04-16-2009
 * Description:
 *      SQL*Plus script to display Index Statistics in relation to clustering factor
 *      Script originated from Jonathan Lewis bug I have heavily modified it
 * Modifications:

set linesize 300
spool index_info.txt

SELECT i.table_name, i.index_name, t.num_rows, t.blocks, i.clustering_factor, 
case when nvl(i.clustering_factor,0) = 0                       then 'No Stats'
     when nvl(t.num_rows,0) = 0                                then 'No Stats'
     when (round(i.clustering_factor / t.num_rows * 100)) < 6  then 'Excellent    '
     when (round(i.clustering_factor / t.num_rows * 100)) between 7 and 11 then 'Good'
     when (round(i.clustering_factor / t.num_rows * 100)) between 12 and 21 then 'Fair'
     else                                                           'Poor' 
     end  Index_Quality,
i.avg_data_blocks_per_key, i.avg_leaf_blocks_per_key, 
to_char(o.created,'MM/DD/YYYY HH24:MI:SSSSS') Created
from user_indexes i, user_objects o, user_tables t
where i.index_name = o.object_name
  and i.table_name = t.table_name
order by 1;

spool off
Idea of this script is to measure index quality. If index has bad ratio of index_clustering_factor/table_rows it's usage may be worse then full table scan! There are mainly two reasons why index has bad quality (without deeper coverage any of these terms):
  1. Index is fragmented-solution is to rebuild index
  2. Underlying table data are in that order that usage of index is really doubt-able. Solution is to drop it (make invisible in 11g!) and try to execute query to see performance. In a case of huge performance downgrade think about creation of index before query and drop after is the best solution for rarely run queries.
And for some interesting indexes in first output, a little modified index quality result is here.
SQL> @index_q NSO_I
Data represented for INDEX like "NSO_I"

                            Table      Table            Index Data Blks Leaf Blks        Clust Index
Table                        Rows     Blocks Index    Size MB   per Key   per Key       Factor Quality
--------------------- ----------- ---------- ------ --------- --------- --------- ------------ -----------
NC_SUBSCRIPTION_SALDO  22,461,175     415103 NSO_I     560.00         9         1   21,749,007 1-Poor
This is classical representative of index with enormous clustering factor who may be a candidate for rebuild (very doubtful on first look) and if that doesn't help then recreate/drop should eb an option if not and pure drop!

SQL> @index_q TRA_I
Data represented for INDEX like "TRA_I"

                            Table      Table            Index Data Blks Leaf Blks        Clust Index
Table                        Rows     Blocks Index    Size MB   per Key   per Key       Factor Quality
--------------------- ----------- ---------- ------ --------- --------- --------- ------------ -----------
NC_TRANSACTION_ARZ_T    5,815,708     137742 TRA_I     184.00        12         1      222,104 5-Excellent
This is representative of high quality index who is unused (used only twice). For such an index I'll recommend deeper analyze for involved SQLs. But this is not a candidate for purge in any mean.

SQL> @index_q TROK_I
Data represented for INDEX like "TROK_I"

                           Table      Table             Index Data Blks Leaf Blks        Clust Index
Table                       Rows     Blocks Index     Size MB   per Key   per Key       Factor Quality
--------------------- ---------- ---------- ------- --------- --------- --------- ------------ -----------
NC_TRANSACTION_OK_T   24,492,333     851796 TROK_I     536.00         1         1   21,977,784 1-Poor
This index has never been used-highly recommended for purge!

SQL> @index_q TRUT_I
Data represented for INDEX like "TRUT_I"

                           Table      Table             Index Data Blks Leaf Blks        Clust Index
Table                       Rows     Blocks Index     Size MB   per Key   per Key       Factor Quality
--------------------- ---------- ---------- ------- --------- --------- --------- ------------ -----------
NC_TRANSACTION_ULAZ_TT   840,179      27437 TRUT_I     774.00         1         1      731,609 1-Poor
This index is pretty big (774 MB), with low index quality, and use only twice. Recommendation rebuild and, if that doesn't help, purge.

The end

Manipulating indexes and keep them in good shape are in many cases very important. Fortunately, perfect Oracle algorithms for index balance, on normally driven OLTP database (without many deletion or purging), make us less worry in this DBA area. However checking their usage is not bad idea from time to time. Hope that this article helps someone in this task.



  1. count between 0 and 5
    should be
    count between 1 and 5


  2. Correct!

    However, it is not a bug it is a "feature"!

  3. PPPP_VALUE_I shows as having had 1 full scan. However it may also have had 10,000 range scans in the same period, which wouldn't appear as the count is beyond the bounds.

    Is that correct, or is there something that I've mis-understood ?

  4. Gary,
    No it is not correct.

    Mine solution shows all occurrences of index/index_operation combination (look at group by clause).

    To prove that, please look at "IR_DJK_KONTROLIRAN_OD_FK_I". It has two entries (FULL SCAN and RANGE SCAN). If (for that index) there was "SAMPLE FAST FULL SCAN" or "FAST FULL SCAN" usage, it will be in the list as well.

    So one occurrence in the list means really just one use in monitored period.

  5. Hi!
    Regardless I didn't realize comment from Gary in the firs place, now I found out that something was wrong in script.

    After reviewing, now should all be OK-and I have expand possibility with additional parameter and ability to see unused indexes as well.

    Because I have modified script, output has been changed (as well as some data because "something was purged") I have posted new output so previous comment from Gary may not be found.

    THX, for pointing me a problem and sorry for misunderstanding in first place.

  6. interesting article

    its the script also working in local partitioned indexes ???

    1. Script work with any kind of index (look in dba_indexes entries)

  7. This comment has been removed by the author.

  8. Hi,

    Your posts are awesome. Could you share the script name "index_q" please?


  9. Hi!

    THX for an info ... Here it is:

    set linesize 145
    set pagesize 1000

    clear computes
    clear breaks

    break on TABLE_NAME on NUM_ROWS on BLOCKS

    column owner format a14 heading 'Index owner'
    column table_name format a25 heading 'Table'
    column index_name format a25 heading 'Index'
    column num_rows format 999G999G990 heading 'Table|Rows'
    column MB format 9G990 heading 'Index|Size MB'
    column blocks heading 'Table|Blocks'
    column num_blocks format 9G990 heading 'Data|Blocks'
    column avg_data_blocks_per_key format 999G990 heading 'Data Blks|per Key'
    column avg_leaf_blocks_per_key format 999G990 heading 'Leaf Blks|per Key'
    column clustering_factor format 999G999G990 heading 'Clust|Factor'
    column Index_Quality format A13 heading 'Index|Quality'

    SPOOL index_quality

    PROMPT Data represented for INDEX like "%&&1%"

    o.bytes/1048576 MB,
    CASE when nvl(i.clustering_factor,0) = 0 then '0-No Stats'
    when nvl(t.num_rows,0) = 0 then '0-No Stats'
    when (round(i.clustering_factor / t.num_rows) * 100) < 6 then '5-Excellent'
    when (round(i.clustering_factor / t.num_rows) * 100) between 7 and 11 then '4-Very Good'
    when (round(i.clustering_factor / t.num_rows) * 100) between 12 and 15 then '2-Good'
    when (round(i.clustering_factor / t.num_rows) * 100) between 16 and 25 then '2-Fair'
    else '1-Poor'
    END Index_Quality
    from dba_indexes i,
    dba_segments o,
    dba_tables t
    i.index_name like upper ('%&&1%') and
    i.owner=t.owner and
    i.table_name = t.table_name and
    i.owner=o.owner and
    i.index_name = o.segment_name
    order by TABLE_NAME, NUM_ROWS, BLOCKS, Index_Quality DESC;


    PROMPT Usage: "index_quality [SCHEMA]"

    PROMPT RUN index_usage.sql (for index use) and index_frag.sql (for rebuild indexes)

  10. Hi!
    Now I found out that was a small bug in original script and in last post as well.
    when (round(i.clustering_factor / t.num_rows) * 100) < 6 then '5-Excellent'
    when (round(i.clustering_factor / t.num_rows) * 100) between 7 and 11 then '4-Very Good'
    when (round(i.clustering_factor / t.num_rows) * 100) between 12 and 15 then '2-Good'
    when (round(i.clustering_factor / t.num_rows) * 100) between 16 and 25 then '2-Fair'

    change to

    when (round(i.clustering_factor / t.num_rows * 100)) < 6 then '5-Excellent'
    when (round(i.clustering_factor / t.num_rows * 100)) between 7 and 11 then '4-Very Good'
    when (round(i.clustering_factor / t.num_rows * 100)) between 12 and 15 then '2-Good'
    when (round(i.clustering_factor / t.num_rows * 100)) between 16 and 25 then '2-Fair'


  11. Someone has posted a script on a blog which will work without the need of AWR (license). Is this alternate script any good?

    1. @anonymous
      Your solution is interesting with next limitations:
      1) v$ vies are part of the data dictionary. So they are cleared when instance is restarted. 2) v$ cannot keep entries if instance is up for a longer time. So numbers of executions are wrong.
      3) Your solution does not says what is with indexes not used.

    2. Let me show with numbers. Query which period is in dictionary:

      select min (timestamp) firstdate, max(timestamp) lastdate
      from gv$sql_plan c
      where c.operation = 'INDEX'

      ------------------- -------------------
      19.03.2012 09:29:25 20.03.2012 16:19:40

      As you see, 7 hours is in case.

  12. Hi Damir

    Excellent post and very useful script. Could you please share index_frag.sql script?

    Thank you

    1. Hi!

      This is not part of solution presented here but mine (untested scripts) which I do not share ...
      Hope you do understand.

  13. Hi Damir,
    Thank you for giving us more help.
    In your post you mentioned below:
    All indexes with "Executions" value=0 has never been used in monitored period. Reason may be poor quality (against underlying data) or change of CBO plans.
    As you know, all execution plan are from AWR and default top sql is 30,It means some sql would not be gathered.
    Does the execution value 0 still mean the index never used when I pass the second parameter 5MB or less?

    Hope you to give me a bright.


    1. Correct.
      This sql's limited number is why I use this for 100 MB and bigger indexes, whose sql are always in first 30 in snapshot.
      With 5 MB indexes you should be really careful if your database has bigger (and heavier) queries


  14. Hi Damir,
    Thank you for your reply.
    Still have some question. I found some total size for index columns were not correct.e.g.

    ****************************** ************************* ********** ----------- ----------
    sum 328.00 127

    For the table P_OPIZNOP,the real result of index columns should be 216,but current value is 328.
    Could you explain that?


    1. As you see:
      104.00 RANGE SCAN                    84
                                     IOO_PK                    NORMAL          112.00 RANGE SCAN                    42
                                                                                      FAST FULL SCAN                 1
      ****************************** ************************* ********** -----------                       ----------
      sum                                                                      328.00     

      Sum is not correct because it works only when each table/index combination is shown once ... this is how sql report feature do. Haven't find a way to fix this. If you remove "Index operation" coulmn it will make correct sum ...

  15. That was enjoyable to read, thanks for posting it.

    my webpage Bing (

  16. why can't you use check querying v$sql?

    1. v$sql has a small data retention. After the cursor has been aged out of the shared pool is gone.

  17. Why setting retention for 46 days is better? I know it's longer time than 30 days, but why it's better?:)
    Script is awesome! Thank you :)

    1. You are covering full month and has some spare days ... you know that on 1st each month some scripts runs ... so somitemes they failed and prolonged a few days...and this is exactly why you have "spare 15 days"

  18. what did you mean by "Underlying table data are in that order that usage of index is really doubt-able" ?

    1. if you have situation that using an index would be worse that having full table scan than underlying table data are in that order that are not appropriate for using that index.

  19. Hello Damir,

    Your solution is great and we are using it on version 11! It works like a charm! However, on version it is not working. It returns only zero executions on all indexes. Have you tested it on version 12?


    1. It should work.
      Are you conencted to container database or pluggable database?
      I assume this is your problem.

      Other think that might be a problem is that AWR didn't took any of snapshots of index use...this is mostly for very fast queries ...


Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.

Sign by Danasoft - Get Your Sign