Killing a Session in Oracle Database

Introduction

• Focus: How to kill a particular session in an Oracle Database.
• Importance: Unwanted SQL queries consuming resources during business hours can slow down critical jobs.

Context

• Problem: Unintentional heavy resource consumption due to SQL queries (e.g., full table scans).
• Scenario: Application admin or developer runs resource-heavy queries to get reports or test during business hours.
• Impact: Slowness of critical jobs, leading to complaints.
• Goal: Learn the DBA perspective on killing such sessions.

Application and DBA Sessions

• Application Team: Often uses SQL Developer to execute SQL queries.
• DBA Session: Used to monitor and manage queries.

Example Query Causing Issues

• Query: SELECT * FROM dba_source executed by application team during business hours.
• Result: Full table scan causes other critical jobs to slow down.*

Steps to Kill a Session

1. Identify the Problem Query
   • Understand the action, module, and program being executed.
   • ACTION: Task being performed.
   • MODULE: Application package name.
   • PROGRAM: Similar to module.
2. Collect Necessary Details
   • Execute query to get module, program, action.
   • Example: SELECT module, program, action FROM v$session WHERE module IS NOT NULL AND program IS NOT NULL
   • Identify username, SQL ID, and SQL text.
   • SQL ID can be fetched from v$sql where the SQL text matches the query.
3. Retrieve Session Details
   • Obtain SID and serial number.
   • Example SQL: SELECT sid, serial#, username, module, program, action FROM v$session WHERE sql_id = '<SQL_ID>'
4. Kill the Session
   • Use ALTER SYSTEM KILL SESSION command.
   • Syntax: ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' IMMEDIATE
   • Verifies termination: Look for "connection lost" message.

Known vs. Unknown Activity

• Known Activity: Application team provides SQL details or SID/serial number.
  • Easier to identify and kill the session.
• Unknown Activity: No details provided; multiple processes.
  • DBA has to identify root and child processes to kill.

Next Steps

• OS Kill Command: For sessions that cannot be killed with ALTER SYSTEM KILL SESSION.
• To be discussed in the next session.