Oracle数据库CPU 100%故障诊断实例
前言
这两天一只对外提供查询的数 据库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
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
| 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
SB_ZSXX 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 时避免全表扫描，这样会避免内存的频繁调度。
