How to see index that has never been used?

More direct approach in index analyze is looking for index that are has never been used. When I say "never" keep in mind that this thesis is based on AWR window definition, who has to be at least 30 days long.

So if you accept that index is not necessary if it has not been used for 36 days, then idea how to find those is pretty easy:

1. Find all indexes in chosen schema
2. Remove indexes which are defined for constraints (they might never be used in real query) but are very important because of known locking problems on FK columns
3. Remove indexes which are used at least once (from AWR history)

What is left are unused indexes!

Here is the script for that:

/* ---------------------------------------------------------------------------
 CR/TR#  :
 Purpose : Shows index that are NOT IN USE 
           

 Date    : 25.11.2010.
 Author  : Damir Vadas, damir.vadas@gmail.com

 Remarks : run as DBA user
           Must have AWR run because sql joins data from there

           First parameter is schema and second is min index size to show
           
 Changes (DD.MM.YYYY, Name, CR/TR#):
          30.11.2010, Damir Vadas
                      Added second parameter (index size)
--------------------------------------------------------------------------- */
clear breaks
clear computes

break on TABLE_NAME skip 2
compute sum of MB on TABLE_NAME

SET TIMI OFF

set linesize 140
set pagesize 10000


col TABLE_NAME for a35
col A_INDEX_NAME for a36
col INDEX_CREATED for a19
col MB for 999G990D990 Heading "Size MBytes"

with q as (
   -- all indexes 
   select
      owner      a_owner,
      index_name a_index_name
     from
       dba_indexes di
     where
        owner = '&&1'
  minus
    -- index used for for constraints
    select
       index_owner owner,
       index_name
    from
       dba_constraints dc
    where
       index_owner = '&&1'
  minus
    -- indexes used in AWR
    select distinct
      P.OBJECT_OWNER OWNER,
      p.object_name A_INDEX_NAME
    from
      dba_hist_sql_plan p
    where
      p.object_owner = '&&1' and
      p.operation LIKE '%INDEX%'
)
SELECT 
       TABLE_NAME,
       A_INDEX_NAME,
       to_char(CREATED,'DD.MM.YYYY HH24:MI:SS') INDEX_CREATED,
       d.bytes/1048576 MB
FROM Q,
     dba_segments d,
     dba_indexes i,
     dba_objects o
WHERE 
     d.bytes > 1048576 * &&2 and
     d.owner=q.a_owner and  
     d.segment_name=q.a_index_name and
     i.owner=q.a_owner and
     I.index_name=q.a_index_name and
     O.OWNER=q.a_owner and
     o.object_name=q.a_index_name      
ORDER BY 1, 2
;

PROMPT Showed only NOT USED indexes in &&1 schema in period:
SET HEAD OFF;
select to_char (min(BEGIN_INTERVAL_TIME), 'DD.MM.YYYY')
       || '-' ||
       to_char (max(END_INTERVAL_TIME), 'DD.MM.YYYY')
from dba_hist_snapshot;

And here is sample output of this script for indexes bigger then 100MB whose owner is "SOME_SCHEMA"

SQL> @index_usagen SCHEMA_NAME 100

TABLE_NAME                          A_INDEX_NAME                         INDEX_CREATED        Size MBytes
----------------------------------- ------------------------------------ ------------------- ------------
D_DOPRIX                            D_DOP_DATUM_NDX                      08.05.2010 16:44:04      193.000
                                    D_DPX_ORGJED                         08.05.2010 16:42:34      176.000
***********************************                                                          ------------
sum                                                                                               369.000


I_DATPBZ_S002                       IDS2_AUT_I                           08.05.2010 17:00:37      189.000
                                    IDS2_PZNBR                           08.05.2010 17:00:52      144.000
***********************************                                                          ------------
sum                                                                                               333.000


NC_BESPLATNI                        NSB_GRUPA_I                          08.05.2010 17:09:56      120.000
***********************************                                                          ------------
sum                                                                                               120.000


