Re: 10g Performance: its crawling

Nope, unfortunately I dont have old plan (of 9206). Well, I know the
SQL. I have rebuilt one
of index(reclaimed more than 100M, and now size of the index is 279M)
... I will see if that makes any difference.. I guess it is INDEX
RANGE SCAN... so dont think it makes much difference... if its INDEX
FULL SCAN, and resetting highlevel watermark by rebuild makes sense...

Tuning Advisor recommends SQL profile which <10% benifit. If this
index thing does not help, next option is to create a SQL profile..
even it is <10%, but it matters when no# of executions are more.

Thanks



On 12/28/06, John Kanagaraj <john.kanagaraj@xxxxxxxxx> wrote:

MVR,

The issue we all forget is this: The "Wait Interface" records the last
_instrumented_ *wait* event. The process has then moved on to executing in
the CPU. However, since there is not "Waiting for CPU" event (and this is
not necessarily a "wait" event!!), what you need to do is to look at the
"SESSION_STATE" column, and you _will_ see "ON CPU" for this event. What it
means is that you most probably had the execution of a multi-level nested
loop and all the blocks required was fully in memory, and the last I/O
performed was for the index block you mentioned. You should be able to
determine the SQL from AWR using the SQLID for that event. The execution
plan has changed after the upgrade - so do you have the old plan?


John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **



--
"Happy people plan actions, they don't plan results."
--
http://www.freelists.org/webpage/oracle-l

Other related posts: