Insert select parallel hints 正确写法 parallel ()

select /*+ parallel 16 */ 不对，oracle自动并行5

select /*+ parallel （16） */  union all select /*+ parallel  16  */   不并行 

select /*+ parallel （16） */   并行16

1. Parallel hints 不生效

2.

3.

How to use Parallel Hints in INSERT SELECT Queries (Doc ID 3027686.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

GOAL

 The conventional way of enabling parallel DML is to enable it with an ALTER SESSION command as explained in the documentation. This command enables parallel DML for the session and all subsequent DML statements are candidates for parallel execution provided that the rules and restrictions for parallel DML are met.

12c introduces a new way of enabling and disabling parallel DML. Rather than enabling or disabling it session-wise you can enable or disable it statement-wise using new hints. The hint ENABLE_PARALLEL_DML enables parallel DML for the statement, and the hint DISABLE_PARALLEL_DML disables it for the statement. All rules and restrictions for parallel DML still apply, these hints are only alternatives to the related ALTER SESSION commands.

Along with 'ENABLE_PARALLEL_DML' Hint, we have to add another hint called 'parallel(DOP)' in the DML statements where you wish run those (INSERT SELECT) DML queries in parallel.

SOLUTION

See the below cases to know about the correct usage of those hints (ENABLE_PARALLEL_DML & PARALLEL)

Incorrect Usage
 

Case1:

insert into big_table select /*+ parallel(2) */* from big_table

select * from table(dbms_xplan.display_cursor('10hnsc76wgw5h', null, 'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +COST +BYTES +PARALLEL +PARTITION +REMOTE +OUTLINE +HINT_REPORT +NOTE'))

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 10hnsc76wgw5h, child number 0
-------------------------------------
insert into big_table select /*+ parallel(2) */* from big_table

Plan hash value: 4182993142

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 101K(100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | BIG_TABLE | | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 20M| 2098M| 101K (1)| Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 20M| 2098M| 101K (1)| Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | BIG_TABLE | 20M| 2098M| 101K (1)| Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

0 - STATEMENT
- parallel(2)

Note
-----
- Degree of Parallelism is 2 because of hint
- PDML is disabled in current session

Note: Missed the 'ENABLE_PARALLEL_DML' and parallel Hints in INSERT QUERY, thats why, we are seeing the note as 'PDML is disabled in current session'. Parallel is applied only for SELECT query.

Case2:

insert /*+ parallel(4) */ into big_table select /*+ parallel(2) */ * from big_table;

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID d1un26571q8zv, child number 0
-------------------------------------
insert /*+ parallel(4) */ into big_table select /*+ parallel(2) */ * from big_table

Plan hash value: 4185194225

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 182K(100)| | | |
| 1 | LOAD AS SELECT | BIG_TABLE | | | | 2070K| 2070K| 2070K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 20M| 2098M| 182K (1)| 256K| 256K| |
| 3 | TABLE ACCESS FULL | BIG_TABLE | 20M| 2098M| 182K (1)| | | |
---------------------------------------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

0 - STATEMENT
U - parallel(2) / hint conflicts with another in sibling query block
U - parallel(4) / hint conflicts with another in sibling query block

Note: Missed the 'ENABLE_PARALLEL_DML' Hint and parallel hints are with different DOP's. DOP's should be maintained same for both INSERT and SELECT queries.

Case3:

insert /*+ parallel(2) */ into big_table select /*+ parallel(2) */ * from big_table;

select * from table(dbms_xplan.display_cursor('1j10mw8mfssrv', null, 'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +COST +BYTES +PARALLEL +PARTITION +REMOTE +OUTLINE +HINT_REPORT +NOTE'))

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1j10mw8mfssrv, child number 0
-------------------------------------
insert /*+ APPEND parallel(2) */ into big_table select /*+ parallel(2) */ * from big_table;

Plan hash value: 475914122

----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| TQ |IN-OUT| PQ Distrib | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 101K(100)| | | | | | |
| 1 | LOAD AS SELECT | BIG_TABLE | | | | | | | 2070K| 2070K| 2070K (0)|
| 2 | PX COORDINATOR | | | | | | | | 73728 | 73728 | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 20M| 2098M| 101K (1)| Q1,00 | P->S | QC (RAND) | | | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 20M| 2098M| 101K (1)| Q1,00 | PCWC | | 256K| 256K| 1792K (0)|
| 5 | PX BLOCK ITERATOR | | 20M| 2098M| 101K (1)| Q1,00 | PCWC | | | | |
|* 6 | TABLE ACCESS FULL | BIG_TABLE | 20M| 2098M| 101K (1)| Q1,00 | PCWP | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------

0 - STATEMENT
U - parallel(2) / duplicate hint
- parallel(2)

Note
-----
- Degree of Parallelism is 2 because of hint
- PDML is disabled in current session

 Note: Missed the 'ENABLE_PARALLEL_DML' Hint thats why we are seeing the note as 'PDML is disabled in current session'. Parallel is applied only at SELECT query.

Correct Usage

Case4:

insert /*+ APPEND parallel(2) enable_parallel_dml */ into big_table select /*+ parallel(2) */ * from big_table;

20000000 rows created.

Elapsed: 00:00:56.98

select * from table(dbms_xplan.display_cursor('bd5xyz6g5t3ts', null, 'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +COST +BYTES +PARALLEL +PARTITION +REMOTE +OUTLINE +HINT_REPORT +NOTE'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bd5xyz6g5t3ts, child number 0
-------------------------------------
insert /*+ APPEND parallel(2) enable_parallel_dml */ into big_table
select /*+ parallel(2) */ * from big_table

Plan hash value: 3773427756

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 101K(100)| | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 20M| 2098M| 101K (1)| Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| BIG_TABLE | | | | Q1,00 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 20M| 2098M| 101K (1)| Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 20M| 2098M| 101K (1)| Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | BIG_TABLE | 20M| 2098M| 101K (1)| Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------

Note
-----
- Degree of Parallelism is 2 because of hint

Note: Here we see that the notes section does not mention parallel DML as disabled and the LOAD operation is under the PX COORDINATOR, both of these indicate that parallel DML is enabled.

These two new hints are available starting with 12.1.0.1 and can be used regardless of the value of the OPTIMIZER_FEATURES_ENABLE parameter. They can be used in INSERT, UPDATE, DELETE, and MERGE statements.