RE: Hot indexes

  • From: DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 12 Jul 2004 07:17:38 -0500

When this question was asked earlier, one suggestion was to capture =
samples
of the SQL in v$sql, then run it through EXPLAIN PLAN to find SQL =
statements
that are using indexes in this tablespace. I don't recall the success =
or
failure of this method being posted.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx=20
I said it "looked" clear - Riddick


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mogens N=F8rgaard
Sent: Sunday, July 11, 2004 11:55 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Hot indexes



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

Tracking of individual LIO calls including the file number, block =
number=20
and number of blocks is externalized in v$session_wait and in 10046=20
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,=20
but will require at least 42 GB of storage for the trace files and 42=20
days to interpret the results afterwards :).

Mogens

solbeach@xxxxxxx 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: