Skip to Content
0
Former Member
Dec 02, 2011 at 09:24 AM

Oracle 11.2 slow with range scan

279 Views

L.S.,

I have an issue with performance with select.

The select only selects 40 objects via index. The table is generated by SAP, but in customer namespace.

With ST05 the explain shows:

SELECT

*

FROM

"ZCSNN_CAS"

WHERE

"MANDT" = :A0 AND "BUSOBJ_TYPE" = :A1 AND "BUSOBJ_ID" IN ( :A2 , :A3 , :A4 , :A5 , :A6 , :A7 ,

:A8 , :A9 , :A10 , :A11 , :A12 , :A13 , :A14 , :A15 , :A16 , :A17 , :A18 , :A19 , :A20 , :A21 ,

:A22 , :A23 , :A24 , :A25 , :A26 , :A27 , :A28 , :A29 , :A30 , :A31 , :A32 , :A33 ) AND

"BUSOBJ_VERSDATE" >= :A34 AND "STATUS_VERSION" = :A35 AND "STATUS_WORK" = :A36 AND

"FLG_CANCEL_VERS" = :A37 AND "FLG_CANCEL_OBJ" <> :A38

The execution plan is:

SELECT STATEMENT ( Estimated Costs = 68 , Estimated #Rows = 4 )

3 INLIST ITERATOR

2 TABLE ACCESS BY INDEX ROWID ZCSNN_CAS

( Estim. Costs = 67 , Estim. #Rows = 4 )

Estim. CPU-Costs = 735.327 Estim. IO-Costs = 67

Filter Predicates

1 INDEX RANGE SCAN ZCSNN_CAS~Z02

( Estim. Costs = 7 , Estim. #Rows = 316 )

Search Columns: 3

Estim. CPU-Costs = 213.909 Estim. IO-Costs = 7

Access Predicates Filter Predicates

Then then following fetches show long runtimes:

Runtime Object Operation Returncode

329 ZCSNN_CAS PREPARE 0

2 ZCSNN_CAS OPEN 0

150.678 ZCSNN_CAS FETCH 46 0

194.637 ZCSNN_CAS FETCH 46 0

157.639 ZCSNN_CAS FETCH 46 0

12.707 ZCSNN_CAS FETCH 46 0

90.340 ZCSNN_CAS FETCH 46 0

138.845 ZCSNN_CAS FETCH 46 0

49.715 ZCSNN_CAS FETCH 46 0

137.186 ZCSNN_CAS FETCH 46 0

204.770 ZCSNN_CAS FETCH 46 0

339.622 ZCSNN_CAS FETCH 46 0

173.157 ZCSNN_CAS FETCH 14 1403

To me it looks like an Oracle issue? Changes in the ABAP-code did not help.

Can Oracle be tweaked?

Regards,

Walter