Re: Trace files to include "SQL*Net message from client" data

On 24 Jul., 22:33, Luch <DarthL..._at_gmail.com> wrote:
> I'd like help on how to make a Trace file include this content:
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 5 0.14 0.16 0 0
> 0 0
> Execute 5 0.11 0.13 0 722
> 0 0
> Fetch 5 0.01 0.00 2 15
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 15 0.26 0.30 2 737
> 0 0
>
> Misses in library cache during parse: 4
> Misses in library cache during execute: 2
> Optimizer mode: ALL_ROWS
> Parsing user id: 32
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> latch: shared pool 1
> 0.00 0.00
> SQL*Net message to client 5
> 0.00 0.00
> SQL*Net message from client 5 39.07
> 154.53
> db file sequential read 2
> 0.00 0.00
> ********************************************************************************
>
> My Trace file only shows this:
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.03 0 0
> 0 0
> Execute 1 0.00 0.00 0 0
> 0 0
> Fetch 1 0.00 0.01 2 2
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 0.00 0.05 2 2
> 0 0
>
> Misses in library cache during parse: 1
> Optimizer mode: ALL_ROWS
> Parsing user id: 66
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 COUNT STOPKEY (cr=2 pr=2 pw=0 time=14404 us)
> 0 INDEX RANGE SCAN I_NOTE_PAD_MESSAGE_USER (cr=2 pr=2 pw=0
> time=14391 us)(object id 60141)
>
> I want mine to include the portion that shows the content of
> SQL*Net message from client 5 39.07
> 154.53
>
> . How do I make it do that?

I suppose, you are tracing, setting sql_trace=true in your session. What you need is a trace with wait events.

So try setting event 10043 with level 8 like this: 1.
alter session set events '10043 trace name context forever, level 8';

2.
begin
sys.dbms_system.set_ev(<sid>,<serial#>,10043,8,''); end;

3. In Oracle 10g you can use also DBMS_MONITOR.SESSION_TRACE_ENABLE: begin
DBMS_MONITOR.SESSION_TRACE_ENABLE (session_id=><sid>, serial_num=><serial#>, waits=>true, binds=>false); end;

Run TKPROF for the trace and you will see also summaries of the wait events in the tkprof output.

Regards
Elena Received on Thu Jul 26 2007 - 06:15:29 CDT