Database Tracing in Oracle

You can trace statements in Oracle using a package called "dbms_monitor" - it allows you to see statements that are executed along with some indication of how efficient they are (sort of like using an explain plan, but after the fact). Why would you want to do this? Well, if you have an application that is misbehaving or if you want to know more about how your statements are running live (as opposed to using the explain plan) this can help. Also, if you have a closed package and want to know more about what it is doing in the database this might help. You'll need to be a DBA or be friendly with your DBA to do this, as it requires some privileges and logs are written to the database servers' filesystem.

1. Get access to the dbms_monitor package. Your DBA can grant you rights. I do not know the details on this step, I'm afraid.
   
   
2. Do a select to find out what sessions are active:
   

   
     SELECT sid, serial#, client_identifier, service_name, action, module, username
     FROM V$SESSION 
   

   
   I'd actually use a "WHERE username='my_username'" clause of some kind here to limit what I get, but YMMV. You'll end up with rows that look something like this:
   

   
   SID SERIAL# CLIENT_IDENTIFIER SERVICE_NAME ACTION      MODULE     USERNAME
   266 24923                     My_Instance  SQL Window  SQL Devel  my_username
   

   
   
3. Enable logging:
   
   
   
   1. To log by user session, you need to make a note of the SID and SERIAL# columns, and run this to begin tracing:
      

      
        begin
        dbms_monitor.SESSION_TRACE_ENABLE(266, 24923, TRUE, TRUE);
        end;
      

      
      
   2. To log by service and module, you can use those columns:
      

      
        begin
        dbms_monitor.serv_mod_act_trace_enable('My_Instance', 'SQL Devel', 
          DBMS_MONITOR.ALL_ACTIONS, TRUE, TRUE, NULL);
        end;
      

      
      An upside to doing tracing by service is you can verify it is running:
      

      
      select * from DBA_ENABLED_TRACES
      
      TRACE_TYPE      PRIMARY_ID  QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS INSTANCE_NAME
      SERVICE_MODULE  My_Instance SQL Devel                   TRUE  TRUE 
      

      
      
   
   
4. At some point you will have had enough logging, and you'll want to turn it off.
   
   
   
   1. If you are tracing by user/session:
      

      
        begin
        dbms_monitor.SESSION_TRACE_DISABLE(266, 24923);
        end;
      

      
      
   2. If you are tracing by service/module:
      

      
        begin
        dbms_monitor.serv_mod_act_trace_disable ('My_Instance', 'SQL Devel');
        end;
      

      
      note: with service level tracing you can select * from DBA_ENABLED_TRACES to verify tracing has been turned off, just as you did to determine it was on.
      
   
   
5. So you've traced some information. Where is it? In a file. Where is the file?
   

   
   select value from v$parameter where name = 'user_dump_dest'
   
   VALUE
   /usr/local/oracle/10204/admin/My_Instance/udump
   

   
   
6. Once you have the file (ask your DBA for the contents of that folder, or better yet the files from today, or this hour or something) you might wonder what is inside. Well, it is only semi-readable at this point. You need to use a utility called TKPROF to reformat the trace into something you can really read. I found I already had a copy of TKPROF in my oracle bin directory:
   

   
   "C:\oracle\product\10.2.0\client_1\BIN\tkprof.exe" c:\my_instance_ora_23486.trc c:\my_oracle.log
   

   
   At this point, c:\my_oracle.log should contain a list of SQL statements and performance data for each statement for as long as you had tracing on.
   
   
7. For more information, check out Oracle's site:
   http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm