Oracle SQL性能事故案例报告

事故概述

事故编号

DB-PERF-2026-001

事故级别

P2（高优先级性能问题）

影响范围

• 系统：风控决策系统
• 模块：用户信息查询模块
• 影响时间：2026年X月X日 10:00 - 2026年X月X日 16:30
• 影响用户：所有通过API查询用户联系信息的客户端

事故现象

用户反馈

• 用户信息查询接口响应时间从平均50ms增加到800ms以上
• 部分查询超时（超过3秒）
• 系统监控显示数据库CPU使用率从15%飙升到85%

监控告警

10:15:00 - 数据库慢查询告警：t_apply_risk_extend_params表查询超时 10:30:00 - 应用服务器线程池满告警 11:00:00 - API平均响应时间超过1秒告警

问题定位过程

第一阶段：初步排查（1000）

1.1 查看数据库负载

-- 查看当前活跃会话 SELECT sid, serial#, username, program,  sql_id, event, seconds_in_wait FROM v$session WHERE status = 'ACTIVE' AND username = 'APP_USER'; -- 结果：发现大量会话等待事件为'read by other session'

1.2 识别问题SQL

-- 查找高负载SQL SELECT sql_id, sql_text, executions, elapsed_time/1000000 as elapsed_sec, buffer_gets, disk_reads, rows_processed FROM v$sql WHERE elapsed_time/executions/1000 > 100 -- 平均超过100ms AND executions > 1000 ORDER BY elapsed_time DESC; -- 发现TOP 1问题SQL SQL_ID: 8q3m7n5v2s1t9 执行次数：1,200,000 平均耗时：450ms 总耗时：540,000秒（150小时）

第二阶段：深入分析（1100）

2.1 获取问题SQL详情

-- 查看SQL文本 SELECT sql_fulltext FROM v$sql WHERE sql_id = '8q3m7n5v2s1t9'; -- SQL内容： WITH USER_SQL AS ( SELECT contact FROM t_apply_risk_extend_params WHERE (id_no=:1) AND (NOT (contact IS NULL)) ORDER BY apply_risk_id DESC ), PAGINATION AS ( SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL ) SELECT * FROM PAGINATION WHERE rownum <= 2;

2.2 分析执行计划

-- 获取执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('8q3m7n5v2s1t9', NULL, 'ALLSTATS LAST')); -- 执行计划显示： Plan hash value: 2069830097 ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 |00:00:00.45| 8200| |* 1 | COUNT STOPKEY | | 1 | 2 |00:00:00.45| 8200| | 2 | VIEW | | 1 | 6 |00:00:00.45| 8200| | 3 | COUNT | | 1 | 6 |00:00:00.45| 8200| | 4 | VIEW | | 1 | 6 |00:00:00.45| 8200| | 5 | SORT ORDER BY | | 1 | 6 |00:00:00.45| 8200| |* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T_APPLY_RISK_EXTEND_PARAMS | 1 | 6 |00:00:00.01| 6| |* 7 | INDEX RANGE SCAN | IDX_ID_NO | 1 | 6 |00:00:00.01| 3| -----------------------------------------------------------------------------------------------------------------------

关键发现：

• 每次执行需要8200个buffer gets（逻辑读）
• 虽然只返回2行，但需要排序6行
• 存在SORT ORDER BY操作

第三阶段：对比测试（1200）

3.1 测试优化版本

-- 测试FETCH FIRST版本 EXPLAIN PLAN FOR SELECT contact FROM t_apply_risk_extend_params WHERE id_no = :id_no AND contact IS NOT NULL ORDER BY apply_risk_id DESC FETCH FIRST 2 ROWS ONLY; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -- 结果： Plan hash value: 2624921305 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 4056 | 6 (17)| 00:00:01 | |* 1 | VIEW | | 2 | 4056 | 6 (17)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK | | 6 | 198 | 6 (17)| 00:00:01 | |* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T_APPLY_RISK_EXTEND_PARAMS | 6 | 198 | 5 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IDX_ID_NO | 6 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------------

3.2 性能对比测试

-- 实际执行测试 SET TIMING ON SET AUTOTRACE TRACEONLY STATISTICS -- 原SQL（CTE+ROWNUM） VAR id_no VARCHAR2(20); EXEC :id_no := '610115200112177901'; WITH USER_SQL AS (...) SELECT * FROM PAGINATION WHERE rownum <= 2; -- 结果： 统计信息： 0 recursive calls 0 db block gets 8200 consistent gets -- 高逻辑读！ 0 physical reads 0 redo size 450 elapsed: 00:00:00.45 -- 优化SQL（FETCH FIRST） SELECT contact FROM ... FETCH FIRST 2 ROWS ONLY; -- 结果： 统计信息： 0 recursive calls 0 db block gets 45 consistent gets -- 逻辑读大幅降低！ 0 physical reads 0 redo size 15 elapsed: 00:00:00.015

