Oracle数据库CPU 100%故障诊断实例(原创)

czmmiao

浏览: 4421148 次
性别:
来自: 厦门

最近访客更多访客>>

zzbing

sky3063

hotsunshine

zyi74

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Oracle故障诊断

前言

这两天一只对外提供查询的数据库CPU使用率频繁攀升到100%，客户记得焦头烂额，总希望我抓点sql让开发商优化。和客户通完电话后，我心里想到，这烂系统，抓几个sql顶什么用，问题早就提过好几次了，每次都不了了之，出了问题就知道在那瞎忙，找点表面问题修修补补，本质问题从来都是置之不理。一通抱怨后，开始逐步分析，人就是这样，吃人嘴软，谁让客户是上帝呢？抱怨归抱怨，工作还是要认认真真去对待的，分析报告如下，抛砖引玉，如有错误，望批评指正，谢谢！

分析报告

系统环境：AIX 6.1 Oracle 10g 10.2.0.5.4

查询库db2 2012-07-02 09:00~~10:00 AWR 报告

查询库db2 2012-07-04 11:00~~12:00 AWR 报告

通过以上等待事件的对比可以发现，CPU等待事件明显，同时都伴随着gc cr multi block request和db file sequential read等待事件。CPU等待事件与应用上表现出CPU占用率100%的现象相吻合。结合gc cr multi block request和db file sequential read事件明显这个因素，推测是由于节点之间频繁交换数据块(构造gc cr时所进行的请求和调度需要消耗CPU时间)和磁盘与内存直接频繁读写(内存的分配与撤销同样需要消耗CPU时间)。

查看磁盘信息如下

#sar -d 1

AIX fjlt_zgcx_db02 1 6 00F65AD44C00 07/04/12

System configuration: lcpu=32 drives=150 mode=Capped

11:56:31 device %busy avque r+w/s Kbs/s avwait avserv

11:56:32 hdisk3 0 0.0 0 0 0.0 0.0

hdisk5 0 0.0 0 0 0.0 0.0

hdisk10 0 0.0 0 0 0.0 0.0

hdisk16 0 0.0 0 0 0.0 0.0

hdisk7 0 0.0 0 0 0.0 0.0

hdisk9 0 0.0 0 0 0.0 0.0

hdisk14 0 0.0 0 0 0.0 0.0

hdisk13 0 0.0 0 0 0.0 0.0

hdisk8 0 0.0 0 0 0.0 0.0

hdisk15 0 0.0 0 0 0.0 0.0

hdisk18 23 0.0 792 6481 0.0 0.3

hdisk19 0 0.0 0 0 0.0 0.0

hdisk20 2 0.0 33 270 0.0 0.8

hdisk22 10 0.0 320 2563 0.0 0.5

hdisk23 0 0.0 0 0 0.0 0.0

hdisk24 0 0.0 0 0 0.0 0.0

hdisk21 0 0.0 0 0 0.0 0.0

hdisk25 0 0.0 0 0 0.0 0.0

hdisk26 0 0.0 0 0 0.0 0.0

hdisk27 0 0.0 0 0 0.0 0.0

hdisk28 0 0.0 0 0 0.0 0.0

hdisk29 0 0.0 0 0 0.0 0.0

hdisk30 0 0.0 0 0 0.0 0.0

hdisk31 35 0.0 1140 9122 0.0 0.4

hdisk32 0 0.0 0 0 0.0 0.0

hdisk33 0 0.0 5 47 0.0 0.2

hdisk34 0 0.0 0 0 0.0 0.0

hdisk35 0 0.0 0 0 0.0 0.0

hdisk36 0 0.0 0 0 0.0 0.0

hdisk37 0 0.0 0 0 0.0 0.0

hdisk39 0 0.0 0 0 0.0 0.0

hdisk40 0 0.0 0 0 0.0 0.0

hdisk41 0 0.0 0 0 0.0 0.0

hdisk38 0 0.0 0 0 0.0 0.0

hdisk42 0 0.0 0 0 0.0 0.0

