业务停产3小时,竟然是一条SQL惹的祸!
👋 我是三笠,一名专注数据库运维与架构的 DBA,平时分享数据库实战经验与技术笔记,个人网站:ora100.com

前言
昨天下班路上,突然接到产线电话,反馈 MES 系统出现严重卡顿,已经影响到正常生产。到家后,第一时间登录数据库检查,发现两个 RAC 实例运行正常,监听和连接也没有明显异常。
数据库没有宕机,业务却无法正常使用,看来有可能是性能问题。经过一番排查后,我发现问题竟然出在一条查询 SQL 上。
更值得注意的是,这次故障再次验证了 Oracle 19c CDB 架构下大家很容易忽略的一个问题:
不同 PDB 虽然在逻辑上相互隔离,但底层资源并不是完全独立的。一个 PDB 的高负载操作,可能会对同一个 CDB 下的其他业务产生影响。
本文记录一下这次生产故障的排查过程,以及后续对 PDB 资源竞争问题的一些讨论。
一、业务反馈系统卡顿,先从 AWR 和 ASH 入手
接到业务反馈后,我先检查了 Oracle 19c RAC 两个实例的运行状态,数据库均正常 OPEN,监听和连接也没有明显异常。
随后,跟产线确认了具体的故障时间,我抓取了对应时段的 AWR 和 ASH 报告,重点从数据库负载、SQL 执行情况以及主要等待事件几个方面入手。

在 AWR 报告中,通过等待事件可以发现等待主要发生在 IO 上:

接着通过 SQL ordered by Gets 和 SQL ordered by Reads 两个部分信息:

