cancel
Showing results for 
Search instead for 
Did you mean: 

Performance problem after data archiving and reorg

former_member674271
Contributor
0 Kudos

Dear SAP gurus,

We have a customized report which reads tables LIKP and LIPS. After we archived these two tables and reorg, the report takes execution time over 10 times as that before archiving (about 5,000 seconds before while 60,000 seconds after). but nothing changed to the report before or after archiving.

Our basis gave us some source code extraction as below which takes long execution time as below,

SELECT

/*+

FIRST_ROWS

*/

T_00 . "VBELN"

FROM

"LIKP" T_00 , "LIPS" T_01

WHERE

( T_01 . "MANDT" = :A0 AND T_01 . "VBELN" = T_00 . "VBELN" ) AND T_00 . "MANDT" = :A1 AND T_00 .

"ERDAT" BETWEEN :A2 AND :A3 AND T_01 . "MATNR" = :A4 AND ROWNUM <= :A5#

To modify the statement could improve some performance but anyway no source code change before or after archiving and reorg. I'm a functional consultant and from my perspective, data archiving and reorg should improve performance but do not know why this case is not applicable.

Thanks and best regards

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

What exactly did happen during reorg?

Make sure indexes have been rebuilt, and statistics have been updated.

For older releases of SAP and Oracle you may have to do so manually.

Answers (1)

Answers (1)

0 Kudos

> data archiving and reorg should improve performance

why?

if that query is the one "behaving badly" then you can use the AWR in order to find out what was oracle doing before and what it is doing now.

If it is doing something different (using another index now) you should analyze why uses it (smaller index/different statisitcs...)

If it is doing the "same", it may be as easy as a different sorting of the table that causes more accesses to it from an index (increased clustering factor of the "good" index.

If you have a "sort" AWR retention (default 7 days, SAP recommendation 42) then you have to find this information quickly. otherwise you will never "know" for sure and only guesses can be done.

You may open a message on the component BC-DB-ORA or SV-BO-DB-ORA, support consultants could connect to your system and analyze the query.

former_member674271
Contributor
0 Kudos

Thank you for your helpful information. I will create a message as your suggestion.

Regards