2011 Feb 14 10:53 AM
In a standard SAP transaction, there is the statement INSERT BKPF. The SQL trace shows this statement can take a long time to execute.
The oracle SQL is
SQL Statement
----------------------------------------------------------------------------------------------------------------------
INSERT
INTO
"BKPF"
VALUES
(
:A0 , :A1 , :A2 , :A3 , :A4 , :A5 , :A6 , :A7 , :A8 , :A9 , :A10 , :A11 , :A12 , :A13 , :A14 ,
:A15 , :A16 , :A17 , :A18 , :A19 , :A20 , :A21 , :A22 , :A23 , :A24 , :A25 , :A26 , :A27 , :A28 ,
:A29 , :A30 , :A31 , :A32 , :A33 , :A34 , :A35 , :A36 , :A37 , :A38 , :A39 , :A40 , :A41 , :A42 ,
:A43 , :A44 , :A45 , :A46 , :A47 , :A48 , :A49 , :A50 , :A51 , :A52 , :A53 , :A54 , :A55 , :A56 ,
:A57 , :A58 , :A59 , :A60 , :A61 , :A62 , :A63 , :A64 , :A65 , :A66 , :A67 , :A68 , :A69 , :A70 ,
:A71 , :A72 , :A73 , :A74 , :A75 , :A76 , :A77 , :A78 , :A79 , :A80 , :A81 , :A82 , :A83 , :A84 ,
:A85 , :A86 , :A87 , :A88 , :A89 , :A90 , :A91 , :A92 , :A93 , :A94 , :A95 , :A96 , :A97 , :A98
)
Execution Plan
----------------------------------------------------------------------------------------------------------------------
System: xxx
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 369 | 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | BKPF | | | | |
---------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - INS$1
Could a badly defined index causing an INSERT performance problem? Obviously, adding an index increases the time taken to process an insert, but it couldn't cause actual performance problems, could i?
Edited by: Matt on Feb 14, 2011 11:54 AM
2011 Feb 14 2:11 PM
When was the last database re-org and do you have any idea of how many records were inserted since then?
Rob
2011 Feb 14 12:37 PM
Hi Matt,
if this is a single insert in a single transaction, additional indexes are not that costy.
Of course the index has to be maintained upon insert, and if it is a unique one,
additional checks need to be done. So this might cost a bit, but it should not be that bad.
If a table has many indexes, you should not rebuild them frequently all at the same time.
There are sometimes benfits for read in doing so, but for inserts, you will soon face the
need to do blocksplits for additional inserts and those are expensive, esp. if you have to do
some of them on more than one index for the same insert.
If you have many inserts in parallel, you might also face db-lock situations, either data related
(all databases) or db-specific. For Oracle that might be related to the INITRANS parameter of
either tables or indexes. Those can be identified by some ITL-contention wait event in the session monitor.
How many Indexes has your BKPF? 5-7 are not unusual for BKPF.
Volker
2011 Feb 14 1:01 PM
Hi Matt,
I can see your SQL statement, but where is the problem? Neither costs nor time are seeming to become a problem!
Is there any further information?
Regards,
Klaus
2011 Feb 14 1:43 PM
Hi Matt,
please post the ST05 summary data. How long does it take to insert one record?
We have to compare that time with your answer for Volker's questions (number of indexes, parallelism involved)
Kind regards,
Hermann
2011 Feb 14 2:11 PM
When was the last database re-org and do you have any idea of how many records were inserted since then?
Rob
2011 Feb 14 3:00 PM
It's a single insert. There are 6 indexes on the table. On one run it took 300'000 microseconds. The next time the statement executed, it to 30'000.
I should have explained further - sorry! The reason for posting the question is that we added an index, and testing has shown some transactions are slower than before. Certain groups are saying it's because of the indexes, and demanding they be removed.
However, the load on the machine is certainly not constant. My feeling is that the "after" testing results - of the order of 20s for a transaction rather than 15s - is more likely to be caused by normal variation within the instance - db contention, load, network etc.
I traced the access paths on BKPF for the transactions in question (VA01, VF01 and the like). and found that the new index wasn't used. I just wanted to be sure that there wasn't some esoteric scenario where adding an index would dramatically increase the runtime of an insert. This is quite sensitive as we did have trouble with index conflict on read, which took quite a long time to resolve.
If there's still resistance, I'll suggest a reorg.
Thanks
matt
2011 Feb 14 3:09 PM
2011 Feb 14 3:22 PM
2011 Feb 14 4:11 PM
You might try creating a batch input session using RFBIBL00 (or some other method) to create say 1,000 transactions and process it when system load is light. Then remove the index (making sure it is removed at the database level as well) and re-process the transactions.
If the run times are comparable, then the index is not the issue.
Rob