Re: PB with performance request

"astalavista" <nobody_at_nowhere.com> wrote in news:447c99a6$0$10274$626a54ce_at_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
>
>
>
>
>
>

>-- AND DVACCPER.ACNTYY = '2006'
I suspect implicit variable conversion which precludes use of index

Post the results from
SQL> DESC DVACCPER.ACNTYY Received on Tue May 30 2006 - 15:06:23 CDT