FreeLists / oracle-l / Re: Log file sync and log file parallel write.

[Asif Momen <asif_oracle@xxxxxxxxx>]

Hi,

> I bounced the instance and changed the log_buffer to 1MB

I think Log buffer of 1MB is too small, I would suggest you to revert its size.

> log buffer space      3,584         298     7.41
 
After the change, you may see that "log buffer space" wait event popped up in 
your Top 5 Wait events section.


> Currently the redo logs are on the same physical disks that hold the control 
> files, data files and redo logs.

You should consider separating them on to different mount points. 

How many members do you have in your redo log group??

> direct path write           23,693         945    22.30This wait event occurs 
> when Direct load operations are performed, when a sort goes to disk or during 
> parallel DML operations.

In your case, I think its because of your index rebuild activity.

Lastly, why are you rebuilding your indexes???


Regards

Asif Momen
http://momendba.blogspot.com

"Johnson, William L (TEIS)" <WLJohnson@xxxxxxxxxxxxxxxxxxx> wrote:          
st1\:*{behavior:url(#default#ieooui) }           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.
   
  
  

       
---------------------------------
Looking for last minute shopping deals?  Find them fast with Yahoo! Search.