hdisk43 0 0.0 0 0 0.0 0.0

hdisk44 17 0.0 951 7609 0.0 0.2

hdisk46 0 0.0 10 87 0.0 0.2

hdisk47 0 0.0 0 0 0.0 0.0

hdisk48 0 0.0 0 0 0.0 0.0

hdisk45 0 0.0 0 0 0.0 0.0

hdisk49 0 0.0 0 0 0.0 0.0

hdisk50 0 0.0 0 0 0.0 0.0

hdisk51 0 0.0 0 0 0.0 0.0

hdisk52 0 0.0 0 0 0.0 0.0

hdisk53 0 0.0 0 0 0.0 0.0

hdisk54 0 0.0 0 0 0.0 0.0

hdisk55 0 0.0 0 0 0.0 0.0

hdisk57 7 0.0 487 3900 0.0 0.2

hdisk58 0 0.0 0 0 0.0 0.0

hdisk59 0 0.0 23 191 0.0 0.5

hdisk56 0 0.0 0 0 0.0 0.0

hdisk60 0 0.0 0 0 0.0 0.0

hdisk61 7 0.0 540 4322 0.0 0.2

hdisk62 0 0.0 0 0 0.0 0.0

hdisk63 0 0.0 0 0 0.0 0.0

hdisk64 0 0.0 0 0 0.0 0.0

hdisk65 0 0.0 0 0 0.0 0.0

hdisk66 0 0.0 0 0 0.0 0.0

hdisk68 0 0.0 0 0 0.0 0.0

hdisk69 0 0.0 0 0 0.0 0.0

hdisk67 0 0.0 0 0 0.0 0.0

hdisk71 0 0.0 0 0 0.0 0.0

hdisk70 0 0.0 0 0 0.0 0.0

hdisk74 0 0.0 0 0 0.0 0.0

hdisk76 0 0.0 0 0 0.0 0.0

hdisk77 0 0.0 0 0 0.0 0.0

hdisk78 0 0.0 0 0 0.0 0.0

hdisk75 0 0.0 0 0 0.0 0.0

hdisk73 0 0.0 0 0 0.0 0.0

hdisk80 0 0.0 0 0 0.0 0.0

hdisk81 0 0.0 0 0 0.0 0.0

hdisk82 0 0.0 0 0 0.0 0.0

hdisk79 0 0.0 0 0 0.0 0.0

hdisk84 0 0.0 0 0 0.0 0.0

hdisk72 0 0.0 0 0 0.0 0.0

hdisk83 0 0.0 0 0 0.0 0.0

hdisk87 0 0.0 0 0 0.0 0.0

hdisk88 0 0.0 0 0 0.0 0.0

hdisk89 0 0.0 0 0 0.0 0.0

hdisk86 0 0.0 0 0 0.0 0.0

hdisk92 0 0.0 0 0 0.0 0.0

hdisk85 0 0.0 0 0 0.0 0.0

hdisk93 0 0.0 0 0 0.0 0.0

hdisk90 0 0.0 0 0 0.0 0.0

hdisk94 0 0.0 0 0 0.0 0.0

hdisk91 0 0.0 0 0 0.0 0.0

hdisk96 0 0.0 0 0 0.0 0.0

hdisk98 0 0.0 0 0 0.0 0.0

hdisk95 0 0.0 0 0 0.0 0.0

hdisk99 0 0.0 0 0 0.0 0.0

hdisk100 0 0.0 0 0 0.0 0.0

hdisk97 0 0.0 0 0 0.0 0.0

hdisk101 0 0.0 0 0 0.0 0.0

hdisk102 0 0.0 0 0 0.0 0.0

hdisk103 0 0.0 0 0 0.0 0.0

hdisk105 0 0.0 0 0 0.0 0.0

hdisk106 0 0.0 0 0 0.0 0.0

hdisk107 0 0.0 0 0 0.0 0.0

hdisk104 0 0.0 0 0 0.0 0.0

hdisk109 0 0.0 0 0 0.0 0.0

hdisk110 0 0.0 0 0 0.0 0.0

