RE: Hot indexes
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 12 Jul 2004 12:25:13 -0500
The more difficult parts of the job to do by yourself include:
1. Store the plans in a database.
2. Recognize when two statements are similar (e.g., identical except for
literal values) and store only one copy instead of two.
3. Write a smart differencing engine that can do the right set
intersections, set differences, etc.
4. Perform data collection directly from the application source code =
instead
of waiting for everything to execute before you have a complete set.
www.hotsos.com/products/laredo.html explains how we do it, if you're
interested.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 7/20 Cleveland, 8/10 Boston, 9/14 San =
Francisco
- SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 =
Hartford
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Freeman Robert - IL
Sent: Monday, July 12, 2004 10:42 AM
To: 'oracle-l@xxxxxxxxxxxxx '
Subject: RE: Hot indexes
How about sweeping through v$sql_plan on a regular basis? Of course, =
the
problem with either v$sql_plan or v$sqlarea is aging if you are getting =
lots
of unreusable SQL... I've been tinkering with a job that sweeps =
v$sql_plan
on a regular basis and, based on some defined criteria marks certain SQL
statements as worthy of further review...
RF
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Sent: 7/12/2004 10:11 AM
Subject: RE: Hot indexes
That's what we do with our Laredo product: capture SQL from various =3D
sources,
run EXPLAIN PLAN, store the results in a database, and then use a smart
comparison tool to do reporting. It works very well.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 7/20 Cleveland, 8/10 Boston, 9/14 San =3D
Francisco
- SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 =3D
Hartford
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =3D
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of DENNIS WILLIAMS
Sent: Monday, July 12, 2004 7:18 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Hot indexes
When this question was asked earlier, one suggestion was to capture =
=3D3D
samples
of the SQL in v$sql, then run it through EXPLAIN PLAN to find SQL =3D3D
statements
that are using indexes in this tablespace. I don't recall the success =
=3D
=3D3D
or
failure of this method being posted.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx=3D3D20
I said it "looked" clear - Riddick
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mogens N=3D3DF8rgaard
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=3D3D20
overloaded, whatever), then you would need to track which =3D
user(s)/apps=3D3D20
issued the LIO calls to the datablock adresses of this tablespace.
Tracking of individual LIO calls including the file number, block =3D3D
number=3D3D20
and number of blocks is externalized in v$session_wait and in =
10046=3D3D20
trace files (excluding exotic direct memory access methods and probably
=3D
=3D3D
some decent 3rd party tools).
So putting on 10046 level 8 trace on your whole instance will show =3D
it,=3D3D20
but will require at least 42 GB of storage for the trace files and =3D
42=3D3D20
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
