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:
- Find all indexes in chosen schema
- 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
- Remove indexes which are used at least once (from AWR history)
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:- "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! - 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!
