同一个SQL为何忽快忽慢，揭秘Oracle多执行计划之谜

原创 szrsu 2026-04-07

603

生产环境SQL时而闪电完成，时而蜗牛爬行，背后元凶竟是同一个SQL藏了多个"分身"！

一、为什么同一个SQL会有多个执行计划？

在Oracle数据库中，同一个SQL_ID可能对应多个不同的执行计划，就像一个人拥有多重人格，在不同场景下表现出完全不同的行为模式。这种现象的主要原因包括：

绑定变量窥探：优化器在硬解析时会"偷看"绑定变量的具体值，不同值可能导向完全不同的执行路径。比如WHERE order_date = :date，传入"2024-01-01"（少量数据）可能走索引，传入"2024-01-01"（占全表80%）可能走全表扫描。
自适应游标共享：Oracle 11g+引入的特性，允许同一SQL根据运行时性能反馈，自动创建多个子游标以适应不同绑定变量值。初衷是好的，但副作用就是"计划膨胀"。
统计信息变化：表数据量从1万行暴增到1000万行后重新收集统计信息，执行计划可能从嵌套循环变为哈希连接，新旧计划同时存在。
参数/环境变化：优化器模式从ALL_ROWS变为FIRST_ROWS、系统统计信息更新、或全局表连接顺序改变等，都可能导致新生成的计划与旧计划不同。

⚠️ 重要警示：ORACLE数据库SQL语句出现多个执行计划会引发严重的性能抖动问题，有时快有时慢，导致应用响应时间像过山车一样不稳定，用户体验和系统吞吐量都会受到严重影响。

二、定位sql语句有多个执行计划的方法

方法1：V$SQL视图

快速定位多计划SQL，最直接的方法，查询哪些SQL存在多个执行计划：

-- 查找有多个执行计划的SQL
SELECT 
    SQL_ID,
    COUNT(*) AS CHILD_CURSOR_COUNT,
    COUNT(DISTINCT PLAN_HASH_VALUE) AS PLAN_COUNT,
    MIN(EXECUTIONS) AS MIN_EXEC,
    MAX(EXECUTIONS) AS MAX_EXEC
FROM V$SQL
WHERE SQL_TEXT NOT LIKE '%V$SQL%'
GROUP BY SQL_ID
HAVING COUNT(DISTINCT PLAN_HASH_VALUE) > 1
ORDER BY PLAN_COUNT DESC;

SQL_ID	      CHILD_CURSOR_COUNT PLAN_COUNT   MIN_EXEC	 MAX_EXEC
------------- ------------------ ---------- ---------- ----------
fvwns1h4zxp2k		       3	  2	     1		1
79w2cqu2gmjm8		       2	  2	     1		2
3d6ph39wbq0zq		       4	  2	     2		2
0fr8zhn4ymu3v		       3	  2	     5	      111
719qpgsc28an3		       6	  2	     2		9
gx4mv66pvj3xz		       3	  2	     7	      589
2jr8c42qx700h		       2	  2	     1		2
1gu8t96d0bdmu		       5	  2	     8	     1007
10s3r3f17ccu3		       7	  2	     2		6
2m7ax0c761q1q		       6	  2	     2		9
3mqvkt9as1phq		       7	  2	     2		5

SQL_ID	      CHILD_CURSOR_COUNT PLAN_COUNT   MIN_EXEC	 MAX_EXEC
------------- ------------------ ---------- ---------- ----------
05jx2pqv4ww0r		       4	  2	     2		2
bsa0wjtftg3uw		       2	  2	     1	      574
7ng34ruy5awxq		       6	  2	     4	     1787
53saa2zkr6wc3		       3	  2	    13	     2518
0d1jkfx7rgx2t		       3	  2	     2		4
0kugqg48477gf		       2	  2	     1	       33
g07bjs22tcg72		       2	  2	     1	       11
6qg99cfg26kwb		       3	  2	    80	     6743
f3g84j69n0tjh		       3	  2	     5	      111
d00a21h5ybffr		       2	  2	     1		1