根本原因分析

1. 技术原因

1.1 SQL写法缺陷

-- 问题写法：CTE内排序 + 外层ROWNUM限制 WITH USER_SQL AS ( SELECT ... ORDER BY ... -- 必须完整执行排序 ) SELECT * FROM USER_SQL WHERE rownum <= 2; -- 限制在外层

问题：

• Oracle必须完全执行CTE中的查询（包括排序所有匹配行）
• 然后才能应用外层的ROWNUM限制
• 即使只需要2行，也要排序所有匹配行

1.2 排序算法差异

1.3 索引不完善

现有索引：

CREATE INDEX IDX_ID_NO ON t_apply_risk_extend_params(id_no);

缺陷：

• 不包含排序列（apply_risk_id）
• 不包含查询列（contact）
• 导致回表操作和额外排序

2. 流程原因

2.1 开发阶段

• 开发人员使用YII框架自动生成分页查询
• 未对生成的SQL进行性能审查
• 缺乏SQL性能测试规范

2.2 测试阶段

• 测试环境数据量小（仅测试数据）
• 未进行大数据量性能测试
• 未对比不同SQL写法的性能差异

2.3 上线阶段

• 未进行SQL执行计划审查
• 缺乏生产环境性能基线
• 监控告警阈值设置不合理

影响评估

1. 业务影响

• 影响时长：6.5小时
• 影响用户：约50,000名活跃用户
• 失败请求：约120,000次
• 业务损失：约200,000元（估算）

2. 系统影响

3. 资源浪费

-- 事故期间资源浪费计算 总执行次数：1,200,000次 每次浪费逻辑读：8200 - 45 = 8155次 总浪费逻辑读：1,200,000 × 8155 = 9,786,000,000次（98亿次） 每次浪费时间：450ms - 15ms = 435ms 总浪费时间：1,200,000 × 0.435 = 522,000秒 = 145小时

解决方案

紧急处理（已执行）

1. SQL热修复（1330）

-- 创建SQL Patch强制使用优化计划 DECLARE l_sql_text CLOB; l_patch_name VARCHAR2(30); BEGIN -- 获取原SQL文本 SELECT sql_fulltext INTO l_sql_text FROM v$sql WHERE sql_id = '8q3m7n5v2s1t9'; -- 创建SQL Patch l_patch_name := 'FIX_SQL_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MI'); DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH( sql_text => l_sql_text, hint_text => 'ORDERED USE_NL(@"SEL$1" "T_APPLY_RISK_EXTEND_PARAMS"@"SEL$1")', name => l_patch_name ); -- 立即刷新共享池 EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH SHARED_POOL'; END; /

2. 应用紧急发布（1300）

// 修改YII框架查询代码 // 原代码： $query = "WITH USER_SQL AS (...) SELECT * FROM PAGINATION WHERE rownum <= 2"; // 修改为： $query = "SELECT contact FROM t_apply_risk_extend_params WHERE id_no = :id_no AND contact IS NOT NULL ORDER BY apply_risk_id DESC FETCH FIRST 2 ROWS ONLY";

3. 创建临时索引（1415）

-- 创建覆盖索引加速查询 CREATE INDEX idx_apply_risk_temp ON t_apply_risk_extend_params (id_no, apply_risk_id DESC, contact) PARALLEL 8 NOLOGGING; -- 收集统计信息 BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => 'RISK_USER', tabname => 'T_APPLY_RISK_EXTEND_PARAMS', estimate_percent => 10, cascade => TRUE, degree => 8 ); END; /

中期优化（1周内完成）

1. 索引优化

-- 评估并创建最优索引 -- 方案1：复合索引（推荐） CREATE INDEX idx_apply_risk_optimal ON t_apply_risk_extend_params (id_no, apply_risk_id DESC, contact) TABLESPACE risk_idx STORAGE (INITIAL 256M NEXT 64M) COMPRESS 2 PARALLEL 8 NOLOGGING; -- 方案2：函数索引（如果需要） CREATE INDEX idx_apply_risk_func ON t_apply_risk_extend_params (id_no, NVL(contact, 'NULL')) TABLESPACE risk_idx;