hdisk111 0 0.0 0 0 0.0 0.0

hdisk113 0 0.0 0 0 0.0 0.0

hdisk114 0 0.0 0 0 0.0 0.0

hdisk108 0 0.0 0 0 0.0 0.0

hdisk115 0 0.0 0 0 0.0 0.0

hdisk112 0 0.0 0 0 0.0 0.0

hdisk117 0 0.0 0 0 0.0 0.0

hdisk118 0 0.0 0 0 0.0 0.0

hdisk119 0 0.0 0 0 0.0 0.0

hdisk116 0 0.0 0 0 0.0 0.0

hdisk121 0 0.0 0 0 0.0 0.0

hdisk122 0 0.0 0 0 0.0 0.0

hdisk123 0 0.0 0 0 0.0 0.0

hdisk125 0 0.0 0 0 0.0 0.0

hdisk120 0 0.0 0 0 0.0 0.0

hdisk124 0 0.0 0 0 0.0 0.0

hdisk128 0 0.0 0 0 0.0 0.0

hdisk126 0 0.0 0 0 0.0 0.0

hdisk127 0 0.0 0 0 0.0 0.0

hdisk131 0 0.0 0 0 0.0 0.0

hdisk132 0 0.0 0 0 0.0 0.0

hdisk129 0 0.0 0 0 0.0 0.0

hdisk130 0 0.0 0 0 0.0 0.0

hdisk134 0 0.0 0 0 0.0 0.0

hdisk135 0 0.0 0 0 0.0 0.0

hdisk133 0 0.0 0 0 0.0 0.0

hdisk136 0 0.0 0 0 0.0 0.0

hdisk138 0 0.0 0 0 0.0 0.0

hdisk139 0 0.0 0 0 0.0 0.0

hdisk140 0 0.0 0 0 0.0 0.0

hdisk137 0 0.0 0 0 0.0 0.1

hdisk141 0 0.0 0 0 0.0 0.0

hdisk143 0 0.0 0 0 0.0 0.0

hdisk144 0 0.0 0 0 0.0 0.0

hdisk142 0 0.0 0 0 0.0 0.0

hdisk147 0 0.0 0 0 0.0 0.0

hdisk148 0 0.0 0 0 0.0 0.0

hdisk149 0 0.0 0 0 0.0 0.0

hdisk146 0 0.0 0 0 0.0 0.0

hdisk145 0 0.0 0 0 0.0 0.0

hdisk4 0 0.0 0 0 0.0 0.0

hdisk12 0 0.0 0 0 0.0 0.0

hdisk11 0 0.0 0 0 0.0 0.0

hdisk17 0 0.0 0 0 0.0 0.0

hdisk6 0 0.0 0 0 0.0 0.0

hdisk0 0 0.0 0 0 0.0 0.0

hdisk1 0 0.0 0 0 0.0 0.0

hdisk2 0 0.0 0 0 0.0 0.0

可以看到虽然本机连接的磁盘有140 余块，但读写主要发生在disk18,disk22,disk44,disk57,disk61 。

查看热点对象

SQL> set linesize 180

SQL> col object_name for a40

SQL> select * from

2 (select

3 ob.owner, ob.object_name, sum(b.tch) Touchs

4 from x$bh b , dba_objects ob

5 where b.obj = ob.data_object_id

6 and b.ts# > 0

7 group by ob.owner, ob.object_name

8 order by sum(tch) desc)

9 where rownum <=10 ;

OWNER OBJECT_NAME TOUCHS

------------------------------ ---------------------------------------- ----------

DSG DJ_NSRXX 8248380

DSG SB_ZSXX 4351836

DSG FP_DEAL_INFO 4013427

DSG IDX_SB_ZSXX_FQ_DZDAH 317808

DSG SB_SBXX 258592

DSG HD_DQDEHDB 141183

DSG IDX_SB_SBXX_DZDAH_SSQ 128800

DSG DJ_SZ_JBXX 112147

DSG IND_SB_ZSXX_FQ_NSRSWJG 68625

