Wierd Parallel DDL Behaviour ?

From: Gokul Kumar Gopal <gokulkumar.gopal_at_gmail.com>
Date: Fri, 16 Dec 2016 01:36:52 +0530
Message-ID: <CAEVfUVkGMgQoHRvAGqJBHuRLUP2n2bGA7zb-rxibXYyffcUwrA_at_mail.gmail.com>



Hello,

I am trying to understand the following run..

sqlplus <user>/<pwd>_at_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

At this point, I had to stop the process as it created 289 sessions and was still running..

I made some changes and re-ran..

sqlplus <user>/<pwd>_at_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

The highlighted part is the only changed I made, but I cannot explain why this worked with no additional sessions.. I have never seen this syntax mentioned anywhere but a casual try worked.

Does anyone know how this works ?

Rgds,
Gokul

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 15 2016 - 21:06:52 CET

Original text of this message