Mani's Blog

March 15, 2011

>How to kill SQL with specific wait event

Filed under: Oracle — mani @ 10:19 pm
Tags: ,

>In our DB instance, we happened to encounter “cursor: pin S wait on X” once in a while.  In our environment, it is okay to clear this session until we get a patch or solution from Oracle.  Schedule the script to run from the cron.

Following script was very useful, which kills that session automatically.

#!/bin/bash
# Oracle Environment settings
export ORA_CRS_HOME=/local/crs/oracle/product/11.1.0/crs
export ORACLE_SID=XNODB02B1
export ORACLE_BASE=/local/opt/oracle
export ORACLE_HOME=/local/opt/oracle/product/11.1.0/db_1
cat $HOME/dba/kill_pin_waits.log >> $HOME/dba/history_kill_pin_waits.log
$ORACLE_HOME/bin/sqlplus -s “/ as sysdba” << EOF > $HOME/dba/kill_pin_waits.log
connect / as sysdba
set echo off
set feedback off
set term off
set pagesize 0
spool /local/home/oracle/dba/pin_waits.sql
select ‘alter system kill session ‘||””||SID||’,’||SERIAL#||””||’ immediate;’  from v\$session where sid in (select sid from v\$session_wait where event like ‘cursor: pin S wait on X’);
spool off;
@/local/home/oracle/dba/pin_waits.sql
exit;
EOF
tmp=`wc -l $HOME/dba/kill_pin_waits.log|awk ‘{print $1}’`
if [ $tmp -ge 2 ]
then
echo “error occured”
cat $HOME/dba/kill_pin_waits.log | echo “library cache mutex occurred.”  | /bin/mailx -s “Cursor Pin Wait occurred” ‘nobody@nobody.com’
fi

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: