-
log file sync
DBAs,
We recently got a lot of complaints about one database for its slowness.
I took a snapshot during the slow time whose time window is 15 minuets.
From the stats report, in the top 5 waits, we saw the log file sync.
How can we know it is abnormal or normal?
The important part in the report is as follows:
Code:
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.24 In-memory Sort %: 99.96
Library Hit %: 85.99 Soft Parse %: 44.28
Execute to Parse %: 15.49 Latch Hit %: 98.70
Parse CPU to Parse Elapsd %: 89.44 % Non-Parse CPU: 55.74
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
log file sync 61,458 7,386 47.32
latch free 20,065 3,686 23.61
db file sequential read 17,752 1,641 10.51
log file parallel write 56,093 1,610 10.31
db file parallel write 1,610 908 5.82
------------------------------------------------------
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
log file sync 61,458 0 7,386 1 1.0
latch free 20,065 15,166 3,686 2 0.3
db file sequential read 17,752 0 1,641 1 0.3
log file parallel write 56,093 0 1,610 0 1.0
db file parallel write 1,610 0 908 6 0.0
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 35,105 39.0 0.6
Any idea from this report?
Thnaks for the help.
Last edited by marist89; 09-28-2005 at 02:49 PM.
Reason: formatting
-
In this report,
CPU used by this session 35,105
Does it mean cpu during the 15 minuet time window has been
used for 35,105 unit -- cs ?
log file sync wait time is 7,386 cs?
-
-
Soft Parse %: 44.28
Execute to Parse %: 15.49
This is very low.
-
One. Decrease your log buffer Say to 1mb (I dont know your current size but 1 m is normally good).
Two. You might be keeping other datafiles or temp files in the redo log area. Move redo to a separate device. Do not keep them in RAID 5.
Three. See the log file size, if frequent switches are there increase the size.
I also face frequent log switches in one of my db with solaris 8,
might be the undo the reason for the redo fast filling.
-
Originally Posted by engiri
One. Decrease your log buffer Say to 1mb (I dont know your current size but 1 m is normally good).
Two. You might be keeping other datafiles or temp files in the redo log area. Move redo to a separate device. Do not keep them in RAID 5.
Three. See the log file size, if frequent switches are there increase the size.
I also face frequent log switches in one of my db with solaris 8,
might be the undo the reason for the redo fast filling.
Your points 1.) and 3.) have nothing to do with log file sync wait event.
What the original poster realy need to check is if there realy is the need for apparent high commit rate. The most likely reason for log file sync event is when user issues a commit (or rollback). So he/she need to check who (or what) is causing frequent commits.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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
|