Question Re: Only SEQUENTIAL READ on all the table in Technology Q&A

Only SEQUENTIAL READ on all the table

FredericGirod — Mon, 03 Oct 2011 13:12:10 GMT

Hi,

I have a little problem, I see only "Sequential Read" in the SM50, the system is very slow ...

If I made a test between two server,

The KO server :

SQL Statement

SELECT

"VBELN" , "ERDAT" , "ERZET" , "VBTYP"

FROM

"VBAK"

WHERE

"MANDT" = :A0 AND "ERDAT" BETWEEN :A1 AND :A2 AND ( "VKBUR" = :A3 OR "VKBUR" = :A4 )

Execution Plan

System: FEP

Operation

Name

Rows

Bytes

Cost (%CPU)

0	SELECT STATEMENT		231	8778	23754 (2)
1	FILTER
2	TABLE ACCESS FULL	VBAK	231	8778	23753 (2)

Predicate Information (identified by operation id):

1 - filter(:A1<=:A2)

2 - filter("ERDAT">=:A1 AND "ERDAT"<=:A2 AND ("VKBUR"=:A3 OR

"VKBUR"=:A4) AND "MANDT"=:A0)

The OK server :

SQL Statement

SELECT

"VBELN" , "ERDAT" , "ERZET" , "VBTYP"

FROM

"VBAK"

WHERE

"MANDT" = :A0 AND "ERDAT" BETWEEN :A1 AND :A2 AND ( "VKBUR" = :A3 OR "VKBUR" = :A4 )

Execution Plan

System: FED

Operation

Name

Rows

Bytes

Cost (%CPU)

0	SELECT STATEMENT		106	4028	11225 (1)
1	FILTER
2	TABLE ACCESS BY INDEX ROWID	VBAK	106	4028	11224 (1)
3	INDEX RANGE SCAN	VBAK~AUD	655K		445 (2)

Predicate Information (identified by operation id):

1 - filter(:A1<=:A2)

2 - filter("ERDAT">=:A1 AND "ERDAT"<=:A2 AND ("VKBUR"=:A3 OR

"VKBUR"=:A4))

3 - access("MANDT"=:A0)

Re: Only SEQUENTIAL READ on all the table

Former Member — Mon, 03 Oct 2011 13:55:06 GMT

Hi,

It is obvious that optimizer is choosing the wrong path. On "System: FEP", optimizer is choosing "Full Access" whereas "System: FED" is reading data by using "VBAK~AUD" index.

Let's investigate the problem step by step. Firstly, check "VBAK~AUD" is exist on "FEP" on table VBAK. Then, check "check update and optimizer statistics" job on DB13.

Best regards,

Orkun Gedik

Re: Only SEQUENTIAL READ on all the table

stefan_koehler — Mon, 03 Oct 2011 14:27:42 GMT

Hello Frédéric,

well at first - "sequential reads" in SM50 is absolutely common and the norm.

For more information check the preview of the book "SAP Performance Optimization":

http://www.sap-press.de/download/dateien/259/sap_press_pog_007.pdf


A table access in an ABAP program is called a request. We differentiate between five different types of requests: Direct Reads, Sequential Reads, Inserts, Updates and Deletes. Direct Reads are SELECT SINGLE statements that have specified all the primary key fields in the WHERE clause with an EQUALS condition. All other select statements are known as sequential reads. Inserts, Updates and Deletes are referred to as Changes.
Changes.

So SAP itself does not differ between full table scans or index with table by rowid access in SM50.

Regarding to your performance issue - the provided information is not enough. The optimizer has "generated" a different execution plan - which is bad or good depends on your data.

If you want us to help you we need more information - please collect the needed information with the SQL_ID Data Collector script from sapnote #1257075 and provide the output. Anything else is just like looking in a crystal ball and not very serious.

Regards

Stefan

Re: Only SEQUENTIAL READ on all the table

FredericGirod — Tue, 04 Oct 2011 06:09:47 GMT

