Same with me - but one thing we did have to do was
move the outline tables to their own tspace (good
practice anyway I suppose) and bump up the freelists
since we got some contention when things got busy.
hth
connor
- Larry Elkins <elkinsl_at_flash.net> wrote: > Tony is
thinking along the same lines I was. Go
> ahead and capture the
> outlines and then querying the DD where the indexes
> aren't found in the
> OL$HINTS.HINT_TEXT column could tell you what
> indices haven't been used.
> Something like:
>
> select i.owner, i.table_name, i.index_name
> from dba_indexes i, outln.OL$HINTS h
> where index_name not like ('%'||hint_text||'%')
> and owner not in
> ('SYS','SYSTEM','PORTAL30','DES6I')
>
> Just add the schema's to exclude. Only as good as
> the code coverage from
> your collection timeframe but still seems like a
> pretty solid approach.
>
> Anyone tried this? I've kicked it around but have
> never actually tried it in
> anything other than a test environment.
>
> Regards,
>
> Larry G. Elkins
> elkinsl_at_flash.net
> 214.954.1781
> -----Original Message-----
> Sent: Monday, February 04, 2002 4:31 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Another option you have if your Oracle version is
> high enough is to use
> Stored Outlines. Enable automatic generation of
> stored outlines for a full
> processing cycle as defined by the application (full
> month, Qtr, etc.) Then
> extract all of the indexes used during that cycle
> from OL$HINTS.HINT_TEXT
> (i.e.. WHERE HINT_TEXT LIKE 'INDEX%'). If all of
> your application code has
> been traversed in the cycle then this list will be
> pretty darn close to real
> usage. Even if you can't wait until year-end
> processing, you can eliminate
> the bulk of code to be mined for embedded SQL and
> focus on those once-a-year
> programs.
> HTH
> Tony Aponte
> -----Original Message-----
> Sent: Monday, February 04, 2002 12:55 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi
> I there any view which can tell us which indexes are
> not in use?
> Thx
> -Seema
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Larry Elkins
> INET: elkinsl_at_flash.net
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Feb 06 2002 - 06:42:59 CST
