| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: last used time of an index ??
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
*
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
 |
 |