-
How to find unused index in 8.1.7.4
Hi Friends,
I am using oracle 8.1.7.4 on sun solaris,
We have a table which has quite a few indexes and i wanted to know which index is being used and wanted to drop the unused indexes.
can u please let me know how i can do this.
I know i can query v$sqlarea to find out all the query running on this table and do an explain plan on all those queries and find the unused index and drop them. But i would like to know if there is any other simple way.
regards
anandkl
anandkl
-
Check http://www.dbasupport.com/forums/sho...t=unused+index
There's some good general subject related information on this thread.
-
But this script does not give the list of unused indexes.
regards
anandkl
anandkl
-
Sorry, didn't see that.
Here's an interesting answer I found on faqchest.com
"an 'unused index' is any index that the Oracle query optimizer will never choose for any SQL statement in the entire application."
-Cary Millsap
He suggests explaining all the code in the application to determine if the optimizer will ever choose an index.
It's an interesting approach. Not my first choice.
I think I'd try to minimize the indexes in a test environment, then test the app and add as needed. Or remove one index and test any application componient that touched it's root table.
There's probably alot of appropriate/proper testing considerations that need to be thought through.
Best of luck.
-
We talked about this a while back: http://forums.dbasupport.com/forums/...0&pagenumber=2 The method in my last post of this thread (much revised) seems to be giving sensible results and I have started gentle pruning with no adverse effects.
-
Originally posted by DaPi
The method in my last post of this thread (much revised) seems to be giving sensible results and I have started gentle pruning with no adverse effects.
Dapi :
Did you test ?
I wonder if this part of the code is logically correct & will execute without error
Code:
DECLARE
CURSOR SQL_LINES IS
SELECT t.HASH_VALUE, t.PIECE, t.SQL_TEXT
FROM v$sqltext t, v$sqlarea a, all_users u
WHERE t.hash_value= a.hash_value
AND u.user_id = a.parsing_user_id
AND u.username = 'MY_USER'
ORDER BY t.HASH_VALUE, t.PIECE, t.SQL_TEXT;
LAST_HASH NUMBER := 0;
FULL_LINE VARCHAR2(4000) := NULL;
BEGIN
FOR S IN SQL_LINES LOOP
IF s.hash_value <> last_hash THEN
IF FULL_LINE <> NULL THEN
INSERT INTO IDX$SQLAREA VALUES(FULL_LINE,0);
END IF;
FULL_LINE := s.sql_text;
last_hash := s.hash_value;
ELSE
FULL_LINE := FULL_LINE||s.sql_text;
END IF;
END LOOP;
INSERT INTO IDX$SQLAREA VALUES(FULL_LINE,0);
COMMIT;
END;
Ok lets consider for one val of HASH
Code:
WW15_DBA> ed
Wrote file afiedt.buf
1 SELECT t.HASH_VALUE, t.PIECE, t.SQL_TEXT
2 FROM v$sqltext t, v$sqlarea a, all_users u
3 WHERE t.hash_value= a.hash_value
4 AND u.user_id = a.parsing_user_id
5 AND u.username = 'WWCIW_DBA' and t.hash_value='27553361'
6* ORDER BY t.HASH_VALUE, t.PIECE, t.SQL_TEXT
WW15_DBA> /
HASH_VALUE PIECE SQL_TEXT
-------------------- -------------------- ----------------------------------------------------------------
27553361 0 SELECT t.HASH_VALUE, t.PIECE, t.SQL_TEXT FROM v$sqltext t, v$s
27553361 1 qlarea a, all_users u WHERE t.hash_value= a.hash_value AND u
27553361 2 .user_id = a.parsing_user_id AND u.username = 'WWCIW_DBA' and
27553361 3 rownum<11 ORDER BY t.HASH_VALUE, t.PIECE, t.SQL_TEXT
s.hash_value <> last_hash is satisfied when s.hash_value changes but FULL_LINE <> NULL is not satisfied
and will never., so string dosent get inserted at all ( except at the end )
PS
Code:
WW15_DBA> truncate table IDX$SQLAREA;
Table truncated.
WW15_DBA> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR SQL_LINES IS
3 SELECT t.HASH_VALUE, t.PIECE, t.SQL_TEXT
4 FROM v$sqltext t, v$sqlarea a, all_users u
5 WHERE t.hash_value= a.hash_value
6 AND u.user_id = a.parsing_user_id
7 AND u.username = 'WWCIW_DBA'
8 AND t.HASH_VALUE = '2755361'
9 ORDER BY t.HASH_VALUE, t.PIECE, t.SQL_TEXT;
10 LAST_HASH NUMBER := 0;
11 FULL_LINE VARCHAR2(4000) := NULL;
12 BEGIN
13 FOR S IN SQL_LINES LOOP
14 IF s.hash_value <> last_hash THEN
15 IF FULL_LINE <> NULL THEN
16 INSERT INTO IDX$SQLAREA VALUES(FULL_LINE,0);
17 END IF;
18 FULL_LINE := s.sql_text;
19 last_hash := s.hash_value;
20 ELSE
21 FULL_LINE := FULL_LINE||s.sql_text;
22 END IF;
23 END LOOP;
24 INSERT INTO IDX$SQLAREA VALUES(FULL_LINE,0);
25 COMMIT;
26* END;
27 /
PL/SQL procedure successfully completed.
WW15_DBA> select * from IDX$SQLAREA;
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
COMPERR
--------------------
0
Ok, how about this one?
Code:
DECLARE
CURSOR SQL_LINES IS
SELECT t.HASH_VALUE, t.PIECE, t.SQL_TEXT
FROM v$sqltext t, v$sqlarea a, all_users u
WHERE t.hash_value= a.hash_value
AND u.user_id = a.parsing_user_id
AND u.username = 'MY_USER'
ORDER BY t.HASH_VALUE, t.PIECE, t.SQL_TEXT;
LAST_HASH NUMBER := 0;
FULL_LINE VARCHAR2(4000) ;
BEGIN
SELECT min(t.HASH_VALUE) into LAST_HASH
FROM v$sqltext t, v$sqlarea a, all_users u
WHERE t.hash_value= a.hash_value
AND u.user_id = a.parsing_user_id
AND u.username = 'MY_USER'
;
FOR S IN SQL_LINES LOOP
IF s.hash_value <> last_hash THEN
/* IF FULL_LINE <> NULL THEN */
INSERT INTO IDX$SQLAREA VALUES(FULL_LINE,0);
/* END IF; */
-- Why is this IF FULL_LINE <> NULL required ? if you have you cant compare with NULL coz result will be null
-- so insert never happens .
FULL_LINE := s.sql_text;
last_hash := s.hash_value;
ELSE
FULL_LINE := FULL_LINE||s.sql_text;
END IF;
END LOOP;
INSERT INTO IDX$SQLAREA VALUES(FULL_LINE,0);
COMMIT;
END;
Last edited by abhaysk; 08-13-2003 at 07:24 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
FULL_LINE <> NULL is not satisfied and will never
Just making sure you're all awake (Can't for the life of me see how I skrewed it up like that when copying.) It works quite well with the obvious correction.
Last edited by DaPi; 08-13-2003 at 08:33 AM.
-
Originally posted by DaPi
Just making sure you're all awake (Can't for the life of me see how I skrewed it up like that when copying.) It works quite well with the obvious correction.
DAPI :
well i am not sure if your last part (Stored_Outlines) using DBMS_PARSE is working as desired?
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
well i am not sure if your last part (Stored_Outlines) using DBMS_PARSE is working as desired?
Will you tell me what is not working, or do I have to guess?
-
Originally posted by abhaysk
IF FULL_LINE <> NULL THEN
WHERE THE HELL DID THIS COME FROM?
I've just been to look at the original post - it correctly has IS NOT NULL ? ? ? ? ?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|