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

[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