Wait events tuning

[#1]

Hello SAP Community,

I start by mentioning a few details about the system I'll be talking about in this subject:

- SAP NetWeaver 7.0

- Oracle Database 10.2g

I was reading the following Note: "Note 618868 - FAQ: Oracle performance", in order to try to understand what's causing the oracle database to have slow performance.

While reading section 3 "How can I determine whether the general database performance can be optimized?" I found out that the ratio of "Busy wait time to CPU time" is away above the recommended 60:40 value. I'm getting a 94:6 ratio. This value was calculated using the query:

SELECT

ROUND((STM1.VALUE - STM2.VALUE) / 1000000) "BUSY WAIT TIME (S)",

ROUND(STM2.VALUE / 1000000) "CPU TIME (S)",

ROUND((STM1.VALUE - STM2.VALUE) / STM1.VALUE * 100) || ' : ' ||

ROUND(STM2.VALUE / STM1.VALUE * 100) RATIO

FROM V$SYS_TIME_MODEL STM1, V$SYS_TIME_MODEL STM2

WHERE STM1.STAT_NAME = 'DB time' AND STM2.STAT_NAME = 'DB CPU';

With such high values, SAP recommends to improve system performance doing some "wait event tuning".

Can someone give me some directions about this subject? Some guides specific to this subject would be nice. Any further information about my system you may require, please ask me.

Thanks in advance.

Best regards,

Daniel Garrido

[fidel_vales]

Hello,

>

> With such high values, SAP recommends to improve system performance doing some "wait event tuning".
> 
> Can someone give me some directions about this subject? Some guides specific to this subject would be nice. Any further information about my system you may require, please ask me.

Correct, the ratio indicates that oracle, when it is "working" is spending most of the time "waiting" for needed things to do that work.

You should read the note

619188 FAQ: Oracle wait events

There you can find a "definition" and typical wait events with the possible solutions.

The analysis will help you to find, possible I/O bottlenecks, or query tuning or other issues.

Parameters and patches have been already recommended.

Daily statistics discussion could be or not needed, but check if you have calculated the oracle data dictionary, fixed tables and system stats.

But I would focus on the mentioned note