Re: High waits/Timeouts - log file parallel write

From: TP <pele_00_2000_at_yahoo.com>
Date: Mon, 27 Feb 2006 17:20:14 -0800 (PST)
Message-ID: <20060228012014.21869.qmail@web30001.mail.mud.yahoo.com>

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_at_ixora.com.au> 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

Received on Mon Feb 27 2006 - 19:20:14 CST