RE: Log file sync and log file parallel write.
- From: "Guang Mei" <GuangMei@xxxxxxx>
- To: <WLJohnson@xxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 3 Mar 2008 14:06:05 -0500
Have you tried rebuilding index with nologging option?
Guang
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Johnson, William L
(TEIS)
Sent: Monday, March 03, 2008 2:01 PM
To: oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