Hi,

the result of the script is very big I don't know how to put it here, do you need this part?

|PARAMETER_NAME IS_DEFAULT VALUE

|db_cache_size FALSE 3221225472

|db_file_multiblock_read_count TRUE 128

|event FALSE 10027 trace name context for|ever, level 1

|event FALSE 10028 trace name context for|ever, level 1

|event FALSE 10142 trace name context for|ever, level 1

|event FALSE 10183 trace name context for|ever, level 1

|event FALSE 10191 trace name context for|ever, level 1

|event FALSE 10411 trace name context for|ever, level 1

|event FALSE 10629 trace name context for|ever, level 32

|event FALSE 10891 trace name context for|ever, level 1

|event FALSE 14532 trace name context for|ever, level 1

|event FALSE 38068 trace name context for|ever, level 100

|event FALSE 38085 trace name context for|ever, level 1

|event FALSE 38087 trace name context for|ever, level 1

|event FALSE 44951 trace name context for|ever, level 1024

optimizer_dynamic_sampling FALSE 6

optimizer_features_enable TRUE 10.2.0.4

optimizer_index_caching FALSE 50

optimizer_index_cost_adj FALSE 20

optimizer_mode TRUE ALL_ROWS

optimizer_secure_view_merging TRUE TRUE

pga_aggregate_target FALSE 2147483648

sga_target TRUE 0

star_transformation_enabled FALSE TRUE

workarea_size_policy FALSE AUTO

fixcontrol FALSE 5705630:ON

fixcontrol FALSE 5765456:3

fixcontrol FALSE 6221403:ON

fixcontrol FALSE 6329318:ON

fixcontrol FALSE 6430500:ON

fixcontrol FALSE 6440977:ON

fixcontrol FALSE 6626018:ON

fixcontrol FALSE 6670551:ON

fixcontrol FALSE 6972291:ON

fixcontrol FALSE 7325597:ON

optimizermjc_enabled FALSE FALSE

optimpeek_user_binds FALSE FALSE

pushjoin_union_view FALSE FALSE

|_sort_elimination_cost_ratio FALSE 10 |

best regards

Fred

Re: Only SEQUENTIAL READ on all the table

FredericGirod — Tue, 04 Oct 2011 06:37:07 GMT

An important point, the system behaviour is not "normal". I give you an example, but, in fact, I have something than ~50 sequentials read in the same time.

I didn't see any program using an index.

The performance go down

The response time go down (4x, 5x, 10x long time) ...

I have some jobs, runing more thant 3600 sec instead of 100 ...

Re: Only SEQUENTIAL READ on all the table

Former Member — Tue, 04 Oct 2011 06:46:39 GMT

Hi,

Did you check DB13 and whether the index is exist on the system?

Best regards,

Orkun Gedik

Re: Only SEQUENTIAL READ on all the table

lbreddemann — Tue, 04 Oct 2011 06:49:51 GMT

Hello Frédéric,

it's good to know that the system behavior is not normal - that way we can be sure that there actually is a problem.

Stefans comments however remain correct!

You've to check how the database actually executes the longrunning statements and where the time is spent.

Looking at the read-mode status of the database interface ('single read' or 'sequential read') doesn't help a bit here.

Thus: please check the execution plans try to figure out why they are chosen by the optimizer over more efficient ones.

In most cases, making sure that ALL database parameter recommendations have been implemented is already the solution to such issues.

regards,

Lars

Re: Only SEQUENTIAL READ on all the table

FredericGirod — Tue, 04 Oct 2011 07:29:45 GMT

Yes Orgun, I have check the index exist (if not, my admin will die ).

In fact, it seems that the job of the DB statistic (DB20) doesn't run for ..... more than one month.

(now I will kill the Admin)

Re: Only SEQUENTIAL READ on all the table

Former Member — Tue, 04 Oct 2011 07:40:46 GMT

Hi Frédéric,

Keep him alive he is helping you to keep the systems up

