Home » RDBMS Server » Performance Tuning » statspack top 5 wait event
statspack top 5 wait event [message #119453] Thu, 12 May 2005 10:52 Go to next message
confident
Messages: 28
Registered: March 2005
Junior Member
iam looking in statspack report in oracle 8.1.7.4, can anybody tell me in top 5 wait event, how to know, anything is abnormal..
some time i find different top 5 wait event every time in statpack report... well any body can tell me, what are thing i need to consider in top 5 wait event.... and how to analyze, whether is there any default percent in total wt time to consider as normal..for the these events
db file scattered read,db file sequential read,db file parallel write,log file parallel write,log file sync,latchfree, enqueues and soon..... ... please reply me i will be very grateful...



Top 5 Wait Events
~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file scattered read 686,446 841,612 28.68
db file sequential read 560,899 540,532 18.42
db file parallel write 26,270 277,926 9.47
log file parallel write 289,226 262,024 8.93
log file sync 184,512 240,604 8.20
-------------------------------------------------------------
Re: statspack top 5 wait event [message #119498 is a reply to message #119453] Thu, 12 May 2005 19:28 Go to previous messageGo to next message
confident
Messages: 28
Registered: March 2005
Junior Member
statspack detail report...report will generate daily 2 time, one is morning 7AM and another evening 7PM

DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
xxxxxxxxx 130952321 xxxxxxxxx 1 8.1.7.4.0 NO xxxxxxxx
1

Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 26152 25-Apr-05 06:00:14 167
End Snap: 26164 25-Apr-05 18:00:17 167
Elapsed: 720.05 (mins)

Cache Sizes
~~~~~~~~~~
db_block_buffers: 294912 log_buffer: 491520
db_block_size: 16384 shared_pool_size: 419430400

Load Profile
~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 266,846.91 61,006.21
Logical reads: 8,035.94 1,837.17
Block changes: 1,418.57 324.31
Physical reads: 269.16 61.54
Physical writes: 88.84 20.31
User calls: 408.26 93.33
Parses: 172.62 39.47
Hard parses: 1.10 0.25
Sorts: 2.67 0.61
Logons: 0.20 0.05
Executes: 271.14 61.99
Transactions: 4.37

% Blocks changed per Read: 17.65 Recursive Call %: 50.83
Rollback per transaction %: 0.19 Rows per Sort: 624.25

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.97 Redo NoWait %: 100.00
Buffer Hit %: 96.65 In-memory Sort %: 99.66
Library Hit %: 99.58 Soft Parse %: 99.36
Execute to Parse %: 36.33 Latch Hit %: 99.80
Parse CPU to Parse Elapsd %: 81.24 % Non-Parse CPU: 99.97

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 91.85 91.70
% SQL with executions>1: 48.13 55.18
% Memory for SQL w/exec>1: 4.65 41.82

Top 5 Wait Events
~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file scattered read 686,446 841,612 28.68
db file sequential read 560,899 540,532 18.42
db file parallel write 26,270 277,926 9.47
log file parallel write 289,226 262,024 8.93
log file sync 184,512 240,604 8.20
-------------------------------------------------------------
Wait Events for DB: xxxxxxxx Instance: xxxxxx Snaps: 26152 - 26164
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)



Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
31,931,732 175 182,467.0 9.2 65569463
Re: statspack top 5 wait event [message #119568 is a reply to message #119453] Fri, 13 May 2005 07:24 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
It's no use to evaluate a statspack report over such a long period. Each and every abnormality (i.e. peak) will be smoothed out over time, leaving no noticable traces behind.

hth
Previous Topic: Comparision of Two Execution Plan
Next Topic: Prameters For Improving DB Perfomance
Goto Forum:
  


Current Time: Tue Mar 19 04:07:15 CDT 2024