SHELL SCRIPT TO STOP OR HALT OR CANCEL A SPECIFIC SQL WHICH EXECUTE FOR LONGER TIME
Is it possible to stop a sql query which is executing for quite long time than threshold?
In 19c, we can stop the sql using inbuilt ddl which stops a specific sql based on sid, serial#
This script can be useful, if a particular query is executed beyond the time and blocking other DML in enqueues or such events.
Here elapsed_time from v$sql contains the db time of the sql rather than wall clock time. So modify the script accordingly to needs and time frame.
This script provides basic functionalities to cancel the sql. This script is also inspired based on an incident, where a specific DML was locking a table for 1 hour and blocking 100’s of sessions in enq: tx row lock contention with performance degradation. There are also other options which may help to cancel a query like resource manager or sql quarantine in exadata.
REM Procedure to stop a specific sql
REM
REM Author -- Kishan
REM
CREATE OR REPLACE PROCEDURE stopsql (sql_id IN VARCHAR2)
AS
stmt VARCHAR (1000);
CURSOR get_sql
IS
SELECT s.sid, s.serial# serial, sq.sql_id FROM v$session s
INNER JOIN v$sql sq on (s.sql_id = sq.sql_id)
WHERE sq.sql_text like '%xtbl%'
AND
sq.sql_text not like '%INNER JOIN v$sql%'
AND
sq.elapsed_time/1000000 > 1;
BEGIN
FOR x IN get_sql
LOOP
BEGIN
stmt := 'ALTER SYSTEM CANCEL SQL ''' || x.sid || ',' || x.serial || '''' || '';
BEGIN
EXECUTE IMMEDIATE stmt;
EXCEPTION
WHEN OTHERS
THEN
-- If there are any exceptions specify here
CONTINUE;
END;
END;
END LOOP;
END;
/
######################
# Author - Kishan M #
######################
#!/bin/bash
#Remove the temporary files at the end of execution of script
trap 'rm -rf /home/oracle/oratab_new' EXIT
#For logging purpose
_LOG_0()
{
echo "*************************************$1"
}
#Set the environment variables
_SET_ENV_1()
{
cat /etc/oratab|grep -v '#'|grep -v '^$' > /home/oracle/oratab_new
while read x
do
IFS=':' read -r -a array <<< $x
ORACLE_SID="${array[0]}"
ORACLE_HOME="${array[1]}"
echo $ORACLE_SID
echo $ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME/bin
done < /home/oracle/oratab_new
}
#Fetch the sql which takes time
_GET_SQL(){
$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' <<EOF
spool stopsql.txt
set heading off
set feedback off
SELECT s.sid, s.serial# serial, sq.sql_id FROM v\$session s
INNER JOIN v\$sql sq on (s.sql_id = sq.sql_id)
WHERE sq.sql_text like '%xtbl%'
AND
sq.sql_text not like '%INNER JOIN v$sql%'
AND
sq.elapsed_time/1000000 > 1;
spool off
exit;
EOF
}
#Stop only the specific sql without killing the session
_STOP_SQL(){
touch sql.sql && echo -ne "EXEC stopsql('sql')\n exit;" > sql.sql
sqlid=/home/oracle/stopsql.txt
if [ -s $sqlid ]
then
sql=`cat $sqlid |awk '{print $NF}'|uniq|grep -v '^$'`
sqlplus -S '/ as sysdba' @sql.sql
else
break
fi
}
_SET_ENV_1
_GET_SQL
_STOP_SQL
Demonstration:
Execute the specific sql which need to be stopped after particular time.
[oracle@staxhost ~]$ ./stopsql.sh
eXhydra
/u01/app/oracle/product/19.0.0/dbhome_1
62 53762 gycyg7ysuw1h4
64 22468 gycyg7ysuw1h4
66 49427 gycyg7ysuw1h4
70 10667 gycyg7ysuw1h4
74 55475 gycyg7ysuw1h4
76 50988 gycyg7ysuw1h4
85 26671 gycyg7ysuw1h4
86 36067 gycyg7ysuw1h4
91 13897 gycyg7ysuw1h4
PL/SQL procedure successfully completed.
The executed sql is cancelled with ORA-01013 after the execution of shell script.
SELECT * from atbl a , xtbl x where a.xid <> x.xid and A.salary > 100 and a.city <> 'India'
.....
NAME
--------------------------------------------------------------------------------
XID
----------
CITY
--------------------------------------------------------------------------------
COUNTRY
--------------------------------------------------------------------------------
DEPARTMENT
--------------------------------------------------------------------------------
ZIPCODE XDATE SALARY
---------- --------- ----------
ERROR:
ORA-01013: user requested cancel of current operation
1680 rows selected.