on 01-26-2011 8:49 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
> 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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
71 | |
26 | |
10 | |
9 | |
7 | |
6 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.