Oracle 12.1.0.2 "optimizer_adaptive_plans"特性引起的执行缓慢案例
环境:Oracle 12.1.0.2 RAC 2node
问题现象:某地区公共资源应用系统页面加载缓慢、整体业务模块执行效率不高
awr:
top sql:
很明显第一条sql,执行2500多次,需要着重关注
SELECT executions, end_of_fetch_count, elapsed_time/px_servers elapsed_time, cpu_time/px_servers cpu_time,
buffer_gets/executions buffer_gets FROM (SELECT sum(executions) as executions,
sum(case when px_servers_executions > 0 then px_servers_executions else executions end) as px_servers,
sum(end_of_fetch_count) as end_of_fetch_count, sum(elapsed_time) as elapsed_time, sum(cpu_time) as cpu_time,
sum(buffer_gets) as buffer_gets
FROM gv$sql WHERE executions > 0 AND sql_id = :1 AND parsing_schema_name = :2)
处理方法:
可以看出如上top sql的语句并非是业务用户执行,查阅MOS资料后发现这是12c 12.1.0.2新特性“optimizer_adaptive_features”,默认是打开的,这个特性会在执行sql的时候自动收集统计信息。
将此特性关闭
alter system set optimizer_adaptive_features=false scope=both;
关闭之后业务系统执行效率明显提升,再次观察awr,再无此等待事件
看了下MOS,这个特性oracle也是建议关闭,或者通过打补丁的方式,然而在12.2.0.1这个参数被2个新的参数取代“optimizer_adaptive_plans” 默认值为TRUE,“optimizer_adaptive_statistics” 默认值为FALSE
关于 Oracle Database 12c 版本1的自适应特性的建议 (Adaptive Features, Adaptive Statistics 以及 12c SQL 性能) (文档 ID 2297986.1
)