请教8i移植到10g查询速度变慢的问题

Question

我们的系统以前是用8i,现在升级到10g,可是有些查询现在变得很慢。
我找了一个有代表性的，希望大家给我指点一下。SELECT 
NVL(SUM(DR_INT_ADJ_AMT+DR_INT_ACCR_AMT+DR_INT_AMT),0),
NVL(SUM(PD_INT_ADJ_AMT+PD_INT_ACCR_AMT+PD_INT_AMT),0)
FROM (
SELECT 
DECODE(CTHST.DR_INT_AMT,NULL,0,CTHST.DR_INT_AMT) AS DR_INT_AMT,
0 AS DR_INT_ADJ_AMT, 0 AS DR_INT_ACCR_AMT,
DECODE(CTHST.PD_INT_AMT,NULL,0,CTHST.PD_INT_AMT) AS PD_INT_AMT,
0 AS PD_INT_ADJ_AMT,0 AS PD_INT_ACCR_AMT
FROM AC_FIN_TXN_JRNL FTJRN, AC_CTRCT_TXN_HIST CTHST
WHERE FTJRN.TXN_STATUS='A' 
AND FTJRN.TXN_DATE>=to_date('2004-01-01','yyyy-mm-dd')
AND FTJRN.TXN_DATE<to_date('2004-01-01','yyyy-mm-dd')
AND FTJRN.TXN_ORG_CODE=CTHST.TXN_ORG_CODE 
AND FTJRN.TXN_JRNL_NUM=CTHST.TXN_JRNL_NUM
AND CTHST.ORG_CODE='v_org_code' 
AND CTHST.CTRCT_REF_NBR='v_ctrct_ref_nbr' 
AND CTHST.CCY_CODE='v_ccy_code'
UNION ALL
SELECT 
0 AS DR_INT_AMT,
ROUND(DECODE(INHIS.DR_INT_ADJ_AMT,NULL,0,INHIS.DR_INT_ADJ_AMT),2) AS DR_INT_ADJ_AMT,
ROUND(DECODE(INHIS.DR_INT_ACCR_AMT,NULL,0,INHIS.DR_INT_ACCR_AMT),2) AS DR_INT_ACCR_AMT,
0 AS PD_INT_AMT,
ROUND(DECODE(INHIS.PD_INT_ADJ_AMT,NULL,0,INHIS.PD_INT_ADJ_AMT),2) AS PD_INT_ADJ_AMT,
ROUND(DECODE(INHIS.PD_INT_ACCR_AMT,NULL,0,INHIS.PD_INT_ACCR_AMT),2) AS PD_INT_ACCR_AMT
FROM AC_FIN_TXN_JRNL FTJRN, AC_INT_ACCR_TXN_HIST INHIS, AC_CTRCT_BAL BAL
WHERE FTJRN.TXN_STATUS='A' 
AND FTJRN.TXN_DATE>=to_date('2004-01-01','yyyy-mm-dd')
AND FTJRN.TXN_DATE<to_date('2004-01-01','yyyy-mm-dd')
AND FTJRN.TXN_ORG_CODE=INHIS.TXN_ORG_CODE 
AND FTJRN.TXN_JRNL_NUM=INHIS.TXN_JRNL_NUM
AND INHIS.ORG_CODE=BAL.ORG_CODE 
AND INHIS.PROD_ACCT_NUM=BAL.PROD_ACCT_NUM 
AND INHIS.CTRCT_REF_NBR=BAL.CTRCT_REF_NBR 
AND INHIS.CCY_CODE=BAL.CCY_CODE 
AND BAL.ORG_CODE='v_org_code' 
AND BAL.CTRCT_REF_NBR='v_ctrct_ref_nbr' 
AND BAL.CCY_CODE='v_ccy_code'
);
这个是查询语句，其中v 是参数，现在就将就巴它当作常量。

Answer 1

