我们的系统以前是用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 是参数,现在就将就巴它当作常量。
我找了一个有代表性的,希望大家给我指点一下。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 是参数,现在就将就巴它当作常量。
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的计划
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 的计划
数据量没有改变。
而且我注意到
再8i Optimizer=CHOOSE
再10g Optimizer=ALL_ROWS
请大家帮我分析一下,最主要的影响效率的地方是哪里
谢谢