Monday, November 29, 2010

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!

No comments :

Post a Comment

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign