Poor Performing DB Job

Over night we had a job that overran and caused deadlocks when the morning OLTP operations started. Unlike poor performing code, it can be harder to look into a job as it can often spawn multiple queries while it runs. There are a couple of methods you can use to assist.

 

Run Job Manually

The simplest method if you can do it, is to run the job manually as a code block with tracing turned on:

alter session set tracefile_identifier ='job_trace.trc';
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

exec DBMS_SCHEDULER.run_job('jobname');
/

Then use tkprof to format the trace file:

$ORACLE_HOME/bin/tkprof jobtrace.trc output.prf

 

Add Tracing to the PL Code

Another option is to add an identifier to the code which you can then use for tracing:

CREATE OR REPLACE PROCEDURE tracingtest (<PARAMETERS>)
AS
  v_cmd                        VARCHAR2 (4000);
  v_date                       VARCHAR2 (30) := TO_CHAR (SYSDATE,'DDMMYY');
BEGIN
  dbms_session.set_identifier( 'IDENTIFIER' );
  <CODE>
END;
/

To begin the tracing:

exec dbms_monitor.client_id_trace_enable( client_id => 'MY_IDENTIFIER' )

Submit your job as usual and then using trcsess to compile the trace file:

SQL> !trcsess output=/tmp/IDENTIFIER.trc clientid=IDENTIFIER $ORACLE_HOME/admin/$ORACLE_SID/bdump/*.trc

Leave a Reply