Oracle执行计划不稳定
前段时间,在项目中碰到一个相当棘手的问题。我在写一张报表时需要查询客户数据库中已存在的视图(是早期开发就有的),相对应的销售表中已有上千万条数据,写完后台逻辑将对应数据查出来没任何问题。但是在界面上再点击查询按钮。问题就来了:页面直接卡死。
排除掉其他常见问题,例如语句性能,索引,缓存等等。最后发现奇怪的现象,正常来讲同样的一条SQL查询语句,根据执行计划,第一次查询完以后,后续再执行相同的语句应该很快才对。可是,现实却是第一次查询不慢(优化后2秒内查询),第二次以后就挂掉。困扰了好久不知道问题所在,也向相关群里的大牛去咨询也得不到结果。(数据库为oracle11G,项目为.net EF开发模式。)
最后求助团队老大,老大找来专业的DBA终于找出原因。
其实,算是Oracle11G的一个BUG。。。
11.2开始Oracle有了一种新的特性Cardinality Feedback,Cardinality Feedback是一个优化器自动优化的过程,优化器会自动修正重复执行的查询的执行计划。对于一些复杂的查询,比如多字段条件,字符串范围比较,数据SKEW等等,以及缺乏统计信息,优化器可能不能够产生一个完全准确的基数估计, 如丢失或统计数据不准确,或复杂的谓词的基数估计。cardinality feedback 就是基于这一原因而产生的。_optimizer_use_feedback参数默认是TRUE,即开启Cardinality Feedback,FALSE为关闭Cardinality feedback。由于在11GR2中Cardinality feedback生效存在很多限制且BUG较多,故没必要启用。
上段话是同事知道了原因后整理出来了的。 也就是说要关闭oracle的一个参数,对于结构上不复杂的语句,其实是看不到什么影响的。
alter system set "_optimizer_use_feedback"=false;
可以以一下方式验证参数是否真正关闭。
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describe FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance') AND y.inst_id = USERENV ('Instance') AND
x.indx = y.indx AND x.ksppinm LIKE '%optimizer_use_feedback%';
结果:参数关闭后,同样的语句,多次执行,已经不再会挂掉,跟数据库上千万的数据没有多大关系,每次执行可以保证在两秒左右。至此问题总算能解决。