DSG IDX_FPDEALINFO_NSRDZDAH 41999

可以看到DJ_NSRXX 和SB_ZSXX 这两张表为热点对象

SQL> select TABLE_NAME ,INDEX_NAME ,TABLESPACE_NAME from dba_indexes where table_name='DJ_NSRXX'

TABLE_NAME INDEX_NAME TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

DJ_NSRXX IDX_DJ_NSRXX_NSRDNBM XMZG_TEM_DAT

DJ_NSRXX IDX_DJ_NSRXX_NSRSBH XMZG_TEM_DAT

DJ_NSRXX PK_DJ_NSRXX XMZG_TEM_DAT

DJ_NSRXX PK_DJ_NSRXX FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_DJZCLX_DM FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_HY_DM FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_JDXZ_DM FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_LRR_DM FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_NSRDNBM FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_NSRSBH FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_NSRZT_DM FJLTAIS_IDX

TABLE_NAME INDEX_NAME TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

DJ_NSRXX IDX_DJ_NSRXX_NSR_SWJG_DM FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_WSPZXH FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_ZGSWRY_DM FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_ZJHM FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_NSRMC FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_NSRDNBM FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_DJZCLX_DM

DJ_NSRXX IDX_DJ_NSRXX_HY_DM

DJ_NSRXX IDX_DJ_NSRXX_JDXZ_DM

DJ_NSRXX IDX_DJ_NSRXX_LRR_DM

DJ_NSRXX IDX_DJ_NSRXX_NSRSBH FJLTAIS_IDX

TABLE_NAME INDEX_NAME TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

DJ_NSRXX IDX_DJ_NSRXX_NSRMC

DJ_NSRXX IDX_DJ_NSRXX_SWDJBLX FJLTAIS_IDX

DJ_NSRXX IDX_DJ_NSRXX_NSRZT_DM

DJ_NSRXX IDX_DJ_NSRXX_NSR_SWJG_DM

DJ_NSRXX IDX_DJ_NSRXX_WSPZXH

DJ_NSRXX IDX_DJ_NSRXX_ZGSWRY_DM

DJ_NSRXX IDX_DJ_NSRXX_ZJHM

DJ_NSRXX PK_DJ_NSRXX FJLTAIS_DAT

可以看到

IDX_DJ_NSRXX_DJZCLX_DM

IDX_DJ_NSRXX_HY_DM

IDX_DJ_NSRXX_JDXZ_DM

IDX_DJ_NSRXX_LRR_DM

IDX_DJ_NSRXX_NSRMC

IDX_DJ_NSRXX_NSRZT_DM

IDX_DJ_NSRXX_NSR_SWJG_DM

IDX_DJ_NSRXX_WSPZXH

IDX_DJ_NSRXX_ZGSWRY_DM

IDX_DJ_NSRXX_ZJHM

PK_DJ_NSRXX

IDX_DJ_NSRXX_NSRDNBM

IDX_DJ_NSRXX_NSRSBH

PK_DJ_NSRXX

这些索引并没有正确的存储在索引表空间FJLTAIS_IDX 上，查看消耗CPU 时间最多的sql