NC_CASH_UP_DETAIL_T                 NCD_CASH_UP_I                        08.05.2010 17:29:26   10,048.000
                                    NCD_CASH_UP_IX_REDD                  08.05.2010 18:04:57    4,288.000
                                    NCD_PAIEMENT_I                       08.05.2010 17:54:39    3,264.000
***********************************                                                          ------------
sum                                                                                            17,600.000


NC_SUBSCRIPTION_SALDO               NSO_PROD_I                           08.05.2010 18:35:01      432.000
***********************************                                                          ------------
sum                                                                                               432.000


NC_TRANSACTION_OK_T                 TROK_DATUM_ULAZA_I                   12.05.2010 18:27:51      536.000
                                    TROK_ID_VOIE_I                       08.05.2010 19:09:54      608.000
                                    TROK_KLASA_I                         08.05.2010 19:11:11      632.000
                                    TROK_OP_DATE_I                       08.05.2010 19:12:43      400.000
                                    TROK_PAIEMENT_I                      08.05.2010 19:14:04      616.000
                                    TROK_TAB_RESEAU_I                    08.05.2010 19:20:28      728.000
                                    TROK_VOIE_I                          08.05.2010 19:22:23      624.000
***********************************                                                          ------------
sum                                                                                             4,144.000


NC_TRANSACTION_PRIHOD_T             TRPRH_ISNC_I                         08.05.2010 19:34:24      830.000
***********************************                                                          ------------
sum                                                                                               830.000


NC_TRANSACTION_T                    TR_GRUPA_PROIZVODA_I                 08.05.2010 20:43:25      608.000
                                    TR_ID_OBS_MP_I                       08.05.2010 20:50:53      832.000
                                    TR_ID_OBS_SEQUENCE_I                 08.05.2010 21:03:11      640.000
                                    TR_MATRICULE_I                       08.05.2010 21:27:42    1,152.000
                                    TR_MOP_ISSUER_CODE_I                 08.05.2010 20:47:19      168.000
                                    TR_RTRIM_I                           08.05.2010 21:11:13      592.000
***********************************                                                          ------------
sum                                                                                             3,992.000


NC_TRANSACTION_ULAZ_T               TRU_DATUM_ULAZA                      11.11.2010 10:52:50      600.000
                                    TRU_GRUPA_PROIZVODA_I                08.05.2010 22:19:24      992.000
                                    TRU_ID_OBS_SEQUENCE_I                08.05.2010 22:24:43      688.000
                                    TRU_MATRICULE_I                      08.05.2010 22:36:47    1,152.000
                                    TRU_TDATE_I                          08.05.2010 22:55:09    1,153.000
***********************************                                                          ------------
sum                                                                                             4,585.000


NC_TRANSACTION_ULAZ_TT              TRUT_MATRICULE_I                     12.05.2010 14:28:13      848.000
***********************************                                                          ------------
sum                                                                                               848.000



29 rows selected.

Showed only NOT USED indexes in "SCHEMA_NAME" schema in period:

26.10.2010-01.12.2010

index_usage.sql to see indexes greater then 100 MB that are not used at all!

SQL>

The end

Before you drop some indexes (from previous result) ask your self two simple questions:

1. "Could it be possible that users are not using this app in this business area"?
   If question is affirmative-you have a chance to make pretty much damage. In the other hand, you'll see how your app is used! Think about some periodical job (i.e payroll) who really should be once in 36 days period!
2. If previous question was false,
   "Has DB design or cost plan changed so rapidly?".
   If this is true then you are on the right path for performance tuning but something in "strange" somewhere else ... which will cost you very soon.

Main difference between output from previous topic (where "Execution" columns value=0) is that this output is more clear then mentioned. However, result for unused indexes are the same!

And just for clarification. This method will immediately show you unused indexes in any moment (if you already has AWR up and running), while with "alter index .. monitor" you have to wait several period for any result.

Cheers!

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):

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 66240,           -- = 46 Days
    interval  => 15)              -- = 15 Minutes
  ;          
END;
/ 

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, damir.vadas@gmail.com

 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

