Since this version is 9. Do you have any other issues in OS? High CPU Usage? Paging, swapping etc? What OS are you using? Depending upon that, You could potentially use couple of methods:. Truss or strace lgwr to see if there are any bottlenecks [ Very dangerous since truss or strace can cause LGWR crash and instance crash ] 3. Infact Jonathan Lewis linked me through to your article. You can take that as a compliment Well it is on Solaris10 but I do not have root access.
Observations and questions: 1. Since there are 2 lfpw per sync I would expect the log file sync time to be about 2 times the lfpw time. I will see if I can have root privileges. Please correct me if I am wrong. I had a bit of trouble finding the sid for my LGWR process. Here is the sql I came up with instead of hard coding to 3. Thanks Riyaj. I have one follow up question, I have a data guard setup with high wait on log file sync.
Hi Kay Thank you for reading my blog and sorry for the late reply. Looking at your output, it looks like, you may be able to improve performance of DG increasing network buffer size. Can you also please post few lines from statspack?
And more importantly, how good is your network throughput and latency? Based on the following observations, I think the answer is yes. When network resumed to normal 64 bytes from mcc13asff3. Metalink note From your statspack, I think, you are correct.
In your case, your statspack output indicates that you may be running in to issues with LNS buffer flushing or LNS communication not completing quick enough. I guess, truss or dtrace of LGWR will confirm this. I guess, LNS process tuning is in order to resolve this intermittent issue.
Of course, network latency is another area to look at. In our case, this latency happened to be caused by network. Noted your point that there could be other reasons that slow down LNS too. Thanks for your help.
Thanks for this post, and sorry for my bad English. I have top waits: controlfile sequential read; log file sync enq: CF — contention. Are you have a idea, where is bottleneck? RMAN work without catalog. Hi I am afraid that we need more information to debug this issue. Can you please print top 5 wait events from statspack or AWR report here and also let us know duration of statspack or AWR report?
That should be a good starting point to debug this. My English language skills are bad too. Hello First of all, AWR duration of 15 hours too long. AWR report is an aggregated report and so, it is very hard to decipher details based upon aggregated data. I will try to help. It looks like write performance to log files are not great.
You might want to research that. Same with control files too. Much of the control file writes are performed under the protection of CF enqueue and so, CF enqueue waits may be related to this issue. You might want to consider tuning LNS process also. Simply even increasing buffer size might be a good start. I am not sure why. That might trigger hyperactive LGWR. Last but not least, there are bugs in In some cases, LGWR is not efficient enough when the workload is not so high.
Read You might want to think about In summary, this goes to basic things. Improve write throughput to redo log files and control files. Eliminate bugs with better software etc. We had slow performance on the database and the requests from app server are queued up. I do not see high CPU usage or user commits during that time.
What is the duration of your AWR report? It may be for 1 hour or 30 minutes and so, aggregation might be hiding the details.
Even ASH report of 15 minutes is not useful. How to know semnum is the user process traced? Any method to map a process with a set id and semnum? Hello Daniel I need to think about this little bit. I am trying to see if there is an easier way. Wonderful article and happy to read you. Database is running in no archive log mode.
User commits is low; so is user calls and redo blocks written is 0. Only redo synch time, redo synch writes and redo entries are huge. And the redo size is a negative number. Metalink says this is a bug in the preg releases ours is 9. I had checked the CPU usage. Hello Muthu I am really not sure what problem you are trying to address here? Are you trying to tune that concurrent request? Since there are 5 requests running, all of them may be generating much redo.
Is this a custom program or standard seeded program? Is there a possibility of reducing redo size by dropping few indices on that table and then rebuild them later? Look for the opportunities to tune them. Also, all these inserts, are they inserts with bind variables? Sorry to ask you more questions, but I would like to understand the root cause before giving you some suggestions.
Thanks for the quick update. This is the standard Oracle report Journal Entries Report. Since this is not a real production, I think, we can think about it. Ours is a JFS2 type of file system. Hello Muthu Sorry, it took a while to respond. Do you have tkprof for this report run? Is the parent job slow or report is slow? There have been many performance issues reported for journal entries report.
Bug fix was to truncate that table instead of delete. Bug I guess, we will have to look at tkrpof output files to see where the slowness. If you are trying to tune the instance for log file sync issues, I guess, converting delete statement to truncate statement might help. Great article, thanks. Here is a small example from a Solaris VM. Analysis Period ————— AWR snapshot range from to Time period starts at MAR Database version However step 3 will also include the network write time and the redo write to the standby redo logs.
If the total wait time is significant, review the average wait time. So he ran a simply query comparing the previous and current day log file sync wait times. He figured that if there was a significant difference in log file sync wait times, then the […]. However, careful analysis should be performed when changing this parameter. Save my name, email, and website in this browser for the next time I comment.
Toggle navigation. The wait is for LGWR to flush up to this buffer. Try to reduce resource contention. Oracle guru Steve Adams notes details on how Oracle processes log file sync waits:. There is also the possibility that bugs can cause high log file sync waits.
Oracle Training from Don Burleson The best on site " Oracle training classes " are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop! Feel free to ask questions on our Oracle forum.
0コメント