Wierd Parallel DDL Behaviour ?
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-lReceived on Thu Dec 15 2016 - 21:06:52 CET