Oracle 临时表空间与 PGA 治理实战：从 ORA-01652 磁盘排序暴涨到工作区与 SQL 双线收敛

dbaai · 发表于 14 小时前

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？用户注册

×

一、具体的问题

上周有个报表库找我，说每天凌晨两点半开始，监控就一片红：

应用端报 ORA-01652: unable to extend temp segment by 128 in tablespace TEMP，报表任务直接中断；
数据库主机 iostat 里 TEMP 所在盘 %util 打到 100，await 冲到 300ms 以上；
v$tempseg_usage 里同时有 40 多个会话挂着，单个会话占用的 TEMP 段最大到了 68GB；
但奇怪的是，白天这套库闲得发慌，CPU 常年 10% 以下，物理内存 256GB，pga_aggregate_target 也给了 40GB。

第一反应通常是"加内存""把 TEMP 扩到 500GB"。这两个动作都能让今晚的任务跑完，但一周之后同样的事会再来一次，因为真正的因果链不在容量上：

一个带 ORDER BY + HASH GROUP BY 的大报表 SQL，估算返回 300 万行，实际返回 1.2 亿行；优化器按 300 万行分配工作区，实际排序数据量超了 40 倍，工作区放不下就一路 spill 到 TEMP；十几个报表会话并跑，TEMP 被瞬时吃穿。

所以这条链要拆成两段看：内存给得够不够（PGA 与工作区），和 SQL 该不该做这么大面积的排序（执行计划与过滤条件）。只治一段，另一段还会把问题顶回来。

二、核心原理

Oracle 的排序和哈希连接优先在 PGA 的工作区（work area）里做，工作区不够才落 TEMP。关键参数与判定链是这样的：

pga_aggregate_target（PGA 总目标）不是硬上限，是"软目标"。Oracle 会把其中一部分切成可用的工作区额度，单个进程能拿到的上限受隐藏参数 _smm_max_size（通常约为 PGA 目标的 5%，且不超过 _pga_max_size 的一半）和 _smm_px_max_size（并行场景）约束。
一个操作需要的工作区，由优化器估算的字节数决定（v$sql_workarea.ESTIMATED_OPTIMAL_SIZE）。估算依据是基数（cardinality）与行宽。估算错，后面全错：估算偏小 → 走 ONE PASS 甚至 MULTI_PASS → 大量 TEMP 读写；估算偏大 → 抢走其他会话的工作区，PGA_AGGREGATE_TARGET 超用，触发 pga_aggregate_limit 时甚至直接报 ORA-04036。
所以要看的不是"TEMP 用了多少 GB"，而是每个会话的工作区执行模式（optimal / onepass / multipass）以及估算 vs 实际行数的偏差倍数。TEMP 用量只是结果。
还有一层容易忽略：TEMP 表空间是共享的。一个会话释放的临时段，块被标记可复用，但并不等于文件变小。所以 TEMP 使用率掉下来不代表空间回来了，dba_temp_files 里的文件一旦涨上去就不会自己缩。
更隐蔽的一层：TEMP 上的空间压力会通过 enq: TS - contention（临时段争用）扩散到所有用 TEMP 的会话，包括那些本来只需要 100MB 的小查询。一个坏 SQL 能把整个实例拖慢，这就是为什么"只影响报表"从来不成立。

一句话：TEMP 暴涨是症状，工作区模式是证据，基数估算与 SQL 形态是病根。

三、实例参考（动手步骤）

以下命令按顺序执行，每步都有明确的判据。

步骤 0：确认现状基线

-- 1. PGA 相关参数与当前使用
SELECT name, value/1024/1024 AS mb
FROM v$pgastat
WHERE name IN ('aggregate PGA target parameter','aggregate PGA auto target',
'total PGA inuse','total PGA allocated',
'over allocation count','cache hit percentage',
'extra bytes read/written','maximum PGA allocated');
-- 2. 工作区执行模式分布（关键！onepass/multipass 必须为 0）
SELECT exec_type, COUNT(*) AS cnt, ROUND(SUM(temp_space_allocated)/1024/1024) AS mb
FROM v$sql_workarea_active
GROUP BY exec_type;
-- 历史侧看 v$sysstat
SELECT name, value FROM v$sysstat
WHERE name LIKE 'workarea executions%'; -- optimal / onepass / multipass 三个数
-- 3. TEMP 当前占用 TOP 会话
SELECT s.sid, s.serial#, s.username, s.sql_id,
ROUND(u.blocks * 8192/1024/1024) AS mb,
u.segtype, u.tablespace
FROM v$tempseg_usage u JOIN v$session s ON s.saddr = u.session_addr
ORDER BY u.blocks DESC FETCH FIRST 20 ROWS ONLY;
-- 4. TEMP 文件与使用率
SELECT tablespace_name, file_name, bytes/1024/1024 AS mb, autoextensible, maxbytes/1024/1024 AS max_mb
FROM dba_temp_files;

复制代码

