4 Replies Latest reply: Jan 25, 2013 10:11 AM by Verdi RSS

    DBMS_MONITOR usage and trace file is empty

    Verdi
      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
          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
            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
              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
                I'm really sorry, it's my mistake. Closing the thread.