List tables that stats job doesn’t finish

We are running the Oracle delivered automatic optimizer stats job on some of our 11.2 Oracle databases and the job is not completing for some of our tables.  But, the list of these tables is buried in a trace file on the database server on some random RAC node.  I built a script to figure out which node and file the trace was in and edit the output down to a list of tables that stats didn’t finish on.  Output is like this:

Job log start time: 2013-07-17 09:00:07
MYOWNER1.MYTABLE1
MYOWNER2.MYTABLE2
...

The outer script calls a SQL script that builds a list of Unix commands to search through the logs for the table names.

statlogs.sh

rm statlogstmp.sh
sqlplus -s /nolog < statlogs.sql > statlogstmp.sh
chmod 744 statlogstmp.sh
./statlogstmp.sh

statlogs.sql

connect YOURUSERNAME/YOURPASSWORD
set linesize 32000
set pagesize 1000
set echo off
set termout off
set trimspool on
set serveroutput on size 1000000
set feedback off
set heading off

-- This script builds unix commands to find tables 
-- whose stats did not complete during the automatic stats job runs
-- over the past 7 days.

DECLARE 
    CURSOR JRD_CURSOR IS 
    select
      INSTANCE_ID,
      SLAVE_PID,
      cast(ACTUAL_START_DATE as date) start_date_time,
      cast(ACTUAL_START_DATE+RUN_DURATION as date) end_date_time
    from
      DBA_SCHEDULER_JOB_RUN_DETAILS
    where
      JOB_NAME like 'ORA$AT_OS_OPT_SY%' and
      log_date > sysdate - 7
    order by LOG_DATE;    

    JRD_REC JRD_CURSOR%ROWTYPE;

    AT_LEAST_ONE_JOB_RAN boolean;
    TRACE_DIR varchar2(2000);
    log_inst_name varchar2(2000);
    log_host_name varchar2(2000);
    log_INSTANCE_ID number;
    job_slave_pid number;
    job_start_date_time date;
    job_end_date_time date; 

BEGIN
    AT_LEAST_ONE_JOB_RAN := FALSE;
    OPEN JRD_CURSOR;
LOOP
    FETCH JRD_CURSOR INTO JRD_REC;
    EXIT WHEN JRD_CURSOR%NOTFOUND;
    AT_LEAST_ONE_JOB_RAN := TRUE;
    log_INSTANCE_ID := JRD_REC.INSTANCE_ID;
    job_slave_pid :=JRD_REC.SLAVE_PID;
    job_start_date_time := JRD_REC.start_date_time;
    job_end_date_time := JRD_REC.end_date_time;

-- Output echo command to display date and time that the current stats job log was opened    

    DBMS_OUTPUT.PUT_LINE('echo "Job log start time: '|| 
    to_char(job_start_date_time,'YYYY-MM-DD HH24:MI:SS') || '"');

-- Trace directory for the stats job log

    select VALUE into TRACE_DIR from gv$parameter where name='background_dump_dest'
      and INST_ID=log_INSTANCE_ID;  

-- Details needed for name of stats job log

   select INSTANCE_NAME,HOST_NAME into log_inst_name,log_host_name
   from gv$instance
   where INSTANCE_NUMBER=log_INSTANCE_ID;

-- Output ssh command to find names of tables whose stats did not complete

   DBMS_OUTPUT.PUT_LINE('ssh '||log_host_name||' "grep TABLE: '||TRACE_DIR||'/'||log_inst_name||'_j0*_'||
     job_slave_pid||'.trc" | awk ''{ print $3 }'' | sed ''s/"//'' | sed ''s/"."/./'' | sed ''s/"\.".*//'' | sort -u');

END LOOP;
CLOSE JRD_CURSOR;
IF (AT_LEAST_ONE_JOB_RAN = FALSE) THEN
    DBMS_OUTPUT.PUT_LINE('No stats jobs have run in the past 7 days.');
END IF;
END;
/

Put your username and password at the top of the sql script.

Here is a link to a zip of these two files.

– Bobby