0

I have a problem in a query that runs parallel with the

"CREATE TABLE ... AS SELECT" statement,

but it doesn't run in parallel with

"INSERT INTO ... SELECT" query.

I have used below statement in my procedure

EXECUTE IMMEDIATE 'ALTER SESSION ENABLE/FORCE PARALLEL DML'

. It works well to "delete from " statement, but doesn't affect on insert with /*+parallel(30)*/ hint.

What kind of problem could it be and do other solutions exist to parallel insert? Thanks in advance.

Improve this question
3
  • 2
    Can you show full real execution plan or real time sql report? Commented Aug 30, 2020 at 8:40
  • I'm not sure what you mean. Are the create table as select and insert two different steps that you want to run concurrently? Are they populating the same table? How is your code structured? Commented Aug 30, 2020 at 8:42
  • Does your server have 30 CPUs sitting idle? Commented Aug 30, 2020 at 17:13

1 Answer 1

Reset to default
2

You don't explain very well your data model, so let me explain different possibilities regarding Oracle Parallel Execution. In the case of an insert/select statement, there are two operations that might or might not run in parallel. One is the select and the other one is the insert.

Test Case Tables

SQL> create table t ( id number , c1 varchar2(130) ) ;

Table created.

SQL> create table x as select object_id , object_name from dba_objects ;

Table created.

SQL> select count(*) from x ;

  COUNT(*)
----------
    172514

Scenario 1

Table has no parallel definition , no hints are informed and no alter session parallel has been enabled. The insert and the select will run in serial mode.

SQL> explain plan for
  2  insert into t select * from x ;

Explained.

SQL> select * from table(dbms_xplan.display) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2941724873

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |   194K|    14M|   172   (1)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | T    |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | X    |   194K|    14M|   172   (1)| 00:00:01 |
---------------------------------------------------------------------------------

Scenario 2

Hint parallel is used in the insert statement, table has no parallel definition and no alter session has been enabled. The query will run in serial mode, both insert and select.

SQL> explain plan for insert /*+parallel(t,10)*/ into t select * from x ;

Explained.

SQL>  select * from table(dbms_xplan.display) ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------Plan hash value: 2941724873

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |   194K|    14M|   172   (1)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | T    |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | X    |   194K|    14M|   172   (1)| 00:00:01 |
---------------------------------------------------------------------------------

Scenario 3

Hints are both used in select and insert. No parallel definition in any table and no alter session has been used. The insert will run in serial mode, but the select will run in parallel. ( Check notes at the end of explain plan )

SQL>  explain plan for insert /*+parallel(t,10)*/ into t select /*+parallel(x,10)*/ * from x ;

Explained.

SQL>  select * from table(dbms_xplan.display) ;

PLAN_TABLE_OUTPUT
----------------------------------------
Plan hash value: 3828196258

--------------------------------------------------------------------------------------

----------------------------
    | Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    ------------------------------------------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT         |          |   194K|    14M|   172   (1)| 00:00:01 |        |      |            |
    |   1 |  LOAD TABLE CONVENTIONAL | T        |       |       |            |          |        |      |            |
    |   2 |   PX COORDINATOR         |          |       |       |            |          |        |      |            |
    |   3 |    PX SEND QC (RANDOM)   | :TQ10000 |   194K|    14M|   172   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
    |   4 |     PX BLOCK ITERATOR    |          |   194K|    14M|   172   (1)| 00:00:01 |  Q1,00 | PCWC |            |
    |   5 |      TABLE ACCESS FULL   | X        |   194K|    14M|   172   (1)| 00:00:01 |  Q1,00 | PCWP |            |
    ------------------------------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 10 because of table property
   - PDML is disabled in current session

As you can see above in the explain plan, the select is running in parallel because of the hint. The reason is that, by default, enable parallel query is set for each session, so if you use a hint parallel in a select, the select will run in parallel.

Scenario 4

Hints are used both on insert and select, but this time we enable parallel dml. The tables have no parallel definition. The insert will run in parallel as well as the select. Check notes of Explain plan.

SQL>  alter session enable parallel dml ;

Session altered.

SQL> explain plan for insert /*+parallel(t,10)*/ into t select /*+parallel(x,10)*/ * from x ;

Explained.

SQL> select * from table(dbms_xplan.display) ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2234885790

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |   194K|    14M|    19   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |   194K|    14M|    19   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| T        |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR              |          |   194K|    14M|    19   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL             | X        |   194K|    14M|    19   (0)| 00:00:01 |  Q1,00 | PCWP |            |

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 10 because of table property

If you use the hint parallel on an insert operation when you have enabled parallel dml, the insert will run as a direct path statement, not as a conventional insert. Keep in mind that direct path operations are table-lock , so in this case the table will be locked for any other dml operation until the transaction is commited.

Scenario 5

You can also change the table parallel properties, then you don't need to use any hint. Only enabling parallel dml.

SQL> alter table t parallel 8 ;

Table altered.

SQL> alter table x parallel 8 ;

Table altered.

SQL> alter session enable parallel dml ;

Session altered.

SQL> explain plan for insert  into t select * from x ;

Explained.

SQL> select * from table(dbms_xplan.display) ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------Plan hash value: 2234885790

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |   194K|    14M|    24   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |   194K|    14M|    24   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| T        |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR              |          |   194K|    14M|    24   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL             | X        |   194K|    14M|    24   (0)| 00:00:01 |  Q1,00 | PCWP |            |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 8 because of table property

Keep also in mind some restrictions:

  • If the table where you are making insert has triggers enabled, the operation will not run in parallel.
  • You must always enable parallel DML if you want that the insert runs in parallel. In this case, either use a hint parallel or change the degree of the table. Hints used not properly will not throw any error, just they won't work.
  • If you run an insert/select in parallel, it is in most of the cases very important to run both operations in parallel, thereby the consumers of each QC can work collectively. If you run only the select in parallel, the consumers of the select will finally be idle meanwhile a single threat is running the insert.
  • This mode (enable parallel dml) is required because parallel DML and serial DML have different locking, transaction, and disk space requirements and parallel DML is disabled for a session by default. When parallel DML is disabled, no DML is executed in parallel even if the PARALLEL hint is used.

You can read more about restrictions on parallel dml here

Restrictions on Parallel DML

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

There is a typo in the hint in Scenario 2 parallel(a,10) - there is no alias a in the insert statement
@SayanMalakshinov , thank you. you are right. typo corrected

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.