Performance Problem with several Database accesses

[Former Member]

Hello

We have SAP ERP 2004 running on a Oracle DB 9.x and we face a strange behavior of our system. Sometimes it is very slow. MB51 with 1 Material and 1 Month as selection runs into timeout. But i faced the most significant (or strange) problem using SE16 with table bseg. If i use Company code, one item number and year as a selection, it is very fast. If i put in 2 items numbers instead of one, it takes 15 minutes to show the result. This happens everytime i try it.

For me it looks like the system is doing a sequential read instead of using the proper index. This "wrong index" behavior could be the source of many of our performance problems.

Does anybody have a suggestion for me, how to fix this problem?

Thanks in advance

Hans-Peter

[Former Member]

It looks like you suffered from the famous "3rd Column Blues" as described in SAP Note 176754 (1). With Oracle >= 10g and / or CBO statistics on RFBLG you will not suffer from this problem any more.

[Former Member]

Hello Hans-Peter,

also check if you have up to date optimizer-statistics. You can update statistics of a single table with transaction DB20 or check and update for all tables via DB13.

Greetings

Christian

[Former Member]

Hello Christian

Thanks. After building the statistik data for the table behind bseg (RFBLG) with DB20 the se16 access to bseg was very fast.

In DB21 i can see, that RFBLG is set to P3 (we changed that to P10 yesterday).

But instead of building the statistic.-data for this table, the log of BRCONNECT says that the job deleted all the statistikdata for this table.

Performance is back to very bad again.

I do not fully understand the use of the table behind DB21. What does the system do with a table listed in DB21 with

- Usage Type A

- Method C

Does it build the optimizer statistics or not?

Thanks again

Regards

Hans-Peter

[Former Member]

Hello Hans-Peter,

in our system (ERP 6.0, Oracle 10.2.0.2) the settings for RFBLG are:

- Usage Type A

- Method E

- Sample Size P3

This seems to be the SAP standard. I also only know whats in the help for 'Usage Type' and 'Method', but most tables have Usage Type A and with that we do not hava any problems, means optimizer statistics are created.

Method C analysis the whole table, what has sure performance impacts and is because of that not used for RFBLG which has mostly millions of records.

Greetings

Christian

[stefan_koehler]

Hello Hans-Peter,

> I do not fully understand the use of the table behind DB21. What does the system do with a table listed in DB21 with

All this stuff is explained in the official documentation (http://help.sap.com/saphelp_nw04/helpdata/en/a0/7d1020dac8994fa63ff270dbceb87f/content.htm) and in sapnote #106047

Regards

Stefan

[Former Member]

> We have SAP ERP 2004 running on a Oracle DB 9.x and we face a strange behavior of our system. Sometimes it is very slow. MB51 with 1 Material and 1 Month as selection runs into timeout. But i faced the most significant (or strange) problem using SE16 with table bseg. If i use Company code, one item number and year as a selection, it is very fast. If i put in 2 items numbers instead of one, it takes 15 minutes to show the result. This happens everytime i try it.

>

> For me it looks like the system is doing a sequential read instead of using the proper index. This "wrong index" behavior could be the source of many of our performance problems.

As mentioned above you can active trace with ST05 to monitor the problematic SQL request. With the explain function, you will see the estimated cost of the request and the used index.

An other way is during the execution of the request --> ST04 -> performance -> Wait event analysis -> Session monitor

If you use the IN option, your request could be very expensive. And in many cases, the reads can only performed with sequential access.

For an overlook of possible indexes for a table, you can use the DB05. To interpret the results : an index is representative when you have numerous distinct values for a few 'rows per generic keys'

Hope this will help you

[stefan_koehler]

Hello Hans-Peter,

> MB51 with 1 Material and 1 Month as selection runs into timeout

Ok, in this case i would run a SQL Trace with transaction ST05.

With the help of this trace you can identify the statement which is causing the long runtime.

> If i put in 2 items numbers instead of one, it takes 15 minutes to show the result. This happens everytime i try it.

So it sems like the IN List (or OR concatenation) is forcing a different execution plan. You could also check this with the SQL trace.

> Does anybody have a suggestion for me, how to fix this problem?

Unfortunately you are on Oracle 9i - so you can not use the script of sapnote #1257075 to provide us detailed information about your problematic SQL statement. There is also sapnote #1438410 which contains scripts for such cases, but i never used them until yet.

My suggestion is to open a SAP Service Request for this issues (maybe you also hit an optimizer bug), because of if you don't know how to analyze such a problem, you will not be able to solve it. Unfortunately we also can't help you very quickly here on SDN, because of the missing information about statistics, etc.

Regards

Stefan