RE: sql question
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 1 Apr 2004 16:57:51 -0500
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@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of thump604@xxxxxxxxxxx
Sent: Thursday, April 01, 2004 4:13 PM
To: oracle-l@xxxxxxxxxxxxx
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
