|
-
statspack top 5 wait event
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...
Code:
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
-------------------------------------------------------------
Last edited by marist89; 05-12-2005 at 11:18 AM.
-
I think the info you gave us is incomplete but you have FTS, disk i/O. You may want to give us more information.
-
How long did you run statspack for? Are you looking for something in particular that is running slowly? THere are no "silver bullet" percentages to look out for. It is totally application specific.
Assistance is Futile...
-
This may be perfectly fine or horrible. Please post the header information for statspack
Jeff Hunter
-
Jeff,
could you please let us know in what case it's perfectly fine and in what case it's horrible.
I would like to know speically guys like Jeff
thanks so much
-
Originally posted by hannah00
could you please let us know in what case it's perfectly fine and in what case it's horrible.
If this were an OLAP system or a OLTP during a nightly reporting phase, a high number of "db file scattered read" waits might be perfectly reasonable. In an OLTP system during normal transactional operation, I would think this is a high number of "db file scattered read" waits and some query probablyneeds to be optimized.
Jeff Hunter
-
statspack detail report...report will generate daily 2 time, one is morning 7AM and another evening 7PM..
Code:
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
Last edited by marist89; 05-12-2005 at 09:47 PM.
-
This window is way too big. It is impossible to tell anything. Try 15 minute intervals.
Jeff Hunter
-
Originally posted by marist89
This window is way too big. It is impossible to tell anything. Try 15 minute intervals.
Sounds good.
Also, the average wait on the db file parallel write looks a little high, which means your i/o is a little slow. What o/s is this running on?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
