Home » RDBMS Server » Performance Tuning » wait events
wait events [message #127061] Fri, 08 July 2005 06:24 Go to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
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

CLASS	COUNT	TIME
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


thanks

subu
Re: wait events [message #127094 is a reply to message #127061] Fri, 08 July 2005 10:18 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/toc.htm
Re: wait events [message #127774 is a reply to message #127061] Wed, 13 July 2005 05:01 Go to previous message
SoporteDBA
Messages: 7
Registered: July 2005
Location: Écija, Sevilla
Junior Member
Hello,

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

BUFFER BUSY WAITS

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: Sun Sep 20 16:18:32 CDT 2020