Automating Killing Oracle SELECT Queries Using Shell Script

Introduction In large Oracle database environments, it is common to encounter long-running SELECT queries that can impact system performance. These queries may be inactive or active but consuming excessive resources. To address this issue, we can automate the process of identifying and killing these select queries. In this blog, we will explore a shell script that can be used to kill SELECT queries in Oracle databases based on specified criteria.

Script Overview The shell script presented here is designed to identify and terminate SELECT queries that meet certain conditions. Let’s go through the key components of the script:

Database Selection: The script starts by allowing you to choose the database you want to work with. It displays a menu of databases defined in the oratab file and prompts you to select the desired one. The script uses oraenv to set the necessary environment variables based on the selected database.
Defining Parameters: The script accepts three parameters: cdb, pdb, and duration. The cdb parameter represents the Container Database (CDB) name, the pdb parameter represents the Pluggable Database (PDB) name, and the duration parameter specifies the duration (in hours) after which the SELECT queries should be killed.
Checking Running Sessions: The script connects to the Oracle database using SQL*Plus and retrieves the count of SELECT queries that meet the defined criteria. It looks for sessions that are active or inactive, have a non-Oracle OS user, and are running SELECT statements. The criteria also include checking the last call duration and the logon time to ensure the session has been running for a sufficient duration.
Killing Sessions: If there are candidate sessions to be killed, the script proceeds to terminate them. It creates a table to store information about the terminated sessions if it doesn’t exist already. Then it inserts the details of the sessions into the table and generates a SQL script to kill the sessions immediately. The script generates an ALTER SYSTEM command for each session and spools them to a log file.
Execution and Notification: The generated SQL script is executed to kill the SELECT sessions. The script also sends an email notification to the specified recipients, informing them about the number of SELECT sessions killed and provides the log file for reference.

Conclusion Automating the identification and termination of long-running SELECT queries in Oracle databases can significantly improve system performance. The shell script provided in this blog offers a convenient solution to handle this task. By setting the required parameters and executing the script, you can proactively identify and terminate SELECT queries that exceed the specified duration. Remember to adjust the script to match your specific environment and security requirements.

Feel free to customize the script further based on your needs and incorporate additional functionalities. Always exercise caution when terminating sessions in a production environment and ensure proper testing before deploying the script.

By automating the process of killing SELECT queries, you can enhance the efficiency and stability of your Oracle database, resulting in improved overall performance.

That’s it for this blog! We hope you find this information useful in your Oracle database administration tasks. Happy automating!

sh kill_select_queries_sh cdb pdb 1800

cat kill_select_queries_oci.sh

#!/bin/bash
# October 2022 Sandeep Reddy 
#
# Interactive database picker
# Displays menu of databases defined in oratab then asks which one to use then uses oraenv to set env variables
# Script will kill session which are inactive and running more than 10 hours
# Script will kill session which are active and running more than 60 mins
export SCRIPT_NAME="kill_select_queries"
export SRV_NAME=`uname -n`
#export LNXVER=`cat /etc/redhat-release | grep -o '[0-9]'|head -1`
export TERMINATOR_SCRIPT=/tmp/KILL_LONG_QUERIES.sql
export ORACLE_SID=$1
export ORACLE_HOME=`grep "^${ORACLE_SID}:" /etc/oratab|cut -d: -f2 -s`
export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export SERVICE_NAME=${2}
export CDATE=`date '+%Y%m%d_%H%M'`
export LOGFILE=/tmp/${SCRIPT_NAME}_${SERVICE_NAME}.log
echo ${LOGFILE}
# Email Recipients:
# ################
MAIL_LIST="youremail@yourcompany.com"
export ML=""
export ML


# #######################################
# SCRIPT OPTIONS:
# #######################################

# Duration [In hours and its fraction] when exceeded the query will get killed:
# e.g. To kill the queries that exceed 3 hours and 30 minutes export DURATION="3.5"
export DURATION="${3}"

echo "kill SELECT sessions which are active and inactive where IDLE time is set ${DURATION}"

# CHECKING RUNNING SESSIONS:
SESSIONS_COUNT_RAW=$(${ORACLE_HOME}/bin/sqlplus -s user/pass@${SERVICE_NAME} <<EOF
set pages 0 feedback off;
prompt
SELECT count(*)
  FROM  GV\$SQLAREA SQLAREA JOIN GV\$SESSION SESS ON SESS.PREV_HASH_VALUE = SQLAREA.HASH_VALUE AND SESS.PREV_SQL_ADDR = SQLAREA.ADDRESS
WHERE
  SESS.OSUSER NOT IN ('oracle')
  AND SESS.username NOT IN ('SYS', 'SYSTEM')
  AND SESS.type = 'USER'
  AND SESS.USERNAME IS NOT NULL
  AND (SESS.STATUS = 'ACTIVE' OR SESS.STATUS = 'INACTIVE')
  AND SQLAREA.SQL_TEXT LIKE 'SELECT%'
  AND (SESS.last_call_et > ${DURATION} OR (SYSDATE - SESS.logon_time) > 30 / 24);
  exit;
EOF
)

SESSIONS_COUNT=`echo ${SESSIONS_COUNT_RAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`


function kill_session() {
echo ${ORACLE_SID}
export ORACLE_HOME=`grep "^${ORACLE_SID}:" /etc/oratab|cut -d: -f2 -s`
${ORACLE_HOME}/bin/sqlplus -s user/pass@${SERVICE_NAME}<<SQL
SET SERVEROUTPUT ON
DECLARE
  table_exists NUMBER := 0;
BEGIN
  SELECT COUNT(*) INTO table_exists FROM ALL_TABLES WHERE OWNER='BMCDBA' AND table_name = 'KILL_SESSION_DETAILS';

  IF table_exists = 0 THEN
    EXECUTE IMMEDIATE '
      CREATE TABLE KILL_SESSION_DETAILS(
        sid          NUMBER,
        serial#      NUMBER,
        inst_id      NUMBER,
        username     VARCHAR2(100),
        status       VARCHAR2(20),
        machine      VARCHAR2(100),
        program      VARCHAR2(100),
        sql_text     CLOB,
        last_call_et NUMBER,
        kill_time    TIMESTAMP,
        logon_time   TIMESTAMP
      )';
    DBMS_OUTPUT.PUT_LINE('Table session_details created.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Table session_details already exists.');
  END IF; -- Add missing END IF statement here
END;
/

DELETE FROM KILL_SESSION_DETAILS WHERE kill_time < SYSTIMESTAMP - INTERVAL '2' DAY;

COMMIT;

INSERT INTO KILL_SESSION_DETAILS (sid,  serial#,  inst_id,  username,  status,  machine,  program,  sql_text,  last_call_et,  kill_time,  logon_time)
SELECT   SESS.sid,  SESS.serial#,  SESS.inst_id,  SESS.username,  SESS.status,  SESS.machine,  SESS.program,  SQLAREA.sql_text,  SESS.last_call_et,  SYSTIMESTAMP,  SESS.logon_time
FROM  GV\$SQLAREA SQLAREA JOIN GV\$SESSION SESS ON SESS.PREV_HASH_VALUE = SQLAREA.HASH_VALUE AND SESS.PREV_SQL_ADDR = SQLAREA.ADDRESS
WHERE
  SESS.OSUSER NOT IN ('oracle')
  AND SESS.username NOT IN ('SYS', 'SYSTEM')
  AND SESS.type = 'USER'
  AND SESS.USERNAME IS NOT NULL
  AND (SESS.STATUS = 'ACTIVE' OR SESS.STATUS = 'INACTIVE')
  AND SQLAREA.SQL_TEXT LIKE 'SELECT%'
  AND (SESS.last_call_et > ${DURATION} OR (SYSDATE - SESS.logon_time) > 30 / 24);

COMMIT;

  SPOOL ON;
  spool ${LOGFILE}
  -- Kill SQL Script log creation:
  SET FEEDBACK OFF
  SET LINES 1000
  SET PAGES 0
  SELECT 'ALTER SYSTEM KILL SESSION ''' || SESS.sid||','||SESS.serial#||',@'||SESS.inst_id || ''' IMMEDIATE;'
  FROM  GV\$SQLAREA SQLAREA JOIN GV\$SESSION SESS ON SESS.PREV_HASH_VALUE = SQLAREA.HASH_VALUE AND SESS.PREV_SQL_ADDR = SQLAREA.ADDRESS
  WHERE
  SESS.OSUSER NOT IN ('oracle')
  AND SESS.username NOT IN ('SYS', 'SYSTEM')
  AND SESS.type = 'USER'
  AND SESS.USERNAME IS NOT NULL
  AND (SESS.STATUS = 'ACTIVE' OR SESS.STATUS = 'INACTIVE')
  AND SQLAREA.SQL_TEXT LIKE 'SELECT%'
  AND (SESS.last_call_et > ${DURATION} OR (SYSDATE - SESS.logon_time) > 30 / 24);
  SPOOL OFF;
END;
/
SQL
}

function kill_session1() {
echo ${ORACLE_SID}
export ORACLE_HOME=`grep "^${ORACLE_SID}:" /etc/oratab|cut -d: -f2 -s`
${ORACLE_HOME}/bin/sqlplus -s user/pass@${SERVICE_NAME}<<SQL
@@${LOGFILE}
  SET SERVEROUTPUT ON
  BEGIN
    SYS.DBMS_OUTPUT.PUT_LINE('Time 1: ' || TO_CHAR(SYSTIMESTAMP, 'hh24:mi:ss.ff'));
    SYS.DBMS_SESSION.SLEEP(3);
  END;
@@${LOGFILE}
SQL
}

if [ ${SESSIONS_COUNT} -gt 0 ]
         then
    echo "Found ${SESSIONS_COUNT} Candidate sessions to be killed!"
    #echo "Display Session which are to be killed"
    #run=$(get_session)
    #echo $run
    kill=$(kill_session)
    echo $kill
    run=$(kill_session1)
    #echo $run
    echo "Found ${SESSIONS_COUNT} SELECT INACTIVE SESSIONS TO BE KILLED" | mailx -s "Found ${SESSIONS_COUNT} SELECT INACTIVE SESSIONS TO BE KILLED ON ${ORACLE_SID} AT ${SERVICE_NAME} , PLEASE REVIEW THE LOGS" ${ML} < ${LOGFILE}
   echo "removing logfile"
   rm ${LOGFILE}
  else
    echo "No session found"
fi

DBADEEDS