Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: last used time of an index ??

RE: last used time of an index ??

From: Sutton, Reed <Reed_Sutton_at_cable.comcast.com>
Date: Thu, 30 Jan 2003 06:45:03 -0800
Message-ID: <F001.0053E8BC.20030130064503@fatcity.com>


Prem,

I found this on Google. Some guy named Thomas Kyte....? He sounds like maybe he might know what he is talking about.... ;-) Unfortunately I don't have his book here with me now so I am not sure exactly what code he is talking about, but this should be a good start for you.

>Question:
>
>Has anyone tried to create the object_usage 9i feature in 8.1.7?
>
>We're not going to 9i any time soon but would REALLY LIKE the feature
>to use now. I looked into the create scripts and saw the table and
>view creates, but didn't find anything that would build a proc to use
>it.
>
>I would guess that using a stint on object auditing would do the
>trick.
>

index monitoring is only available with 9i and up. You cannot just create the
views in 8i -- there isn't any code in the database to supply the needed values
behind the view! The alter index iname monitoring command doesn't exist in 8i.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter index t_idx monitoring usage; alter index t_idx monitoring usage

                  *

ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER SNAPSHOT option

auditing won't help you out here either as it doesn't audit accesses to indexes.

If you have my book -- i do describe a way to do this using stored query outlines in a fashion. You can ask the database to store the query outline for
all queries executed. From there, you can see in the hints that are stored what
indexes are used by which queries.

-----Original Message-----
Sent: Thursday, January 30, 2003 9:20 AM To: Multiple recipients of list ORACLE-L

Prem

   That is a 9i feature. One way in 8i is to regularly sample the SQL in the buffers, run explain plan on it, and scan the results for that specific index name. Another way is to drop the index and see if anyone complains ;-)

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, January 30, 2003 4:50 AM To: Multiple recipients of list ORACLE-L

guys,

can i find out the time when an index was last used ....in Ver 8.1.6 ?

Regards,
Prem Khanna J.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: oraora  oraora
  INET: oraoraora_at_rediffmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sutton, Reed
  INET: Reed_Sutton_at_cable.comcast.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Thu Jan 30 2003 - 08:45:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US