RE: [External] Wierd Parallel DDL Behaviour ?
Date: Fri, 16 Dec 2016 03:49:58 +0000
Message-ID: <2FE2AA1C5F8DEC478F58DF8DD32BA6370CF5213F_at_HKWPIPXMB03C.zone1.scb.net>
The first statement explicitly specifies the Degree to be used for the CREATE operation. The second statement specifies the Degree associated with the table.
>> The above 2 statements are not behaving the same.
What difference do you find between the two operations ? How do you identify the difference ?
Many questions arise from a misinterpretation or incomplete view of information.
Hemant K Chitale
From: Gokul Gopal [mailto:gokulkumar.gopal_at_gmail.com]
Sent: Friday, December 16, 2016 11:20 AM
To: Chitale, Hemant K; Oracle-L Group
Subject: RE: [External] Wierd Parallel DDL Behaviour ?
Hi Hemant,
CREATE /* PARALLEL (4) */ TABLE T15.... CREATE TABLE T15 PARALLEL 4 The above 2 statements are not behaving the same. If parallelism is applied in either cases then both the above statements must be equivalent ?
Gokul Gopal
From: Chitale, Hemant K<mailto:Hemant-K.Chitale_at_sc.com> Sent: 16/12/2016 08:27
To: Gokul Gopal<mailto:gokulkumar.gopal_at_gmail.com>; Oracle-L Group<mailto:oracle-l_at_freelists.org> Subject: RE: [External] Wierd Parallel DDL Behaviour ? As a Hint, the Degree is specified in brackets.
Thus, when you say /*+ PARALLEL */ and want to specify a specific Degree , put it into brackets -- /*+ PARALLEL (4) */
Hemant K Chitale
From: Gokul Gopal [mailto:gokulkumar.gopal_at_gmail.com]
Sent: Friday, December 16, 2016 10:49 AM
To: Chitale, Hemant K; Oracle-L Group
Subject: RE: [External] Wierd Parallel DDL Behaviour ?
Thanks Hemant.
Interestingly, if I remove /*+ parallel(4) */ and replace it with "parallel 4" at the statement level, it stops working. Any idea why this might be happening?
Gokul Gopal
From: Chitale, Hemant K<mailto:Hemant-K.Chitale_at_sc.com> Sent: 16/12/2016 07:09
To: gokulkumar.gopal_at_gmail.com<mailto:gokulkumar.gopal_at_gmail.com>; Oracle-L Group<mailto:oracle-l_at_freelists.org> Subject: RE: [External] Wierd Parallel DDL Behaviour ?
>> create /*+ parallel(4) */ table t15
Since at least 7.3.4
See the CREATE TABLE statement and the PARALLEL Clause in the 7.3.4 documentation available online http://docs.oracle.com/pdf/A32538_1.pdf (back then, Parallel Query was a licensed Option in the database)
Hemant K Chitale
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Gokul Kumar Gopal
Sent: Friday, December 16, 2016 4:07 AM
To: Oracle-L Group
Subject: [External] Wierd Parallel DDL Behaviour ?
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;
[The entire original message is not included.]
This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 16 2016 - 04:49:58 CET