lots of waiting on 'db file parallel write'

From: peter <p_msantos_at_yahoo.com>
Date: 24 Feb 2006 09:39:27 -0800
Message-ID: <1140802766.950108.161430@e56g2000cwe.googlegroups.com>

Folks,

I'm currently trying to get to the bottom of why my 4 database writters spend so much time
waiting on 'db file parallel write'... even when it appears that the system is not terribly busy.

  Environment:
   Oracle 10.1.0.4
   Solaris 8 - 64bit
   IBM SAN T700
   Veritas filesystem (mostly everything is 0+1 raid with a few luns at raid 5).

4 db writters

  So at different times during the day I notice that the system just "hangs". When I check what is
  waiting I see the number of wait seconds on my dbwn processes is anywhere from 10-140 seconds.
  Obviously if the db writters can go fast enough, the I start to see sessions pile up and
  lots of waits on 'free buffer wait'.

Our system is growing very fast because the company is growing at almost 50% rate...so I'm just
not sure if we are hitting hardware limitations.

I do have an itar open with Oracle, but I'm just not getting anywhere with them.
Our system is running faster than ever, but now at certain times during the day it just hangs...

  Here are some parameter settings
  DB_CACHE_SIZE = 2GB
  SHARED_POOL_SIZE = 1GB. Something interesting in the AWR reports....

Load Profile

~~~~~~~~~~~~                            Per Second       Per
Transaction
                                   ---------------
---------------
                  Redo size:            416,701.01
17,329.98
              Logical reads:             27,387.68
1,139.01
              Block changes:              2,974.96
123.72
             Physical reads:              5,293.28
220.14
            Physical writes:                532.99
22.17
                 User calls:              1,619.75
67.36
                     Parses:                 53.37
2.22
                Hard parses:                  0.51
0.02
                      Sorts:                 67.82
2.82
                     Logons:                  2.85
0.12
                   Executes:              1,466.37
60.98
               Transactions:                 24.05

Instance Efficiency Percentages (Target 100%)

            Buffer Nowait %:   99.92       Redo NoWait %:  100.00
            Buffer  Hit   %:   83.69    In-memory Sort %:   99.99
            Library Hit   %:   99.94        Soft Parse %:   99.05
         Execute to Parse %:   96.36         Latch Hit %:   99.69
Parse CPU to Parse Elapsd %:   56.18     % Non-Parse CPU:   99.63

 Shared Pool Statistics        Begin    End
                              ------  ------
             Memory Usage %:   93.66   93.77

% SQL with executions>1: 89.18 88.96 % Memory for SQL w/exec>1: 90.72 90.46

Class                    Waits Total Wait Time (s)  Avg Time (ms)
------------------ ----------- ------------------- --------------
data block              76,951               1,206             16
undo header                 71                  39            545
segment header               6                   0             10
undo block                 161                   0              0
          -------------------------------------------------------------

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                        % Total
Event                                 Waits    Time (s)   DB Time

Wait Class

------------------------------ ------------ ----------- ---------
--------------
db file sequential read           6,554,919      42,254     55.17
User I/O
CPU time                                         22,239     29.04
db file parallel write              207,339       5,367      7.01
System I/O
PX Deq Credit: send blkd             28,227       5,064      6.61
   Other
db file scattered read              200,481       3,363      4.39

User I/O

'db file sequentail read' is always the top wait event..but this is normal for us.

truss on one of the db writers.

kaio(AIOWAIT, 0xFFFFFFFF7FFFDE10)               = 1
        timeout: 600.000000 sec
kaio(AIONOTIFY, -2229511408)                    = 0
lwp_mutex_lock(0xFFFFFFFF7C909EC0)              = 0
        mutex type: USYNC_THREAD
kaio(AIONOTIFY, -2232272112)                    = 0
kaio(AIONOTIFY, -2254603504)                    = 0
lwp_mutex_lock(0xFFFFFFFF7C909EC0)              = 0
        mutex type: USYNC_THREAD
lwp_mutex_wakeup(0xFFFFFFFF7C909EC0)            = 0
        mutex type: USYNC_THREAD
kaio(AIONOTIFY, -2229732592)                    = 0
lwp_mutex_wakeup(0xFFFFFFFF7C909EC0)            = 0
        mutex type: USYNC_THREAD
lwp_mutex_lock(0xFFFFFFFF7C909EC0)              = 0
        mutex type: USYNC_THREAD
lwp_mutex_wakeup(0xFFFFFFFF7C909EC0)            = 0
        mutex type: USYNC_THREAD

pwrite(266, "06A2\0\002 A } bF5 6DC }".., 57344, 0x2FAC4000) = 57344

Any help would be appreciated..

thanks
-peter Received on Fri Feb 24 2006 - 11:39:27 CST