Re: sql statements missing in continued tracefile

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 Received on Wed Nov 29 2006 - 11:54:23 CST