SHELL SCRIPT TO STOP OR HALT OR CANCEL A SPECIFIC SQL WHICH EXECUTE FOR LONGER TIME

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.