Re: SQL Trace

Hi Niall

Thanks for your response.
I've got some users feedback that display of information is slow and this happens intermittently.
We've pretty much ruled out that its a network problem. So figured, the next thing would be to check the database configuration. If I was to check the database configuration, should the sql traced be activated from the init.ora ? Also, we've got a few oracle databases on the same server ... how do you activate it for just a particular database ? Will activating it affect the other oracle databases ?

Thanks so much for your help.

Regards.

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3f44f073$0$18494$ed9e5944_at_reading.news.pipex.net...
> "Sha" <ssha53_at_singnet.com.sg> wrote in message
> news:bi2kic$3i8$1_at_reader01.singnet.com.sg...
> > Hi there
> >
> > Can someone pls tell me how to activate the SQL Trace for a database ?
> > How resource intensive is it ?
>
> Taking the questions in reverse order
>
> It isn't especially expensive in processing and memory usage, though
> activated for a whole database system all at once it will eat disk space
> like there is not tomorrow (and not give any useful info either)
>
> To activate it
>
> For your session
> ===========
> alter session set sql_trace=true|false
> alter session set events '10046 trace name context forever, level 8|12'
> alter session set events '10046 trace name context off' - turns it off
> or -- better (gives more flexibility as to what info to collect)
> exec dbms_support.start_trace(true,true);
> exec dbms_support.stop_trace;
>
> For some one else
> ============
> exec dbms_system.set_sql_trace_in_session(<sid>,<serial#>,true)
> exec dbms_system.set_sql_trace_in_session(<sid>,<serial#>,false)
>
> or -- better (gives more flexibility as to what info to collect)
> exec dbms_support.start_trace_in_session(<sid>,<serial#>,true,true)
> exec dbms_support.stop_trace_in_session(<sid>,<serial#>)
>
> For System
> ========
> alter system set sql_trace=true
> set sql_trace=true in init.ora
> set the 10046 event in init.ora
>
> You can look up the parameters to dbms_support in the docs I believe. I
know
>
>
Received on Fri Aug 22 2003 - 08:47:45 CDT