topic INSERT into BKPF - index causing a problem? in Application Development and Automation Discussions

INSERT into BKPF - index causing a problem?

matt — Mon, 14 Feb 2011 10:53:18 GMT

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

Re: INSERT into BKPF - index causing a problem?

volker_borowski2 — Mon, 14 Feb 2011 12:37:28 GMT

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

Re: INSERT into BKPF - index causing a problem?

Former Member — Mon, 14 Feb 2011 13:01:29 GMT

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

Re: INSERT into BKPF - index causing a problem?

HermannGahm — Mon, 14 Feb 2011 13:43:19 GMT

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

Re: INSERT into BKPF - index causing a problem?

Former Member — Mon, 14 Feb 2011 14:11:49 GMT

When was the last database re-org and do you have any idea of how many records were inserted since then?

Rob

Re: INSERT into BKPF - index causing a problem?

matt — Mon, 14 Feb 2011 15:00:38 GMT

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

Re: INSERT into BKPF - index causing a problem?

Former Member — Mon, 14 Feb 2011 15:09:45 GMT

Is this in the production or a test system?

Rob

Re: INSERT into BKPF - index causing a problem?

matt — Mon, 14 Feb 2011 15:22:33 GMT

Test

Re: INSERT into BKPF - index causing a problem?

Former Member — Mon, 14 Feb 2011 16:11:57 GMT

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