Wait events tuning

[Former Member]

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

[Former Member · Accepted solution]

first thing is to follow SAP's recommendations from SAP note 830576 using latest patches.

are you using 10.2.0.2 or 10.2.0.4?

Are you on Windows or Unix?

Have you installed additional patches with OPATCH?

[Former Member · Accepted solution]

Eric Brunelle thank you for your response.

The system is a Windows Server 2K3.

Oracle database version is 10.2.0.2.0 and it was applied the Opatch 18.

Not all parameters are set as defined in Note 830576, I'll apply those SAP recommendations tomorrow.

Anything else you want to recommend me?

Thank you a lot.

[Former Member · Accepted solution]

make sure you update DB statistics on a daily basis.