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_at_gmail.com> 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