Oracle 11.2 slow with range scan

[Former Member]

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

[Former Member]

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

[former_member188883]

Hi,

Oracle database buffer could be one of the reasons.

Whats the db_cache_size value ?

Regards,

Deepak Kori

[Former Member]

I am not a basis consultant, but this is hat I can find.

db_16k_cache_size 0

db_2k_cache_size 0

db_32k_cache_size 0

db_4k_cache_size 0

db_8k_cache_size 0

db_cache_size 3221225472

[former_member188883]

Hi,

Can you look at increasing these parameters in initSID.ora file

db_cache_size => make it 40% of your available RAM

pga_aggregate_target => make it 20% of your available RAM

Check the results and update us.

Regards,

Deepak Kori

[volker_borowski2]

Can Oracle buffering cause the issue below, afer some fetches the reponse jumps to 3 seconds??
> 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

Hi,

now this was the statement, that used index Z02 for which you did not provide info.

Your first trace had an inlist with 30 variables, the second one with 5.

So did you change any of the FAE parameters concerning Blocking ?

Nevertheless: 3 seconds for a fetch of 70-130 rows is way too much, given the fact, the an index must be used,

otherwise, you would never get the first guys so quick, even if they are in the cache.

3 GB cache size should be way enough for a repeated check of the same satetement.

So if the execution is rabbit like in the second&thrid&fourth execution, you could assume caching issues here,

but I really doubt that. If that 3 sec call is a trace of execution 2 or three, you surely have a different issue.

When using autotrace, do

set autotrace on linesize 160 pagesize 200

which will cause less formatting issues.

Can you put in the stats for index Z02 as well?

Index Zero looks ok too me:

stats up to date, Blevel 3 for 30+ mio rows is ok, 294k leafblock is not very big, clustering of 8 mio is

not all too bad as well, and case_id is highly selective, so the 500 usec for 15 records in the first example are perfectly ok.

May be the number ob IDs you have in the internal tables is way too big.

In that case, the coding should be changed, the FAE replaced by a DB join, so the DB could do a hash or a merge join,

which is usually better, when a high number of records has to be processed.

Volker

[Former Member]

Thanks for all the responses so far.

Normally the select for ZCSNN_CAS contains around 40 unique objects.

The result set can contain around a 1000 records.

As the select only contains around 40 unique objects, I did not use blocks.

Would using blocks speed up the performance?

[Former Member]

Statistics of index Z02

-

---

NONUNIQUE Index ZCSNN_CAS~Z02

-

---

Column Name

#Distinct

-

---

MANDT	1
BUSOBJ_ID	374.065
FLG_CANCEL_VERS	2
FLG_CANCEL_OBJ	2
TRIGGER_SYS	4

-

---

Last statistics date 05.12.2011

Analyze Method mple 14.843.651 Rows

Levels of B-Tree 3

Number of leaf blocks 122.376

Number of distinct keys 1.938.307

Average leaf blocks per key 1

Average data blocks per key 7

Clustering factor 14.173.648

-

---

[stefan_koehler]

Hello Walter,

why posting so many information chunks instead of just running the SQL Data Collector script from sapnote #1257075 which provides all the needed information for each SQL.

Regards

Stefan

[Former Member]

I would like to show everything at once. But I do not have access to SQLplus, and big companies move slow.

Getting data via SAP transactions is possible on short term.

[stefan_koehler]

Hello Walter,

if you read sapnote #1257075 carefully - you would have noticed that you don't need SQL*Plus access for this. The script can also be executed in transaction DBACOCKPIT.

Regards

Stefan

[volker_borowski2]

Hi,

index Z02 does not look all too bad either.

Could be benefitial to include the BUSOBJ_TYPE if that narrows down a bit.

7 datablocks per key is not so brilliant, and the clustering is awful, but you can not always avoid that.

The different fetch times up to 3 secs you showed before indicate a completely different issue.

What you can try to do in techical terms:

Reorganize the table with a sort after index Z02 and compress the table in addition.

