oracle 发生硬解析的常见原因

 经常有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.oracle11g 提供了自适应游标功能（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的时候就是重新硬解析。

 

5.除了上述几种oracle自身的机制造成重新硬解析之外，也存在人为因素操作造成的可能

例如人为收集统计信息，人为执行了flush shared_pool 操作，手工调用dbms_shared_pool.purge 来清理某个cursor

执行了ddl 语句等。