Re: Parallel dml and blocks

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