本案例基线（白天闲时抓的，能代表"正常态"）：
workarea executions - optimal = 99.2%，onepass = 0.7%，multipass = 0.1%；over allocation count = 38,412（这个数只增不减，说明一直超配）；TEMP 文件 4 个 × 32GB 不自动扩展；夜间峰值 TOP1 会话占 68GB。

判据：multipass 非 0 就是明确的异常信号，over allocation count 大说明 PGA 长期不够分。

步骤 1：定位谁在吃 TEMP，锁定 SQL

-- 从 ASH 找 TEMP 相关等待的 SQL（近 1 小时）
SELECT sql_id, event, COUNT(*) AS samples,
ROUND(COUNT(*)*10/60) AS approx_minutes
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND event IN ('direct path write temp','direct path read temp',
'enq: TS - contention','PGA memory operation')
GROUP BY sql_id, event
ORDER BY samples DESC FETCH FIRST 20 ROWS ONLY;
-- 拿到 sql_id 后看它的工作区历史
SELECT sql_id, child_number, operation_type, policy,
estimated_optimal_size/1024/1024 AS est_mb,
temp_space_allocated/1024/1024 AS temp_mb,
exec_type, ROUND(actual_mem_used/1024/1024) AS actual_mb
FROM v$sql_workarea
WHERE sql_id = '&sql_id';

复制代码

这一步的判据非常硬：如果 policy = AUTO 但 exec_type 出现 ONE PASS/MULTI_PASS，说明工作区不足；如果 est_mb 明显小于 temp_mb（本例 1.6GB 估算 vs 68GB 实际），说明估算严重偏低，问题在基数。

步骤 2：判断到底是"内存不足"还是"SQL 不合理"

-- 估算 vs 实际行数偏差（执行完的语句才有）
SELECT sql_id, child_number, plan_hash_value,
ROUND(elapsed_time/1e6) AS elapsed_s,
buffer_gets, disk_reads, rows_processed,
executions
FROM v$sql
WHERE sql_id = '&sql_id';
-- 看真实执行计划，重点看 rows 与 E-Rows 的比值
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST +PEEKED_BINDS'));

复制代码

本例 ALLSTATS LAST 输出里，一个 TABLE ACCESS FULL 的 E-Rows 是 3,102,000，A-Rows 是 121,884,930，差 39 倍；后面的 HASH GROUP BY 与 SORT ORDER BY 的估算基数随之全错。

判据表（这是决定该调内存还是改 SQL 的分水岭）：

现象	结论	动作方向
E-Rows 与 A-Rows 差 < 2 倍，仍 onepass	工作区确实小	调 PGA / 工作区
E-Rows 与 A-Rows 差 > 10 倍	基数估算失真	改统计信息 / 改 SQL
单会话 optimal 但 TEMP 仍爆	并发会话数 > 工作区可容纳数	降低并发或分批
同一 SQL 白天 optimal、夜间 onepass	夜间并发抢工作区	错峰 + 限并发

步骤 3：先调工作区（止血，当天生效）

-- 保守上调 PGA（本例 40GB → 64GB，物理内存 256GB，留足 SGA 与 OS）
ALTER SYSTEM SET pga_aggregate_target = 64G SCOPE=BOTH;
-- 确认单进程工作区上限（隐藏参数，务必先看现值再决定动不动）
SELECT a.ksppinm, b.ksppstvl
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm IN ('_smm_max_size','_pga_max_size','_smm_px_max_size');
-- 生产建议：不要动隐藏参数，先用 pga_aggregate_target + 限制并发
-- 若确需临时放宽单进程上限（session 级，重启失效，风险可控）
ALTER SESSION SET workarea_size_policy = MANUAL;
ALTER SESSION SET sort_area_size = 1073741824; -- 1GB，仅针对单个批处理会话

复制代码

注意：sort_area_size 在 MANUAL 模式下对每个排序独立生效，一个 SQL 里有 3 个排序就是 3 份，很容易把 PGA 吃穿并触发 ORA-04036。生产上我只在单独跑的补数会话里用它，绝不在应用连接池里用。

步骤 4：再改 SQL 与统计信息（治本）

本例真正的修法只有三条，成本从低到高：

-- (a) 补齐过滤条件所依赖列的统计信息，并把直方图从默认 SIZE 1 提到 254
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'RPT',
tabname => 'F_ORDER_DETAIL',
method_opt => 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 ORDER_DATE,ORG_ID',
degree => 8,
cascade => TRUE,
granularity => 'AUTO');
END;
/
-- (b) 用 SQL Patch 临时钉住更好的计划（不改应用代码，可回退）
BEGIN
DBMS_SQLDIAG.CREATE_SQL_PATCH(
sql_id => '&sql_id',
hint_text => 'INDEX(@"SEL$1" "F_ORDER_DETAIL"@"SEL$1" ("F_ORDER_DETAIL"."ORDER_DATE"))',
name => 'patch_rpt_order_01',
description => 'force index on order_date to cut sort volume');
END;
/
-- (c) 应用侧分批：把一次 1.2 亿行的 GROUP BY 拆成按天 30 次
-- 这是最根本的一条，TEMP 峰值从 68GB 降到 2.4GB