-- 查看特定SQL的所有子游标信息
SELECT 
    CHILD_NUMBER, 
    PLAN_HASH_VALUE, 
    EXECUTIONS, 
    BUFFER_GETS,
    DISK_READS,
    ROWS_PROCESSED,
    IS_BIND_SENSITIVE,
    IS_BIND_AWARE
FROM V$SQL
WHERE SQL_ID = 'g07bjs22tcg72'
ORDER BY CHILD_NUMBER;

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BUFFER_GETS DISK_READS ROWS_PROCESSED I I
------------ --------------- ---------- ----------- ---------- -------------- - -
	   0	  2724115676	      1 	283	     8		    1 Y N
	   1	   590501966	     11 	365	     0		   11 N N

**关键字段解读**：
- `IS_BIND_SENSITIVE` = 'Y'：表示Oracle认为该游标对绑定变量值敏感，会监控其性能
- `IS_BIND_AWARE` = 'Y'：表示该游标已启用自适应游标共享，可以为不同绑定值创建多个计划

--或者用下面的语句查看：
set linesize 400;
col sql_text_sample for a150;
SELECT
    sql_id,
    COUNT(DISTINCT plan_hash_value) as plan_count,
    COUNT(DISTINCT child_number) as child_count,
    SUM(executions) as total_executions,
    MIN(ROUND(elapsed_time/NULLIF(executions, 0)/1000000, 4)) as min_avg_elapsed_sec,
    MAX(ROUND(elapsed_time/NULLIF(executions, 0)/1000000, 4)) as max_avg_elapsed_sec,
    SUBSTR((SELECT sql_text FROM v$sqltext_with_newlines
            WHERE sql_id = v.sql_id AND piece = 0), 1, 150) as sql_text_sample
FROM v$sql v
WHERE executions > 0
AND plan_hash_value > 0
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER BY plan_count DESC, max_avg_elapsed_sec DESC;

方法2：DBMS_XPLAN.DISPLAY_CURSOR

查看每个子游标的详细计划，找到SQL_ID和CHILD_NUMBER后，逐个查看执行计划：

-- 查看子游标0的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('g07bjs22tcg72', 0));

-- 查看子游标1的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('g07bjs22tcg72', 1));

输出示例：

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	g07bjs22tcg72, child number 1
-------------------------------------
SELECT COUNT(*) FROM ( SELECT R.PRIVILEGE FROM SYS.USER_ROLE_PRIVS U,
SYS.ROLE_SYS_PRIVS R WHERE U.GRANTED_ROLE=R.ROLE UNION ALL SELECT
PRIVILEGE FROM SYS.USER_SYS_PRIVS ) WHERE PRIVILEGE = :B1 AND ROWNUM < 2

Plan hash value: 590501966

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation					      | Name		       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT				      | 		       |       |       |    28 (100)|	       |
|   1 |  SORT AGGREGATE 				      | 		       |     1 |    22 |	    |	       |
|*  2 |   COUNT STOPKEY 				      | 		       |       |       |	    |	       |
|   3 |    VIEW 					      | 		       |     3 |    66 |    28	(22)| 00:00:01 |
|   4 |     UNION-ALL					      | 		       |       |       |	    |	       |
|*  5 |      HASH JOIN					      | 		       |     2 |   104 |    21	(24)| 00:00:01 |
|   6 |       VIEW					      | ROLE_SYS_PRIVS	       |     1 |    35 |     7	(29)| 00:00:01 |
|   7 |        HASH GROUP BY				      | 		       |     1 |    57 |     7	(29)| 00:00:01 |
|   8 | 	NESTED LOOPS				      | 		       |     1 |    57 |     6	(17)| 00:00:01 |
...

小技巧：比较不同CHILD_NUMBER的执行计划，差异越大说明执行路径差异越明显，性能抖动越严重。

方法3：DBMS_XPLAN.DISPLAY_AWR

从AWR历史快照中查找，如果SQL已经在AWR中存在（即执行过足够多次被快照捕获），可以从历史快照中查看：