SELECT (select NSRDNBM from dj_nsrxx where nsrdzdah=a.nsrdzdah) AS NSRDNBM, A.MC AS NSRMC, (select NSRSBH from dj_nsrxx where nsrdzdah=a.nsrdzdah) AS NSRSBH, A.ZJHM AS ZJHM, A.XSSR AS JSJE, A.SL AS SL, A.YNSE AS YNSE, A.SE AS SE, TO_CHAR(A.SBRQ, 'YYYY-MM-DD') AS SBRQ, TO_CHAR(A.SSSQ_Q, 'YYYY-MM-DD') || ' - ' || TO_CHAR(A.SSSQ_Z, 'YYYY-MM-DD') AS SKSSQ, TO_CHAR(A.XJQX, 'YYYY-MM-DD') AS XJQX, TO_CHAR(A.SJRQ_JZ, 'YYYY-MM-DD') AS SJRQ, TO_CHAR(A.RKRQ, 'YYYY-MM-DD') AS RKRQ, TO_CHAR(A.RKRQ_JZ, 'YYYY-MM-DD') AS RKRQ_JZ, TO_CHAR(A.KPRQ, 'YYYY-MM-DD') AS KPRQ, P_GET_CODENAME('DM_SBFS', A.SBFS_DM) AS SBFS_MC, P_GET_CODENAME('DM_SKZT', A.SKZT_DM) AS SKZT_MC, P_GET_CODENAME('DM_SKSX', A.SKSX_DM) AS SKSX_MC, (SELECT ZSPM_MC FROM DM_ZSPM WHERE ZSXM_DM =A.ZSXM_DM AND ZSPM_DM=A.ZSPM_DM) AS ZSPM_MC, A.NSR_SWJG_DM AS NSR_SWJG_DM, A.ZSXM_DM AS ZSXM_DM, A.YSKM_DM AS YSKM_DM, A.YSFPBL_DM AS YSFPBL_DM, A.JKPZLRR_DM AS JKPZLRR_DM, A.SJXHR_DM AS SJXHR_DM, A.RKXHR_DM AS RKXHR_DM, A.ZGSWRY_DM AS ZGSWRY_DM, A.LRR_DM AS LRR_DM, A.SKSS_SWJG_DM AS SKSS_SWJG_DM, A.ZSJG_DM AS ZSJG_DM, A.JKPZZL_DM AS JKPZZL_DM, TO_CHAR(A.JKPZXH) AS JKPZXH, A.ZG AS ZG, A.ZH AS ZH, A.SKGK_DM as skgkDm, DECODE( B.DDLXQY_BZ, 'Z', '????????', 'S', '????????', '') AS "ddlxqyBz" FROM SB_ZSXX A, DJ_DDLXQY B, (SELECT E.SWJG_DM FROM DM_SWJG E WHERE E.SWJG_BZ = 'J' CONNECT BY SJ_SWJG_DM = PRIOR SWJG_DM START WITH SWJG_DM = :1) C WHERE A.NSR_SWJG_DM = C.SWJG_DM AND A.NSRDZDAH = B.NSRDZDAH(+) AND A.TZLX_DM IN('1', '4') AND A.SE!=0 AND A.YZFSRQ_JZ IS NOT NULL AND A.SBRQ >= TO_DATE(:2, 'YYYY-MM-DD') AND A.SBRQ <= TO_DATE(:3, 'YYYY-MM-DD') AND A.ZSXM_DM = :4 AND A.DQ=:5
执行计划如下

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1223 | 273K| | 17606 (1)| 00:03:32 | | |

|* 1 | TABLE ACCESS BY INDEX ROWID | GF_CBDJYB_DZ | 1 | 37 | | 4 (0)| 00:00:01 | | |

|* 2 | INDEX RANGE SCAN | IDX_GF_CBDJYB_DZ_YBIDXH | 1 | | | 3 (0)| 00:00:01 | | |

|* 3 | TABLE ACCESS BY INDEX ROWID | GF_CBDJYB_DZ | 1 | 37 | | 4 (0)| 00:00:01 | | |

|* 4 | INDEX RANGE SCAN | IDX_GF_CBDJYB_DZ_YBIDXH | 1 | | | 3 (0)| 00:00:01 | | |

| 5 | SORT AGGREGATE | | 1 | 27 | | | | | |

|* 6 | TABLE ACCESS FULL | GF_CBDJYB_DZ | 1 | 27 | | 332 (1)| 00:00:04 | | |

| 7 | SORT AGGREGATE | | 1 | 27 | | | | | |

|* 8 | TABLE ACCESS FULL | GF_CBDJYB_DZ | 1 | 27 | | 332 (1)| 00:00:04 | | |

|* 10 | HASH JOIN SEMI | | 1223 | 200K| | 15159 (1)| 00:03:02 | | |

|* 11 | HASH JOIN | | 5633 | 836K| 2104K| 12688 (1)| 00:02:33 | | |

