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 语句等。