What about the deviation percentage in DB20 for the table?

Best regards,

Orkun Gedik

Edited by: Orkun Gedik on Oct 4, 2011 10:42 AM

Re: Only SEQUENTIAL READ on all the table

FredericGirod — Tue, 04 Oct 2011 08:04:27 GMT

The deviation percentage is ~2%

I made some SQL trace on SE16 queries, and the performance go down each time ..

Objet RC Statement

13 VAPMA REOPEN 0 SELECT WHERE "MANDT" = '100' AND "MATNR" = '000000000000100697' AND ROWNUM <= 2000

1.501.034 VAPMA FETCH 104 0

1.020.228 VAPMA FETCH 104 0

862.231 VAPMA FETCH 104 0

915.610 VAPMA FETCH 104 0

796.848 VAPMA FETCH 104 0

620.512 VAPMA FETCH 104 0

751.528 VAPMA FETCH 104 0

691.738 VAPMA FETCH 104 0

517.323 VAPMA FETCH 104 0

693.317 VAPMA FETCH 104 0

722.447 VAPMA FETCH 104 0

550.333 VAPMA FETCH 104 0

...

Is there a way to test the memory usage of the DB ?

Re: Only SEQUENTIAL READ on all the table

stefan_koehler — Tue, 04 Oct 2011 10:29:03 GMT

Hello Frédéric,

well we still waiting for that script output.

If you can observe some performance issues for the whole SAP system - an AWR report (for more information check sapnote #853576) would be great.

You can zip all that files and upload them to a file hoster like rapidshare and post the link.

We need detailed information to help you. If you can not provide that values - open a SAP SR for this issue or get some performance consultant to help you.

Regards

Stefan

Re: Only SEQUENTIAL READ on all the table

FredericGirod — Tue, 04 Oct 2011 13:34:44 GMT

I have problems to put file in the web here, too much security

I send the sapnote #853576 to the Admin ..

Re: Only SEQUENTIAL READ on all the table

FredericGirod — Wed, 05 Oct 2011 07:01:16 GMT

Hi,

I have found a way to put file on the web.

So, the SQL_SQL_ID_DataCollector (it seems incomplete)

Another one, more complete FEP_DB_Overview_Script

Best regards

Frédéric

Re: Only SEQUENTIAL READ on all the table

stefan_koehler — Wed, 05 Oct 2011 08:32:20 GMT

Hello Frédéric,

yes the SQL_ID DataCollector is incomplete. It contains no information about the SQL. It seems like - you have done something wrong while executing that script.

Well i don't know where you get the output of "FEP_DB_Overview_Script", but it is not an AWR. We would need an AWR report of the previous 24 hours and one of the previous 2 hours to get the needed information.

Howto create an AWR report is described in the previous mentioned sapnote #853576.

Well i would really suggest to get some professional help if this performance issue is "critical", because of requesting all the needed information is pretty picky - open a SAP SR or get a performance consultant (check my homepage for example).

Regards

Stefan

Re: Only SEQUENTIAL READ on all the table

FredericGirod — Wed, 05 Oct 2011 09:00:59 GMT

There was more than one month the stat (DB20) didn't run ... today the system is "normal".

We asked an audit of our system.

thank you for your help

Re: Only SEQUENTIAL READ on all the table

Former Member — Sun, 06 Apr 2014 09:11:09 GMT

A "Sequential Read" refers to all other read database accesses, in which there may be more than one line returned. It is true that the term "Sequential read" is a little deceptive since it implies that blocks are always read sequentially (as in a full table scan). In reality, however, when you perform a trace on these queries you will see that index is used in most cases.

Re: Only SEQUENTIAL READ on all the table

Former Member — Sun, 06 Apr 2014 09:13:37 GMT

For expensive SQL statements analysis, "Reads/User Calls" value (TCODE BACOCKPIT or ST04OLD) is an indication. If this value is above 15, the SQL statements should be checked in detail. - See more at: