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_member204746 · 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_member204746 · Accepted solution]

make sure you update DB statistics on a daily basis.

[Former Member]

Hello again,

Before I did any changes to the Oracle's parameters I checked the Note 619188 - FAQ: Oracle wait events, to understand what could be causing such high event wait time.

With the query:

SELECT EVENT, TOTAL_WAITS, TIME_WAITED, AVG_MS,

ROUND(RATIO_TO_REPORT(TIME_WAITED) OVER () * 100) PERCENT

FROM (SELECT SUBSTR(EVENT, 1, 30) EVENT, TOTAL_WAITS, TIME_WAITED,

ROUND(TIME_WAITED_MICRO / TOTAL_WAITS / 1000, 2) AVG_MS

FROM V$SYSTEM_EVENT

WHERE WAIT_CLASS NOT IN ('Idle', 'System I/O')

UNION

SELECT 'CPU' EVENT, NULL, VALUE, NULL

FROM V$SYSSTAT

WHERE STATISTIC# = 12

ORDER BY 3 DESC)

WHERE ROWNUM <=10;

I got the non-idle events that took more time in my system and the result was:

-

---

Result of the SELECT statement

-

---

EVENT

TOTAL_WAITS

TIME_WAITED

AVG_MS

PERCENT

-

---

log file switch (archiving nee	578.686	57.850.863	999.69	80
buffer busy waits	712.163	6.420.932	90.16	9
CPU	0	2.791.238		4
db file sequential read	4.005.546	1.746.442	4.36	2
log file sync	10.176.490	1.577.177	1.55	2
enq: TX - row lock contention	854.451	642.955	7.52	1
db file scattered read	1.055.533	621.332	5.89	1
enq: CF - contention	210.085	246.910	11.75	0
read by other session	561.558	119.910	2.14	0
log file switch completion	10.777	85.843	79.65	0

-

---

So most of the TIME_WAITED for wait events was because of the "log file switch (archiving needed)", after reading what could cause such wait event, I understood this was related with a problem I previously had in the server, where the archiving folder was with no space left. (Meanwhile the backup of the archives is being done and so the folder is being cleaned on a daily basis).

Thank you all for your help!

[stefan_koehler]

Hello Daniel,

as you already have Oracle 10g - please use the AWR for such analysis. With the AWR report you have the possibility to select time ranges and detailed analysis.

You can create such an AWR report with

> shell> sqlplus / as sysdba

> SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

If you want to you can upload the AWR report to the web and we can take a look at it.

Just for my information.. do you have performance problems or are your analysis "just for fun"?

Regards

Stefan

[Former Member]

Stefan Koehler,

Your tip was of great value, I was not aware of such valuable report.

Now answering to your question, my analysis started because at this moment the server is being used only by 30 users and next week, more 140 will be connecting to it, so I wanted to do a proactive move studying the present performance statistics in order to figure out if something could be wrong, in order to prevent major issues with more users using the server.

Thank you for your time.

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