SELECT STATEMENT Optimizer=CHOOSE
  SORT (AGGREGATE)
    VIEW
      UNION-ALL
        NESTED LOOPS
          TABLE ACCESS (BY INDEX ROWID) OF AC_CTRCT_TXN_HIST
            INDEX (RANGE SCAN) OF AC_CTRCT_TXN_HIST_IDX2 (NON-UNIQUE)
          TABLE ACCESS (BY INDEX ROWID) OF AC_FIN_TXN_JRNL
            INDEX (UNIQUE SCAN) OF AC_FIN_TXN_JRNL_PK (UNIQUE)
        NESTED LOOPS
          NESTED LOOPS
            TABLE ACCESS (BY INDEX ROWID) OF AC_CTRCT_BAL
              INDEX (UNIQUE SCAN) OF AC_CTRCT_BAL_PK (UNIQUE)
            TABLE ACCESS (BY INDEX ROWID) OF AC_INT_ACCR_TXN_HIST
              INDEX (RANGE SCAN) OF AC_INT_ACCR_TXN_HIST_IDX1 (NON-UNIQUE)
          TABLE ACCESS (BY INDEX ROWID) OF AC_FIN_TXN_JRNL
            INDEX (UNIQUE SCAN) OF AC_FIN_TXN_JRNL_PK (UNIQUE)
这个是8i的计划

Answer 2

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11.5098715845193 Card=1 Bytes=78)
  SORT (AGGREGATE)
    VIEW (Cost=11.5098715845193 Card=2 Bytes=156)
      UNION-ALL
        FILTER
          TABLE ACCESS (BY INDEX ROWID) OF AC_FIN_TXN_JRNL (TABLE) (Cost=1.00087085199847 Card=1 Bytes=22)
            NESTED LOOPS (Cost=4.00330133054377 Card=1 Bytes=86)
              TABLE ACCESS (BY INDEX ROWID) OF AC_CTRCT_TXN_HIST (TABLE) (Cost=3.0024304785453 Card=1 Bytes=64)
                INDEX (RANGE SCAN) OF AC_CTRCT_TXN_HIST_IDX3 (INDEX) (Cost=2.00157783423217 Card=1)
              INDEX (RANGE SCAN) OF AC_FIN_TXN_JRNL_IDX1 (INDEX) (Cost=1.00087085199847 Card=1)
        FILTER
          TABLE ACCESS (BY INDEX ROWID) OF AC_FIN_TXN_JRNL (TABLE) (Cost=1.00087085199847 Card=1 Bytes=22)
            NESTED LOOPS (Cost=7.50657025397555 Card=1 Bytes=125)
              HASH JOIN (Cost=6.50569940197708 Card=1 Bytes=103)
                TABLE ACCESS (BY INDEX ROWID) OF AC_CTRCT_BAL (TABLE) (Cost=2.00248801497418 Card=1 Bytes=40)
                  INDEX (UNIQUE SCAN) OF AC_CTRCT_BAL_PK (INDEX (UNIQUE)) (Cost=1.00157783423217 Card=1)
                TABLE ACCESS (BY INDEX ROWID) OF AC_INT_ACCR_TXN_HIST (TABLE) (Cost=4.00318407537543 Card=1 Bytes=63)
                  INDEX (RANGE SCAN) OF AC_INT_ACCR_TXN_HIST_IDX1 (INDEX) (Cost=3.0023612918413 Card=1)
              INDEX (RANGE SCAN) OF AC_FIN_TXN_JRNL_IDX1 (INDEX) (Cost=1.00087085199847 Card=1)这个是10i 的计划

Answer 3

再8i上只要4-5分钟，再10ｇ上需要　１个多小时，
数据量没有改变。
而且我注意到
再８ｉ　 Optimizer=CHOOSE
再１０ｇ　Optimizer=ALL_ROWS
请大家帮我分析一下，最主要的影响效率的地方是哪里
谢谢