经常有客户提说某个SQL的执行计划变差了,导致出现了性能问题,进而就问为啥解析出了新的
执行计划。首先可以肯定突然出现了新的执行计划表明SQL进行了重新硬解析(注意重新硬解析不一定
产生新的执行计划),那么为啥好好的SQL需要重新硬解析呢?今天我们就列举几种常见的原因:
1。自动收集统计信息
为了保证sql的最佳执行性能,甲骨文需要找到一个最优的执行计划,基于CBO模式的优化器必须
要知道最新的统计信息,例如条数,数块量,某个字段的选择率等,所以甲骨文每天凌晨都会运行一
个自动收集统计信息的工作,来收集那些变化超过10%的表的最新统计信息,收集完成之后,理所当然
要对新来的sql进行使用,所以就需要进行硬解析.oracle收集某个表统计信息后默认是不会立即无效
所有相关的游标,因为这样做太暴力,会引发硬解析相关的性能问题,所以巧妙的设计了一下,当某个相关sql执行的时候发现一个依赖对象最近收集过统计信息,便随机的打个一个时间戳,这个时间戳是
5个小时内某个时间戳,等到下次sql解析的时候若是发现了这个时间戳就会和当前时间进行比较,若是超过就说明已经到期,立即进行硬解析,否则还继续进行软解析。
2。没有符合条件的孩子光标典型的如绑定不匹配(其他原因可以参考v $ sql_shared_cursor)
当某个sql使用了绑定变量的时候,ORACLE会记录光标第一次硬解析时候的绑定变量的相关元数据,
当后续解析的时候便会进行检查,若是发现绑定变量类型或长度不匹配就会进行重新解析、下面我们就用一个小例子验证一下:
CREATE TABLE MAOB_T从DBA_TABlES选择;
VAR B1 char (20);
执行:B1:=癕AOB”;
SELECT COUNT ()从MAOB_T TABLE_NAME=: B1;
查看游标情况
选择sql_id child_number, first_load_time从V $ SQL SQL_TEXT像的%数% MAOB_T %
4 v22rgk83gjnc 0 2017 - 12 - 15/22:52:46 & lt; & lt;可以看到新游标已经生成
把绑定变量类型变成varchar2, SQL文本不变,再次执行:
VAR B1 varchar2 (20);
执行:B1:=癕AOB”;
SELECT COUNT(*)从MAOB_T TABLE_NAME=: B1;
执行上述语句后再次查看
选择sql_id child_number, first_load_time从V $ SQL SQL_TEXT像的%数% MAOB_T %
4 v22rgk83gjnc 0 2017 - 12 - 15/22:52:46
2017 - 12 - 4 v22rgk83gjnc 1 15/22:52:46
可以看到已经有两个只游标,进一步查看光标不能分享的原因:
选择sql_id child_number, bind_mismatch v $ sql_shared_cursor ROWNUM<10和sql_id=4 v22rgk83gjnc
sql_id, child_number bind_mismatch
4 v22rgk83gjnc 0 N
4 v22rgk83gjnc 1 Y & lt; & lt; & lt; bind_mismatch
可以看到由于绑定变量的原因造成的不匹配,所以硬解析产生了第二个子游标。
3。oracle11g提供了自适应游标功能(自适应光标共享),如果表上的字段存在直方图并且数据存在倾斜的场景下,那么对于传入不同的数据就会造成甲骨文重新尝试硬解析。具体内容,可以参考另外一篇博客。
https://blogs.oracle.com/database4cn/oracle-11g-sql-adaptive-cursor-sharing
4。光标已经被ageout内存
我们都知道,甲骨文对于内存管理机制和很多操作系统管理内存机制一样,都采用了LRU(最近最少使用,最近最少使用)算法,
指针作为一种库缓存中的对象也不例外,若是某个sql解析需要共享池内存时,发现空闲列表上并没有合适大小的内存块(块)
就会触发清理机制,那么之前光标申请的块就依据LRU算法规则被清理掉,这种就是年龄,需要注意的是,这个是甲骨文的
机制,并不是操作系统上的互换机制,一旦某个游标的被ageout出共享池,那么下次执行这个sql的时候就是重新硬解析。
5。除了上述几种甲骨文自身的机制造成重新硬解析之外,也存在人为因素操作造成的可能
例如人为收集统计信息,人为执行了冲洗shared_pool操作,手工调用dbms_shared_pool。清洗来清理某个光标
执行了ddl语句等。
