RE: Parallel index creates causing blocking lock alerting

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 25 Oct 2012 11:28:17 -0400
Message-ID: <04f001cdb2c5$5c480e60$14d82b20$_at_rsiz.com>

If you have "a slew" of them, then why not run twice as many of them at a time with only one thread per create?

The advantage of parallel <pretty much anything> is to use more resources to get a particular job done in less elapsed time. When you have a lot of jobs to do, why take on the overhead of parallel?

If we are talking about multiple schema, and you have multiple temp areas on independent i/o you can create additional efficiencies there as well. If you have multiple indexes to create on one table, you may be able to accelerate that as well by creating multiple indexes on the same table in parallel. Now you might not get sga double dipping without playing games with KEEP rotation and direct read might kick in anyway, but you still might win from device and SAN cache.

So lose the locks and make it all faster too.

Good luck,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse
Sent: Thursday, October 25, 2012 10:03 AM To: oracle-l_at_freelists.org
Subject: Re: Parallel index creates causing blocking lock alerting

1+ week ago, I wrote:

> After upgrading to 11.2.0.3 (64-bit AIX 5.3), during index creates
> with DOP
> 2 on our dev DB, EM12c goes nuts in alerting me to blocking locks. It
> looks like the issue is in the DB:
>
> There are several messages in DBA_ALERT_HISTORY with a REASON of
> "Session nnn is blocking 0 other sessions". If it's blocking zero
> sessions, then why the message? The only thing of consequence
> happening at the time of these messages on a Friday night in this dev
> DB is a slew of CREATE INDEX...PARALLEL 2 statements running
> sequentially (one at a time). ASH and AWR reports seem to confirm this.

No one else seeing this? Oracle tells me that this is normal. Yes, I saw the behavior in 10.1 as well, but that version didn't feel the need to wake me up about it...

[sigh] I think I preferred my old unsupported bugs rather than the supported ones I have now.

Rich

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 25 2012 - 17:28:17 CEST