Chinese

一个查询语句执行时占用单核CPU资源100%,请帮忙分析原因。

SQL及执行计划如下:
*** 2012-08-29 11:46:25.764
WAIT #9: nam='SQL*Net message from client' ela= 16994971 driver id=1413697536 #bytes=1 p3=0 obj#=16923 tim=1314660142349078
STAT #7 id=1 cnt=3 pid=0 pos=1 obj=0 op='HASH GROUP BY (cr=8283188 pr=0 pw=0 time=75576024 us)'
STAT #7 id=2 cnt=3 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=8283188 pr=0 pw=0 time=22207347 us)'
STAT #7 id=3 cnt=20138 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=58213 pr=0 pw=0 time=503569 us)'
STAT #7 id=4 cnt=20138 pid=3 pos=1 obj=16923 op='TABLE ACCESS BY INDEX ROWID REAL_XS_BMSDHZ (cr=17935 pr=0 pw=0 time=161196 us)'
STAT #7 id=5 cnt=20138 pid=4 pos=1 obj=16925 op='INDEX RANGE SCAN IDX_REAL_XS_BMSDHZ_SP (cr=205 pr=0 pw=0 time=20201 us)'
STAT #7 id=6 cnt=20138 pid=3 pos=2 obj=16307 op='TABLE ACCESS BY INDEX ROWID BM (cr=40278 pr=0 pw=0 time=263247 us)'
STAT #7 id=7 cnt=20138 pid=6 pos=1 obj=16308 op='INDEX UNIQUE SCAN PK_BM (cr=20140 pr=0 pw=0 time=130059 us)'
STAT #7 id=8 cnt=3 pid=2 pos=2 obj=16307 op='TABLE ACCESS BY INDEX ROWID BM (cr=8224975 pr=0 pw=0 time=75131469 us)'
STAT #7 id=9 cnt=3 pid=8 pos=1 obj=16309 op='INDEX UNIQUE SCAN BM_BMDM (cr=8224972 pr=0 pw=0 time=75063047 us)'
STAT #7 id=10 cnt=12084 pid=9 pos=1 obj=0 op='NESTED LOOPS (cr=8204832 pr=0 pw=0 time=74863852 us)'
STAT #7 id=11 cnt=3452523 pid=10 pos=1 obj=16924 op='INDEX RANGE SCAN PK_REAL_XS_BMSDHZ (cr=1287698 pr=0 pw=0 time=34756285 us)'
STAT #7 id=12 cnt=12084 pid=10 pos=2 obj=16307 op='TABLE ACCESS BY INDEX ROWID BM (cr=6917134 pr=0 pw=0 time=36180549 us)'
STAT #7 id=13 cnt=3452523 pid=12 pos=1 obj=16308 op='INDEX UNIQUE SCAN PK_BM (cr=3464611 pr=0 pw=0 time=17895710 us)'
=====================
PARSING IN CURSOR #5 len=985 dep=0 uid=36 oct=3 lid=36 tim=1314660142349798 hv=3549420567 ad='fb568520'
SELECT C.BMDM,
C.DEPT_NAME,
A.XSSD1,
A.XSSD2,
0 XSJE,
0 XSBS,
0 YHJE,
0 XSJE_BHQ,
SUM (A.XSJE) OLD_XSJE,
SUM (A.YHJE) OLD_YHJE,
SUM (A.XSJE - A.YHJE) OLD_XSJE_BHQ,
SUM (A.XSBS) OLD_XSBS,
0 COMPARE,
0 KDJ,
0 OLD_KDJ,
0 KDJ_BHQ,
0 OLD_KDJ_BHQ
FROM REAL_XS_BMSDHZ A, BM B, BM C
WHERE A.DEPTID = B.DEPTID
AND SUBSTR (B.BMDM, 1, 4) = C.BMDM
AND A.JZRQ = '2012-8-22'
AND NOT EXISTS
(SELECT 1
FROM REAL_XS_BMSDHZ Z1, BM B1
WHERE Z1.XSSD1 = A.XSSD1
AND Z1.DEPTID = B1.DEPTID
AND SUBSTR (B1.BMDM, 1, 4) = C.BMDM
AND Z1.JZRQ = '2012-8-23')
GROUP BY C.BMDM,
C.DEPT_NAME,
A.XSSD1,
A.XSSD2
END OF STMT
PARSE #5:c=0,e=186,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1314660142349795
EXEC #5:c=0,e=128,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1314660142350084
WAIT #5: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=16923 tim=1314660142350130
WAIT #5: nam='gc cr block 2-way' ela= 394 p1=129 p2=25709 p3=1 obj#=16923 tim=1314660144811060
*** 2012-08-29 11:47:46.104
FETCH #5:c=80317790,e=78455566,p=0,cr=8283188,cu=0,mis=0,r=1,dep=0,og=1,tim=1314660220805742
WAIT #5: nam='SQL*Net message from client' ela= 3424 driver id=1413697536 #bytes=1 p3=0 obj#=16923 tim=1314660220809320
WAIT #5: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=16923 tim=1314660220809468
FETCH #5:c=0,e=78,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,tim=1314660220809529
WAIT #5: nam='SQL*Net message from client' ela= 12233 driver id=1413697536 #bytes=1 p3=0 obj#=16923 tim=1314660220821911
=====================

在fetch阶段出现耗费大量CPU等待时间，这是为何？

环境：
OS：
lsb_release -d
Description: Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
数据库版本：
SQL> select * From v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production