RE: Log file sync and log file parallel write.
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,36632.21
direct path write 23,693 94522.30
log file parallel write 14,149 76017.94
log file sync 5,572 63014.86
db file scattered read 62,191 1443.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,26331.39
direct path write 23,918 96423.96
log file parallel write 19,654 77919.38
log file sync 4,937 3458.58
log buffer space 3,584 2987.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-lReceived on Mon Mar 03 2008 - 13:06:05 CST