This should give you a much better clustering for index Z02, which seems to have an

average of 40 records per BUSOBJ_ID. So this should do a bit better.

Clustering on the primary key will suffer, so you might get trouble elsewhere.

Nevertheless, I'd not expect this to be helpfull to avoid your 3 sec fetches.

This might be someting else, probably lock or concurrency related.

Volker

[Former Member]

I have tried the note, but with the normal Datacollector, I am getting a dump. The SQL-statement is bigger 65000.

And with the Fallback script, I am getting an error from the parser

[Former Member]

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

[volker_borowski2]

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

[Former Member]

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

-

---

[Former Member]

The where clause from your first post contains these columns:

MANDT, BUSOBJ_TYPE, BUSOBJ_ID (inlist 33 values), BUSOBJ_VERSDATE, STATUS_VERSION, STATUS_WORK, FLG_CANCEL_VERS, FLG_CANCEL_OBJ

Primary index has these columns:

MANDT, IMPORT_YEAR, CASE_ID (this is selective), CASE_VERS, ACT_POS

Thus so far we don't see a suitable index, the table is huge resulting in obvious bad fetch times. Depending on your application you have to change the WHERE clause to hit the index or create a new index.

Cheers Michael

Edit: ah, i see you were probable referring to this WHERE clause

 WHERE import_year = i_case-import_year AND case_id = i_case-case_id AND case_vers = i_case-case_vers 

This should be running much better. I recommend to double check that execution plan (the explain could show the wrong plan). I would really like to know how much consistent gets the statement has. Could you run the statement in SQLplus with autotrace on?

SQL> set autotrace on

[Former Member]

I am not familiar with SQL-plus.

What does the syntax in SQL-plus look like?

[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

[volker_borowski2]

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

[stefan_koehler]

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

[Former Member]

Before getting details from Oracle (installation of note will take some time), just one other example:

Select:

SELECT * FROM ZCSNN_act INTO TABLE e_activity

FOR ALL ENTRIES IN i_case

WHERE import_year = i_case-import_year AND

case_id = i_case-case_id AND

case_vers = i_case-case_vers.

Explain:

SELECT STATEMENT ( Estimated Costs = 3 , Estimated #Rows = 5 )

3 INLIST ITERATOR

2 TABLE ACCESS BY INDEX ROWID ZCSNN_ACT

( Estim. Costs = 2 , Estim. #Rows = 5 )

Estim. CPU-Costs = 31.964 Estim. IO-Costs = 2

1 INDEX RANGE SCAN ZCSNN_ACT~0

( Estim. Costs = 2 , Estim. #Rows = 5 )

Search Columns: 4

Estim. CPU-Costs = 28.686 Estim. IO-Costs = 2

Access Predicates

Results in trace:

7 ZCSNN_ACT REOPEN 0

299.091 ZCSNN_ACT FETCH 15 1403

7 ZCSNN_ACT REOPEN 0

466.488 ZCSNN_ACT FETCH 15 1403

7 ZCSNN_ACT REOPEN 0

430.965 ZCSNN_ACT FETCH 15 1403

5 ZCSNN_ACT REOPEN 0

414.701 ZCSNN_ACT FETCH 15 1403

7 ZCSNN_ACT REOPEN 0

488.169 ZCSNN_ACT FETCH 15 1403

8 ZCSNN_ACT REOPEN 0

293.375 ZCSNN_ACT FETCH 15 1403

8 ZCSNN_ACT REOPEN 0

436.194 ZCSNN_ACT FETCH 15 1403

7 ZCSNN_ACT REOPEN 0

655.620 ZCSNN_ACT FETCH 15 1403

8 ZCSNN_ACT REOPEN 0

279.897 ZCSNN_ACT FETCH 11 1403

4 ZCSNN_ACT REOPEN 0

[former_member188883]

Hi,

Ensure that your database is tuned as per SAP's recommendation . refer SAP note 1431798 for oracle 11g.

Also ensure that your database statistics are upto date.

Hope this helps.

Regards,

Deepak Kori