Several methods for Oracle to turn on parallelism

Parallel execution is the simultaneous opening of multiple processes/threads to complete the same task, and each process/thread executing in parallel consumes additional hardware resources, so the essence of parallel execution is to reduce the execution time with additional hardware resource consumption. The additional hardware resource consumption here refers to the additional utilization of multiple database nodes on the database server, from several CPU, memory, I/O channels, and even a RAC environment.

Here's a summary of some of the approaches to open parallelism in Oracle

1. Change the parallelism of the target table

There are two ways to modify the parallelism of the target table

• ALTER TABLE table_name parallel;

• ALTER TABLE table_name parallel n;

Where Method 1 is to change the degree of parallelism of the specified table to the default value, Method 2 is to modify the degree of parallelism of the specified table to n;

View table emp Current degree of parallelism is 1

[Email protected]>select table_name,degree from user_tables where table_name= ' EMP '; TABLE_NAME degree----------------------------------------EMP1

To access the table EMP with the default degree of parallelism

[email protected]>alter table emp parallel; Table altered. [Email protected]>select table_name,degree from user_tables where table_name= ' EMP '; Table_name       degree------------------------------ ----------EMP   default[email protected]>set autotrace traceonly[email protected]>select  * from emp;14 rows selected. Execution plan----------------------------------------------------------plan hash value:  2873591275----------------------------------------------------------------------------------------------------- ---------| id  | operation     | name| rows| bytes  | Cost  (%CPU) | time|    tq  | in-out| pq distrib |--------------------------------------------------------------------------- -----------------------------------|   0 | select statement      | |     14 |  1218 |     2    (0) |  00:00:01 | | |      | |    1 |  px coordinator      | | | |      | |  | |      | |    2 |   PX SEND QC  (RANDOM) | :tq10000 |     14 |  1218 |     2    (0) |  00:00:01 |  q1,00 | p->s | qc  (RAND)   | |    3 |    px block iterator | |     14 |  1218 |     2    (0) |  00:00:01 |  q1,00 |&nbsp pcwc |     | |    4 |     TABLE ACCESS FULL| EMP|     14 |  1218 |     2    (0) |  00:00:01 |  q1,00 | pcwp |     |----------------------- ---------------------------------------------------------------------------------------.....

From the above implementation plan can be seen, walking is the table EMP Full table scan, PX ... That means walking in parallel.

The default parallelism algorithm is as follows:

Default degree of parallelism =parallel_threads_per_cpu*cpu_count

If you want to turn on the table 8 degrees of parallelism is performed: ALTER TABLE EMP parallel 8;

[Email protected]>select table_name,degree from user_tables where table_name= ' EMP '; TABLE_NAME degree----------------------------------------EMP default[email protected]>alter table emp Parallel 8; Table altered. [Email protected]>select table_name,degree from user_tables where table_name= ' EMP '; TABLE_NAME degree----------------------------------------EMP8

2. Using parallel hint

There are a number of parallel hint that can be used to control whether parallel and specified parallelism are enabled

1)/*+ parallel (Table[,degree]) */#用于指定并行度去访问指定表, if no degree of parallelism is specified degree, use the Oracle default degree of parallelism

2)/*+ noparallel (table) */#对指定表不使用并行访问

3)/*+ Parallel_index (Table[,index[,degree]) */#对指定的分区索引以指定的并行度去做并行范围扫描

4)/*+ No_parallel_index (Table[,index]) */#对指定的分区索不使用并行访问

5)/*+ Pq_distribute (table,out,in) */#对指定表以out/in the way specified to pass the data, here Out/in value can be any one of hash/none/broadcast/partition, such as/*+ Pq_distribute (table,none,partition) */

Change the table EMP back to a degree of parallelism of 1

[Email protected]>alter table emp Noparallel; Table altered. [Email protected]>select table_name,degree from user_tables where table_name= ' EMP '; TABLE_NAME degree----------------------------------------EMP1

Using parallel hint to perform previous SQL

[Email protected]>select /*+ parallel (EMP)  */* from emp;14 rows  Selected. Execution plan----------------------------------------------------------plan hash value:  2873591275----------------------------------------------------------------------------------------------------- ---------| id  | operation     | name| rows| bytes  | Cost  (%CPU) | time|    tq  | in-out| pq distrib |--------------------------------------------------------------------------- -----------------------------------|   0 | select statement      | |     14 |  1218 |     2    (0) |  00:00:01 | | |      | |    1 |  px coordinator      | | | |      | |  | |      | |    2 |   PX SEND QC  (RANDOM) | :tq10000 |     14 |  1218 |     2    (0) |  00:00:01 |  q1,00 | p->s | qc  (RAND)   | |    3 |    px block iterator | |     14 |  1218 |     2    (0) |  00:00:01 |  q1,00 | pcwc |     | |    4 |     TABLE ACCESS FULL| EMP|     14 |  1218 |     2    (0) |  00:00:01 |  q1,00 | pcwp |     |-------------------------------------------------------------------------------------------------------------- 

As can be seen from the implementation plan above, the parallel

3. Use the ALTER session command

Using the ALTER session command, you can force parallel queries or parallel DML to be enabled in the current session. If a parallel query or parallel DML is forced to be enabled, it means that the point at which the alter session command is forced to open parallel will begin, and all subsequent SQL executed in this session is executed in a parallel manner. There are four ways to force parallel opening in the current session

1) Alter session parallel query

Enforce parallel queries in the current session, no parallelism specified, Oracle uses default degree of parallelism

2) Alter session parallel query parallel n

Forces the parallel query to open in the current session and specifies that the degree of parallelism is n

3) Alter session parallel DML

Enforce parallel DML in the current session, no parallelism specified, Oracle uses default degree of parallelism

4) Alter session parallel DML parallel n

Forces parallel DML to open in the current session and specifies that the degree of parallelism is n

The table emp parallelism is still 1, forcing parallel opening in session:

[Email protected]>select table_name,degree from user_tables where table_name= ' EMP '; Table_name       degree------------------------------ ----------emp1[ email protected]>set autotrace traceonly[email protected]>alter session  force parallel query; Session altered. [Email protected]>select * from emp;14 rows selected. Execution plan----------------------------------------------------------plan hash value:  2873591275----------------------------------------------------------------------------------------------------- ---------| id  | operation     | name| rows| bytes  | Cost  (%CPU) | time|    tq  | in-out| pq distrib |--------------------------------------------------------------------------- -----------------------------------|   0 | select statement      ||     14 |  1218 |     2    (0) |  00:00:01 | | |      | |    1 |  px coordinator      | | | |      | |  | |      | |    2 |   PX SEND QC  (RANDOM) | :tq10000 |     14 |  1218 |     2    (0) |  00:00:01 |  q1,00 | p->s | qc  (RAND)   | |    3 |    px block iterator | |     14 |  1218 |     2    (0) |  00:00:01 |  q1,00 |&nBsp pcwc |     | |    4 |     TABLE ACCESS FULL| EMP|     14 |  1218 |     2    (0) |  00:00:01 |  q1,00 | pcwp |     |----------------------- ---------------------------------------------------------------------------------------......

From the execution plan, it can be seen that parallelism is taken.

Cancel the current session parallel using the following statement alter session disable parallel query;

[email protected]>alter session disable parallel query; Session altered. [Email protected]>select * from emp;14 rows selected. Execution plan----------------------------------------------------------plan hash value:  3956160932--------------------------------------------------------------------------| id  |  Operation  | Name | Rows  | Bytes | Cost  (%CPU) |  time |--------------------------------------------------------------------------|    0 | select statement  | |    14 |  1218  |     3    (0) | 00:00:01 | |    1 |  TABLE ACCESS FULL| EMP  |     14 |  1218 |     3  &nbSP; (0) | 00:00:01 |--------------------------------------------------------------------------...... 

4, 11GR2 automatic parallel

Oracle introduced automatic parallelism (auto DOP) in 11GR2, and automatic parallel opening is controlled by the parameter parallel_degree_policy, whose default value is manual, that is, automatic parallelism is not turned on by default. If automatic parallelism is turned on by changing the value of Parallel_degree_policy, then the execution of the SQL executed is serially or in parallel, and the degree of parallelism of parallel execution is determined by Oracle automatically.

[Email protected]>select table_name,degree from user_tables where table_name  in  (' EMP ', ' emp_temp '); table_name    Degree------------------------------------------------------------------------------------------ -------- ----------------------------------------------------emp    1emp_temp     1[email protected]>alter session set parallel_degree_policy=AUTO; Session altered. [Email protected]>set autotrace traceonly[email protected]>select * from  emp;14 rows selected. Execution plan----------------------------------------------------------plan hash value:  3956160932--------------------------------------------------------------------------| id  |  Operation  | Name | Rows  | Bytes | Cost  (%CPU) |  time |--------------------------------------------------------------------------|   0 | select  statement  | |    14 |  1218 |      3    (0) | 00:00:01 | |    1 |  TABLE ACCESS FULL| EMP  |     14 |  1218 |     3    (0) | 00:00:01  |--------------------------------------------------------------------------...... [Email protected]>select * from emp_temp;1835008 rows selected. Execution plan----------------------------------------------------------plan hash value:  2661083444----------------------------------------------------------------------------------------------------- ---------| id  | operation     | name| rows| bytes  |&nbSp cost  (%CPU) | time|    tq  | in-out| pq distrib |--------------------------------------------------------------------------- -----------------------------------|   0 | select statement      | |   1835K|    66M|  1683    (1) | 00:00:21 |  | |      | |    1 |  px coordinator      | | | |      | |  | |      | |    2 |   PX SEND QC  (RANDOM) | :tq10000 |   1835K|    66M|  1683    (1) | 00:00:21 |   Q1,00 | P->S | QC  (RAND)   | |    3 |    px block iterator | |   1835k|    66m|  1683    (1) | 00:00:21 |  q1,00  | pcwc |     | |    4 |     table access full| emp_temp |   1835K|    66M|  1683    (1) | 00:00:21 |   q1,00 | pcwp |     |-------------------------------------- ------------------------------------------------------------------------......

From the above output can be seen that the table EMP and emp_temp parallelism is 1, but two tables of data is very large correlation, EMP only 14 data, Emp_temp has 1,835,008 data. At execution time, Oracle chooses to perform differently, the EMP is serially executed, and the emp_temp is executed in parallel.

Refer to Oracle-based SQL optimization

Official Document: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2013.htm#i2231814

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1908054

Several methods for Oracle to turn on parallelism