Home » RDBMS Server » Performance Tuning » wait events
wait events [message #127061] Fri, 08 July 2005 06:24 Go to next message
Messages: 70
Registered: March 2005
Location: delhi
If database is showing the wait events as shown below
How to tune these ??

SELECT class,"COUNT", time  FROM v$waitstat 
where count > 0 
order by count desc

data block	117,060,745.00	25,210,366.00
undo block	809,918.00	494,929.00
segment header	358,642.00	38,325.00
undo header	297,939.00	478,941.00
file header block	19,544.00	3,738,337.00
extent map	678.00	462.00


Re: wait events [message #127094 is a reply to message #127061] Fri, 08 July 2005 10:18 Go to previous messageGo to next message
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Re: wait events [message #127774 is a reply to message #127061] Wed, 13 July 2005 05:01 Go to previous message
Messages: 7
Registered: July 2005
Location: Écija, Sevilla
Junior Member

You should also look at v$system_event, (order by total_waits), you´ll probably see this events on the top:


All this wait stats shows that there is contention in objects due a big insertion rate, (SEGMENT_HEADER, DATA_BLOCK), so , you should identified what objects are afected and considering increase the parameters "freelist" and/or "freelist groups".

UNDO_HEADER and UNDO_BLOCK waits stats shows that you should increase the number of rollback segments and the size of each one.

To identify segments that have contention due the FREELIST CONTENTION :

SELECT class, count FROM v$waitstat WHERE class IN (‘free list’,’segment header’);

If you see non 0 values, then you have freelist contention, to see on which objects:

select vw.sid, segment_name, segment_type
from dba_extents, v$session_wait vw
where file_id = vw.p1 and vw.p2 between block_id and block_id+blocks and
vw.event='buffer busy waits';

You should have big performance results with an apropiate use of freelist and freelist groups object parameters.

You should also look for the document : "Freelist Management With Oracle 8i" by Stephan Haisley.

Previous Topic: steps for tuning oracle...!
Next Topic: Sql Tuning - (urgent)
Goto Forum:

Current Time: Wed Feb 28 22:50:53 CST 2024