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

Hi,

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

Not that I know of, unless that information is explicitly captured in some db trigger.

There are bigger brains than mine on this list... I'm interested in what they say.

No, there is (in general) no way to find this out, at least not until 9i. In 9i you have the ability to monitor the index usage.

no script, but there is a way.

Put the index into a dedicated tablespace and
use statspack to monitor whether there is read from
that tablespace.

Quote:

---

Originally posted by sysdba
no script, but there is a way.

Put the index into a dedicated tablespace and
use statspack to monitor whether there is read from
that tablespace.

---

When index is updated, isn't it read into memeory first?

Hi

Yes you can put the index in a dedicated tablespace and use v$filestat to mointor it..it would give you a crude way of finding out wheather a index is used or not..

regards
Hrishy

Quote:

---

Originally posted by jmodic
No, there is (in general) no way to find this out, at least not until 9i. In 9i you have the ability to monitor the index usage.

---

Please can u get details as to monitor usage of index on 9x

Abhay.

Hi

In 9i you have something called as v$object_useage..through which you can monitor the index useage to a certain extent..

alter index my_index monitoring useage;

it cant tell us how many times a index has been used or nor hwne it was used..

The used column in v$object_useage will have an yes if the index was used since we turned on monitoring

But read the oracle docs before using it..

regards
Hrishy

Quote:

---

Originally posted by sysdba
no script, but there is a way.

Put the index into a dedicated tablespace and
use statspack to monitor whether there is read from
that tablespace.

---

Well, the question was something like that:

"I have 821 indexes on my system and I want to determine which of those 821 indexes is never used."

So you are suggesting to create 821 datafiles, put each index into a sepparate datafile and monitor datafile usage? Or are you suggesting to create one separate tablespace/datafile, and put each index into that tablespace one at a time for a monitoring period? Khm... :rolleyes: :D

Anyway, even if you try to do this index-isolation method you'll find out that is not that straightforward as it seems at first. As chao_ping has allready mentioned, index writes might also cause index reads - some writes will be preceded with reads, some will not. So how will you tell which reads were caused by writes (inserts, deletes, updates) and which were not? Unless of course the table is a read-only one...

I have come across two possible methods for 8i:

Tom Kyte in 1-on-1 suggests capturing stored outlines, from which you can extract a list of used indexes. I have a performance problem with this - so I can't just have it set all the time on production, which is really what I want.

John Weeg in http://www.dbazine.com/weeg12.html gives a solution by periodically sampling the objects in the buffers. I'm working on this now.

Either way: unused-indexes = all-indexes MINUS used-indexes.

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?