After system reorg the access to tabel WLK1 has slowed down tremendously

[Former Member]

Hi,

Abap here, looking for basis help!

SAP Basis 620

SAP_APPL 470

Our basis guys ran a system reorg on the production system which has affected the performance of a program. I have tracked this down to a particular table (WLK1) which seems to have indexing problems. Even when doing SE16 and getting table entries there is a performance issue.

On the QA system the table exists with approximately the same amount of entries, i.e. 31 million, and exactly the same indexes, as Production. Yet running SE16 in QA takes less than 2 minutes to return data, whereas the Prod system takes 20 minutes.

Any ideas or assistance is greatly appreciated.

Kind regards,

Warren.

[stefan_koehler · Accepted solution]

Hello Warren,

why does your basis guy not reorg the QA system at first to find performance impacts?

But ok.. if the sql statement is exactly the same in PR and QA, we need some more informations.

1) Which oracle database version (could be find over the system status)

2) We need the execution plan of the sql statement in PR and QA

3) Has your basis admin collect new table/index statistics after the reorg?

4) Does the table include LOB fields (maybe he made a LOB migration with the reorg)

But the important questions is the 1 - 3.

You can find the execution plan by running the SE16 and running the transaction ST04 parallel.

ST04 => Detail analysis menu => Oracle Sessions => Search your session and click the execution plan button => expand the statistic "menu" and check when the latest statistics were collected.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Thank you for the help.

The answer to your question 3 is 'yes'.

The Oracle database version is 9.2.0.6.0 (if I am looking in the right place!)

I am trying to get the execution plan from ST04 but system hangs when I click on 'Oracle Sessions'. When this comes back I will send the data through.

Not sure what you mean by LOB fields?

Regards,

Warren.

[Former Member · Accepted solution]

Not sure what you mean by LOB fields?

A lob is a data type for large objects (lots of text, images). SAP formerly used long datatypes as well. The IDOC table EDI40 has a lob column for example.

By the way, WLK1 does not contain a lob, so we can forget about that here.

Best regards, Michael

[Former Member]

you need to Reindex the indexes of the this table WLK1

find all the index on WLK1 table . try to Re-index index one by one

to find the all indexes related to this table

use query

select index_name from all_indexes where table_name = 'WLK1'