Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: lots of waiting on 'db file parallel write'

Re: lots of waiting on 'db file parallel write'

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 25 Feb 2006 03:23:56 GMT
Message-Id: <pan.2006.02.25.03.22.34.775838@sbcglobal.net>


On Fri, 24 Feb 2006 09:39:27 -0800, peter wrote:

>
> 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'.

Question here is what are you writing? You have more then one dbwr (you used plural when talking about db WRITERS) but statistics below doesn't explain all that writing.

>
> 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 wouldn't want to exclude that possibility, and the occasional waits for free buffers certainly point in that direction, but the huge amount of spent CPU time shows me that you are running very expensive SQL commands. You may want to tune them properly. I assume that your site is an OLTP site?

>
> 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

This is very small number of writes, compared to the number of reads. How is it that so few writes are generating such an enormous quantity of redo? Are you using BLOBS or something of that nature?

> 22.17
> User calls: 1,619.75
> 67.36
> Parses: 53.37
> 2.22
> Hard parses: 0.51
> 0.02
> Sorts: 67.82

More sorts then transactions? Hmmm, your CPU usage may be rather high. Are you sure that all those sorts are necessary?

> 2.82
> Logons: 2.85
> 0.12
> Executes: 1,466.37
> 60.98
> Transactions: 24.05

Number of transactions is extremely low....Confusing.

>
>
> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 99.92 Redo NoWait %: 100.00

Hey, your system is performing well, you have almost 100% BCHR :)

> 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

You are either using bind variables or masterfully reusing your SQL.

> % 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

This is index access. That's is OK for an OLTP site. Some indicators that I see are completely inconsistent with an assumption about you managing an OLTP site.

> User I/O
> CPU time 22,239 29.04

CPU time is extremely high. You may want to tune your SQL and make sure that it uses the best possible algorithms. What is using all this CPU? You have very few transactions, very few writes, yet you have an exorbitant amount of CPU usage My experience tells me that so high amount of CPU time is usually spent sorting or hashing. This is inconsistent with my initial assumption about your site being an OLTP site.

> db file parallel write 207,339 5,367 7.01

Are you using parallel DML? You should figure out what is it that you are writing so intensely and what is burning your CPU? If there are batch jobs running on the system, you may want to reschedule them, you will have to look for the most expensive SQL statements and tune them, you will have to profile your applications and figure out what is going on. That is a job for a very serious performance analyst. You can always submit your trace files to Hotsos (http://www.hotsos.com) and they do a great job with figuring out what's going on. If you are in the US, they can probably send you a consultant, which I don't know because I've never worked for them. Your problem sounds very serious and it's not a job for an inexperienced person. You need a good performance analyst, someone who knows both the OS side and Oracle. I must confess that your data looks very puzzling and confusing to me.

-- 
http://www.mgogala.com
Received on Fri Feb 24 2006 - 21:23:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US