I/O slave wait等待事件的理解和相关分析

今天收到了一封项目组请求协助的邮件，起因比较简单：项目组在一张名为'TBL_TXN_DTL'分区表上创建了物化视图，
 某一天发现物化视图不能自动刷新了，进一步检查，这张表的一条select语句需要2-3秒钟。同时根据addm报告的确
 发现了这张表上有查询需要3.1秒的警告。
 就本次分析过程进行记录以做备忘录。
    1、查询语句很简单
    SQL> SELECT a.INST_DATE, a.INST_TIME, a.HOST_DATE, a.TXN_NUM,
               a.CARD_ACCP_TERM_ID, a.ACCT_ID1,
             CASE WHEN a.TXN_NUM IN ('1035') THEN a.ADDTNL_DATA ELSE ' ' END AS ADDTNL_DATA,
               a.PAN, a.AMT_TRANS,
             CASE WHEN a.TXN_NUM IN ('1035') THEN a.TRANS_FEE1 ELSE a.TRANS_FEE2 END AS TRANS_FEE,
               a.MSG_TYPE, a.REVSAL_FLAG, a.RESP_CODE,
               a.BATCH_FLAG, a.RETRIVL_REF
        FROM TBL_TXN_DTL a
        WHERE a.CARD_ACCP_ID = '305430159980092' AND a.INST_DATE = '20121219'
              AND a.CARD_ACCP_TERM_ID = '31000145'
              AND a.CANCEL_FLAG = '0'
              AND a.TXN_NUM IN ('1011', '1015', '1035')
              ORDER BY a.INST_TIME DESC;
    2、确定当前session 的sid为 298
    3、根据sid确定执行第一步时的等待事件
         select sid, program, event, blocking_session, blocking_session_status from v$session where sid = 298;
       SID                 PROGRAM                                EVENT                                    BLOCKING_SESSION  BLOCKING_SESSION_STATU
       ---------- ------------------      --------------------- ----------------- ----------------------------
       298                 oracle@GEPOSP2 (I102) i/o slave wait    
       
    4、等待事件的统计信息
    SQL> select event, count(1) from v$session_wait group by event;
            EVENT                                                                         COUNT(1)
            --------------------------------------------------------------------------- ----------
            SQL*Net message to client                                                            1
            gcs remote message                                                                   2
            SQL*Net message from client                                                        129
            smon timer                                                                           1
            pmon timer                                                                           1
            i/o slave wait                                                                      12
            DIAG idle wait                                                                       1
            rdbms ipc message                                                                   13
            ges remote message                                                                   1
            Streams AQ: qmn slave idle wait                                                      1
            Streams AQ: qmn coordinator idle wait                                                1
            Streams AQ: waiting for time management or cleanup tasks                             1
           
           
        5、该表的定义
       
        create table TBL_TXN_DTL (
            INST_DATE CHAR(8) not null,
            SYS_SEQ_NUM CHAR(6) not null,
            INST_TIME CHAR(6) not null,
            MSG_SRC_ID CHAR(4) not null,
            TXN_NUM CHAR(4) not null,
            TRANS_CODE CHAR(3) not null,
            TRANS_TYPE CHAR(1) not null,
            TRANS_STATE CHAR(1) not null,
            REVSAL_FLAG CHAR(1) not null,
            REVSAL_SSN CHAR(6),
            CANCEL_FLAG CHAR(1) not null,
            CANCEL_SSN CHAR(6),
            RESP_CODE CHAR(2),
            HOST_DATE CHAR(8),
            HOST_SSN CHAR(12),
            TERM_SSN CHAR(12),
            KEY_RSP CHAR(32) not null,
            KEY_REVSAL CHAR(32),
            KEY_CANCEL CHAR(32),
            HEADER_BUF CHAR(46),
            MSG_TYPE CHAR(4),
            CTI_ID CHAR(6),
            TXN_TYPE CHAR(2),
            TEL_NO CHAR(15),
            BRH_ID CHAR(10),
            SYN_RAN CHAR(8),
            SYN_NO CHAR(2),
            TSAM_NO CHAR(32),
            CENT_ID CHAR(16),
            PAN CHAR(19),
            AMT_TRANS CHAR(12),
            AMT_CDHLDR_BIL CHAR(12),
            MCHNT_TYPE CHAR(4),
            ACQ_INST_ID_CODE CHAR(11) not null,
            FWD_INST_ID_CODE CHAR(11) not null,
            RETRIVL_REF CHAR(12),
            AUTHR_ID_RESP CHAR(6),
            CARD_ACCP_TERM_ID CHAR(8) not null,
            CARD_ACCP_ID CHAR(15) not null,
            CARD_ACCP_NAME CHAR(40),
            ADDTNL_DATA_LEN CHAR(2),
            ADDTNL_DATA VARCHAR2(64),
            ADDTNL_AMT CHAR(40),
            ACCT_ID1_LEN CHAR(2),
            ACCT_ID1 CHAR(28),
            ACCT_ID2_LEN CHAR(2),
            ACCT_ID2 CHAR(28),
            TRANS_FEE1 CHAR(12),
            TRANS_FEE2 CHAR(12),
            OPEN_INST CHAR(15),
            STLM_INST CHAR(15),
            BATCH_FLAG CHAR(1),
            BATCH_DATE CHAR(8),
            AMT_RETURN CHAR(12),
            AUTHR_ID_R CHAR(6),
            MISC VARCHAR2(256),
            constraint PK_TBL_TXN_DTL primary key (INST_DATE, KEY_RSP, REVSAL_FLAG)
            using index
            local
            tablespace TBS_EPOS_TGT_IDX
            )
            tablespace TBS_EPOS_TGT_DAT
            partition by list
            (INST_DATE)
            (partition
            TXN_DTL_20110301
            values ('20110301'));

