Diagnosis of the expensive ABAP programs having database time > 90%

[Former Member]

Hullo,

We have an issue with the Production server running very slow. On diagnosis through ST03N we found out that the database time in percentile for the total response time is 50%. Eg if response time for a task is 100 then 50 % of the time is spent in the database.

Details of my environment:

SAP ECC 6.0

AIX 5.3

Oracle 10g

Through further diagnosis ST03N we sorted out several Z* (custom) programs that have database time between 97% to 99%. Now my confusion is that can this be possible ? or am I getting wrong statistics. If these are correct statistics then how can all the other reponse times(wait time, cu time, roll time etc) be within 1% and database time is 99%.

Also how can I further troubleshoot this, because as I understand this could be an issue with expensive SQL statements / bad programming of the ABAP programs.

Experts please suggest !

Thank you,

Kind regards,

Zaheer Shaikh

[Former Member · Accepted solution]

Hi, are you update database statistc for oracle every day? Can you check the

ST04 --> Quality of Data Buffer and Shared Poll are fine ( > 95 %) ?

P.S. if this is from badly created abap programs, you need to correct them ...

Regards.

[Former Member · Accepted solution]

Hello,

Sergo:

Update statistics are run twice a week as the db size is 8 Tb.

The Data buffer quality is 91.2 % (The system was restarted 2 days back that means these statistics are collected after 2 days from restart of the system, as I understand the statistics get cleared after restart), and the Shared Pool = 98.1 %.

I feel these are good, so does that mean that the ABAP programs are badly written, if yes how can we trouble shoot this from a database perspective.

Manoj:

How can we check the abap program (sequential reads, proper joins, appropriate indexes). How can we conclude with proof that the slow time is caused by ABAP program.

Waiting for your response.

Thank you,

Kind regards,

Zaheer Shaikh

[Former Member]

Thank you all for your valuable suggestions. I will go ahead and assist in the evaluation of the expensive ABAP programs.

[fidel_vales]

> Through further diagnosis ST03N we sorted out several Z* (custom) programs that have database time between 97% to 99%. Now my confusion is that can this be possible ? or am I getting wrong statistics. If these are correct statistics then how can all the other reponse times(wait time, cu time, roll time etc) be within 1% and database time is 99%.

why not?

A developer creates a new program that retrieves 100% of the data in a table and then it filters at the ABAP side.

Or it only needs one row, but the conditions set by the user or developer are such that the complete table needs to be read to return only one row, or ....

that 99% of DB time probably indicates a wrong access to the data or too much data being returned.

> Also how can I further troubleshoot this, because as I understand this could be an issue with expensive SQL statements / bad programming of the ABAP programs.

You should trace the programs to see what they do (what sql statements are being issued) and then tune those statements.

[Former Member]

Hi Fidel,

Thank you for the response. So what you mean to say is that after I have performed the following pre checks for the db performance as a BASIS consultant:

IF

1)the database update statistics are running regularly (in my case twice a week)

2)& the data buffer quality and shared pool are above 90%

3)& there are no missing indexes

4)& database parameters are fine as per SAP standard note

THEN

I can conclude that the ABAP Z* programs listed in ST03N (top expensive programs) are badly written and I use tx SE30 to debug the programs along with the ABAPer.

Please let me know if I missed any points.

Thank you

[fidel_vales]

Hi Zaheer,

You can have all "generic" checks OK:

> 1)the database update statistics are running regularly (in my case twice a week)

> 2)& the data buffer quality and shared pool are above 90%

> 3)& there are no missing indexes

> 4)& database parameters are fine as per SAP standard note

but a program not run properly.

It could be caused as you mention by a

> badly written program

but also could be that the program is OK, but the developer neverthought that it would need an index.

Or the user input is such that Oracle is not using the correct access path

Or "a couple of million" other things ...

For that reason the programs should be analyzed, to find out why.

Instead of SE30 I'd use St12, as it gives you the ABAP and SQL traces.

I forgot, it also could be an oracle bug

Edited by: Fidel Vales on Mar 23, 2009 2:19 PM

[Former Member]

Hello Fidel,

Thank you for the answer. I would appreciate if you can clear one more nagging doubt I have about this issue. As mentioned earlier the database time is 97% of the total response time, the CPU time being minimal (0.7%).

But as I understand to run SQl statements CPU is utilized, so shouldnt there be more cpu consumtion if a lot of SQL statements are being run.

Please clarify my doubt.

Thank you,

Regards Zaheer

[fidel_vales]

Hi,

> But as I understand to run SQl statements CPU is utilized, so shouldnt there be more cpu consumtion if a lot of SQL statements are being run.

That would be easy.

The CPU utilization measured in ST03, is the one made by "SAP" not by the statement.

The CPU that you are talking about (CPU utilization by SQL statement) is included in the "db time" reported by ST03.

You have to go to the database level to see how all that "db time" is distributed.

For example, you can find that information (global, not for a statement) in ST04, time statistics.

Read in detail the following notes:

1) 805934: FAQ: Database time

2) 619188: FAQ: Wait events

3) 766349: FAQ: SQL Optimization

[Former Member]

Zaheer,

It looks like you need to get checked all the suspicious Z* programs by abaper. Also check if there are any unnecessary sequential reads, proper joins and appropriate use of index.

Hope this helps.

Manoj

[Former Member]

Hello,

Sergo:

Update statistics are run twice a week as the db size is 8 Tb.

The Data buffer quality is 91.2 % (The system was restarted 2 days back that means these statistics are collected after 2 days from restart of the system, as I understand the statistics get cleared after restart), and the Shared Pool = 98.1 %.

I feel these are good, so does that mean that the ABAP programs are badly written, if yes how can we trouble shoot this from a database perspective.

Manoj:

How can we check the abap program (sequential reads, proper joins, appropriate indexes). How can we conclude with proof that the slow time is caused by ABAP program.

Waiting for your response.

Thank you,

Kind regards,

Zaheer Shaikh