Re: PB with performance request

On Tue, 30 May 2006 21:14:49 +0200, astalavista wrote:

> 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
With the year condition enabled, you get Cartesian merge join with the table DWACCPER with the plan like below:

           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

If that condition is not present, all remaining conditions can be resolved by using the primary key:

           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 

Which is a classical nested loops range scan, usually much faster then a Cartesian merge join. From what I see, the simple answer would be that the column is not indexed and the condition cannot be resolved by using index. If that is a DW table, create a bitmap index on DVACCPER.ACNTYY. If that is an OLTP table, you're SOL (Sadly Out of Luck) and you will have to re-write the query.

-- 
http://www.mgogala.com