RE: Log file sync and log file parallel write.

From: Guang Mei <GuangMei_at_crd.com>
Date: Mon, 3 Mar 2008 14:06:05 -0500
Message-ID: <BB2F960B8F4F7C40A09F44F50CF40A784419E0@mail3.crd.com>


Have you tried rebuilding index with nologging option?  

Guang  


From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Johnson, William L (TEIS)
Sent: Monday, March 03, 2008 2:01 PM
To: oracle-l_at_freelists.org
Subject: Log file sync and log file parallel write.  

I know this topic has been discussed in the past as I have found many threads. I have been trying to sift through the various postings on Freelists and on Metalink, but my head is spinning. Here is the situation...  

Solaris release 5.9.

Enterprise Edition Oracle 9.2.0.6. (Yeah - I know - upgrade - but the third party vendor doesn't support a newer release with the current version of their software. The application upgrade is not scheduled until May and I have to stop the application from crashing the database through our cluster monitoring tools while index rebuilds are occurring.)  

A simple exercise of rebuilding about 10 different indexes on-line serially takes 45 minutes. Here are the top 5 timed events from the statpack report for a log_buffer of 3MB...these reports both only encompass 45 minutes of activity.

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     %
Total
Event                                               Waits    Time (s)
Ela Time
  • ------------ -----------
CPU time                                                        1,366
32.21
direct path write                                  23,693         945
22.30
log file parallel write                            14,149         760
17.94
log file sync                                       5,572         630
14.86
db file scattered read                             62,191         144
3.41  

I bounced the instance and changed the log_buffer to 1MB...here are the results...not much improvement...

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     %
Total
Event                                               Waits    Time (s)
Ela Time
  • ------------ -----------
CPU time                                                        1,263
31.39
direct path write                                  23,918         964
23.96
log file parallel write                            19,654         779
19.38
log file sync                                       4,937         345
8.58
log buffer space                                    3,584         298
7.41  

I hate to make this statement, but I must. Our UNIX Admins are telling us that they see no problems with the physical disk. Currently the redo logs are on the same physical disks that hold the control files, data files and redo logs.  

What am I doing wrong that I can not correct this? Is this something simple - aka fix the hardware? How can I verify this is a hardware issue?  

Index rebuilds shouldn't be doing this to our system. Our production database was crashed yesterday due to other applications "timing out" through the cluster while trying to read data while this type of activity was occurring.  

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 03 2008 - 13:06:05 CST

Original text of this message