Re: SQL Trace

"Sha" <ssha53_at_singnet.com.sg> wrote in message news:bi5689$3eo$1_at_mawar.singnet.com.sg...
> 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 ?

You will need

1. a 'tame' user - i.e. one who can let you know when the system is slow.
2. to have run dbmssupp.sql (in $ORACLE_HOME/rdbms/admin) whilst connected as sys.

at that point you would

query v$session to find the sid and serial# for their session, then issue exec dbms_support.start_trace_in_session(<sid>,<serial#>,true,true); let them do the work that is slow
then issue exec dbms_support.stop_trace_in_session(<sid>,<serial#>);

You will get a trace file in udump with all the sql that they executed, how long it took and what if anything it was waiting for. Run tkprof on the raw trace to get a formatted file and find out where the program is spending its time, likely (since the problem is intermittent) you will find it is waiting on a shared resource

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK