| wait events [message #127061] |
Fri, 08 July 2005 06:24  |
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 #127774 is a reply to message #127061] |
Wed, 13 July 2005 05:01  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
