Re: High waits/Timeouts - log file parallel write

Hello Steve:
Thanks for your inputs.  What can be causing
''control file parallel write' to be so high?  I
have disk_asyn_io on by default(solaris 9).  I
have just run another statpack report and I'll
paste it here if you don't mind?

STATSPACK report for
                                                 
                            
DB Name         DB Id    Instance     Inst Num
Release     Cluster Host
------------ ----------- ------------ --------
----------- ------- ------------
C3PO          1109063266 C3PO                1
9.2.0.5.0   NO      sworks
                                                 
                            
            Snap Id     Snap Time      Sessions
Curs/Sess Comment
            ------- ------------------ --------
--------- -------------------
Begin Snap:      22 27-Feb-06 16:30:20       80  
   34.0
  End Snap:      23 27-Feb-06 17:00:56       82  
   33.7
   Elapsed:               30.60 (mins)
                                                 
                            
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:     1,392M      Std
Block Size:         8K
           Shared Pool Size:       480M         
Log Buffer:     9,766K
                                                 
                            
Load Profile
~~~~~~~~~~~~                            Per
Second       Per Transaction
                                  
---------------       ---------------
                  Redo size:            
15,734.86             77,450.97
              Logical reads:            
96,411.06            474,559.56
              Block changes:               
143.62                706.95
             Physical reads:                 
0.02                  0.12
            Physical writes:                 
2.56                 12.58
                 User calls:                 
9.02                 44.38
                     Parses:                
12.35                 60.79
                Hard parses:                 
0.02                  0.08
                      Sorts:                 
3.20                 15.75
                     Logons:                 
0.03                  0.16
                   Executes:                
25.87                127.35
               Transactions:                 
0.20
 
  % Blocks changed per Read:    0.15    Recursive
Call %:    79.23
 Rollback per transaction %:    0.00       Rows
per Sort:     3.77
 
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo
NoWait %:  100.00
            Buffer  Hit   %:  100.00    In-memory
Sort %:  100.00
            Library Hit   %:   99.94        Soft
Parse %:   99.88
         Execute to Parse %:   52.27        
Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:  100.73     %
Non-Parse CPU:   63.49
 
 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   23.59   23.64
    % SQL with executions>1:   53.60   55.12
  % Memory for SQL w/exec>1:   34.73   35.25
 
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                               
                     % TotalEvent                
                              Waits    Time (s)
Ela
Time--------------------------------------------
------------ ----------- --------log file
parallel write                             9,477 
        24    66.09control file parallel write   
                       610           5   
12.95CPU time                                    
                       4    10.61process startup 
                                      30         
 1     3.13log file sync                         
               374           1     2.81         
-------------------------------------------------------------

The autoinvoice job is has been running for over
2+hrs and it's the only job running on this
instance.
Regards,
Tom

--- Steve Adams <steve.adams@xxxxxxxxxxxx> wrote:

> Hi Tom,
> 
> The 'log file parallel write' waits affect LGWR
> only. They do not affect 
> the performance of user processes directly
> (although they do impact 
> somewhat on your 'log file sync' wait
> duration). The number of timeouts 
> is influenced by your use of asynchronous I/O
> and by how often LGWR is 
> posted by user processes. It is not a useful
> indicator of performance 
> problems.
> 
> I think you should be more concerned about the
> 'control file parallel 
> write' waits that are probably affecting user
> processes directly.
> 
> @   Regards,
> @   Steve Adams
> @   http://www.ixora.com.au/         - For DBAs
> @   http://www.christianity.net.au/  - For all
> 
> 
> TP wrote:
> > Hello all,
> > 
> > I seeing high Waits, Timeouts from one the 
> > problem instance.  
> > 
> > Top 5 Timed Events
> > ~~~~~~~~~~~~~~~~~~                           
>    
> >                      % Total
> > Event                                        
>    
> >   Waits    Time (s) Ela Time
> > --------------------------------------------
> > ------------ ----------- --------
> > CPU time                                     
>    
> >                   8    40.51
> > control file parallel write                  
>    
> >     642           5    25.56
> > db file sequential read                      
>    
> >     343           2     9.40
> > log file parallel write                      
>    
> >   9,697           2     7.99
> > log file sync                                
>    
> >     425           1     5.69
> >          
> >
>
-------------------------------------------------------------
> > ^LWait Events for DB: C3PO  Instance: C3PO 
> [snip]
> >                  Avg
> >                                              
>    
> >    Total Wait   wait    Waits
> > Event                               Waits  
> > Timeouts   Time (s)   (ms)     /txn
> > ---------------------------- ------------
> > ---------- ---------- ------ --------
> > control file parallel write           642    
>    
> >  0          5      8      1.4
> > db file sequential read               343    
>    
> >  0          2      6      0.7
> > log file parallel write             9,697    
> 
> > 8,429          2      0     21.1
> > log file sync                         425    
>    
> >  0          1      3      0.9
> [snip]
> > 
> > -----------------------------------------
> > It seems like I'm having some issue with the
> LGWR
> > "log file parallel write" (timeouts). 
> > Can you suggest and/or provide some
> > recommendations?  The DB version is 9205 on
> > Solaris 2.9.
> > 
> > Thanks in advance!
> > Tom  
> [snip]
> 


"  ,--o                          
  -\\_<_   Do you ride? "                     
(*)/-'(*)
--
http://www.freelists.org/webpage/oracle-l

Other related posts: