🚨 一次 Oracle 11g 数据库“卡死”事故复盘：LIKE + 大表 = 全表扫描雪崩
生产环境实录
Oracle 11g + 千万级大表
接口集体变慢、数据库负载飙升
最终定位到一条“看似无害”的 SQL
一、事故背景
某天线上系统突然出现：
- 接口响应从 200ms → 10s+
- 数据库会话大量堆积
- CPU、Buffer Gets 持续拉满
- 应用层无明显异常日志
👉 第一反应：数据库慢 SQL
二、快速定位正在执行的慢 SQL
查看执行超过 10 秒的 SQL
SELECT
s.sid,
s.serial#,
s.username,
s.program,
sq.sql_id,
SUBSTR(sq.sql_text, 1, 100) AS sql_text,
s.last_call_et AS run_seconds,
s.event
FROM v$session s
JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.status = 'ACTIVE'
AND s.username NOT IN ('SYS','SYSTEM')
AND s.last_call_et > 10
ORDER BY s.last_call_et DESC;
发现异常：
- 多个会话在执行相同 SQL
- 持续运行几十秒不结束
三、内存中慢 SQL 一览（已执行完但仍占资源）
SELECT
sql_id,
executions,
ROUND(elapsed_time/1000000,2) AS total_seconds,
buffer_gets,
rows_processed
FROM v$sql
WHERE parsing_schema_name = 'GG'
AND elapsed_time/1000000 > 5
ORDER BY elapsed_time DESC;
🚨 核心发现
CHECK_MEMBER表被反复 Table Full Scan- 单表 2000 万行
- 多会话并发扫描
四、问题聚焦：CHECK_MEMBER 表有多大？
SELECT
num_rows,
blocks,
avg_row_len,
last_analyzed
FROM dba_tables
WHERE owner='GSTPGG'
AND table_name='CEN_CHECK_MEMBER';
结果：
- 行数：20,496,308
- 数据块：440,297
- 典型“大表”
👉 大表 + 全表扫描 = 灾难
五、确认是否真的在“扫表”
SELECT
l.sid,
l.opname,
l.sofar,
l.totalwork,
ROUND(l.sofar/l.totalwork*100,2) AS pct
FROM v$session_longops l
WHERE l.opname LIKE '%Table Scan%'
AND l.time_remaining > 0;
结论
- 5 个会话
- 同时扫描同一张表
- 扫描进度 8%～28%
六、锁定真正的“元凶 SQL”
查看完整 SQL（防止被截断）
SELECT LISTAGG(sql_text,'')
WITHIN GROUP (ORDER BY piece)
FROM v$sqltext
WHERE sql_id='9jdbd64yqbwcf';
❌ 问题 SQL
SELECT ...
FROM CEN_CHECK_MEMBER
WHERE card_id LIKE :1
AND is_delete = :2;
七、为什么这条 SQL 会拖垮数据库？
1️⃣ LIKE 使用方式致命
card_id LIKE '%123%'
- 前置
% - B-Tree 索引直接失效
- Oracle 只能选择 全表扫描
2️⃣ 表太大 + 并发执行
- 单次扫描：2000 万行
- 执行次数：1000+
- 逻辑读：6 亿+
📌 这不是“慢”，是“必炸”
八、紧急止血：批量 Kill 问题会话
SELECT
'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;'
FROM v$session
WHERE sql_id='9jdbd64yqbwcf'
AND status='ACTIVE'
AND last_call_et>10;
⚠️ 止血，不是解决方案
九、真正的解决方案
✅ 方案一：正确的组合索引（最推荐）
CREATE INDEX IDX_CEN_CHECK_MEMBER_CARD_DEL
ON CEN_CHECK_MEMBER(card_id, is_delete)
NOLOGGING
PARALLEL 8;
前提：
card_id LIKE '123%'
⚠️ 方案二：必须 %xxx% 怎么办？
CREATE INDEX IDX_CEN_CHECK_MEMBER_CARD_REV
ON CEN_CHECK_MEMBER(REVERSE(card_id));
SQL 改写：
WHERE REVERSE(card_id) LIKE REVERSE(:1)
十、SQL 层面的最佳实践
❌ 错误示例
SELECT *
FROM CEN_CHECK_MEMBER
WHERE card_id LIKE '%123%';
✅ 正确姿势
SELECT mem_number, mem_name, card_id
FROM CEN_CHECK_MEMBER
WHERE card_id LIKE '123%'
AND is_delete = 0
FETCH FIRST 50 ROWS ONLY;
十一、给后端同学的忠告
任何一条 SQL，只要满足下面任意一条，就必须评审：
- LIKE
%xxx% - 无分页
- 大表 SELECT *
- 高频接口
- 列表查询
十二、总结
本次事故一句话总结
LIKE + 大表 + 高并发
≈ Oracle 11g 原地爆炸
最重要的三条经验
最重要的三条经验
%xxx%基本等于放弃索引- 大表查询永远要分页
- SQL 性能问题，90% 是“写法问题”
