The Oracle Killer: Infrastructure-Level Slow Query Remediation | System Design Vault

The Context: "The Locking Logjam"

It was a Tuesday afternoon. The primary Oracle RAC cluster began reporting an unusual increase in enq: TX - row lock contention. A junior developer had accidentally pushed an unoptimized "End-of-Month Analytics" query to the production dashboard. The query was performing a full table scan on the LEDGER_ENTRIES table (400M+ rows) while holding a shared lock.

The application connection pool (HikariCP) was set to 50 connections. Within 3 minutes, all 50 threads were stuck waiting for this one transaction. The health checks couldn't even get a simple SELECT 1 FROM DUAL to verify connectivity. The entire banking portal went dark. Total baigan design.

The Problem Statement

Database timeouts are usually enforced at the JDBC or Application level. However:

Orphaned Sessions: If the application crashes or the network cuts, the query continues running on the Oracle server, wasting IO and CPU.
Shared Resources: A long-running query can hold locks that block high-priority transactions, regardless of the application's timeout.
Noisy Neighbors: In a microservice environment, one service's bad query can starve the entire DB cluster's CPU.

We needed a "Database Executioner" that could identify and kill these sessions at the source.

Visual Architecture

The Core Logic / Process

We didn't want to rely on the application to be "well-behaved." Instead, we built a Python-based executioner service running in Kubernetes that connects to the DB with SYSOPER privileges.

1. The Monitor

Every 5 seconds, the Executioner runs a query against v$session and v$sql_monitor.

SELECT sid, serial#, username, last_call_et, sql_id
FROM v$session
WHERE status = 'ACTIVE'
AND type != 'BACKGROUND'
AND last_call_et > 60;

last_call_et tells us how many seconds the current call has been active.

2. The Judge

The judge checks the session against a whitelist/blacklist:

Whitelist: Batch jobs (e.g., Spring Batch) are allowed 2 hours.
Blacklist: User-facing web services are killed after 60 seconds.
Circuit Breaker: If the Executioner sees more than 10 sessions from the same SQL_ID exceeding limits, it kills all of them immediately.

3. The Execution

The Executioner issues the kill command:

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

The "Baigan" Take (Expert Insights)

Many "DBAs" (Database Archons, I call them) hate this. They say, "You should just tune the query!" Well, yeah, obviously. But tuning a query takes hours of PR reviews and testing. A rogue query takes 30 seconds to kill your business.

The baigan mistake here is killing sessions without taking a "Evidence Snapshot" first. If you just kill the session, the developer will say, "My query was fine, it must be the network!"

Before issuing the KILL command, the Executioner MUST run DBMS_SQLTUNE.REPORT_SQL_MONITOR for that SQL_ID and dump the HTML report to an S3 bucket or internal wiki. Now, when the developer complains, you send them the report showing exactly where their nested loop was a total baigan idea.

Performance Checklist

Use a dedicated DB user with the literal bare minimum privileges needed to kill sessions.
Implement a "Safety Valve": If the Executioner tries to kill > 50% of total sessions, it should abort and page a human (to prevent mass disconnection during a legitimate load spike).
Run the Executioner in a different availability zone/cluster than the application to ensure it remains responsive even if the app network is saturated.
Log the MACHINE, OSUSER, and PROGRAM columns from v$session to identify the specific pod responsible for the rogue query.