techblog 

In order to investigate a performance problem in our Oracle database, I needed to trace one of our batch PL/SQL procedures, which has been submitted via the dbms_job package to execute every morning. I had traced the procedure successfully when executing it manually from a SQL*Plus session: a SQL trace file was produced in the directory defined by the database parameter user_dump_dest. Below is a description of how I then went on to trace the procedure when it was executing as a job submitted via dbms_job.

Assume a job has been submitted to execute every morning at 9am:

variable job_num number
exec dbms_job.submit (:job_num, my_procedure, sysdate, '(trunc(sysdate + 1) + 9/24)');
commit;

From a SQL*Plus session examine dba_jobs_running, to verify that the job has started and obtain its session identifier (sid):

select * from dba_jobs_running;

 SID  JOB FAILURES LAST_DATE LAST_SEC THIS_DATE           THIS_SEC INSTANCE
---- ---- -------- --------- -------- ------------------- -------- --------
 356  467                             02/05/2008 14:37:35 14:37:35        0

Obtain the Oracle serial number (serial#) for this database session:

select sid, serial# from v$session where sid = 356;

SID SERIAL#
--- -------
356     701

Use the dbms_monitor.session_trace_enable procedure to start SQL tracing for this session:

exec dbms_monitor.session_trace_enable (356, 701);

A SQL trace file will be produced in the directory defined by the database parameter background_dump_dest. I was originally mistakenly looking for the file in the user_dump_dest directory, but background_dump_dest is the correct one, as it is an Oracle background process being traced here.

Once the job has completed use the tkprof utility to convert the output to a summarised, readable format:

tkprof livedb_j000_1850.trc sql_trace.txt sys=no explain=username/password