同一SQL_ID占用临时表空间差异过大问题排查咨询

1. 提取并对比同一SQL的多版本执行计划 #

从v$sql中筛选目标sql_id的所有执行记录，定位异常计划的特征：

SELECT sql_id, plan_hash_value, executions, temp_space,
       last_load_time, optimizer_cost
FROM v$sql
WHERE sql_id = '你的目标SQL_ID';

重点对比temp_space（临时空间占用量）、plan_hash_value（执行计划哈希值）、optimizer_cost（优化器估算成本），找出对应20GB临时空间占用的异常计划哈希值。

2. 生成并分析异常执行计划的细节 #

通过DBMS_XPLAN导出异常计划的完整执行步骤，定位高消耗临时空间的操作：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id => '你的目标SQL_ID', 
  plan_hash_value => '异常计划的HASH值', 
  format => 'ALLSTATS LAST'
));

重点排查是否存在SORT ORDER BY、HASH JOIN、SORT AGGREGATE这类操作，对比正常计划的执行逻辑，看是否是优化器对数据行数、分布的估算出现严重偏差，导致排序/哈希操作溢出到临时表空间。

3. 回溯统计信息的有效性 #

虽然执行过GATHER_TABLE_STATS但当时无效，需确认统计信息是否真的更新到位：

-- 检查表级统计信息
SELECT table_name, last_analyzed, num_rows, blocks
FROM dba_tables
WHERE table_name IN ('查询涉及的表名列表');

-- 检查列级统计信息（重点看过滤/连接字段）
SELECT column_name, num_distinct, last_analyzed
FROM dba_tab_columns
WHERE table_name IN ('查询涉及的表名列表')
AND column_name IN ('查询用到的过滤/连接列');

验证last_analyzed是否在手动收集后更新，以及num_rows、num_distinct是否与实际数据量匹配——若统计信息未更新（比如收集时表被锁定）或统计值严重偏离真实数据，会导致优化器选错执行计划。

4. 排查绑定变量窥探与自适应计划影响 #

同一绑定变量出现不同执行计划，可能是绑定变量窥探或自适应计划导致：

-- 检查是否启用自适应计划
SELECT sql_id, is_adaptive_plan, is_resolved_adaptive_plan
FROM v$sql
WHERE sql_id = '你的目标SQL_ID';

-- 查看绑定变量捕获记录
SELECT sql_id, child_number, bind_data
FROM v$sql_bind_capture
WHERE sql_id = '你的目标SQL_ID';

对比异常执行时与正常执行时的绑定变量值差异，看是否是特定变量值触发了优化器对数据分布的错误判断，进而生成低效计划。

5. 检查PGA内存配置与执行时的工作区状态 #

PGA内存不足会导致排序/哈希操作溢出到临时表空间，需验证：

-- 查看PGA配置
SELECT name, value
FROM v$parameter
WHERE name = 'pga_aggregate_target';

-- 查看SQL工作区执行情况
SELECT sql_id, plan_hash_value, workarea_size_policy,
       optimal_executions, onepass_executions, multipass_executions
FROM v$sql_workarea
WHERE sql_id = '你的目标SQL_ID';

若multipass_executions大于0，说明排序/哈希操作多次写入临时表空间，会大幅增加临时空间占用。

6. 锁定正常执行计划验证问题根源 #

若已定位到正常执行计划的哈希值，可通过SQL基线锁定该计划，验证是否能稳定解决问题：

DECLARE
  l_plans PLS_INTEGER;
BEGIN
  l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '你的目标SQL_ID',
    plan_hash_value => '正常计划的HASH值'
  );
END;
/

之后观察该SQL的临时空间占用是否稳定在正常水平。

内容的提问来源于stack exchange，提问作者cheeeeen