Re: sql statements missing in continued tracefile

"Andreas Piesk" <a.piesk_at_gmx.net> wrote in message news:1164749079.191235.122410_at_45g2000cws.googlegroups.com...
> Hi,
>
> i have a little problem with tracing. database is 10.2.0.2 with rac
> option.
> inserts issued by a continuously running process running sometimes too
> long, more than 3000ms. unfortunately this occures very randomly and
> infrequently and statspack/awr or fixed views didn't catch the problem
> because they show only cumulated values or simply nothing.
> i tried to pin down the problem by tracing with dbms_monitor, 10046 and
> oradebug in level12. when tracing the sesion for the very first time i
> see all the sql text in the tracefile. after stopping it and starting
> it again (because the tracefiles tend to be very huge and the problem
> occurs only from 10:00-17:00) all sql statements are missing and
> tkprof/trca/etc. show 0 user statements.
> this is bad and i'm looking for way to bring the sql text back in order
> to analyze the traces.
>
> any ideas?
>
> regards,
> -ap
>

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 ?

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}
/

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

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html