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:
- Adding new index very often change current SQL plans (what, as Murphy says, mostly finish badly!)
- Set overhead on I/O and CPU in any DML operation on involved table (maintain index blocks)
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:
- Regardless how many times index was used it is recorded just once
- There is no other way to see any additional info on index usage
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 100Here 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!):- 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!
- 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)!
- 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
- Indexes where FULL SCAN is using mode are candidates for rebuild or checking it's quality*
- Tables which have many indexes with small index usage are candidates for relation model redesign
- 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): - Index is fragmented-solution is to rebuild index
- 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.
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!
