RE: Parallel slaves blocking each other during CTAS with Parallel Clause

> When we're creating a table using CTAS with parallel clause, we observed the
> parallel slave sessions are blocking each other. We are using this temp.
> table for the report purpose and before querying the temp table, it will be
> created with the required data.

I just saw this thread. I'm very familiar with this issue and I believe it's a bug in 11.2, as I only started seeing this after upgrading from 10.1.  I've had SRs opened (and closed) since 10/2012 trying to deal with this.

On my 11.2.0.3.3 DB on AIX, I can easily reproduce this parallel thread blocking with a simple statement:

CREATE INDEX TEST01.T1IDX ON TEST01.LGTABLE (COL01, COL02 DESC, COL03) COMPUTE STATISTICS NOLOGGING TABLESPACE TEST01I PARALLEL 2; (names have been changed to protect the innocent)

LGTABLE is about 35M rows over 40GB. The block shows up in DBA_ALERT_HISTORY as "Session nnnn is blocking 0 other sessions", but polling V$LOCK every 3 seconds, I'm not able to capture it. The blocking is consistently repeatable for me, but I'm not sure if Support can duplicate it. This is across all parallel activity including some PQs.

Since it shows up in DBA_ALERT_HISTORY (or, more correctly, DBA_OUTSTANDING_ALERTS), EM12c goes nuts with alerting when there's parallel activity. This bug has caused me to shut down blocking lock checking from EM. Nice. We've had a couple of cascading lock issues in Production since (one major) because of the lack of monitoring...[sigh]

My original SR was in EM, where they determined (correctly, IMHO) that it's a DB issue and an SR was opened in that group. They said it's working correctly, time passed, and Support finally closed that SR even though I requested it stay open. They opened a new SR to track the hapless bug 15996428, which has stagnated in Development.

If anyone has any info that might be helpful with what appears to me to be a major bug, it would be most appreciated.

Rich

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