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


    问题分析

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

    
    ********************************************************************************
    
    SQL ID: frjd8zfy2jfdq Plan Hash: 510421217
    
    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)
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse       64      0.00       0.00          0          0          0           0
    Execute     64      0.24       0.91          0          0          0           0
    Fetch       64      0.14       0.42          0          0          0          64
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse       64      0.00       0.00          0          0          0           0
    Execute     64      0.24       0.91          0          0          0           0
    Fetch       64      0.14       0.42          0          0          0          64
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      192      0.39       1.34          0          0          0          64
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 64
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  VIEW  (cr=0 pr=0 pw=0 time=20602 us)
             1          1          1   SORT AGGREGATE (cr=0 pr=0 pw=0 time=20596 us)
             0          0          0    PX COORDINATOR  (cr=0 pr=0 pw=0 time=20573 us)
             0          0          0     PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
             0          0          0      VIEW  GV$SQL (cr=0 pr=0 pw=0 time=0 us)
             0          0          0       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. Wait  Total Waited ----------------------------------------   Waited  ----------  ------------ PX Deq: reap credit                          1448        0.00          0.02
      PX Deq: Join ACK                              193        0.00          0.25
      IPC send completion sync                      128        0.00          0.08
      PX Deq: Parse Reply                           128        0.06          0.24
      PX Deq: Execute Reply                         128        0.00          0.06
      reliable message                               64        0.00          0.06
      PX Deq: Signal ACK EXT                        128        0.00          0.01
      PX Deq: Slave Session Stats                   128        0.00          0.01
      enq: PS - contention                           66        0.00          0.09
      KJC: Wait for msg sends to complete             5        0.00          0.00
      latch: shared pool                              1        0.00          0.00
    ********************************************************************************
    
    SQL ID: 4b4wp0a8dvkf0 Plan Hash: 4033942373
    
    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_delta) as EXECUTIONS,
      sum(case when px_servers_execs_delta > 0
      then px_servers_execs_delta                                       else
      executions_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
                               WHERE  s.dbid = d.dbid
           AND  bitand(nvl(s.flag, 0), 1) = 0                    AND
      sn.end_interval_time >                                   (select
      systimestamp at TIME ZONE dbtimezone                  from dual) - 7
                             AND  s.sql_id = :1
       AND  s.snap_id = sn.snap_id                           AND
      s.instance_number = sn.instance_number           AND  s.dbid = sn.dbid
                                 AND  parsing_schema_name = :2)
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse       64      0.00       0.00          0          0          0           0
    Execute     64      0.08       0.08          0          0          0           0
    Fetch       64      0.13       0.27          0       1170          0          64
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      192      0.21       0.36          0       1170          0          64
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 3
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  VIEW  (cr=18 pr=0 pw=0 time=6289 us cost=17 size=78 card=1)
             1          1          1   SORT AGGREGATE (cr=18 pr=0 pw=0 time=6282 us)
             0          0          0    NESTED LOOPS  (cr=18 pr=0 pw=0 time=6263 us cost=15 size=124 card=1)
             0          0          0     NESTED LOOPS  (cr=18 pr=0 pw=0 time=6258 us cost=15 size=124 card=1)
             0          0          0      HASH JOIN  (cr=18 pr=0 pw=0 time=6256 us cost=14 size=97 card=1)
             0          0          0       NESTED LOOPS  (cr=18 pr=0 pw=0 time=6244 us cost=14 size=97 card=1)
             0          0          0        STATISTICS COLLECTOR  (cr=18 pr=0 pw=0 time=6241 us)
             0          0          0         NESTED LOOPS  (cr=18 pr=0 pw=0 time=6223 us cost=13 size=81 card=1)
             1          1          1          MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=5648 us cost=0 size=10 card=1)
             1          1          1           FIXED TABLE FULL X$KCCDI (cr=0 pr=0 pw=0 time=3449 us cost=0 size=10 card=1)
             1          1          1           BUFFER SORT (cr=0 pr=0 pw=0 time=2187 us cost=0 size=0 card=1)
             1          1          1            FIXED TABLE FULL X$KCCDI2 (cr=0 pr=0 pw=0 time=2136 us cost=0 size=0 card=1)
             0          0          0          PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=18 pr=0 pw=0 time=568 us cost=13 size=71 card=1)
             0          0          0           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)
             0          0          0            INDEX 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)
             0          0          0        TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT (cr=0 pr=0 pw=0 time=0 us cost=1 size=16 card=1)
             0          0          0         INDEX UNIQUE SCAN WRM$_SNAPSHOT_PK (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 8695)
             0          0          0       TABLE ACCESS FULL WRM$_SNAPSHOT (cr=0 pr=0 pw=0 time=0 us cost=1 size=16 card=1)
             0          0          0      INDEX UNIQUE SCAN WRM$_SNAPSHOT_PK (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 8695)
             0          0          0     TABLE ACCESS BY INDEX ROWID WRM$_SNAPSHOT (cr=0 pr=0 pw=0 time=0 us cost=1 size=27 card=1)
             0          0          0      FAST 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. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      Disk file operations I/O                        3        0.00          0.00
      control file sequential read                  448        0.00          0.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;
    

    版权说明:camash原创,转载请注明出处 http://www.cnblogs.com/shenfeng/
    --EOF--

  • 相关阅读:
    java语言基础--标识符、关键字
    #考研碎碎念#3
    #考研笔记#计算机之word问题
    #考研#计算机文化知识1(局域网及网络互联)
    #学习笔记#jsp
    #学习笔记#JSP数据交互
    考研随笔2
    考研随笔1
    几个人
    全局变量
  • 原文地址:https://www.cnblogs.com/shenfeng/p/12C_RAC_optimizer_adaptive_features.html
Copyright © 2020-2023  润新知