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

peter wrote:
> Thanks for the links Joel.
>
> Looking that v$bh the following query returns..
>
> 1 select decode( status, 'free', 'free', 'used' )status,count(*)
> 2 from v$bh
> 3* group by decode( status, 'free', 'free', 'used' )
>
> STATUS| COUNT(*)
> ---------|----------
> free | 2
> used | 248385
>
> It appears to stay this way throughout the day... Not sure if that's
> good or not.
> I will look through the docs and see where the KEEP and/or RECYCLE
> pools might
> benefit me.

That's kind of too general of a query. What you really want to see is what objects are using space, and how much. From what I've seen on mixed systems, the big win comes when you put the large full-scanned tables/indices in the recycle pool. Your range-scanned index might be the exception, jam it in keep. Then Oracle can handle all the other stuff going on in the default pool.

Again, this isn't solving your actual parallelization or i/o problem, but may impact the system enough to affect it. You might want to think about the routes your data takes through luns, controllers to disks and back, including redo, undo, archiving and regular data, to see where the conflict arises that your parallelization has to wait for. That's outside of Oracle, of course.

>
> I don't think I'm swapping... the machine has 12GB of memory of which
> I've allocated
> 5GB to SGA .. I'm currently using about 3GB (DB_CACHE_SIZE=2GB,
> SHARED_POOL = 1GB) so I can dynamically increase the memory if I need
> to.
>
> My pga_aggregate_target is set to 1500MB so that still leaves me about
> 6GB of memory ...
>
> Here is some output from top. (This is a 12 cpu machine).
>
> load averages: 6.61, 6.92, 7.11
> 15:44:32
> 134 processes: 127 sleeping, 7 on cpu
> CPU states: 26.8% idle, 35.5% user, 12.1% kernel, 25.6% iowait, 0.0%
> swap
> Memory: 12G real, 6109M free, 5799M swap in use, 5867M swap free

You want to get swapinfo during the hangs. Or was that top during/just after a hang? I'm still not clear on exactly the symptoms you are calling a hang. No immediate echo on an xterm? Forms not filling with data? Browser icons spinning? sqlplus not responding?

>
> Thanks for all the great information.
> -peter

jg

--
@home.com is bogus.
Had to be continued on the next fan:
http://news.bbc.co.uk/1/hi/programmes/newsnight/4758636.stm