From time to time i get questions like “Batch program A was running in X minutes on Day Y, Same program ran for 5X minutes on Day Y+1, can you figure out why ? I have found that collecting and analyzing Ash reports for the two given time periods is a good way to start looking at such issues.

The first step is to determine which was the database session that was running the batch program. I step through the following process to identify the database session.

– Find a sql that the batch program executes

– Identify the sqlid for that sql statement

– Query dba_hist_active_sess_history to find the session_id’s executing the sql in a given time period.

select session_id from
dba_hist_active_sess_history
where
sql_id = '&sqlid'
and instance_number = &inst
and snap_id between &Begin and &End
/

Then I run the following shell script to generate the ash report for the given SID and time period.

The syntax for running the script is

./genash.sh dbid 'MM/DD/YY HH24:MI' duration credentials inst sid

eg:
./genashsid.sh 131424457 '01/30/11 11:00' 10 system/manager@rk01 1 1021

 

The actual script is below

#!/bin/bash

if [ $# != 6 ]
then
echo Syntax  genash.sh dbid \'01/30/11 11:00\' duration credentials inst sid
exit 1
fi

l_dbid=$1
l_start_time=$2
l_starttime_log=`echo $l_start_time|sed 's/\///g' | sed 's/ //'|sed 's/://'`
l_duration=$3
l_credentials=$4
l_inst=$5
l_sid=$6

l_logdir='/u01/Rk/Docs/ashs'

l_ash_log_file=${l_logdir}/ashrpt_${l_inst}_${l_sid}_${l_starttime_log}.html

sqlplus -s $l_credentials < /dev/null

set head off
set pages 0
set lines 132
set echo off
set feedback off
set termout off

define inst_num=${l_inst};
define dbid=${l_dbid};
define report_type='html';
define report_name=${l_ash_log_file};
define begin_time="${l_start_time}";
define duration=${l_duration};
define target_session_id   = ${l_sid};

define slot_width  = '';
define target_sql_id       = '';
define target_wait_class   = '';
define target_service_hash = '';
define target_module_name  = '';
define target_action_name  = '';
define target_client_id    = '';
define target_plsql_entry  = '';

@$ORACLE_HOME/rdbms/admin/ashrpti
EOC

You can modify the script fairly easily to run it for
– All sessions
– For a specific module name
– A specific sqlid etc etc.