Re: Hot indexes

From: Mogens Nørgaard <mln_at_miracleas.dk>
Date: Mon, 12 Jul 2004 06:55:03 +0200
Message-ID: <40F219A7.4080804@miracleas.dk>

If it really is a problem (somebody complaining, devices being overloaded, whatever), then you would need to track which user(s)/apps issued the LIO calls to the datablock adresses of this tablespace.

Tracking of individual LIO calls including the file number, block number and number of blocks is externalized in v$session_wait and in 10046 trace files (excluding exotic direct memory access methods and probably some decent 3rd party tools).

So putting on 10046 level 8 trace on your whole instance will show it, but will require at least 42 GB of storage for the trace files and 42 days to interpret the results afterwards :).

Mogens

solbeach_at_cox.net wrote:

> I can see that one tablespace which contains only a few
> indexes is doing many, many reads.
> How do I track back to the SQL & user(s) behind this activity?

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sun Jul 11 2004 - 23:51:38 CDT