Any script to find which index has never been used.?

Quote:

---

Originally posted by DaPi
John Weeg in http://www.dbazine.com/weeg12.html gives a solution by periodically sampling the objects in the buffers.

---

Hmmmm . . . does chao_ping's observation kick this one out ? ? ?

another 9i method might be to look at v$segment_statistics, which will tell you the number of logical io's (among other things) that have occurred on a table or index.

Run this to find the redundant indexes:

column redundant_index format a39
column sufficient_index format a39

select
o1.name||'.'||n1.name redundant_index,
o2.name||'.'||n2.name sufficient_index
from
sys.icol$ ic1,
sys.icol$ ic2,
sys.ind$ i1,
sys.obj$ n1,
sys.obj$ n2,
sys.user$ o1,
sys.user$ o2
where
ic1.pos# = 1 and
ic2.bo# = ic1.bo# and
ic2.obj# != ic1.obj# and
ic2.pos# = 1 and
ic2.intcol# = ic1.intcol# and
i1.obj# = ic1.obj# and
bitand(i1.property, 1) = 0 and
( select
max(pos#) * (max(pos#) + 1) / 2
from
sys.icol$
where
obj# = ic1.obj#
) =
( select
sum(xc1.pos#)
from
sys.icol$ xc1,
sys.icol$ xc2
where
xc1.obj# = ic1.obj# and
xc2.obj# = ic2.obj# and
xc1.pos# = xc2.pos# and
xc1.intcol# = xc2.intcol#
) and
n1.obj# = ic1.obj# and
n2.obj# = ic2.obj# and
o1.user# = n1.owner# and
o2.user# = n2.owner#
/

hi,
Another way of solving this ( i think ) is to let generate outlines from all Queries against the database.
An outline is a stored execution-path from a Query, The information which index was/will be used is stored.

Orca
:cool:

This should work even with 8i Databases

Hi Julian,

Thanks for that. Getting rid of the LOGICALLY redundant indexes should be the first step - since both "redundant" & "sufficient" might end up in any list of used indexes (if ever we can work out how to make one).

http://www.ixora.com.au/scripts/sql/...nt_indexes.sql

This has been on my back burner for a while, but some progress today. All the code is in one schema and I don't actually *use* stored outlines. This is what I'm trying:

1) Since I want (as the first step) the indexes actually used (not those from functionality that no one uses) I want to sample v$sqltext at some interval and build the sql:

Code:

---

CREATE TABLE DJP$SQLAREA (sql_text varchar2(4000), comperr number);

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 = 'MYUSER'
        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 IS NOT NULL THEN
                                INSERT INTO DJP$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 DJP$SQLAREA VALUES(FULL_LINE,0);
        COMMIT;
END;
/

---

2) Then I parse this to get the outline:

Code:

---

DECLARE

        CURSOR SQL_CACHE IS
        SELECT SQL_TEXT FROM DJP$SQLAREA FOR UPDATE;

        l_cursor INTEGER;

BEGIN
        EXECUTE IMMEDIATE('ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE');

        l_cursor := DBMS_SQL.OPEN_CURSOR;
        FOR sql_C IN sql_cache LOOP

                BEGIN
                        dbms_sql.parse(l_cursor, sql_c.sql_text, DBMS_SQL.NATIVE);
                EXCEPTION WHEN OTHERS THEN
                        UPDATE DJP$SQLAREA SET comperr=1 WHERE CURRENT OF sql_cache;
                END;
       
        END LOOP;
        DBMS_SQL.CLOSE_CURSOR(l_cursor);
        COMMIT;

        EXECUTE IMMEDIATE('ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE');
END;
/

---

Code generated in parallel query fails to parse, but I think the original SQL is captured.



3) OUTLN.OL$HINTS.HINT_TEXT contains the name of the index used.


Cany anyone see flaws in this approach?