Skip to Content
author's profile photo Former Member
Former Member

Oracle 11.2 slow with range scan

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 05, 2011 at 02:57 PM

    Can Oracle buffering cause the issue below, afer some fetches the reponse jumps to 3 seconds??

    410 ZCSNN_CAS PREPARE 0 SELECT WHERE "MANDT" = :A0 AND "BUSOBJ_ID" IN ( :A1 , :A2 , :A3 , :A4 , :A5 )

    3 ZCSNN_CAS OPEN 0 SELECT WHERE "MANDT" = '400' AND "BUSOBJ_ID" IN (

    1.517 ZCSNN_CAS FETCH 102 1403

    3 ZCSNN_CAS REOPEN 0 SELECT WHERE "MANDT" = '400' AND "BUSOBJ_ID" IN

    2.206 ZCSNN_CAS FETCH 251 1403

    3 ZCSNN_CAS REOPEN 0 SELECT WHERE "MANDT" = '400' AND "BUSOBJ_ID" IN

    1.235 ZCSNN_CAS FETCH 123 1403

    5 ZCSNN_CAS REOPEN 0 SELECT WHERE "MANDT" = '400' AND "BUSOBJ_ID" IN

    977 ZCSNN_CAS FETCH 96 1403

    3 ZCSNN_CAS REOPEN 0 SELECT WHERE "MANDT" = '400' AND "BUSOBJ_ID" IN

    841 ZCSNN_CAS FETCH 83 1403

    2 ZCSNN_CAS REOPEN 0 SELECT WHERE "MANDT" = '400' AND "BUSOBJ_ID" IN

    1.055 ZCSNN_CAS FETCH 112 1403

    2 ZCSNN_CAS REOPEN 0 SELECT WHERE "MANDT" = '400' AND "BUSOBJ_ID" IN

    593 ZCSNN_CAS FETCH 45 1403

    3 ZCSNN_CAS REOPEN 0 SELECT WHERE "MANDT" = '400' AND "BUSOBJ_ID" IN

    3.303.147 ZCSNN_CAS FETCH 197 1403

    7 ZCSNN_CAS REOPEN 0 SELECT WHERE "MANDT" = '400' AND "BUSOBJ_ID" IN

    2.119.204 ZCSNN_CAS FETCH 86 1403

    5 ZCSNN_CAS REOPEN 0 SELECT WHERE "MANDT" = '400' AND "BUSOBJ_ID" IN

    2.734.447 ZCSNN_CAS FETCH 122 1403

    6 ZCSNN_CAS REOPEN 0 SELECT WHERE "MANDT" = '400' AND "BUSOBJ_ID" IN

    1.510.695 ZCSNN_CAS FETCH 73 1403

    Add a comment
    10|10000 characters needed characters exceeded

    • Active session history shows use of index only from time to time.
      > This does not sound logical.
      > 
      > Time waited (ms)        Obj.nr    Object name            File number
      > 14.290	              160.024	ZCSNN_CAS 	83
      > 52.692	              160.024	ZCSNN_CAS	                    104
      > 470.264	              160.024	ZCSNN_CAS                    115
      > 430.084             160.024	ZCSNN_CAS	                    110
      > 25.135	              160.024	ZCSNN_CAS	                    101
      > 240.831	              160.024	ZCSNN_CAS	                     98
      > 42.823	              160.024	ZCSNN_CAS	                     90
      > 89.932	              160.024	ZCSNN_CAS	                    108
      > 107.571	              160.024	ZCSNN_CAS	                    111
      > 29.924	              160.024	ZCSNN_CAS	                     85
      > 415.502	              160.024	ZCSNN_CAS	                    107
      > 42.316	              160.024	ZCSNN_CAS	                    104
      > 38.820	              200.340	ZCSNN_CAS*~Z02*         101

      >

      > Edited by: W. van Veen on Dec 6, 2011 10:01 PM

      Hi,

      any chance that datafiles 107,110,115 are on slower Disks?

      May be you have gotten SATA instead of SAS LUNS from your storage guys ?

      Volker

  • Posted on Dec 02, 2011 at 09:30 AM

    Hello Walter,

    could be an oracle (optimizer) issue, could be a design issue, could be anything else.

    You just provided to less information. Please run the SQL Data Collector script from sapnote #1257075 and post the output.

    The best way is to copy that output into a file, upload it to some webshare and post the link here.

    Regards

    Stefan

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 02, 2011 at 03:53 PM

    Hi,

    difficult to say something with this data.

    Essential: What fields are in both indexes ?

    Essential: Any of the fields involved of datatype RAW (like a guid)

    Important:

    Blevel Value of both indexes?

    How are the distinct values on the related columns?

    How many records in the table according to stats?

    How many records in the table according SELCET COUNT(*) ?

    That would be just for starters...

    Best info: click on the table in the ST05 explain, and

    you'll get a window with lots of stats. Press "Index stats" at the bottom.

    Do a rightclick and "save as" to clipboard and post the result here using code tags.

    Volker

    forgot: in your FAE: How many Ids are blocked together ?

    Edited by: Volker Borowski on Dec 2, 2011 4:55 PM

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 05, 2011 at 09:23 AM

    First of all, thanks for all the responses!

    Below the requested data for starters!

    -


    Table ZCSNN_ACT

    -


    Last statistics date 05.12.2011

    Analyze Method Sample 344.222 Rows

    Number of rows 34.422.200

    Number of blocks allocated 416.900

    Number of empty blocks 0

    Average space 0

    Chain count 0

    Average row length 82

    Partitioned NO

    -


    -


    UNIQUE Index ZCSNN_ACT~0

    -


    Column Name

    #Distinct

    -


    MANDT

    1

    IMPORT_YEAR

    9

    CASE_ID

    2.459.014

    CASE_VERS

    3

    ACT_POS

    30

    -


    Last statistics date 05.12.2011

    Analyze Method Sample 362.683 Rows

    Levels of B-Tree 3

    Number of leaf blocks 294.140

    Number of distinct keys 36.268.300

    Average leaf blocks per key 1

    Average data blocks per key 1

    Clustering factor 8.322.500

    -


    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Ah, ok

      orasid> sqlplus sapsr3
      SQL>set autotrace on
      SQL> SELECT * FROM ZCSNN_ACT WHERE mandt  = <mandt> AND import_year = <year>
      AND case_id = <case_id> AND case_vers = <case_vers>
      

      sapsr3 -> SAP schema user, could also be sapr3, sapsid in your case

      substitute <year>, <case_id>, <case_vers> with real values in single quotes like '100' for mandt 100

      This will be fast, i was just noticing you have a FOR ALL ENTRIES in your example, but lets start with this example.

      Cheers Michael

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.