Kill vs CANCEL a deep dive into SQL and Session management

Kill vs CANCEL a deep dive into SQL and Session management

1. What is different between Kill a Session and
Canceling a SQL statement in Oracle?
When use Kill command?

When use Cancel command?

Which command if preferred in multiple
situations?

DATABASE
BOX

Alireza Kamrani

Concepts Overview:

In Oracle Database, both canceling SQL and
killing a session are ways to stop something
that's running, but they act at different levels and
have different impacts.

2. 1. CANCEL SQL

What it does

+ Cancels only the currently running SQL
statement in a session.

+ The session remains connected.

+ The user can continue to issue other SQL
statements after cancellation.

How it's done:

From another session (for example, as DBA):
ALTER SYSTEM CANCEL SQL
'sid,serial#,@inst_id';

+ Requires the ALTER SYSTEM privilege.

e Available from Oracle 10g onward.

e The SQL must currently be active (executing,

not waiting or idle).

When to use:
Use CANCEL SQL when:
e You want to stop a long-running query but
don't want to disconnect the user.
+ The session is executing a query that's
hogging resources but the user still needs to

3. stay connected.

What happens internally

+ Oracle tries to safely interrupt the running
SQL.

+ The session receives an ORA-01013: user
requested cancel of current operation.

e If the SQL was performing DML (INSERT/
UPDATE/DELETE), Oracle rolls back that
statement's work only (not the whole
transaction).

DATABASE
BOX

2. KILL SESSION:

What it does

+ Terminates the entire session.

e Rolls back any uncommitted transactions.
+ Eventually releases all locks and resources.
+ The user is disconnected.

4. How it's done:

From another session:

ALTER SYSTEM KILL SESSION 'sid,serial#'
IMMEDIATE;

Optionally, include @inst_id for RAC.
Or at the OS level (if the session is hung and not
responding):

e Find the OS process (SPID): SELECT s.sid,
s.serial#, p.spid FROM v$session s JOIN
v$process p ON s.paddr = p.addr WHERE
s.sid = <sid>;

e Kill the process from the OS (Linux
example): kill -9 <spid>

When to use:
Use KILL SESSION when:
© The session is hung, unresponsive, or
blocking other sessions.
+ The user can reconnect later.
+ CANCEL SQL didn't work or the session is
idle-in-transaction.

5. What happens internally:
+ The session is marked as “killed”.
+ The process eventually ends (sometimes
immediately, sometimes after cleanup).
e If you used IMMEDIATE, Oracle forces
disconnection and rollback right away.

Tip:
If you're not sure which to use:
e Try CANCEL SQL first — it's gentler and
doesn't disconnect the user.
e If that fails or the session is not responding
— then use KILL SESSION.

DATABASE
BOX

6. What happened in both scenarios if we are on
RAC with TAC

Let's break it down carefully:
Background: RAC and TAC:

+ RAC (Real Application Clusters) = multiple
instances accessing one database.

+ Each instance has its own SID and
background processes.

+ Sessions connect to one instance at a time.

e TAC (Transparent Application Continuity) =
Oracle feature that replays database
requests transparently after certain failures
— to make failures invisible to the
application.

So when you cancel or kill a session in RAC, the
impact depends on:
« Which instance the session is on.
+ Whether TAC is enabled for the client/
service.

7. 1. ALTER SYSTEM CANCEL SQL on RAC

What happens:

+ The cancel command can target a session
on any instance if you specify @inst_id.
ALTER SYSTEM CANCEL SQL
'sid,serial#,@inst_id';

e Oracle sends a message via the Global
Cache Service (GCS) to the target instance.

e The target instance stops only that SQL,
same as in single-instance Oracle.

+ The session remains connected to its
instance and can continue.

With TAC:

e Since the session is not terminated, TAC
doesn't need to replay anything.

+ From the client's view, the current SQL may
error with ORA-01013, but the session stays
alive.

+ TAC won't intervene — it only acts on failures
or disconnections, not cancellations.

8. Result:

Gentle stop. The SQL stops; the connection
remains stable. TAC is unaffected.

2. ALTER SYSTEM KILL SESSION on RAC

What happens:

+ You can kill a session on the same instance:
ALTER SYSTEM KILL SESSION 'sid,serial#'
IMMEDIATE;

+ Or on another instance in the cluster: ALTER
SYSTEM KILL SESSION 'sid,serial#,@inst_id'
IMMEDIATE;

+ The command is routed via the cluster
interconnect to the target instance.

+ The target instance terminates the session,
rolls back uncommitted work, and frees
resources.

e If IMMEDIATE is not used, it's a “polite” kill —
session marked as killed, cleaned up when it
becomes active again.

9. With TAC:
Now this gets interesting:

From the client perspective:

The connection to that instance is lost — the
application sees a break or reset.

If TAC is configured for that service:

TAC detects the failure.

It replays the last committed request
automatically to another surviving instance.
The app session continues as if nothing
happened — transparent recovery.

If TAC is not configured:

The application receives an ORA-03113 or
ORA-03135 (end-of-file / connection lost) and

DATABASE
BOX

must reconnect manually.

Result:

The session dies on that instance.
With TAC, the connection may transparently
move to another instance and replay.

10. e Without TAC, the user is disconnected.
Therefore:

e Try CANCEL SQL first — safe across RAC, no
impact to TAC or other instances.

e Use KILL SESSION only when necessary —
can cause application failover or replay.

e If using TAC, configure services with
-replay_init_time and commit_outcome =
TRUE to ensure smooth replay on kill.

+ Always specify @inst_id when managing
sessions across RAC instances: SELECT
inst_id, sid, serial#, username, status FROM
gv$session;

Although in some critical cases DBA may decide
to run Kill command to rescue database and
bring it to a reliable state from bad SQL queries
or bad user session with a high performance
degradation.