Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Pradeep.

Asked: September 08, 2016 - 10:15 am UTC

Last updated: July 26, 2017 - 5:10 am UTC

Version: Oracle 11g 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Will Parallel hints works with CTAS selecting data from a Remote object?

Eg: In the below example, please tell me how parallel works?

create table table_name
parallel 4
as
select * from remote_table@dbLink;

and Connor said...

This can work, but you need to differentiate between source and target.

create table table_name parallel 4 as
select * from remote_table@dbLink;

will create parallel slaves on THIS database, but you will only have ONE connection to the remote database (assuming the remote table is not defined with a parallel degree).

But if you do (for example)

create table table_name parallel 4 as
select /*+ parallel(t) */ * from remote_table@dbLink t;

then you will get parallel slaves on both this database and remote database.

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you but need a clarification

Pradeep, September 09, 2016 - 2:22 am UTC

Hi Tom,
Thanks for the quick response, but as per the Oracle Docs,

"You cannot parallelize the query portion of a DDL or DML statement if it references a remote object. When you issue a parallel DML or DDL statement in which the query portion references a remote object, the operation is automatically run serially."

https://docs.oracle.com/cd/B10500_01/server.920/a96524/c20paral.htm

Not sure this is addressed in 11g or later versions
Connor McDonald
September 09, 2016 - 7:51 am UTC

Before I replied, I wasn't actually sure what would happen (which is why I love working on AskTom - its like free training). So I actually did a test, and saw the 'n' slaves on the remote site.

(My test was an 12c source, and 11g remote).

Will this work if there is a CLOB on the Source Table?

Amin Adatia, July 25, 2017 - 7:43 pm UTC

Will this parallel work if there is a CLOB Column on the sources table?
Connor McDonald
July 26, 2017 - 5:10 am UTC

SQL> create table t_local parallel 4
2 as select /*+ parallel(t,4) */ * from t@np121;

I get this for the execution plan

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | TQ/Ins |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT             |          |   999K|    43M| 59163   (1)| 00:00:03 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10001 |   999K|    43M| 57991   (1)| 00:00:03 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| T_LOCAL  |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |   999K|    43M| 57991   (1)| 00:00:03 |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE                    |          |   999K|    43M| 57991   (1)| 00:00:03 |  Q1,01 | PCWP |            |
|   6 |       PX SEND ROUND-ROBIN          | :TQ10000 |   999K|    43M| 57991   (1)| 00:00:03 |        | S->P | RND-ROBIN  |
|   7 |        REMOTE                      | T        |   999K|    43M| 57991   (1)| 00:00:03 |  NP121 | R->S |            |
----------------------------------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   7 - SELECT /*+ PARALLEL ("T",4) */ "X","C" FROM "T" "T" (accessing 'NP121' )


And if I run the command I see the following on the remote node before and during the execution

SQL> select idle_time_cur, busy_time_cur from V$PQ_SLAVE;

IDLE_TIME_CUR BUSY_TIME_CUR
------------- -------------
        25531             0
        25531             0
        25531             0
        25531             0
        25531             0
        25531             0
        25531             0
        25531             0
        25531             0
        25531             0
        25531             0
        25531             0
        25531             0
        25531             0
        25531             0
        25531             0

16 rows selected.

SQL> select idle_time_cur, busy_time_cur from V$PQ_SLAVE;

IDLE_TIME_CUR BUSY_TIME_CUR
------------- -------------
            0             0
            0             0
            0             0
            0             0
        25532             0
        25532             0
        25532             0
        25532             0
        25532             0
        25532             0
        25532             0
        25532             0
        25532             0
        25532             0
        25532             0
        25532             0
            0             0
            0             0
            0             0
            0             0

20 rows selected.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions