Re: query delay

I would run this against the session in question: select sid, event, p1text, p1, p2text, p2, p3text, p3, wait_time, SECONDS_IN_WAIT, state
from v$session_wait
where sid=&1;

I can find what it is waiting on

On 10/5/06, Siva Valiveru <SValiveru_at_looksmart.net> wrote:
>
> When this is happening what are the top wait events from the AWR report.
>
> If you have not enabled awr stats collection do that at a 10-15 min
> frequency.
> exec dbms_workload_repository.modify_snapshot_settings(retention=>10080,
> interval=> 10);
> /* for 10 min snapshots */
>
> If you have problem(slowness) at say at 12.03 AM, take a AWR report from
> 12.00 and 12:10 and see what the top 5 waits, compare with good timing
> waits, that may show some light on further debugging.
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Steiner, Randy
> *Sent:* Thursday, October 05, 2006 4:50 AM
> *To:* Oracle-L_at_freelists.org
> *Subject:* query delay
>
>
>
> I am running 10gr2 on 4 blades, everything is running much faster than on
> the old server ……except;
>
>
>
> Periodically simple queries will take between 1 – 2 minutes. Simple as in
>
>
> SELECT *
>
> FROM small_table.
>
>
>
> This delay does not happen all the time. I do not think this is an index
> issue. I think this is a RAC wait issue.
>
>
>
>
>
> I will occasionally (maybe once a day) get some of the following:
>
>
>
> Metrics "Global Cache Blocks Lost" is at 25
>
> Metrics "Database Time Spent Waiting (%)" is at 57.65708 for event
> class "Cluster"
>
>
>
>
>
> Can anyone think of a way to investigate this?
>
>
>
> Thanks
>
>
>
> Randy
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l