cancel
Showing results for 
Search instead for 
Did you mean: 

Inquiry about execution times at DB level and ABAP

Former Member
0 Kudos

Hello,

Replenishment of stocks in our business centers require checking if an RO of similar request is already in the database. Thus, business operation is being disrupted. The checking for existing RO and the actual RO creation is implemented via a custom BAPI. This BAPI queries table RESB. Before the upgrade, it used to run in seconds (max 24 seconds). After the upgrade, it reached a high of 13 minutes.

The standard report for Reservation List (MB25) used to execute in seconds only but after the our patch upgrade ECC 6.0 patch level 17 and database upgrade (from Oracle 10.2.0.2 to 10.2.0.4) it becomes 2 minutes.

Part of our upgrade activity was the gathering of stats for the database tables using BR tools.

We've activated and adjusted the RESB indices in SE14 to try to address this issue. We've also tried checking the execution plan in ST05 and it shows that the indices are being used.

We've tried running the same select statement used in the custom BAPI directly at the database level and it executes in milliseconds (as expected). However when queried through ABAP, the runtime reaches minutes (~5 mins).

We were wondering what could be the cause of the disparity between the execution times at DB level and ABAP, and if maybe we missed some important steps during the upgrade that may have led to the bloated runtimes.

Any thoughts on this?

Thank you!

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Some things to check regarding the disparity:

1) How many excutions of the SQL statement are getting executed (SAP will sometimes execute the same SQL many times). When you execute at the DB level (via sqlplus) you're only executing it once...just double check this. i.e. FOR ALL ENTRIES statments or just the ABAP logic...

2) If things look good with the number of executions then confirm that the exact same plans are actually being executed. 1 thing to check here is that SAP is using Bind Variables (i.e. make sure your script uses Bind Variables and that you are using the exact same data values that SAP is sending the DB). You can also verify SQL execution statistics via Oracle's v$sql table...just another data point to look at

3) You many want to perform a 10046 SQL trace (of the offending SQL) to dig into where the waits are actually occuring and how long they are taking.

4) In case the issue is not actually at the DB level...i.e. CPU, I/O, or Network...you'll need to broaden your investigative scope.

...

Former Member
0 Kudos

Weird...

Could you please post the statement and the execution plan? The size and number of rows of the table would be nice to know too. Are database and SAP instance located on the same server or not?

Some things that might help:

- try to double check in ST03 or STAD, if the spent time is really lost in abap and is not database time

- double check the execution plans from SAP and when you run the statement on db level, do not focus only on execution time but on db block gets (use ST04 -> SQL Statement Analysis -> Shared Cursor Cache), in sqlplus you can also use "set autotrace on"

- you could also try to run the statement from ST04 -> Additional Functions -> SQL Command Editor, just to check how it runs there

Another reason could be a huge inlist resulting in a lot of smaller statements.

Cheers Michael