| 12 | NESTED LOOPS | | 18507 | 1879K| | 10148 (1)| 00:02:02 | | |

| 13 | VIEW | | 15 | 195 | | 2 (0)| 00:00:01 | | |

|* 14 | FILTER | | | | | | | | |

|* 15 | CONNECT BY WITH FILTERING | | | | | | | | |

| 16 | TABLE ACCESS BY INDEX ROWID | DM_SWJG | 1 | 24 | | 2 (0)| 00:00:01 | | |

|* 17 | INDEX UNIQUE SCAN | PK_DM_SWJG | 1 | | | 1 (0)| 00:00:01 | | |

| 18 | NESTED LOOPS | | | | | | | | |

| 19 | CONNECT BY PUMP | | | | | | | | |

| 20 | TABLE ACCESS BY INDEX ROWID | DM_SWJG | 15 | 360 | | 2 (0)| 00:00:01 | | |

|* 21 | INDEX RANGE SCAN | IDX_DM_SWJG_SJ_SWJG_DM | 15 | | | 1 (0)| 00:00:01 | | |

| 22 | PARTITION RANGE ITERATOR | | 1234 | 109K| | 676 (0)| 00:00:09 | KEY | KEY |

|* 23 | TABLE ACCESS BY LOCAL INDEX ROWID| DJ_NSRXX | 1234 | 109K| | 676 (0)| 00:00:09 | KEY | KEY |

|* 24 | INDEX RANGE SCAN | IDX_DJ_NSRXX_NSR_SWJG_DM | 2049 | | | 42 (0)| 00:00:01 | KEY | KEY |

|* 25 | TABLE ACCESS FULL | GF_NFRXX | 240K| 11M| | 1752 (1)| 00:00:22 | | |

|* 26 | TABLE ACCESS FULL | GF_DJXZ | 229K| 3592K| | 2469 (2)| 00:00:30 | | |

| 27 | TABLE ACCESS BY INDEX ROWID | DJ_NSRXX_KZ | 1 | 61 | | 2 (0)| 00:00:01 | | |

|* 28 | INDEX UNIQUE SCAN | PK_DJ_NSRXX_KZ | 1 | | | 1 (0)| 00:00:01 | | |

-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("CBDJYIDZ"."NSRDZDAH"=:B1 AND "CBDJYIDZ"."ZIDBZ"='Y' AND "CBDJYIDZ"."YXBZ"='Y' AND "CBDJYIDZ"."XYBZ"='Y')

2 - access("CBDJYIDZ"."YBIDXH"=:B1)

3 - filter("CBDJYIDZ"."NSRDZDAH"=:B1 AND "CBDJYIDZ"."ZIDBZ"='Y' AND "CBDJYIDZ"."YXBZ"='Y' AND "CBDJYIDZ"."XYBZ"='Y')

4 - access("CBDJYIDZ"."YBIDXH"=:B1)

6 - filter("CBDJYIDZ"."NSRDZDAH"=:B1 AND "CBDJYIDZ"."ZIDBZ"<>'Y' AND "CBDJYIDZ"."YXBZ"='Y' AND "CBDJYIDZ"."XYBZ"='Y')

8 - filter("CBDJYIDZ"."NSRDZDAH"=:B1 AND "CBDJYIDZ"."ZIDBZ"<>'Y' AND "CBDJYIDZ"."YXBZ"='Y' AND "CBDJYIDZ"."XYBZ"='Y')

10 - access("DJXZ"."NSRDZDAH"="NSRXX"."NSRDZDAH")

11 - access("NSRXX"."NSRDZDAH"="NFRXX"."NSRDZDAH")

14 - filter("YXBZ"='Y')

15 - access("SJ_SWJG_DM"=PRIOR "SWJG_DM")

17 - access("SWJG_DM"='23503000000')

21 - access("SJ_SWJG_DM"=PRIOR "SWJG_DM")

23 - filter("NSRXX"."HJBZ_DM"<>'000' AND "NSRXX"."HJBZ_DM"<>'001')

24 - access("F"."SWJG_DM"="NSRXX"."NSR_SWJG_DM")

