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