create index IDX_T_TXNDTL_1 on TBL_TXN_DTL
 (INST_DATE ASC,TXN_NUM ASC,TRANS_STATE ASC,REVSAL_FLAG ASC,CANCEL_FLAG ASC,RESP_CODE ASC,TSAM_NO ASC)local


create index IDX_T_TXNDTL_2 on TBL_TXN_DTL
(INST_DATE ASC,TXN_NUM ASC,RESP_CODE ASC,KEY_REVSAL ASC,SYN_RAN ASC,CARD_ACCP_TERM_ID ASC,CARD_ACCP_NAME ASC) local


create index IDX_T_TXNDTL_3 on TBL_TXN_DTL (INST_DATE ASC,INST_TIME ASC,TXN_NUM ASC,TERM_SSN ASC,TSAM_NO ASC) local


create index IDX_T_TXNDTL_4 on TBL_TXN_DTL (INST_DATE ASC,OPEN_INST ASC,CARD_ACCP_ID ASC,INST_TIME DESC) local

        6、AWR报告
   
      进行的分析如下：
      A --TBL_TXN_DTL 表每秒insert 84181/7200=11.6次，每秒update (80326+61861)/7200=19.8次
        B --TBL_TXN_DTL 表有索引 4个，其中一个update语句就会触发产生3个被索引键值的修改
            create index IDX_T_TXNDTL_1 on TBL_TXN_DTL
             (INST_DATE ASC,TXN_NUM ASC,TRANS_STATE ASC,REVSAL_FLAG ASC,CANCEL_FLAG ASC,RESP_CODE ASC,TSAM_NO ASC)local
            该索引在update语句中：REVSAL_FLAG、CANCEL_FLAG、RESP_CODE被修改
           
            create index IDX_T_TXNDTL_2 on TBL_TXN_DTL
            (INST_DATE ASC,TXN_NUM ASC,RESP_CODE ASC,KEY_REVSAL ASC,SYN_RAN ASC,CARD_ACCP_TERM_ID ASC,CARD_ACCP_NAME ASC) local
            该索引在update语句中：RESP_CODE被修改
           
            create index IDX_T_TXNDTL_3 on TBL_TXN_DTL (INST_DATE ASC,INST_TIME ASC,TXN_NUM ASC,TERM_SSN ASC,TSAM_NO ASC)local
           
            create index IDX_T_TXNDTL_4 on TBL_TXN_DTL (INST_DATE ASC,OPEN_INST ASC,CARD_ACCP_ID ASC,INST_TIME DESC)
            该索引update语句中OPEN_INST被修改
       
        C、--4个索引中，存在索引设计上存在不合理的问题
             被索引字段重复：
             IDX_T_TXNDTL_1 索引和IDX_T_TXNDTL_2的INST_DATE、TXN_NUM、RESP_CODE 字段重复被索引
             IDX_T_TXNDTL_1 索引和IDX_T_TXNDTL_3的INST_DATE、TSAM_NO字段重复被索引，其中TSAM_NO字段长度为32，不适合做索引字段
             IDX_T_TXNDTL_1 索引和IDX_T_TXNDTL_3的INST_DATE、INST_TIME字段被重复索引
     
        D、如果问题发生在当天的分区上，则是因为前一天的分区已经被创建，
           可能产生当天日期表分区以及索引分区上的统计信息值均为0，此时
           频繁insert和update时统计信息值不准确，建议统计信息值清空实现动态采样，
           或者将前一天的信息值复制到当前分区上。
  
        E、当前session的出现了     i/o slave wait
            io slave wait 是一个Idle的等待事件，通常出现该等待事件表示I/O资源不足，那么什么东西影响了I/O资源不足呢？
            有建议说可能是出现频繁的提交，考虑到这个问题，根据AWR报告中，两个小时内发生的相关事务进行大概统计，目标是获得数据来确认想法：
               1、TBL_MONITOR_E1DETAIL表执行delete操作 250,062次
                 delete from TBL_MONITOR_E1DETAIL where CRT_TIME<=TO_CHAR((SYSDATE-(((:b0/24)/60)/60)), 'YYYYMMDDHH24MISS')
                  2、TBL_MONITOR_E1DETAIL表执行insert操作 250,057次
                  nsert into TBL_MONITOR_E1DETAIL (CTI_ID, DSP, CHNL, STAT, TEL_NO, CRT_TIME) values (:b0, :b1, :b2, :b3, :b4, to_char(sysdate, 'YYYYMMDDHH24MISS'))
                  3、tbl_mng_dtl表上执行insert操作172,464次
                      insert into tbl_mng_dtl (txn_date, txn_time, tel_no, brh_id, txn_code, msg_type, syn_ran, syn_no, chk_data, menu_snd_flg, menu_snd_cnt, opr_snd_flg, opr_snd_cnt, func_snd_flg, func_snd_cnt, err_snd_flg, err_snd_cnt, prt_snd_flg, prt_snd_cnt, app_snd_flg, app_snd_cnt, int_seq_num, term_seq_num) values (:b0, :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10, :b11, :b12, :b13, :b14, :b15, :b16, :b17, :b18, :b19, :b20, :b21, :b22)
                  4、 tbl_txn_dtl表上执行insert 84,181次，update （80,326+61,861）次
  我们要看一下平均日志写大小
          (redo blocks written/redo writes)*512 B=(2110812 /723872)*512B=1492B
                  那么会不会导致出现i/o slave wait？
                  现在想起来，该数据库并发也不高，联机事务处理数据库肯定是有很高的提高的。
        核心问题是：为什么session的等待事件是i/o slave wait,而不是其他的 sql net message from client等等其他的idle呢？
                 既然是idle类型的等待事件（IO slave wait被分在了idle类中），通常讲idle类的等待事件是不用太多在意的，怎么理解这个idle？
                 当一个session连接到oracle server时，会建立生成一个后台process来处理，那么这个后台process和IO有什么关系？
                 后台process要读取数据，要使用cpu实现IO资源的申请和使用，当一个session连接上来，而不发起任何请求的时候，
                 数据库中该session的等待事件是SQL*Net message from client，当client发起请求时，交给了数据库处理数据并等待返回数据时，
                 数据库的cpu会也在等待吗？当然不是，此时cpu会给其它繁忙的进行使用，此时等待事件就成为了 IO slave wait，这个时候在
                 操作系统上也应该有相应的体现，就是cpu的wait会增加，即top时或者vmstat时cpu的wa字段会增加，并不是wa高就是好事，相反
                 这个等待事件也反应了session在等待数据库返回结果的时间过长，即在对应的sql可能就有问题了，或者对应的表上就有问题了。
                 产生这个问题的原因可能就是表上的设计问题，也可能是上面的几种因素引起的。
          这个问题还在继续跟踪，也需要再斟酌一下。
       
   ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
以下为补充内容：

          

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/759027/viewspace-751462/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/759027/viewspace-751462/