This discussion is archived
4 Replies Latest reply: Jan 25, 2013 8:11 AM by Verdi RSS

DBMS_MONITOR usage and trace file is empty

Verdi Newbie
Currently Being Moderated
NLSRTL      11.2.0.3.0     Production
Oracle Database 11g Enterprise Edition      11.2.0.3.0     64bit Production
PL/SQL      11.2.0.3.0     Production
TNS for IBM/AIX RISC System/6000:      11.2.0.3.0     Production

I want to trace a block of PL/SQL code by using the package DBMS_MONITOR.
And I do it this way:
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'ET2';

DECLARE
   <some delarations>
BEGIN

   DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);
   
   <some selects and updates are running here>
 
   DBMS_MONITOR.SESSION_TRACE_DISABLE;

END;
/

COMMIT;
Then I locate the trace file by running this query:
select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File"
from V$PARAMETER u_dump 
cross join V$PARAMETER instance 
cross join V$PROCESS 
join V$SESSION on v$process.addr = V$SESSION.paddr 
where u_dump.name = 'user_dump_dest' 
and instance.name = 'instance_name'
and V$SESSION.audsid=sys_context('userenv','sessionid');
This gives me the path: /ORACLE/DBDEV/dump/diag/rdbms/dbdev/DBDEV/trace/DBDEV_ora_30277668_ET2.trc

Then I go to the file and what I see in it is just two rows which look like this:
Trace file /ORACLE/DBDEV/dump/diag/rdbms/dbdev/DBDEV/trace/DBDEV_ora_30277668_ET2.trc
CLOSE #4574493152:c=1,e=1,dep=1,type=3,tim=57812311082909
And it is the same no matter how many times I run the block.

Please, help me understand what do I miss to make the DBMS_MONITOR package generate tracing information.

Thank you
  • 1. Re: DBMS_MONITOR usage and trace file is empty
    karan Pro
    Currently Being Moderated
    Are you sure the trace file was same as reported by SQL> select value from v$diag_info where name='Default Trace File';
  • 2. Re: DBMS_MONITOR usage and trace file is empty
    Verdi Newbie
    Currently Being Moderated
    Yes, it gives the same output:
    select value from v$diag_info where name='Default Trace File';
    
    /ORACLE/DBDEV/dump/diag/rdbms/dbdev/DBDEV/trace/DBDEV_ora_30277668_ET2.trc
  • 3. Re: DBMS_MONITOR usage and trace file is empty
    karan Pro
    Currently Being Moderated
    It works for me

    SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'ET2';

    Session altered.

    SQL> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);

    PL/SQL procedure successfully completed.

    SQL> select value from v$diag_info where name='Default Trace File';

    VALUE
    --------------------------------------------------------------------------------
    /u01/app/oracle/diag/rdbms/sales/sales/trace/sales_ora_11754_ET2.trc

    SQL> select ename from scott.emp;

    ENAME
    ----------
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    SCOTT
    KING
    TURNER
    ADAMS

    ENAME
    ----------
    JAMES
    FORD
    MILLER

    14 rows selected.

    SQL> exec DBMS_MONITOR.SESSION_TRACE_DISABLE;

    PL/SQL procedure successfully completed.


    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, Oracle Label Security, OLAP, Data Mining,
    Oracle Database Vault and Real Application Testing options

    [oracle@node1 ~]$ tail -30 /u01/app/oracle/diag/rdbms/sales/sales/trace/sales_ora_11754_ET2.trc
    FETCH #1:c=3000,e=17735,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1636480816,tim=1359129991410263
    STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$DIAG_INFO (cr=0 pr=0 pw=0 time=0 us cost=0 size=305 card=1)'
    WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1359129991410526
    
    *** 2013-01-25 21:36:36.162
    WAIT #1: nam='SQL*Net message from client' ela= 4752240 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1359129996162871
    CLOSE #1:c=0,e=58,dep=0,type=0,tim=1359129996163090
    =====================
    PARSING IN CURSOR #1 len=27 dep=0 uid=0 oct=3 lid=0 tim=1359129996163357 hv=2660398526 ad='725a16e0' sqlid='5nwx8yqg94xdy'
    select ename from scott.emp
    END OF STMT
    PARSE #1:c=0,e=177,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=1359129996163354
    EXEC #1:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=1359129996163651
    WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1359129996163714
    WAIT #1: nam='Disk file operations I/O' ela= 76 FileOperation=2 fileno=4 filetype=2 obj#=-1 tim=1359129996163938
    FETCH #1:c=0,e=644,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=3956160932,tim=1359129996164409
    WAIT #1: nam='SQL*Net message from client' ela= 246 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1359129996164737
    WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1359129996164827
    FETCH #1:c=0,e=72,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,plh=3956160932,tim=1359129996164880
    STAT #1 id=1 cnt=14 pid=0 pos=1 obj=73201 op='TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=0 us cost=3 size=84 card=14)'
    
    *** 2013-01-25 21:36:41.591
    WAIT #1: nam='SQL*Net message from client' ela= 5426334 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1359130001591408
    CLOSE #1:c=0,e=46,dep=0,type=0,tim=1359130001591630
    =====================
    PARSING IN CURSOR #1 len=48 dep=0 uid=0 oct=47 lid=0 tim=1359130001592054 hv=3127860446 ad='6ce47f80' sqlid='80u1a4kx6yr6y'
    BEGIN DBMS_MONITOR.SESSION_TRACE_DISABLE; END;
    END OF STMT
    PARSE #1:c=0,e=347,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1359130001592052
    EXEC #1:c=1999,e=1105,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1359130001593421
    Edited by: Karan on Jan 25, 2013 9:38 PM
  • 4. Re: DBMS_MONITOR usage and trace file is empty
    Verdi Newbie
    Currently Being Moderated
    I'm really sorry, it's my mistake. Closing the thread.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points