Home » RDBMS Server » Performance Tuning » log file sync waits
| log file sync waits [message #133174] |
Wed, 17 August 2005 23:45  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I need some help with tuning log file sync waits.
This parameter has been always among the top 5 for our application because of it's nature - hundreds of small transactions/min, from 70 to 700 processes in the same time, hundred of single commits per second. Now we have a new application version which performs much worse then the old one. In the custom application log, I can see that there are significant waits for some queries, but that doesn't tell me where this time comes from - it's overall from the time the app issue the statement till the moment it comes back from the database. I do not know what has been changed in the app code, but what I see on the statpack is that this param has been increased several times. I tried to explain the nature of this param and that we need to check the code commits, but it has been said that this is a "pure database problem".
What system views I could use so I could trace where the time goes from the application call to its completion back to the app. I run a trace but there's no much more info inside.
The v$waits views do not have any significant waits for my session.
Thanks a lot for any idea.mj
|
|
|
|
|
|
|
|
| Re: log file sync waits [message #133531 is a reply to message #133174] |
Fri, 19 August 2005 08:28  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I cannot change the app. This is the way it works. I need to tune up my DB to handle it. I tried to increase the Log_Buffer to 10M and it help a little. I have also log parallel writes issue. The logs are on separate devise - good speed disks. I could also move them on raw device RAID 0+1...
Please, advise what are my options. It should be something...
The same app runs with different data on DB2 and performs 5 time better and stable... I cannot believe that Oracle cannot do it. It should be a config issue.
I could change the size of redo logs, to tune the checkpoint time, etc.
Thanks a lot for the help.
Bellow is my stat pack:
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 1430 18-Aug-05 16:00:04 14 19,439.9
End Snap: 1431 18-Aug-05 17:00:04 52 5,430.8
Elapsed: 60.00 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 5,072M Std Block Size: 8K
Shared Pool Size: 992M Log Buffer: 9,766K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,686,822.29 14,913.33
Logical reads: 22,329.04 197.41
Block changes: 10,514.69 92.96
Physical reads: 0.01 0.00
Physical writes: 154.07 1.36
User calls: 4,206.65 37.19
Parses: 2,055.97 18.18
Hard parses: 0.33 0.00
Sorts: 7.31 0.06
Logons: 0.10 0.00
Executes: 2,056.93 18.19
Transactions: 113.11
% Blocks changed per Read: 47.09 Recursive Call %: 6.21
Rollback per transaction %: 0.04 Rows per Sort: 40.98
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.83 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 99.94 Soft Parse %: 99.98
Execute to Parse %: 0.05 Latch Hit %: 99.81
Parse CPU to Parse Elapsd %: 92.38 % Non-Parse CPU: 88.70
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 28.31 30.75
% SQL with executions>1: 88.62 87.42
% Memory for SQL w/exec>1: 78.76 79.76
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
log file sync 412,330 12,830 61.11
CPU time 5,994 28.55
log file parallel write 678,265 1,646 7.84
db file parallel write 37,587 203 .97
buffer busy waits 133,038 126 .60
-------------------------------------------------------------
Wait Events DB/Inst: ORAPERF/oraperf Snaps: 1430-1431
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file sync 412,330 3,497 12,830 31 1.0
log file parallel write 678,265 0 1,646 2 1.7
db file parallel write 37,587 0 203 5 0.1
buffer busy waits 133,038 1 126 1 0.3
SQL*Net break/reset to clien 97,882 0 110 1 0.2
control file parallel write 1,423 0 51 36 0.0
latch: cache buffers chains 21,602 21,557 18 1 0.1
control file single write 117 0 3 28 0.0
process startup 54 0 3 50 0.0
latch: library cache 917 0 2 3 0.0
library cache pin 42 0 2 38 0.0
log file switch completion 62 0 1 21 0.0
latch free 388 193 1 2 0.0
LGWR wait for redo copy 14,686 17 1 0 0.0
control file sequential read 1,503 0 1 1 0.0
enq: TX - contention 189 0 0 2 0.0
rdbms ipc reply 881 0 0 0 0.0
latch: In memory undo latch 118 0 0 3 0.0
enq: TX - index contention 147 0 0 2 0.0
enq: HW - contention 748 0 0 0 0.0
latch: shared pool 152 0 0 2 0.0
latch: library cache lock 102 0 0 2 0.0
latch: undo global data 73 0 0 2 0.0
latch: redo allocation 51 0 0 2 0.0
latch: enqueue hash chains 35 0 0 3 0.0
enq: TX - row lock contentio 33 0 0 2 0.0
direct path write 48 0 0 1 0.0
latch: library cache pin 21 0 0 2 0.0
direct path read 48 0 0 0 0.0
log file single write 6 0 0 4 0.0
log file sequential read 6 0 0 2 0.0
latch: session allocation 9 0 0 1 0.0
row cache lock 9 0 0 0 0.0
latch: cache buffers lru cha 5 0 0 1 0.0
enq: US - contention 3 0 0 1 0.0
latch: redo writing 2 0 0 1 0.0
latch: messages 1 0 0 1 0.0
enq: TX - allocate ITL entry 4 0 0 0 0.0
enq: FB - contention 1 0 0 0 0.0
SQL*Net more data to client 8 0 0 0 0.0
buffer deadlock 61 61 0 0 0.0
SQL*Net message from client 7,825,219 0 145,990 19 19.2
queue messages 720 720 3,514 4880 0.0
virtual circuit status 120 120 3,495 29122 0.0
jobq slave wait 1,108 1,105 3,243 2927 0.0
SQL*Net message to client 7,825,240 0 14 0 19.2
SQL*Net more data from clien 65 0 6 87 0.0
[Updated on: Fri, 19 August 2005 08:41] Report message to a moderator |
|
|
|
|
|
| Re: log file sync waits [message #133872 is a reply to message #133174] |
Mon, 22 August 2005 08:37 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Initial guess is that you have undersized the log buffer and log file sizes and numbers of files for the work you are doing.
Also, you can't compare apples to oranges. To say the same app works 5 times faster on db2 doesn't really make sense. You can't treat a RDBMS as a black box and one app fits all. There are fundamental differences in each RDBMS that must be considered when developing an application, or the application will suffer performance wise, and may not even return correct results. Suggest you check out Effective Oracle By Design by Tom Kyte who discusses this issue in much greater detail.
But by definition, due to architectural differences in different databases, a single application can not perform well on more than one of them. They must be customized to the database, at least with regards to data access issues.
|
|
|
|
Goto Forum:
Current Time: Wed Nov 27 10:09:52 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
