Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: query delay

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US