-- 从AWR中查找SQL的所有执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('g07bjs22tcg72'));

-- 查找AWR中记录的多计划SQL
SELECT 
    SQL_ID,
    PLAN_HASH_VALUE,
    COUNT(*) AS SNAP_COUNT
FROM DBA_HIST_SQLSTAT
WHERE SQL_ID = 'g07bjs22tcg72'
GROUP BY SQL_ID, PLAN_HASH_VALUE
ORDER BY PLAN_HASH_VALUE;

适用场景：当问题SQL已经不在Shared Pool中（被aged out），但AWR快照还保留着历史记录时，这个方法就是救命稻草。

方法4：V$SQL_PLAN视图

通过V$SQL_PLAN视图直接获取计划信息，适合批量分析或脚本自动化：

-- 查询特定SQL的所有计划
SELECT DISTINCT
    SQL_ID,
    CHILD_NUMBER,
    PLAN_HASH_VALUE,
    OPERATION,
    OPTIONS,
    OBJECT_NAME
FROM V$SQL_PLAN
WHERE SQL_ID = 'g07bjs22tcg72'
ORDER BY CHILD_NUMBER, SQL_ID;

优势：可以结合其他视图进行复杂的关联分析，比如关联V$SQL统计资源消耗差异。

方法5：使用awrsqrpt.sql脚本

通过 v$sql 查到当前sql的sql_id，然后执行awrsqrpt.sql脚本生成专用的SQL报告：

sqlplus / as sysdba @?/rdbms/admin/awrsqrpt.sql

获取该sql_id的报告，查看有几个执行计划以及当前使用的执行计划。

操作步骤：

执行脚本后，按提示选择快照范围（开始和结束快照ID）
输入目标SQL_ID
生成HTML或TXT格式报告
报告中会清晰列出该SQL在不同时间段的执行计划演变历史

这是比较推荐的方法，生成的报告包含执行计划对比、性能指标变化趋势等丰富信息。

三、总结

如果碰到多个执行计划会引发性能问题，有时快有时慢，该怎么办？

（1）使用SQL Plan Management固定好计划（最快速的方法）

将之前验证过的优秀执行计划固化，强制Oracle始终使用该计划。

（2）使用Hint临时干预（快速止血）

在代码中紧急添加Hint，强制走你需要的执行计划。

注意：Hint方式需要修改应用代码，且下次SQL变更时可能需要重新调整，属于临时方案。

（3）根治的方法：分析和修复根本原因

这是最彻底的解决方案，从源头杜绝问题复发：

针对统计信息问题：

-- 检查统计信息新鲜度
SELECT table_name, stale_stats, last_analyzed 
FROM dba_tab_statistics 
WHERE table_name = 'TABLE_NAME';

-- 重新收集统计信息（使用更合理的采样比例）
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', estimate_percent=>100, method_opt=>'FOR ALL COLUMNS SIZE AUTO');

-- 对于倾斜严重的列，创建直方图
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', method_opt=>'FOR COLUMNS COLUMN_NAME SIZE 254');

针对绑定变量窥探问题：

-- 关闭绑定变量窥探（系统级，谨慎）
-- 更精细的方法：为倾斜数据使用自适应游标共享（默认开启），确保表统计信息包含直方图，让ACS自动生效
-- 或者使用SQL Plan Management固定计划

针对参数/环境变化：

-- 检查优化器相关参数
-- 确保会话级参数与期望一致

最佳实践建议：

定期巡检：将同一个sql执行时间差异过大查询加入日常巡检脚本
监控告警：当某个SQL的PLAN_COUNT >= 2且sql执行时间差异过大时触发告警
变更管控：统计信息收集、参数调整等操作应在非高峰期进行，并观察影响

通过以上方法的组合使用，你不仅能快速定位多计划SQL，还能从根本上解决性能抖动问题，让数据库回归稳定可靠的状态。
记住：发现问题SQL只是第一步，理解为什么会发生、如何彻底解决，才是DBA真正的价值所在！

墨力计划 oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者