PB with performance request

Hi,

I have the following request ( 9.2.0.5 AIX 5.1 )

with
AND DVACCPER.ACNTYY = '2006'
cost = 518 and elapse 3 hours

with commented
-- AND DVACCPER.ACNTYY = '2006'
cost = 1781 and elapse 3 sec

Could you explain me why ?

Thanks in advance ....


Request and execution plans below:

SELECT
DXACCCV.VENDOR,
DXACCCV.TRADING_PARTNER,
sum(DVACC_VD.LOCAL_VALUE),
DXACCARE.BUSINESS_AREA,
DVACC_VD.FIMVT,
DVACCPER.PERMVT,
DVACC_VD.DOCUMENT_DATE
FROM
DXACCCV,
DVACC_VD,
DXACCARE,
DVACCPER,
DXACCGA
WHERE
( DVACCPER.PERMVT=DVACC_VD.PERMVT )
AND ( DVACC_VD.GL_ACCOUNT=DXACCGA.GL_ACCOUNT )
AND ( DVACC_VD.BUSINESS_AREA=DXACCARE.BUSINESS_AREA )
AND ( DVACC_VD.PAYER=DXACCCV.PAYER and DVACC_VD.VENDOR=DXACCCV.VENDOR and
DVACC_VD.CPYMVT=DXACCCV.COMPANY )
AND (
( DVACC_VD.CPYMVT='FR01' )
AND ( DVACC_VD.COMPUTE_VERSION
in('F','M') )
AND DVACCPER.PERMVT BETWEEN '200601' AND '200605'
AND DXACCGA.GL_ACCOUNT LIKE '00320%'
AND DXACCCV.VENDOR NOT LIKE 'FR01%'
AND DXACCCV.VENDOR LIKE 'FR%'
AND DVACC_VD.FIMVT NOT LIKE '01%'
--------------------------------------------------------------
AND DVACCPER.ACNTYY = '2006'
------------------------------------------------------------
)
GROUP BY
DXACCCV.VENDOR,
DXACCCV.TRADING_PARTNER,
DXACCARE.BUSINESS_AREA,
DVACC_VD.FIMVT,
DVACCPER.PERMVT,
DVACC_VD.DOCUMENT_DATE




3 HOURS
Operation Object Name Rows Bytes Cost
Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1
518
SORT GROUP BY 1 118 518
NESTED LOOPS 1 118 516
NESTED LOOPS 1 107 515,427239661009
MERGE JOIN CARTESIAN 1 39 62
MERGE JOIN CARTESIAN 1 34 61
TABLE ACCESS BY INDEX ROWID DVNFR.DVACCPER 1
12 2
INDEX RANGE SCAN DVNFR.DVACCPER_PK 1
1
BUFFER SORT 1 22 59
TABLE ACCESS FULL DVNFR.DXACCCV 1 22
59
BUFFER SORT 29 145 3
INDEX FULL SCAN DVNFR.DXACCARE_PK 29 145
1
PARTITION RANGE ITERATOR
KEY KEY
TABLE ACCESS BY LOCAL INDEX ROWID DVNFR.DVACCMVT 1
68 515,427239661009 KEY
KEY
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP CONVERSION FROM ROWIDS
INDEX RANGE SCAN DVNFR.DVACCMVT_I07 9 K
9 KEY KEY
BITMAP CONVERSION FROM ROWIDS
SORT ORDER BY
INDEX RANGE SCAN DVNFR.DVACCMVT_I03 9 K
41 KEY KEY
INDEX UNIQUE SCAN DVNFR.DXACCGA_PK 1 11



30 SECONDS
Operation Object Name Rows Bytes Cost
Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1
1781
SORT GROUP BY 1 113 1781
NESTED LOOPS 1 113 1779
NESTED LOOPS 1 91 1778
NESTED LOOPS 1 86 1778
NESTED LOOPS 1 75 1778
INDEX RANGE SCAN DVNFR.DVACCPER_PK 1 7
1
PARTITION RANGE ITERATOR
KEY KEY
TABLE ACCESS BY LOCAL INDEX ROWID DVNFR.DVACCMVT
1 68 1777 KEY
KEY
INDEX RANGE SCAN DVNFR.DVACCMVT_I03 9 K
33 KEY KEY
INDEX UNIQUE SCAN DVNFR.DXACCGA_PK 1 11
INDEX UNIQUE SCAN DVNFR.DXACCARE_PK 1 5
TABLE ACCESS BY INDEX ROWID DVNFR.DXACCCV 1 22
1
INDEX UNIQUE SCAN DVNFR.DXACCCV_PK 1





.

Relevant Pages