Oracle SQL性能事故案例报告

AI摘要

这是一份关于数据库性能事故的详细技术报告。内容属于【知识分享】，系统性地记录了某风控系统因SQL分页查询写法不当（CTE内排序+外层ROWNUM限制）导致接口性能严重劣化的事故。报告完整描述了事故现象、定位过程（发现高逻辑读SQL）、根本原因分析（排序算法缺陷、索引不完善）、紧急与长期解决方案（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 排序算法差异

对比项	原SQL（SORT ORDER BY）	优化SQL（WINDOW SORT PUSHED RANK）
排序方式	全排序	流式Top N排序
内存使用	所有匹配行	仅Top N行
停止时机	处理完所有行	找到足够行即停
逻辑读	高（8200次）	低（45次）

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. 系统影响

指标	正常值	事故期间峰值	增长倍数
数据库CPU	15%	85%	5.7倍
逻辑读/秒	50万	800万	16倍
API响应时间	50ms	800ms	16倍
应用线程池使用率	30%	100%	3.3倍

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测试）

指标	修复前	修复后	提升倍数
平均响应时间	450ms	15ms	30倍
逻辑读/次	8200	45	182倍
CPU使用率	85%	20%	4.25倍
线程池使用率	100%	35%	2.86倍

业务验证

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

经验教训

1. 技术层面

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

2. 流程层面

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

3. 管理层面

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

改进措施时间表

措施	负责人	完成时间	状态
紧急SQL修复	DBA团队	事故当日	✅完成
应用代码发布	开发团队	事故当日	✅完成
索引优化	DBA团队	3天内	🔄进行中
SQL审核规范	架构组	1周内	📅计划中
自动化测试框架	测试团队	2周内	📅计划中
监控告警增强	运维团队	3周内	📅计划中
开发培训	技术委员会	1月内	📅计划中

根本原因总结

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

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

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

每天一点小知识，到那都是大佬，哈哈

raybon

Laravel 8.x 译者 408 声望

php @ 远程

生活越细越好

0 人点赞

讨论数量: 0

(=￣ω￣=)··· 暂无内容！

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容，与人为善，比聪明更重要！

帮助