25 - filter("NFRXX"."NFRZT_DM"<>'50' AND "NFRXX"."NFRZT_DM"<>'10')

26 - filter("DJXZ"."XZ"='45' AND "DJXZ"."SXBZ"='Y' AND "DJXZ"."YXBZ"='Y')

28 - access("NSRXX"."NSRDZDAH"="NSRXXKZ"."NSRDZDAH")

54 rows selected.

可以看到，使用了IDX_DJ_NSRXX_NSR_SWJG_DM 索引，但由于没有有效分离表和索引的存储，有可能造成db file sequential read 顺序读取磁盘等待事件。个人认为就本例而言，db file sequential read 和 CPU 100% 关系不会太大，但这也是值得注意的一方面。

使用vmstat 1 查看系统内存及cpu 使用情况

并没有发生内存不足的现象，但CPU 却有高达38 的进程等待

查询了下当时系统中活动的session ，发现只在90 左右。并发数不高(查询记录没有及时保存)

查询库db2 2012-07-02 09:00~~10:00 AWR 报告

查询库db2 2012-07-04 11:00~~12:00 AWR 报告

可以看到cache buffers chains 很高，该等待事件是由于不同进程在buffer 中争用同一个数据块导致的，即热点对象，很容易引起CPU 进程堵塞，使用率升高。

查看数据库热点对象如下

热点对象有DJ_NSRXX ，SB_ZSXX ，FP _DEAL_INFO

查看这些表的pct_free 值

SQL> select table_name,pct_free from dba_tables where table_name in ('DJ_NSRXX','SB_ZSXX','FP _DEAL_INFO');

TABLE_NAME PCT_FREE

------------------------------ ----------

SB_ZSXX 10

DJ_NSRXX 10

SB_ZSXX 10

DJ_NSRXX 10

SB_ZSXX

DJ_NSRXX

8 rows selected.

这些表的pct_free 均为10% ，可以考虑扩大pct_free 值，使数分布到多个数据块中以减少热点块争用。Pct_free 值需要逐步调整并同时进行观察才可确认调整为何值合适。

查询库db2 2012-07-02 09:00~~10:00 AWR 报告

Estd Interconnect traffic (KB)

119,976.69

查询库db2 2012-07-04 11:00~~12:00 AWR 报告

Estd Interconnect traffic (KB)

142,074.05

可以看到Estd Interconnect traffic (KB) 非常的高，表示节点之间频繁数据块，进行gc cr 块的构造。而且从上文提到的等待事件也可以看出，gc cr multi block request 在两个awr 中都有体现，是主要等待事件。

查询库db2 2012-07-02 09:00~~10:00 AWR 报告

查询库db2 2012-07-04 11:00~~12:00 AWR 报告

Logical reads 非常高和 gc cr multi block request 等待事件相吻合。如上文所述， gc cr multi block request 会造成CPU 对内存的调度和管理，会消耗CPU 时间。结合gc cr multi block request 和逻辑读非常高以及 cache buffers chains 的情况来看，个人认为，gc cr multi block request 和热点块共同造成了CPU 100% 的问题，且gc cr multi block request 为主要问题，主要依据：Estd Interconnect traffic (KB)异常的高，而且一般 cache buffers chains引起的问题都会在top 5等待事件中体现，故判断 gc cr multi block request为主要原因。
个人建议开放商在调整sql 时避免全表扫描，这样会避免内存的频繁调度。

解决 gc cr multi block request 问题应在rac 层面上进行应用分离，即不同节点处理不同应用，节点之间通过配置，做为彼此的备用节点，在节点宕机时可以结果相关应用，提供高可用性。事实上在整理这篇报告给客户的时候也整理出了一些SQL ordered by CPU Time给客户，毕竟客户要的就是这个吗，总得满足下人家的需求

本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:czmcj@163.com

0
顶

2
踩

分享到：

了解Oracle RAC Brain Split Resolution集 ... | LDAP常用命令解析

2012-07-05 13:55
浏览 16936
评论(0)
分类:数据库
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论