Right, given that the query is seeking data from the 2 most recent
partitions (I earlier said the current, but it spans into last month), and
the facts that most online user activity deals with the same date range, and
that this execution was the 3rd or 4th back to back, and that this instance
has a 1.2 GB buffer cache, it is entirely possible all of the blocks from
the given table/indexes were cached. I still expected there to be some kind
of wait event on this activity.
Even without wait events, your book (pg. 75) told me what I needed to know.
That page explains what the "STAT" lines mean. In this case there are STAT
lines indicating that the index I wanted to use was indeed being used. The
problem was the join order.
Recall that I mentioned the performance of this report tanked a little over
a week ago. It turns out that at that time, due to an intended change,
another index on this table that was a unique index was recreated as a
non-unique index and a unique constraint added to the table. It seems (and
I stress seems), that this and and an accompanying reload of some data from
the affected date range were enough to cause the join order to change and
basically we had a nested loop going the wrong direction.
So, a rebuild of that index and one other and we're back in business.
Thanks for the feedback. Now to page 76 ...
- Original Message -----
From: "Cary Millsap" <cary.millsap_at_hotsos.com>
To: <oracle-l_at_freelists.org>
Sent: Tuesday, August 10, 2004 9:09 PM
Subject: RE: 10046 / wait events question
> Darrell,
>
> So, to be clear, is this true? You are saying that /none/ of the p1 and =
> p2
> values for your 'db file.*' events indicate the file and block numbers =
> of
> blocks that are members of the largest participating table in your =
> query?
> And that the same statement is true of all the table's indexes. Right?
>
> If this /is/ what you're saying, then I would take the trace data at =
> face
> value. It's saying that all the blocks that were required from those
> segments (the table and its indexes) were already in your buffer cache. =
> In
> other words, the Oracle kernel simply didn't execute OS read calls for =
> those
> blocks during the execution of this program.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *
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 Tue Aug 10 2004 - 22:36:22 CDT
