PB with performance request

From: astalavista <nobody_at_nowhere.com>
Date: Tue, 30 May 2006 21:14:49 +0200
Message-ID: <447c99a6$0$10274$626a54ce@news.free.fr>

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

Received on Tue May 30 2006 - 14:14:49 CDT