Chinese

下面的执行计划已经过tkprof处理
不理解为什么NESTED LOOPS 这一行 耗用的资源比较多？另：怎样分析这样一条SQL语句？思路
SQL ID: 05xm4k65075rt
Plan Hash: 2063791283
INSERT INTO SP_FCY_DEPOSIT_RATE_DTL SELECT T.BRANCH_NUM, T.REFERENCE, ''
CUST_NUM, A.ACCT_CODE, '00' || I.RESIDENT, A.CURRENCY, A.CUST_PROP, 'M',
:B1 FROM V_BRZ_IDMS_CASH_TRANS T, V_BRZ_IDMS_ACC_DTL I, (SELECT DISTINCT
T.ACCT_CODE, T.CUST_PROP, T.CURRENCY FROM DM_ACCNTAB T WHERE T.DATA_DATE >
TO_CHAR(ADD_MONTHS(TO_DATE(:B1 , 'yyyy-mm-dd'), -1), 'yyyy-mm-dd') AND
T.DATA_DATE <= :B1 AND T.SRC_SYS = 'IDMS' AND (SUBSTR(T.CUST_NUM, 1, 2) NOT
IN ('37', '52', '51', '47', '41') AND T.CUST_NUM NOT IN ('4000048') AND
SUBSTR(T.CUST_NUM, 1, 3) NOT IN ('352', '369', '302', '356', '502', '419',
'452', '402'))) A WHERE T.VALUE_DATE > TO_CHAR(ADD_MONTHS(TO_DATE(:B1 ,
'yyyy-mm-dd'), -1), 'yyyy-mm-dd') AND T.VALUE_DATE <= :B1 AND I.DATA_DATE >
TO_CHAR(ADD_MONTHS(TO_DATE(:B1 , 'yyyy-mm-dd'), -1), 'yyyy-mm-dd') AND
I.DATA_DATE <= :B1 AND T.CCY IN ('USD', 'EUR', 'JPY', 'HKD') AND
SUBSTR(T.REFERENCE, 1, 3) NOT IN ('IAM', 'LDD', 'CAC') AND T.CRDR_AMT_IND
LIKE 'C%' AND I.ACCT_NO = A.ACCT_CODE AND I.RESIDENT IN ('10', '20') AND
SUBSTR(T.ACC_NUM, 1, 7) || SUBSTR(T.ACC_NUM, 9, 2) || SUBSTR(T.ACC_NUM, 12,
1) = A.ACCT_CODE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 10.11 38.18 185354 294845 6498 93583
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 10.11 38.18 185354 294845 6498 93583

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 39 (DATACORE) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=294845 pr=185354 pw=4909 time=0 us)
93583 VIEW VM_NWVW_1 (cr=293970 pr=185354 pw=4909 time=1240 us cost=40053 size=78 card=1)
93583 SORT UNIQUE (cr=293970 pr=185354 pw=4909 time=572 us cost=40053 size=163 card=1)
1867023 FILTER (cr=293970 pr=182575 pw=2130 time=21845 us)
1867023 HASH JOIN (cr=293970 pr=182575 pw=2130 time=9954 us cost=40052 size=163 card=1)
95875 NESTED LOOPS (cr=262162 pr=148650 pw=0 time=95732 us)
95875 NESTED LOOPS (cr=166287 pr=146178 pw=0 time=68195 us cost=32828 size=129 card=1)
19871 TABLE ACCESS FULL V_BRZ_IDMS_CASH_TRANS (cr=145366 pr=145343 pw=0 time=9846 us cost=32792 size=258 card=3)
95875 INDEX RANGE SCAN IDX_DM_ACCNTAB (cr=20921 pr=835 pw=0 time=5396 us cost=2 size=0 card=10)(object id 115155)
95875 TABLE ACCESS BY INDEX ROWID DM_ACCNTAB (cr=95875 pr=2472 pw=0 time=0 us cost=12 size=43 card=1)
101511 TABLE ACCESS FULL V_BRZ_IDMS_ACC_DTL (cr=31808 pr=31795 pw=0 time=6663 us cost=7222 size=3386536 card=99604)