Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> lots of waiting on 'db file parallel write'
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 TimeWait 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.39User 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
Any help would be appreciated..
thanks
-peter
Received on Fri Feb 24 2006 - 11:39:27 CST