long access CE4XXXX in massive selection

[nicola_blasi]

Good morning

We have a problem in a SAP 46C productive during the access to table

CE4TLCI (CE4XXXX) in program

RK2ATLCI_POST (RK2Axxxx_POST). We have a massive selection of the same

table ending in TIME_OUT.

I've readen some notes about this long access , above all 551750 and

35288. These notes

describes the correct index to define obtaining a better access on

table .

Anyway the access seems good using the secondary index that i show you:

I think the select is the problem but i don't know how to increase the

speed...also the buffer gets is very high.

SELECT

/*+

FIRST_ROWS(5)

*/

FROM

"CE4TLCI"

WHERE

"MANDT" = :A0 AND "AKTBO" = :A1 AND "KNDNR" = :A2 AND "ARTNR" = :A3 AND "FKART" = :A4 AND "KAUFN"

= :A5 AND "KDPOS" = :A6 AND "AUFNR" = :A7 AND "BUKRS" = :A8 AND "KOKRS" = :A9 AND "WERKS" = :A10

AND "GSBER" = :A11 AND "VKORG" = :A12 AND "VTWEG" = :A13 AND "SPART" = :A14 AND "PRCTR" = :A15

AND "PPRCTR" = :A16 AND "KSTRG" = :A17 AND "PSPNR" = :A18 AND "MEINH" = :A19 AND "KDGRP" = :A20

AND "COPA_KOSTL" = :A21 AND "BRSCH" = :A22 AND "WWASA" = :A23 AND "WWSPS" = :A24 AND "GEBIE" =

:A25 AND "WWBE" = :A26 AND "WWDTC" = :A27 AND "VKBUR" = :A28 AND "WWVC1" = :A29 AND "WWVC2" =

:A30 AND "WWVC3" = :A31 AND "WWVC4" = :A32 AND "WWVC5" = :A33 AND "WWVC6" = :A34 AND "WWVC7" =

:A35 AND "WWVC8" = :A36 AND "WWVC9" = :A37 AND "WWVDC" = :A38 AND "VV005_ME" = :A39 AND

"VV006_ME" = :A40 AND "VV007_ME" = :A41 AND "VV008_ME" = :A42 AND "VV009_ME" = :A43 AND

"ABSMG_ME" = :A44 AND "VV001_ME" = :A45 AND "VV002_ME" = :A46 AND "VVCON_ME" = :A47 AND

"VV03A_ME" = :A48 AND "VVIMP_ME" = :A49 AND "VVQNT_ME" = :A50 AND "VVQSD_ME" = :A51 AND

"VVDCD_ME" = :A52 AND "VVTES_ME" = :A53 AND ROWNUM <= :A54#

Execution Plan

SELECT STATEMENT ( Estimated Costs = 2 , Estimated #Rows = 1 )

5 COUNT STOPKEY

5 TABLE ACCESS BY INDEX ROWID CE4TLCI

INDEX RANGE SCAN CE4TLCI~1

The index CE4TLCI~1 is so structured.....

MANDT 1

AKTBO 1

KNDNR 5.442

ARTNR 6.922

BUKRS 5

WERKS 78

VTWEG 22

Last statistics date 02.04.2009

Analyze Method Sample 214.681 Rows

Levels of B-Tree 3

Number of leaf blocks 81.689

Number of distinct keys 764.356

Average leaf blocks per key 1

Average data blocks per key 3

Clustering factor 2.347.350

How can i try to getting better performance to this select?

Thanks in advance

Nicola

[fidel_vales]

in addition:

How big is the table?

what is your oracle version (10g, but 10202 or 10204)?

have you installed all recommended patches?

what optimizer merge fix patch is installed?

The index is goodish, it has 7 fields out of 54.

the problem (probably) is that the fields do not filter many rows and a lot of accesses have to be done to retrieve the 5 rows you are looking for.

This is difficult to say without knowing

• the amount of rows in your table

2 million?

20 million?

• the selectivity of the index fields

[lbreddemann]

Hey there,

you may want to use the script of note

1257075 SQL_ID Data Collector (Oracle >= 10.2.0.2)

to find out more about the runtime of this statement.

Also:

Make sure the DB parameters are set as recommended - be aware: a high buffer cache hit ratio does not mean a good response time at all.

regards,

Lars