很快我就发现了一条异常 SQL:它的逻辑读和物理读都排在报告前列,而且消耗的数据库资源明显高于其他业务 SQL。
为了进一步确认问题,我又通过 GV$SESSION、GV$SQL 等视图检查这条 SQL 在两个 RAC 实例上的执行情况。
从历史执行统计来看:
| 指标 | 实例 1 | 实例 2 |
|---|---|---|
| 执行次数 | 50 次 | 54 次 |
| 累计耗时 | 277,746 秒 | 203,188 秒 |
| 平均耗时 | 92.6 分钟 | 62.7 分钟 |
| BUFFER_GETS | 18.17 亿 | 24.28 亿 |
| DISK_READS | 18.16 亿 | 24.26 亿 |
单次执行竟然需要一个多小时,而且累计逻辑读和物理读都达到了数十亿级别。
这显然不是一条普通的业务查询。
继续查看 SQL 完整文本,发现它主要是根据 BIZ_PARAM_ID 查询业务参数明细,查询条件使用 IN,传入多个绑定变量。
再往下追,发现它访问的表是: APP_SCHEMA.FACT_PARAM_DETAIL,这张表的数据量已经超过 36 亿行。
看到这里,我开始怀疑问题可能出在这张表的访问路径上。
对于这样一张超大表,如果查询没有使用合适的索引,很容易产生大量不必要的扫描和 I/O。
二、36 亿行的大表,查询竟然走全表扫描
确定 SQL_ID 后,我首先查看了它的执行计划:
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
'&SQL_ID',
0,
'ALLSTATS LAST +PREDICATE +PEEKED_BINDS'
)
);
执行计划如下:
---------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | TABLE ACCESS FULL| FACT_PARAM_DETAIL | 602K |
---------------------------------------------------------------
问题找到了,这条 SQL 走的是 TABLE ACCESS FULL,也就是全表扫描。
我又检查了一下这张表的基本信息:
OWNER : APP_SCHEMA
TABLE_NAME : FACT_PARAM_DETAIL
NUM_ROWS : 3,624,381,925
BLOCKS : 52,312,820
表的数据量已经超过 36 亿行。
再看 SQL 本身,它主要通过 BIZ_PARAM_ID 查询对应的业务参数明细,查询条件使用的是 IN,传入多个绑定变量。
我接着检查了 BIZ_PARAM_ID 的列统计信息:
COLUMN_NAME : BIZ_PARAM_ID
NUM_DISTINCT : 6,011,392
NUM_NULLS : 0
DENSITY : 1.6635E-07
这个字段有约 601 万个不同值,而且没有空值。
结合业务查询条件、字段区分度以及当前执行计划,我认为可以通过建立单列索引,为优化器提供更合适的访问路径。
不过,这张表的数据量实在太大,直接建索引可能会带来较大的资源消耗,不能只考虑 SQL 本身,还需要兼顾现有业务的运行情况。
三、和业务及应用厂商沟通,确定索引优化方案
确认问题方向后,我把 SQL 文本、执行计划以及相关统计信息整理出来,与业务人员和应用厂商进行了沟通。
主要讨论了两个问题:
- 当前 SQL 为什么会走全表扫描,是否可以通过调整查询条件或者增加索引改善访问路径;
- 这张表的数据量已经超过 36 亿行,创建索引是否会对现有业务产生影响。
从目前的 SQL 查询条件来看,BIZ_PARAM_ID 是比较明确的过滤字段,而且具有较高的区分度。
结合现有执行计划,我提出在该字段上创建单列索引的优化方案。
经过沟通确认,大家认可这个方向,决定先通过增加索引改善当前 SQL 的访问路径。
考虑到表的数据量较大,为了缩短索引创建时间,采用并行方式创建:
CREATE INDEX APP_SCHEMA.IDX_FACT_PARAM_ID
ON APP_SCHEMA.FACT_PARAM_DETAIL(BIZ_PARAM_ID)
TABLESPACE APP_INDEX_TBS
PARALLEL 8;
索引创建完成后,将索引的并行属性调整为 NOPARALLEL,并收集索引统计信息:
-- 取消索引并行度
ALTER INDEX APP_SCHEMA.IDX_FACT_PARAM_ID NOPARALLEL;
-- 收集统计信息
BEGIN
DBMS_STATS.GATHER_INDEX_STATS(
OWNNAME => 'APP_SCHEMA',
INDNAME => 'IDX_FACT_PARAM_ID',
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
DEGREE => 8,
NO_INVALIDATE => FALSE
);
END;
/
至此,索引创建完成。
四、执行计划发生变化,终于不再全表扫描
接下来,我重新检查了原业务 SQL 的执行计划,看看优化器是否已经选择了新的访问路径。
优化前:
TABLE ACCESS FULL
FACT_PARAM_DETAIL
优化后:
TABLE ACCESS BY INDEX ROWID BATCHED
INDEX RANGE SCAN
IDX_FACT_PARAM_ID
执行计划哈希值也发生了变化:
优化前:3287208169
优化后:1135390774
原来需要扫描整张表的查询,现在已经可以通过新建索引进行 INDEX RANGE SCAN,再根据 ROWID 回表获取所需数据。
从执行计划来看,优化器已经选择了新的访问路径。
不过,我们不能仅凭执行计划就结束这次优化,而是需要继续观察业务 SQL 的实际执行情况。
五、单次执行从 62 分钟降到 12 秒
通过 GV$SQL 查看新执行计划对应的执行次数、累计耗时、逻辑读和物理读等指标,我整理了优化前后的数据:
| 指标 | 优化前 | 优化后 |
|---|---|---|
| 执行计划 | 全表扫描 | 索引范围扫描 |
| PLAN_HASH_VALUE | 3287208169 | 1135390774 |
| 执行次数 | 54 次 | 2 次 |
| 平均执行耗时 | 3,762.75 秒 | 12.5 秒 |
| BUFFER_GETS | 24.28 亿 | 292,527 |
| DISK_READS | 24.26 亿 | 24,268 |
从这组数据来看,单次执行耗时从原来的约 62.7 分钟下降到了 12.5 秒。
按照当前统计口径计算,执行速度提升约 301 倍。
与此同时,逻辑读和物理读也大幅下降,并且从执行计划变化和实际执行数据来看,这次索引优化已经取得了非常明显的效果。
一条长期走全表扫描、单次执行需要一个多小时的 SQL,通过增加一个合适的索引,最终将执行时间缩短到了十几秒。
原本困扰产线的 SQL 问题,至此有了明确的优化结果。
不过,这次排查并没有就此结束。
在继续分析 AWR 和 ASH 报告时,我还注意到一个值得关注的现象:同一个 CDB 下的两个业务 PDB,在故障时段都出现了比较明显的 I/O 活动。
这让我开始重新审视当前数据库的 CDB/PDB 架构。
六、一个 PDB 出现高负载,其他 PDB 会不会受到影响?
先介绍一下当前数据库架构。
这套 Oracle 19c RAC 采用的是 CDB/PDB 多租户架构,同一个 CDB 下承载了多个业务 PDB:
| PDB | 业务类型 |
|---|---|
| PDB_A | 生产业务 |
| PDB_B | 质量管理 |
| PDB_C | 设备管理 |
| PDB_D | 统计分析 |
| PDB_E | 报表业务 |
从架构上看,每个 PDB 都有独立的数据字典、业务对象和用户权限,不同业务系统可以运行在各自的 PDB 中。
但这里有一个容易被忽略的问题:
PDB 的逻辑隔离,并不代表底层资源也完全隔离。
同一个 CDB 下的多个 PDB,最终还是运行在同一套数据库实例和底层基础设施上。
例如:
- CPU:多个 PDB 的会话最终都需要竞争数据库实例和操作系统的 CPU 资源。
- 内存:PDB 使用的工作区、PGA,以及实例级共享内存资源,都受到整个实例资源的影响。
- 存储 I/O:不同 PDB 的数据文件即使逻辑上分开,底层仍可能使用相同的 ASM 磁盘组和存储设备。
- 并行执行:一个 PDB 发起大量并行操作,可能占用较多 PX Server、CPU 和 I/O 资源,影响其他业务的响应速度。
也就是说,一个 PDB 内部的 SQL 出现问题,不一定只影响这个 PDB 自己。
尤其是在多个 PDB 共用一套 RAC 实例和存储资源的情况下,更需要关注跨 PDB 的资源竞争。
七、从 ASH 报告中发现,两个 PDB 同时出现高 I/O
为了验证这个判断,我重新回到 ASH 报告,按照 PDB 维度分析故障时段的活动会话。
当时重点关注的是两个业务 PDB。
排查结果显示,在同一故障时段,两个 PDB 都出现了比较明显的高 I/O 活动:
- PDB_A:一条 SQL 的 ASH 采样记录达到千条以上。
- PDB_B:前面定位的异常 SQL 出现了数百条
direct path read等待采样记录。
这说明两个 PDB 在同一时间段都存在较高的数据库活动,而且都涉及 I/O 相关操作。
这里可以通过 GV$ACTIVE_SESSION_HISTORY 按 PDB、SQL_ID 和等待事件进行统计:
SET LINESIZE 200
SET PAGESIZE 100
SELECT
CON_ID,
SQL_ID,
EVENT,
SESSION_STATE,
COUNT(*) AS ASH_SAMPLES
FROM GV$ACTIVE_SESSION_HISTORY
WHERE SAMPLE_TIME BETWEEN :BEGIN_TIME AND :END_TIME
AND CON_ID IN (
SELECT CON_ID
FROM V$PDBS
WHERE NAME IN ('PDB_A', 'PDB_B')
)
GROUP BY
CON_ID,
SQL_ID,
EVENT,
SESSION_STATE
ORDER BY ASH_SAMPLES DESC;
通过这个查询,可以把不同 PDB 的活动会话放在一起观察,避免只关注某一个 PDB,而忽略了同一时段其他业务的资源消耗。
这里需要注意,ASH 记录的是采样时刻的活动会话,不等于 SQL 实际执行次数。千条采样记录也不代表执行了千次 SQL。
但这些记录能够帮助我们判断:在某个时间段内,哪些 PDB、哪些 SQL 占用了较多的数据库活动时间,以及主要集中在哪些等待事件上。
结合之前的 SQL 执行统计,可以看到其中一个 PDB 内部的大表查询确实产生了非常可观的 I/O 消耗。
而另一个 PDB 在同一时段也存在较高的 I/O 活动。
这至少说明,不能只从单个 PDB 的角度分析数据库性能问题,还需要把整个 CDB 的资源使用情况结合起来看。
当然,仅凭 ASH 采样时间重叠,还不能直接认定其中一个 PDB 的 SQL 导致了另一个 PDB 卡顿。要确认两者之间是否存在直接的资源竞争关系,还需要结合操作系统 I/O、ASM 磁盘组负载、实例 CPU 使用情况以及两个 PDB 的资源消耗进一步验证。
八、CDB/PDB 架构下,资源隔离到底应该怎么做?
通过这次排查,我觉得有必要重新认识一下 Oracle 多租户架构中的资源隔离。
很多时候,我们在规划 CDB/PDB 架构时,会认为不同业务放在不同 PDB 中,就已经实现了业务隔离。
但实际上,PDB 主要提供的是数据库逻辑层面的隔离,并不意味着每个 PDB 都拥有独立的 CPU、内存和存储资源。
如果没有进一步配置资源管理策略,那么某个 PDB 内部出现大量并行 SQL、全表扫描或者大批量数据处理时,仍然可能影响同一个 CDB 下的其他业务。
对于生产环境,我认为至少需要关注以下几个方面。
1. CPU 资源管理
可以通过 Oracle Database Resource Manager 为不同 PDB 配置资源管理策略,根据业务重要程度分配 CPU 资源。
例如,核心业务 PDB 可以设置更高的 CPU 资源优先级,而报表类、统计分析类 PDB 则可以设置相应的资源限制,避免低优先级业务在高峰期占用过多资源。
2. 并行执行控制
并行执行确实可以缩短大表扫描、索引创建等操作的时间,但并行度并不是越高越好。
特别是在 RAC 环境下,多个 PDB 同时执行并行任务时,需要关注 PX Server 数量、CPU 使用率以及存储 I/O 压力。
对于大表索引创建、批量统计信息收集等操作,更应该结合业务低峰期安排,避免多个高资源消耗任务同时运行。
3. TEMP 空间管理
TEMP 也是需要重点关注的资源。
不同 PDB 可以使用各自的临时表空间,但这并不意味着 TEMP 资源可以完全忽略。
对于存在大量排序、Hash Join、并行查询和索引创建的 PDB,需要单独监控 TEMP 使用情况,同时关注底层磁盘资源和 I/O 能力。
不能只看到某个 PDB 的 TEMP 还有剩余,就认为整个数据库的资源没有压力。
4. 存储 I/O 管理
这是这次故障中我认为尤其值得关注的部分。
不同 PDB 的数据文件可能分布在相同的 ASM 磁盘组上,即使业务对象和数据文件彼此独立,最终也可能竞争相同的存储 I/O 能力。
如果一个 PDB 正在进行大规模全表扫描,另一个 PDB 又同时执行大量数据读取,那么底层存储的负载就可能明显增加。
因此,除了数据库层面的等待事件,还需要结合 ASM、操作系统和存储侧的监控数据进行综合判断。
对于有明确资源隔离要求的环境,还可以进一步评估 Oracle Resource Manager 与存储侧 I/O 管理策略的配合。
总结
回顾这次故障,最初只是产线反馈 MES 系统严重卡顿。通过 AWR、ASH 报告分析,我最终定位到一条访问 36 亿行大表的业务 SQL。
原 SQL 长期走全表扫描,单次执行耗时超过一个小时。经过与业务和应用厂商沟通,最终通过增加单列索引,将执行计划调整为 INDEX RANGE SCAN,单次执行时间下降到了十几秒。
但这次排查也让我们注意到另一个问题:在 Oracle 19c CDB/PDB 架构下,不同 PDB 虽然实现了逻辑隔离,但底层 CPU、内存和存储 I/O 仍然存在资源竞争的可能。
因此,日常数据库巡检不能只关注整个 CDB 是否正常,也不能只关注某一个 PDB 的运行状态。对于承载多个核心业务的 CDB,更应该建立 PDB 维度的资源监控和分析机制。
一个 PDB 的 SQL 出现问题,影响的可能不只是它自己。
这也是这次生产故障给我带来的另一个思考:数据库架构上的隔离,不等于资源使用上的隔离。
写文章之外,我也在持续完善自己的数据库学习与运维平台 ORA100。
平时整理的数据库实战案例、常用 SQL、运维脚本和实用工具,都可以在这里找到。
网站地址:ora100.com
感兴趣的话,可以来看看。

- 点赞
- 收藏
- 关注作者
评论(0)