extreme high 'db file parallel wait' on ZFS

From: gengmao <gengmao_at_gmail.com>
Date: Mon, 26 Apr 2010 19:03:58 +0800
Message-ID: <q2tafe23fd51004260403pc1c39b09q71b46b5819e1d788_at_mail.gmail.com>

Hello,

I am running a batch job on an oracle 10.2.0.3 database, which locates on a ZFS file system. The job runs very slow. Got the AWR report, it shows following top events.

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait

Class
------------------------------ ------------ ----------- ------ ------

---

db file parallel write                  544       5,579  10256  220.2 System
I/O
log file parallel write               6,345       1,079    170   42.6 System
I/O
CPU time                                            697          27.5
log file switch (checkpoint in          651         638    981   25.2
Configurat
db file sequential read             241,752         546      2   21.5   User

I/O

Obviously it is very IO intense. But I can't understand why the average 'db file parallel write' is so high! Normally it should close to the average I/O service time, right?

Following is a sample output from iostat -xn 5. Seems the asvc_t is not very high.

                    extended device statistics

    r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device

    0.0    0.0    0.0    0.0  0.0  0.0    0.0    0.0   0   0 c1t0d0
    0.0    1.8    0.0    6.0  0.0  0.0    0.0    0.1   0   0 c0t0d0
   28.2   27.4 3596.2 1140.8  0.0  0.6    0.0   10.7   0  20 c0t1d0
   30.6   17.2 3916.2 1204.4  0.0  1.0    0.0   21.5   0  29 c0t2d0
   28.2   22.2 3609.0 1261.5  0.0  0.8    0.0   16.8   0  18 c0t3d0
   36.0   12.8 4581.7 1268.1  0.0  1.1    0.0   21.9   0  24 c0t4d0
   31.6   12.6 4044.2 1257.2  0.0  0.8    0.0   17.3   0  22 c0t5d0
   31.6   12.6 4044.2 1268.9  0.0  0.9    0.0   20.8   0  29 c0t6d0
   30.4   12.8 3890.6 1177.7  0.0  1.0    0.0   22.5   0  27 c0t7d0

Following is a sample output from zpool iostat -v 5:
               capacity     operations    bandwidth
pool         used  avail   read  write   read  write


----------  -----  -----  -----  -----  -----  -----

oracle       332G   620G    175  1.33K  22.0M  31.9M
  c0t1d0    47.3G  88.7G     23    196  2.97M  4.69M
  c0t2d0    47.4G  88.6G     31    194  3.99M  4.53M
  c0t3d0    47.4G  88.6G     29    195  3.67M  4.43M
  c0t4d0    47.3G  88.7G     20    190  2.60M  4.33M
  c0t5d0    47.4G  88.6G     24    191  3.12M  4.38M
  c0t6d0    47.4G  88.6G     21    194  2.72M  4.80M
  c0t7d0    47.3G  88.7G     23    198  2.90M  4.74M

---------- ----- ----- ----- ----- ----- ----

Could anyone explain the extreme high 'db file parallel wait'? Is it possible tune zfs to get better performance?

Thanks in advance!
Eric Geng

--

http://www.freelists.org/webpage/oracle-l Received on Mon Apr 26 2010 - 06:03:58 CDT