RE: query delay
- From: "Siva Valiveru" <SValiveru@xxxxxxxxxxxxx>
- To: "Shivaswamy Raghunath" <shivaswamykr@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Oct 2006 14:25:49 -0700
But you need to baby sit when this is happening right? I think it looks
more like a global db blockage, AWR report good starting point.
Generally v$session_waits is used(i use) for what's happening NOW! and
AWR(statspack 9i) to what happend in past.
________________________________
From: Shivaswamy Raghunath [mailto:shivaswamykr@xxxxxxxxx]
Sent: Thursday, October 05, 2006 1:54 PM
To: Siva Valiveru; oracle-l@xxxxxxxxxxxxx
Subject: 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@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx> ]
On Behalf Of Steiner, Randy
Sent: Thursday, October 05, 2006 4:50 AM
To: Oracle-L@xxxxxxxxxxxxx
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
Other related posts:
