Hello,
I am trying to understand the behaviour of session level and statement level settings for parallelism.
Test case
{code}
sqlplus <user>/<pwd>@db
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 16 01:25:38 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage
Management, OLAP,
Data Mining and Real Application Testing options
SQL> select * from v$mystat where rownum <= 1;
SID STATISTIC# VALUE
---------- ---------- ----------
278 0 0
SQL>
SQL>
SQL> alter session force parallel DDL;
Session altered.
SQL> alter session force parallel DML;
Session altered.
SQL> alter session force parallel query;
Session altered.
SQL>
SQL> create table t15 as select * from dba_objects union all select * from
dba_objects;
^C
{code}
This run hangs for a very long time. From another session, I can see about 300 sessions created and still running. I made a small change and re-ran as follows.
{code}
sqlplus <user>/<pwd>@db
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 16 01:29:34 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage
Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter session force parallel DDL;
Session altered.
SQL> alter session force parallel DML;
Session altered.
SQL> alter session force parallel query;
Session altered.
SQL>
SQL> set timing on
SQL>
SQL> select * from v$mystat where rownum <= 1;
SID STATISTIC# VALUE
---------- ---------- ----------
278 0 0
Elapsed: 00:00:00.50
SQL>
SQL> create */*+ parallel(4) */* table t15 as select * from dba_objects
union all select * from dba_objects;
Table created.
Elapsed: 00:00:08.02
{code}
The only change I made is put in parallel hint. Although I have never seen this way of hinting DDL statements, but this worked. I am not able to explain how this worked.
If I change the statement to CREATE TABLE T15 PARALLEL 4... this also hangs again with the same high number of sessions problem and never finishes.
I see this behaviour only with FORCE option. If I use ENABLE at session level (instead of FORCE) everything works fine.
From the oracle documentation it appears statement level directive always overrides session level, but in this case it doesn't seem to.
Anyone any ideas ?
Rgds,
Gokul