break on TABLE_NAME skip 2 ON INDEX_NAME ON INDEX_TYPE ON MB 
compute sum of NR_EXEC on TABLE_NAME SKIP 2
compute sum of MB on TABLE_NAME SKIP 2


SET TIMI OFF
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 (
                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
                 GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
                HAVING SUM(S.BYTES) > 1048576 * &&2
        )
        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
;

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

SET HEAD ON
SET TIMI ON

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:

Index
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
                                                         L

                               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:

26.10.2010-30.11.2010

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.

Cheers!

How to redirect sqlplus result in Windows batch script

Recently I had a task to write a Windows batch script for Autonomous RMAN online backup.
In mentioned example, first part of problem is to get sequence numbers from v$log. These numbers will ensure backup of all necessary archive log files. In Linux bash it is very easy and looks like:

MIN1=`echo "select 'xX '||MIN(sequence#) from v\\$log where thread#=1;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`

But on Windows shell it was really a challenge, especially for me-a non Windows guy. So I decide to write this blog to be more like documentation for me and maybe for some others that may fall in same problem.

The solution

echo set timi off head off^&echo. select  MIN(sequence#) from v$log where thread#=1; | sqlplus -s "/ as sysdba" | findstr . > result.tmp
FOR /F %i IN (result.tmp) DO @set MIN1=%i
@echo %MIN1%
del result.tmp

And here is live result:

C:\>echo set timi off head off^&echo. select  MIN(sequence#) from v$log where thread#=1; | sqlplus -s "/ as sysdba" | findstr . > result.tmp

C:\>FOR /F %i IN (result.tmp) DO @set MIN1=%i

C:\>@echo %MIN1%
243

The end

I do not want to mention all the problems that I faced, but the main was "how to create new line" in windows shell (because of "head off and timi off" part that was needed to be passed to sqlplus initially).

As you may see, mine solution store semi result in result.tmp file. I was not able to make solution without file so if anyone of you find a way ... appreciate in front to be informed.

Cheers!

HROUG 2010

From 19.10-23.10, 15th HROUG conference took place in Rovinj, Croatia, but this time in beautiful hotel Katarina.

HROUG, what is abbreviation for Croatian Oracle Users Group, is annual meeting of Oracle users in Croatia.This meeting has it's purpose in several ways. People can see and hear some new stuff from Oracle technology, exchange their experiences, extend acquaintances and in all means see in short time Oracle Croatia market.

After every day's expert lessons, evening was fun for all ages. Atmosphere, as it is every year, through all 5 days, was really fantastic. This is big compliment to HROUG's spiritus movens, Mr. Davor Ranković (who is president of the HROUG board in the same time), Mr. Vladimir Radić (vice president of the HROUG board) and all others people involved in organization, who has done their maximum to feel us as good as possible.

This year I had two lectures/workshops and mine obligation was to post source codes of it on this blog.

Apex-authentication concept

Presentation from that lecture can be downloaded as Adobe .pdf file or as MS Office 2010 .pptx file.
Demo Apex application can be downloaded from this link. Import this app in any existing Apex workspace (in mine example it was "HROUG").
For the end import TOOLS schema expdp file, which can be downloaded from this link. Export is done with 11.2 database. To have consistent import first run tools_user.sql script which create TOOLS user. For all other details please look in log file from that ZIP.
After import place missing grants to user that exists in your database (if Apex app reside in different schema then "HROUG").

DBA tips and tricks

Presentation from that lecture can be downloaded from Adobe .pdf file or as MS Office 2010 .pptx file.
All of codes from this lecture is already published on mine blog.

Blocking session

• Blocking session ... detect, unblock, notify
• Blocking session ... detect, unblock, notify (Part II)

Blocking records

• Blocking session ... show table records that are blocked
• Blocking session ... show table records that are blocked (Part II)

Blocked package/procedure/function

• Compile blocked package/procedure/function
• Compile blocked package/procedure/function (Part II)

"Real life" blocking session examples caught on mail, can be seen in MS Outlook 2010 .msg files stored inside ZIP file.

The End

When I got more time, I'll write a topic on theme "Secure auto login in Apex".
Until then... as always-Cheers!