某游戏大区DB IO负载过高分析

• 【问题】

  下图信息看出机器IO负载过高, IO使用率: 平均值 50%, 峰值 98%, 业务高峰时间段(19:00-22:00)IO使用率持续80%以上.

  

• 【分析】

  • 提取20:00-21:00的AWR报告内容:

    

  • 关联SQL:

    

  • 物理读Top:

    

  对应的SQL语句如下:

  select b.nick, a.itemid  from  (      select usn, tousn, itemid, logdate, gtype       from          r2b2_ap_item_log      where           tousn=:V00001 and          logdate>=:V00002 and          gtype in ('E', 'G', 'A') and          rownum <= 100       union all       select usn, tousn, itemid, logdate, gtype       from          r2b2_cs_item_log      where t          ousn=:V00003 and          logdate>=:V00004 and          gtype in ('E', 'G', 'A') and          rownum <= 100  ) a, r2b2_user b   where       a.usn=b.usn  order by logdate;

---

从AWR以及SQL中可以看出:

1. 这条SQL在1h内执行了3505次, 几乎每秒执行一次, 每次cost time是0.45秒;
2. 看SQL内容, 发现是LOG道具日志表和USER信息表的关联查询;
3. 查看SQL的执行计划命中了索引, 但不是很好;
4. 从Physical Reads中看到发生物理读最多的也是SQL中关联到的R2B2_CS_ITEM_LOG表和R2B2_AP_ITEM_LOG表;

---

• 【解决办法】

  1. 对表R2B2_AP_ITEM_LOG和R2B2_CS_ITEM_LOG创建基于TOUSN和GTYPE的复合索引;
  2. 升级DB机器硬件.

• 【分析测试】

  • 测试中绑定变量为:

    V00001=14963971  V00002=20111026035807  V00003=14963971  V00004=20111026035807

  • 优化前执行计划:

    

    从上面执行计划看到 :

    1. R2B2_AP_ITEM_LOG表和R2B2_CS_ITEM_LOG表均命中了TOUSN的索引;
    2. 观察A-Rows和Buffers以及Reads信息发现: Id=7&8步骤发生物理读130块, 逻辑读130块, 返回1行. 说明索引的效果并没有很好的发挥作用;
    3. 单次SQL执行在最差情况下发生逻辑读233块, 233*8KB=1864KB;
    4. 单次SQL执行在最差情况下发生物理读229块, 229*8KB=1832KB 物理IO交换;

    观察SQL执行计划, 在Id=7&8以及Id=10&11步骤时TOUSN索引效率不高, 不能精确匹配到ROWID信息, 返回的结果集太多, 其中包含很多无用的信息, 在其上的步骤都是根据ROWID信息访问数据结构返回数据.

  因此要解决SQL执行效率问题, 关键在Id=7&8以及Id=10和11步骤.

  考虑尽量在Id=8和Id=11步骤就可以从索引结构中的叶子节点精确返回ROWID信息, 减少返回的结果集. 从而在Id=7和Id=10步骤得到上一步结果集后访问数据结构时, 就可以减少物理IO.

  添加索引:

  在这里复合索引的选择上有2中选择:

  a. TOUSN和LOGDATE复合索引;

  b. TOUSN和GTYPE复合索引;

  观察表数据分布, 对于GTYPE列选择E、G、A类型主要是礼物、活动分发、管理者提供的, 相对量少很多;

  因此, 选择GTYPE做为索引列会增强SQL的selectivity, 提高SQL执行效率;

  

优化后执行计划:

从上面执行计划看出:

1. 查询命中了新的索引(TOUSN和GTYPE复合索引);
2. 观察A-Rows和Buffers以及Reads信息发现: Id=7&8步骤发生物理读4块, 逻辑读4块, 返回1行数据, 物理读和逻辑读大大减少. 说明在索引扫描阶段就可以很精确的匹配到ROWID信息;
3. 单次SQL执行在最差情况下发生逻辑读16块, 16*8KB=128KB;
4. 单次SQL执行在最差情况下发生物理读12块, 12*8KB=96KB 物理IO交换;
5. 从Predicate Information看到, 通过TOUSN访问到叶子节点中GTYPE信息, 然后通过SQL输入的GTYPE值过滤出准确的ROWID信息;

• 【收益】

  	物理读(块/次)	逻辑读(块/次)	物理IO(KB/次)
  优化前	229	233	2394
  优化后	12	16	96

  可以看出:
  1. 单次SQL执行物理读取数据块减少217块;
  2. 单次SQL执行发生逻辑IO减少1864KB-128KB=1736KB=1.7MB;
  3. 单次SQL执行发生物理IO减少1832KB-96KB=1736KB=1.7MB;