Oracle 12C RAC的optimizer_adaptive_features造成数据插入超时

问题分析

使用10046事件追踪方式，直接生成上传时的数据库事件日志进行分析，发现主要区别在于以下两条sql语句在每次长时间上传时都有出现，并且执行用户不是上传用户，而是数据库SYS用户。

********************************************************************************SQL ID: frjd8zfy2jfdq Plan Hash: 510421217SELECT executions, end_of_fetch_count,elapsed_time/px_serverselapsed_time,cpu_time/px_servers cpu_time,buffer_gets/executionsbuffer_getsFROM （SELECT sum（executions） as executions,sum（casewhen px_servers_executions > 0thenpx_servers_executionselse 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）asbuffer_getsFROM gv$sqlWHERE executions > 0 AND sql_id = :1AND parsing_schema_name = :2）call count cpuelapsed diskquerycurrentrows------- -------------- ---------- ---------- ---------- --------------------Parse 640.00 0.00000 0Execute 640.24 0.91000 0Fetch 640.14 0.4200064call count cpuelapsed diskquerycurrentrows------- -------------- ---------- ---------- ---------- --------------------Parse 640.00 0.00000 0Execute 640.24 0.91000 0Fetch 640.14 0.4200064------- -------------- ---------- ---------- ---------- --------------------total1920.39 1.3400064Misses in library cache during parse: 1Misses in library cache during execute: 1Optimizer mode: CHOOSEParsing user id: SYS （recursive depth: 1）Number of plan statistics captured: 64Rows （st） Rows （avg） Rows （max）Row Source Operation---------- ---------- ------------------------------------------------------------- 111VIEW（cr=0 pr=0 pw=0 time=20602 us） 111 SORT AGGREGATE （cr=0 pr=0 pw=0 time=20596 us） 000PX COORDINATOR（cr=0 pr=0 pw=0 time=20573 us） 000 PX SEND QC （RANDOM） :TQ10000 （cr=0 pr=0 pw=0 time=0 us） 000VIEWGV$SQL （cr=0 pr=0 pw=0 time=0 us） 000 FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD （ind:2） （cr=0 pr=0 pw=0 time=0 us）Elapsed times include waiting on following events: Event waited on Times Max. WaitTotal Waited ---------------------------------------- Waited---------------------- PX Deq: reap credit14480.000.02PX Deq: Join ACK1930.000.25IPC send completion sync1280.000.08PX Deq: Parse Reply 1280.060.24PX Deq: Execute Reply 1280.000.06reliable message 640.000.06PX Deq: Signal ACK EXT1280.000.01PX Deq: Slave Session Stats 1280.000.01enq: PS - contention 660.000.09KJC: Wait for msg sends to complete 50.000.00latch: shared pool10.000.00********************************************************************************SQL ID: b4wp0a8dvkf0 Plan Hash: 4033942373SELECT executions, end_of_fetch_count,elapsed_time/px_serverselapsed_time,cpu_time/px_servers cpu_time,buffer_gets/executionsbuffer_getsFROM （SELECT sum（executions_delta） as EXECUTIONS,sum（case when px_servers_execs_delta > 0then px_servers_execs_delta elseexecutions_delta end） as px_servers,sum（end_of_fetch_count_delta） as end_of_fetch_count,sum（elapsed_time_delta） as ELAPSED_TIME,sum（cpu_time_delta）as CPU_TIME,sum（buffer_gets_delta） as BUFFER_GETS FROM DBA_HIST_SQLSTAT s,V$DATABASE d, DBA_HIST_SNAPSHOT sn WHEREs.dbid = d.dbid ANDbitand（nvl（s.flag, 0）, 1） = 0ANDsn.end_interval_time > （selectsystimestamp at TIME ZONE dbtimezonefrom dual） - 7 ANDs.sql_id = :1 ANDs.snap_id = sn.snap_id ANDs.instance_number = sn.instance_number ANDs.dbid = sn.dbid ANDparsing_schema_name = :2）call count cpuelapsed diskquerycurrentrows------- -------------- ---------- ---------- ---------- --------------------Parse 640.00 0.00000 0Execute 640.08 0.08000 0Fetch 640.13 0.270 1170064------- -------------- ---------- ---------- ---------- --------------------total1920.21 0.360 1170064Misses in library cache during parse: 1Misses in library cache during execute: 1Optimizer mode: CHOOSEParsing user id: SYS （recursive depth: 1）Number of plan statistics captured: 3Rows （st） Rows （avg） Rows （max）Row Source Operation---------- ---------- ------------------------------------------------------------- 111VIEW（cr=18 pr=0 pw=0 time=6289 us cost=17 size=78 card=1） 111 SORT AGGREGATE （cr=18 pr=0 pw=0 time=6282 us） 000NESTED LOOPS（cr=18 pr=0 pw=0 time=6263 us cost=15 size=124 card=1） 000 NESTED LOOPS（cr=18 pr=0 pw=0 time=6258 us cost=15 size=124 card=1） 000HASH JOIN（cr=18 pr=0 pw=0 time=6256 us cost=14 size=97 card=1） 000 NESTED LOOPS（cr=18 pr=0 pw=0 time=6244 us cost=14 size=97 card=1） 000STATISTICS COLLECTOR（cr=18 pr=0 pw=0 time=6241 us） 000 NESTED LOOPS（cr=18 pr=0 pw=0 time=6223 us cost=13 size=81 card=1） 111MERGE JOIN CARTESIAN （cr=0 pr=0 pw=0 time=5648 us cost=0 size=10 card=1） 111 FIXED TABLE FULL X$KCCDI （cr=0 pr=0 pw=0 time=3449 us cost=0 size=10 card=1） 111 BUFFER SORT （cr=0 pr=0 pw=0 time=2187 us cost=0 size=0 card=1） 111FIXED TABLE FULL X$KCCDI2 （cr=0 pr=0 pw=0 time=2136 us cost=0 size=0 card=1） 000PARTITION RANGE ITERATOR PARTITION: KEY KEY （cr=18 pr=0 pw=0 time=568 us cost=13 size=71 card=1） 000 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED WRH$_SQLSTAT PARTITION: KEY KEY （cr=18 pr=0 pw=0 time=511 us cost=13 size=71 card=1） 000INDEX RANGE SCAN WRH$_SQLSTAT_INDEX PARTITION: KEY KEY （cr=18 pr=0 pw=0 time=477 us cost=11 size=0 card=2）（object id 8299） 000TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT （cr=0 pr=0 pw=0 time=0 us cost=1 size=16 card=1） 000 INDEX UNIQUE SCAN WRM$_SNAPSHOT_PK （cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1）（object id 8695） 000 TABLE ACCESS FULL WRM$_SNAPSHOT （cr=0 pr=0 pw=0 time=0 us cost=1 size=16 card=1） 000INDEX UNIQUE SCAN WRM$_SNAPSHOT_PK （cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1）（object id 8695） 000 TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT （cr=0 pr=0 pw=0 time=0 us cost=1 size=27 card=1） 000FAST DUAL（cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1）Elapsed times include waiting on following events:Event waited on Times Max. WaitTotal Waited---------------------------------------- Waited----------------------Disk file operations I/O30.000.00control file sequential read4480.000.18********************************************************************************

经过资料分析，这两条语句是由于开通了Oracle 12C的新功能而产生的，此功能为“optimizer_adaptive_features”。其主要功能是为了在语句执行过程中实时收集表的统计信息，方便Oracle选择更准确的执行计划。但是，这个功能在RAC非常损耗性能，因为它需要查找全局视图gv$sql的数据，在多个实例的情况下会进行并发执行，参考这篇资料。因为“optimizer_adaptive_features”参数宣称在OLAP数据仓库环境中可以获得较好的效果，实际在重上传轻查询的OLTP系统上，可以关闭这项新功能。

实验测试

因为该参数支持会话级别和系统级别调整，因为可以在单独一个会话内测试下插入时间和查询时间。

插入测试

采用同时提交多条TARGETTABLE数据。
打开“optimizer_adaptive_features”时为2.2秒，关闭该功能时为0.375。

查询测试

采用系统中执行最多的语句进行测试。打开“optimizer_adaptive_features”时为0.05秒，关闭该功能时为0.02秒。

关闭方式

因为“optimizer_adaptive_features”为动态参数，在系统级别修改时不需要重启。已在测试环境试验可用。执行语句为 alter system set optimizer_adaptive_features=false scope=both;更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址