Rescuing Crashing Databases: Real‑World Bad SQL Cases and How to Fix Them

Introduction

In the early hours of a busy day, a core business database spiked to 98% CPU and transaction response times exceeded ten seconds. An AWR report revealed a mysterious SQL running 200 times per second, exhausting I/O resources. The article uses this incident to showcase typical "bad SQL" patterns that raise DBA stress and demonstrates how to remediate them.

Case 1: Chaotic Multi‑Table Join

Problem scenario : A financial system query joining orders and payments with an Oracle‑specific (+) outer‑join syntax and an IN subquery on a blacklist caused an eight‑minute execution.

SELECT * FROM orders o, payments p
WHERE o.order_id = p.order_id(+) 
AND o.user_id IN (SELECT user_id FROM blacklist);

Analysis :

Abuse of the old outer‑join (+) prevents the optimizer from choosing the best join order.

The IN subquery triggers a FILTER operation that executes millions of times.

Optimization :

SELECT * FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
WHERE EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id = o.user_id);

Switching to ANSI JOIN makes the join logic explicit, allows hash‑join processing, and moves the filter into a more efficient EXISTS clause.

Case 2: NULL‑Value Query – The Invisible Killer

Problem scenario : A report system queried a 10 GB table for rows where remark IS NULL, resulting in a full‑table scan that took 180 seconds.

SELECT * FROM contracts
WHERE remark IS NULL;

Analysis :

Oracle does not index NULL values by default, so a single‑column index on remark is ineffective.

The existing composite index idx_contract does not include the NULL‑value marker column.

Optimization :

CREATE INDEX idx_remark_null ON contracts(remark, 0);
SELECT * FROM contracts
WHERE remark IS NULL AND 0 = 0;

Adding a constant placeholder column forces the index to store NULL rows, and the rewritten predicate can use the index for an index‑only scan.

Case 3: LOB Field Full‑Table Scan

Problem scenario : A contract table stored PDF content in a CLOB column. A query using LIKE '保密协议%' forced a full scan of the 10 GB LOB column, severely degrading I/O.

SELECT * FROM contract
WHERE PDF_CONTENT LIKE '保密协议%';

Optimization :

CREATE INDEX idx_contract_content ON contract(PDF_CONTENT)
INDEXTYPE IS CTXSYS.CONTEXT;
SELECT * FROM contract
WHERE CONTAINS(PDF_CONTENT, '保密协议') > 0;

Oracle Text creates a full‑text index optimized for large text fields, turning the scan into a fast index lookup.

Case 4: Batch Operations and Transaction Management

Problem 1 – Large Transaction : Deleting old rows from a billion‑row table filled the UNDO tablespace.

DELETE FROM billion_rows_table
WHERE create_time < SYSDATE-365;

Fix : Perform the delete in batches (e.g., 10 k rows per commit) to keep UNDO usage low.

Problem 2 – Inefficient INSERT ALL :

INSERT ALL INTO table_a VALUES (...)
SELECT * FROM dual;

Fix : Use direct‑path insert with the APPEND hint for bulk loading, achieving up to ten‑fold speed gains.

Problem 3 – TRUNCATE vs DELETE : DELETE FROM error_data; Fix : Replace with TRUNCATE or load data into a temporary table when the entire dataset can be discarded, avoiding row‑by‑row logging.

Conclusion – DBA Survival Rules

Every poorly written SQL is a teaching case; each performance crisis is an opportunity to demonstrate value. Instead of blaming developers, DBAs should build robust toolchains and preventive measures, because prevention always costs less than emergency rescue.