在数据库管理中，性能问题是每位 DBA 都绕不开的挑战。尤其是在 Oracle 数据库中，突然出现的 SQL 执行计划变化可能会导致性能骤降。那么，是什么原因导致 SQL 需要重新硬解析，进而生成新的执行计划呢？本文将详细探讨几种常见的硬解析原因，帮助你更好地优化和诊断数据库性能。
「1. 自动收集统计信息」
为了确保 SQL 语句的最佳执行性能，Oracle 会根据当前的统计信息来生成最优的执行计划。在基于成本的优化器（CBO）模式下，数据库需要了解最新的统计信息，如表的行数、块数、字段的选择性等。因此，Oracle 每天都会自动运行一个统计信息收集任务，收集表中数据变化超过 10% 的最新统计信息。
在新统计信息收集完成后，相关 SQL 语句在执行时可能会进行硬解析。Oracle 设计了一个巧妙的机制，当某个 SQL 执行时，如果发现依赖的对象最近收集过统计信息，系统会在随机的时间戳内打上标记。当 SQL 在下次解析时，Oracle 会检查时间戳，如果超过了设定的时间（例如 5 小时），便会触发硬解析。
「2. 绑定变量不匹配导致的 Child Cursor 生成」
Oracle 在解析带有绑定变量的 SQL 时，会记录第一次硬解析时的绑定变量相关元数据。如果后续的 SQL 解析时发现绑定变量的类型或长度不匹配，Oracle 会生成一个新的 child cursor 并进行硬解析。
例如，scott下有一个表 emp
，并使用以下代码来插入和查询数据：
VAR a1 CHAR(20);
EXEC :a1 := 'CLERK';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
select count(*) from emp where job=:a1;
COUNT(*)
----------
0
Elapsed: 00:00:00.00
执行后，你可以通过以下 SQL 语句查看生成的 cursor：
SELECT sql_id, child_number, first_load_time FROM V$SQL WHERE SQL_TEXT LIKE '%count%emp%';
SQL_ID CHILD_NUMBER FIRST_LOAD_TIME
------------- ------------ ----------------------------------------------------------------------------
9s2kfv5bd4jwm 0 2024-09-04/17:50:13
Elapsed: 00:00:00.04
如果将绑定变量类型改为 `VARCHAR2` 并再次执行同样的查询，Oracle 会生成一个新的 child cursor，并进行硬解析。通过以下查询可以查看产生新的 child cursor 的原因：
VAR a1 VARCHAR2(20);
EXEC :a1 := 'CLERK';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
18:37:36 SCOTT@qihuo> select count(*) from emp where job=:a1;
COUNT(*)
----------
4
Elapsed: 00:00:00.00
SELECT sql_id, child_number, bind_mismatch FROM v$sql_shared_cursor WHERE ROWNUM < 10 AND sql_id = '9s2kfv5bd4jwm';
SQL_ID CHILD_NUMBER B
------------- ------------ -
9s2kfv5bd4jwm 0 N
9s2kfv5bd4jwm 1 Y
结果中 bind_mismatch
字段为 Y
，说明绑定变量不匹配导致了硬解析。
「3. 自适应游标共享（Adaptive Cursor Sharing）」
在 Oracle 11g 中引入了自适应游标共享功能。当表上的字段存在直方图且数据存在倾斜时，不同的数据输入可能导致 Oracle 尝试进行硬解析，以便生成更合适的执行计划。
「4. Cursor 被 Age Out」
Oracle 采用了类似操作系统的 LRU（Least Recently Used）算法来管理内存。Shared Pool 中的内存块（Chunk）如果在 Free List 上没有合适大小的内存块时，系统会触发清理机制。若某个 cursor 申请的内存块被清理掉，下次执行该 SQL 时就会重新进行硬解析。
需要注意的是，这个清理机制是 Oracle 内部的内存管理机制，并不是操作系统层面的 swap 机制。
「5. 人为因素引发的硬解析」
除了 Oracle 内部机制外，人为操作也可能导致硬解析。例如，手动收集统计信息、执行 flush shared_pool
操作、调用 dbms_shared_pool.purge
清理 cursor、执行 DDL 语句等，都会引发硬解析。
「总结」
硬解析是 Oracle 数据库中一个重要但复杂的过程。了解和避免不必要的硬解析，对于优化数据库性能至关重要。通过本文的介绍，相信你对硬解析的原因有了更清晰的认识，能够更好地进行 SQL 优化和数据库管理。
「欢迎关注我们的公众号，获取更多技术分享与经验交流。」