2. SQL审核规范

-- 创建SQL审核规则 BEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'HIGH_RISK_SQL_PATTERNS', description => '高风险SQL模式' ); -- 添加监控规则 DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name => 'HIGH_RISK_SQL_PATTERNS', populate_cursor => DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'sql_text LIKE ''%WITH%ROWNUM%'' AND sql_text LIKE ''%ORDER BY%''' ) ); END; /

3. 性能监控增强

-- 创建性能监控视图 CREATE OR REPLACE VIEW vw_slow_pagination AS SELECT sql_id, SUBSTR(sql_text, 1, 100) as sql_snippet, executions, elapsed_time/executions/1000 as avg_ms, buffer_gets/executions as avg_gets, disk_reads/executions as avg_reads, last_active_time FROM v$sql WHERE (UPPER(sql_text) LIKE '%WITH%SELECT%ROWNUM%' OR UPPER(sql_text) LIKE '%CTE%ROWNUM%') AND elapsed_time/executions/1000 > 100 -- 超过100ms AND last_active_time > SYSDATE - 1; -- 最近1天活跃

长期预防（1个月内完成）

1. 开发规范制定

《Oracle SQL开发规范 V2.0》 第3章 分页查询规范 3.1 禁止使用的模式： 1. CTE内排序 + 外层ROWNUM限制 2. 多层嵌套视图 + ROWNUM 3. 全表扫描 + ORDER BY + ROWNUM 3.2 推荐写法： 1. Oracle 12c+：使用FETCH FIRST语法 2. 所有版本：子查询内排序 + ROWNUM限制 3. 必须创建合适的复合索引 3.3 强制审查项： 1. 所有分页查询必须提供执行计划 2. 禁止出现SORT ORDER BY操作 3. 逻辑读/次不得超过1000

2. 自动化测试框架

# SQL性能自动化测试脚本 class SQLPerformanceTest: def test_pagination_sql(self, sql_text): """测试分页SQL性能""" # 1. 检查SQL模式 if self._has_high_risk_pattern(sql_text): return False, "包含高风险模式：CTE+ROWNUM" # 2. 获取执行计划 plan = self._get_execution_plan(sql_text) # 3. 检查排序操作 if "SORT ORDER BY" in plan: return False, "存在全排序操作" # 4. 性能测试 stats = self._execute_with_stats(sql_text) if stats['buffer_gets'] > 1000: return False, f"逻辑读过高：{stats['buffer_gets']}" return True, "测试通过"

3. 监控告警体系

-- 创建实时监控作业 BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'MONITOR_SLOW_PAGINATION', job_type => 'PLSQL_BLOCK', job_action => ' DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM vw_slow_pagination; IF v_count > 0 THEN -- 发送告警邮件 UTL_MAIL.SEND( sender => ''dba@company.com'', recipients => ''dev-team@company.com'', subject => ''慢分页查询告警'', message => ''发现' || v_count || ''个慢分页查询'' ); END IF; END;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=MINUTELY;INTERVAL=5', enabled => TRUE ); END; /

验证结果

修复后性能对比（14:30测试）

业务验证

• 15:00：所有API响应时间恢复正常（<50ms）
• 15:30：用户反馈查询超时问题解决
• 16:00：数据库CPU使用率降至正常水平（15-20%）

经验教训

1. 技术层面

• SQL写法比索引更重要：糟糕的SQL写法即使有索引也会性能差
• 理解Oracle优化器：不同SQL写法触发不同的优化路径
• 生产环境测试：必须在真实数据量下测试性能

2. 流程层面

• 代码审查必须包含SQL：特别是框架生成的SQL
• 性能测试标准化：建立性能测试基线
• 监控预警前置：在用户投诉前发现问题

3. 管理层面

• 建立SQL知识库：收集和分享最佳实践
• 定期性能巡检：主动发现潜在问题
• 建立应急预案：快速响应生产问题

改进措施时间表

根本原因总结

本次事故的根本原因是开发框架生成的SQL使用了不合理的CTE+ROWNUM分页写法，导致Oracle必须对所有匹配行进行完整排序，即使只需要返回前2行。这种写法在测试环境（数据量小）表现正常，但在生产环境（数据量大）暴露出严重的性能问题。

核心教训：对于分页查询，必须使用能够利用”提前停止”优化的写法（如FETCH FIRST或子查询+ROWNUM），并创建合适的复合索引。

本作品采用《CC 协议》，转载必须注明作者和本文链接