Re: Parallel dml and blocks

Thanks for the feedback. To date we've not maxed out the CPU (so we're wasting resources!), the cpu does not appear to be thrashing, nor are we causing any paging, so until I hit a problem, I will probably leave those settings alone.

Thanks for the tip on v$session_wait. I was just busy trying to see who was blocking who using a query against v$session and v$lock. Good point! If I see it again today, I'll use that to see where the problem lies with that.

"Christian Antognini" <christian.antognini_at_trivadis.com> wrote in message news:<415a510c$1_at_post.usenet.com>...
> **** Post for FREE via your newsreader at post.usenet.com ****
>
> Hi Denis
>
> "Scrier" <x3ce4unm3_at_yahoo.com> wrote in message
> news:6cc528c9.0409280402.ef10d23_at_posting.google.com...
> > I've been doing a lot of parallel updates lately, and monitoring them
> > in DbArtisan, which is a third party tool. I am seeing that the
> > sqlplus session which spawns the update becomes blocked, while the
> > parallel slaves are processing. In some cases, SMON and QMN0 become
> > blocked as well, and the database seems to come to a screeching halt.
>
> On which event are these processes waiting? (see v$session_wait)
>
> [snip]
>
> > I have
> > parallel_max_servers at 80 and am only using a degree of 8 on the
> > statements. This is a 4 cpu box, and at most I have three scripts
> > running, though there is other database activity at times in other
> > schemas.
>
> Mhmmm... with 4 CPU is very unlikely that you will able to use 80 parallel
> slaves! Usually, as starting point, I set parallel_max_servers at
> 4xcpu_count.
> In my opinion a degree of 8 is too high as well. In fact, when two sets of
> parallel slaves are needed by a single statement (e.g. there is a sort
> operation), 16 processes are started and will fight for 4 cpu. Since, in
> addition, you have 3 scripts running in parallel, this could even explain
> the freeze of the instance.
>
>
> Chris
>
>
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.usenet.com
> Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Received on Wed Sep 29 2004 - 11:41:25 CDT