DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: How to find unused index in 8.1.7.4

Hybrid View

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    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

  2. #2
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Check http://www.dbasupport.com/forums/sho...t=unused+index

    There's some good general subject related information on this thread.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    But this script does not give the list of unused indexes.

    regards
    anandkl
    anandkl

  4. #4
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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?

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
  •  


Click Here to Expand Forum to Full Width