几种常见重新硬解析的原因

本文出自几种常见重新硬解析的原因^[1],最近遇到了一个问题，看到了这篇文章，分享于此。

经常有SQL的执行计划变差了，导致出现了性能问题，为什么会解析出了新的执行计划呢？首先可以肯定突然出现了新的执行计划，表明SQL进行了重新硬解析(注意重新硬解析不一定产生新的执行计划)，那么为什么SQL需要重新硬解析呢？今天就列举几种常见的原因：

1.自动收集统计信息

为了保证SQL的最佳执行性能，oracle需要找到一个最优的执行计划，基于CBO模式的优化器必须要知道最新的统计信息，例如条数，block数量，某个字段的选择率等，所以oracle每天凌晨都会运行一个自动收集统计信息的job，来收集那些变化超过10%的表的最新统计信息，收集完成之后，理所当然要对新来的SQL进行使用，所以就需要进行硬解析。oracle 收集某个表统计信息后默认是不会立即invalid所有相关的cursor，因为这样做太暴力，会引发硬解析相关的性能问题，所以巧妙的设计了一下，当某个相关SQL执行的时候发现一个依赖对象最近收集过统计信息，便随机的打个一个时间戳，这个时间戳是5个小时内某个时间戳，等到下次SQL解析的时候若是发现了这个时间戳就会和当前时间进行比较，若是超过就说明已经到期，立即进行硬解析，否则还继续进行软解析。

2.没有符合条件的child cursor

典型的如bind mismatch (其他原因可以参考v$sql_shared_cursor)

当某个SQL使用了绑定变量的时候,Oracle会记录cursor第一次硬解析时候的绑定变量的相关metadata，当后续解析的时候便会进行检查，若是发现绑定变量类型或长度不匹配就会进行重新解析，下面我们就用一个小例子验证一下：

CREATE TABLE MAOB_T AS SELECT * FROM DBA_TABlES ; 
VAR  B1 char(20); 
EXEC :B1 := 'MAOB'; 
SELECT COUNT(*) FROM MAOB_T WHERE TABLE_NAME=:B1;

查看cursor情况

Select sql_id,child_number,first_load_time from V$SQL WHERE SQL_TEXT LIKE '%COUNT%MAOB_T%'
4v22rgk83gjnc 0 2017-12-15/22:52:46 <<可以看到新cursor已经生成

把绑定变量类型变成varchar2，sql文本不变，再次执行：

VAR  B1 VARCHAR2(20); 
EXEC :B1 := 'MAOB'; 
SELECT COUNT(*) FROM MAOB_T WHERE TABLE_NAME=:B1;

执行上述语句后再次查看

Select sql_id,child_number,first_load_time from V$SQL WHERE SQL_TEXT LIKE '%COUNT%MAOB_T%'
4v22rgk83gjnc 0 2017-12-15/22:52:46
4v22rgk83gjnc 1 2017-12-15/22:52:46

可以看到已经有两个只cursor,进一步查看cursor不能share的原因:

Select sql_id,child_number,bind_mismatch from v$sql_shared_cursor WHERE ROWNUM<10 and sql_id='4v22rgk83gjnc'
sql_id,child_number bind_mismatch
4v22rgk83gjnc 0 N
4v22rgk83gjnc 1 Y  <<<bind_mismatch

可以看到由于绑定变量的原因造成的mismatch，所以硬解析产生了第二个子cursor。

3.Oracle 11g 提供了自适应游标功能（Adaptive Cursor Sharing），如果表上的字段存在直方图并且数据存在倾斜的场景下，那么对于传入不同的数据就会造成Oracle重新尝试硬解析。

4.cursor 已经被ageout 内存

我们都知道，Oracle 对于内存管理机制和很多OS管理内存机制一样，都采用了LRU(Least recently used,最近最少使用)算法，cursor作为一种library cache 中的对象也不例外，若是某个sql解析需要share pool内存时，发现free list 上并没有合适大小的内存块（chunk）就会触发清理机制，那么之前cursor 申请的chunk就依据LRU算法规则被清理掉，这种就是age out，需要注意的是，这个是oracle的机制，并不是OS上的swap机制，一旦某个cursor的被ageout出shared pool，那么下次执行这个sql的时候就是重新硬解析。