| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql question
How many times each index used:
select SUBSTR(hint, INSTR(' ') + 1, LENGTH(hint) - INSTR(' ') - 1),
count(*)
from dba_outline_hints
where hint like '%INDEX%'
GROUP BY SUBSTR(hint, INSTR(' ') + 1, LENGTH(hint) - INSTR(' ') - 1);
List of indices not used:
Select index_name from user_indexes where index_name not in (
Select distinct SUBSTR(hint, INSTR(' ') + 1, LENGTH(hint) - INSTR(' ')
- 1)
from dba_outline_hints
where hint like '%INDEX%');
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of thump604_at_comcast.net
Sent: Thursday, April 01, 2004 4:13 PM
To: oracle-l_at_freelists.org
Subject: sql question
Okay folks...Hopefully someone can help and provide some pointers...
DB is 8174
I have gathered a great deal of outlines in order to identify index
usage.
I would like to document the indices in the database and if they were
used or unused and if they were used how many sql statments or outlines
referenced a given index.
I'm not sure how to start with such in effort and not make it a manual nightmare.
Hints look like:
select hint from dba_outline_hints where hint like '%INDEX%'
..
INDEX(S_OPTY S_OPTY_U1) INDEX(S_OPTY S_OPTY_U1) INDEX(S_OPTY S_OPTY_V2) INDEX(S_OPTY S_OPTY_U1) INDEX(S_OPTY S_OPTY_U1) INDEX(S_OPTY S_OPTY_V2) INDEX(S_CONTACT S_CONTACT_U1) INDEX(S_CONTACT S_CONTACT_U1) INDEX(S_CONTACT S_CONTACT_U1) INDEX(S_CONTACT S_CONTACT_V3) INDEX(S_CONTACT S_CONTACT_P1) INDEX(S_SRV_REQ S_SRV_REQ_U1) INDEX(S_SRV_REQ S_SRV_REQ_U1) INDEX(S_OPTY_CON S_OPTY_CON_U1) INDEX(S_SRV_REQ S_SRV_REQ_U1) INDEX(S_SRV_REQ S_SRV_REQ_U1) INDEX(S_ORDER S_ORDER_U1) INDEX(S_ORDER S_ORDER_U1)
How can I trim off the data to the left and right of the index name, count the number of times an index is listed in dba_outline_hints and list indices not used at all?
Probably pretty straight forward SQL, but it's one of my weaker areas.
Cheers
- David
 |
 |