复制代码

（c）才是决定性的。前两条让单次执行不再 spill，但 1.2 亿行一次做完，工作区永远不够；拆批之后每次只排 400 万行，optimal 稳定成立。

步骤 5：给 TEMP 加兜底与监控

-- TEMP 文件可扩展 + 设上限（防一次性吃满整盘）
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/RPT/temp01.dbf'
AUTOEXTEND ON NEXT 2G MAXSIZE 60G;
-- 每 5 分钟采样落表，用于趋势与告警（配合 DBMS_SCHEDULER）
CREATE TABLE mon_temp_usage AS
SELECT SYSTIMESTAMP AS ts, COUNT(*) AS sessions,
ROUND(SUM(blocks)*8192/1024/1024) AS total_mb,
MAX(ROUND(blocks*8192/1024/1024)) AS max_session_mb
FROM v$tempseg_usage;
-- 告警阈值：总占用 > 70% 或单会话 > 8GB 就发通知

复制代码

治理前后对比

指标	治理前	治理后	说明
夜间 TEMP 峰值	218 GB（4 文件被写穿）	14 GB	分批 + 计划修正后
单会话最大 TEMP 段	68 GB	2.4 GB	单批 400 万行
workarea multipass 次数/日	1,140	0	optimal 稳定
报表任务耗时	5h40m（含失败重跑）	1h52m	分批后并行度更高
enq: TS - contention	夜间 4,300 次	12 次	争用基本消除
ORA-01652	每日 1–3 次	0（连续 21 天）	—
over allocation count 日增	~1,900	~40	PGA 分配趋于平稳

四、实操检查清单

[ ] 先抓 v$sysstat 里 workarea executions - onepass/multipass，确认是否真的在工作区失守（别看 TEMP 用量就下手）。
[ ] 用 ASH 定位 direct path write temp / enq: TS 的 TOP sql_id，锁定到具体语句而非"报表模块"。
[ ] 对每个问题 sql_id 跑 DISPLAY_CURSOR(..., 'ALLSTATS LAST')，逐个算子比对 E-Rows 与 A-Rows。
[ ] 算偏差倍数：小于 2 倍调内存，大于 10 倍改统计信息或 SQL，别倒过来做。
[ ] 调 pga_aggregate_target 时同步核算 SGA + PGA + OS 余量，PGA 不要超过物理内存的 40%。
[ ] 隐藏参数 _smm_max_size 只在明确算过最坏并发后才考虑，改前记录现值与回退语句。
[ ] 禁止在应用连接池里使用 workarea_size_policy=MANUAL，只在独立补数会话用。
[ ] 大报表一律分批（按时间/机构/主键区间），单批目标行数控制在 500 万以内。
[ ] TEMP 文件设 AUTOEXTEND ON 但必须给 MAXSIZE，防止单次事故写满挂载点。
[ ] 建立 TEMP 采样表与阈值告警（总占用 70%、单会话 8GB），事后有趋势可查。
[ ] 改完统计信息后观察一个完整业务周期（含月末/月初），不要当天就宣布收工。

几个容易踩的坑

把 TEMP 扩到 500GB 就算解决。 扩容量只是把报错时间往后推，spill 的 I/O 成本还在，报表照样慢。本例扩到 500GB 后确实不报错了，但耗时从 5h 变成 6h20m——因为排序全落到磁盘上排。
只调 pga_aggregate_target 不看并发。 PGA 是共享池子，40 个会话同时抢，每个分到的还是小。目标调大一倍但并发不变，onepass 只是从 40 个会话变成 20 个会话，问题没消失。
over allocation count 涨了就猛加 PGA。 这个计数器统计的是"分配超出策略预期的次数"，某些版本在并行查询下天然会涨。要结合 total PGA inuse 与 maximum PGA allocated 一起看，单看它容易误判。
忘了 TEMP 是共享的。 修完大 SQL 兴高采烈地看总占用降了，但没意识到期间小查询一直被 enq: TS 阻塞。检查清单里必须包含"其他会话的等待是否同步下降"。
统计信息刷新反而更糟。 直方图从 SIZE 1 提到 254 后，如果列的选择性差异不大，优化器可能转而选更差的计划。改前留存原统计信息（DBMS_STATS.EXPORT_TABLE_STATS），有问题能 IMPORT 回去。
SQL Patch 当成长期方案。 Patch 不随表结构变化而失效，一年后索引被删了 Patch 还在，计划直接崩。上线 Patch 时登记到期日，配合代码修复排期。
分批之后忘了汇总逻辑。 拆成 30 次跑，汇总表的中间结果要能重入（幂等），否则某批失败重跑会重复累加。这是分批改造最常见的二次事故。

账号		自动登录	找回密码
密码			用户注册

[开发应用] Oracle 临时表空间与 PGA 治理实战：从 ORA-01652 磁盘排序暴涨到工作区与 SQL 双线收敛

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。