Re: sql statements missing in continued tracefile

Andreas Piesk wrote:
> 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

You mention AWR. Have you looked into ASH (Active Session History)?

It is far from complete, and there are likely far better ASH references out there, but I do have some queries you might find useful on the ASH page of Morgan's Library at www.psoug.org.

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