Re: sql statements missing in continued tracefile

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 29 Nov 2006 10:14:43 -0800
Message-ID: <1164824083.86850@bubbleator.drizzle.com>

Andreas Piesk wrote:
> Jonathan Lewis schrieb:

>> How do you know that one execution of the insert
>> is taking more than 3,000 m/s ? Is it because your
>> front-end code checks the start and end times ?

>
> yep, exactly.
>

>> If so, could you look at v$session_wait_history,
>> which records the last 10 waits for a session
>> whenever you see a time in excess of a critical
>> limit ? You might be lucky and catch the critical wait.
>>
>> select
>>     seq#, event, p1,p2,p3,wait_time
>> from
>>     V$session_wait_history
>> where
>>         sid = {your sid}
>> /

>
> the statement i've used is similar but didn't catch the wait. maybe the
> next time but i'm not holding my breath :)
>
> today i examined some more application log files and found something
> particular interesting:
>
> there are 3 application servers. every server issues the same set of
> statements. suddenly one specific query issued by all 3 application
> servers run for more than 3 secs. the query is a simple 'select' on a
> partitioned table. needless to say the statement runs normally in less
> than 3 ms. it seems the instance freezes for 3 secs. there were no DDL
> on the table, no checkpoints, no logswitches, nothing. load is very
> light. as this is a 2-node RAC i thought the problem could be the
> cluster manager but according to CRS logfiles not a single heartbeat
> was missed. so the interconnect is stable and fast but i still have
> some of these hangs in the application logs. what could theoretically
> bring a instance to suspend simple selects for some seconds?
>

>> You don't even have to have the licence for the
>> diagnostic pack to query this one.

>
> fortunately licensing is not a problem, thanks to an unlimited license.
>
>
> btw: your CBO book is incredible. i haven't understood everything yet
> but i try my very best :)
>
>
> thanks & regards,
> -ap

Given that you are on RAC ... are you using the GV$ objects rather than V$. You may well find that the problem is on one instance and not the other.

I saw something similar to this with RAC and 10.1.0.3 caused by an operating system message not being properly received. It became a Sev 1 SR and was quickly resolved by Oracle. It might well be time to create an RDA and deliver it at metalink.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Nov 29 2006 - 12:14:43 CST