RE: query delay

From: Siva Valiveru <SValiveru_at_looksmart.net>
Date: Thu, 5 Oct 2006 14:25:49 -0700
Message-ID: <8F8AA525BF5CCB40BF4FA4A6156D6F270B9E278B@sfex3k2.looksmart_sf.ad.looksmart.com>

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_at_gmail.com] Sent: Thursday, October 05, 2006 1:54 PM To: Siva Valiveru; oracle-l_at_freelists.org 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_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 <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

Received on Thu Oct 05 2006 - 16:25:49 CDT