Amit Bansal

In case you came across this link searching for Cron syntax, then you can refer to following link

http://www.adminschoice.com/docs/crontab.htm

Other’s can continue reading the article …. 😉

This was a problem which I faced yesterday. We had a mview refresh which was lagging on one of the server. On checking master site, I found that there were many pending requests coming from the second materialized view site . Checking second site revealed that there were multiple sessions running in database which were waiting on ‘enq :JI Contention’ wait event.

These mviews were being refreshed by job scheduled through a cron. Doing a grep for the refresh script gave following result

 $ps -ef|grep scott|grep -v grep
    oracle 11725 11714  0 13:00:00 ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle 19981 19970  0  Feb  7  ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle 24794 24781  0 06:30:00 ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
    oracle 10538 10527  0  Feb  6  ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
    oracle 13972 13935  0  Feb  6  ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle  2601  2592  0 19:00:00 ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle 17274 17246  0  Feb  6  ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh
  oracle  8308  8294  0 13:30:00 ?         0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

This was definitely wrong. I killed the database sessions and also killed the OS processes

$ ps -ef|grep Refresh_scott.sh|grep -v grep|awk ‘{print $2}’|xargs kill -9

This issue had occurred as one more site was added for mview refresh and due to locking issues (while deleting records from Mlog$), job could not complete before next refresh schedule. While scheduling scripts through cron, care should be taken to put a check if script is already running or not. This is required as Cron will spawn the new job (as per job schedule) irrespective of fact that whether earlier execution has completed or not. I used following logic to implement the same check

cnt=`ps -ef|grep Refresh_scott.sh|grep -v grep |wc -l`
if [ $cnt -eq 1 ]
then
echo "Running Fast Refresh now " > $DIR/Check_refresh_scott.log
sqlplus /nolog <<EOF > $DIR/Refresh_scott.log
conn scott/tiger
exec dbms_mview.refresh ...
...
exit
EOF
else
echo "Fast refresh already running " > $DIR/Check_refresh_scott.log
date >> $DIR/Check_refresh_scott.log
exit
fi

There could be other logics too which can be used to implement the same. e.g Other could be

filename=$DIR/Check_refresh_scott.log
if [ -e $filename  ]
then
echo "Fast Refresh already running " > refresh_runnning.log
exit
else
sqlplus /nolog <<EOF > $DIR/Refresh_scott.log
conn scott/tiger
exec dbms_mview.refresh ...
...
exit
EOF
rm $filename
fi

Other way could be to use DBMS_JOB and DBMS_SCHEDULER to schedule the database job.