Shulou(Shulou.com)06/01 Report--

Parallel execution is to open multiple processes / threads to complete the same task at the same time, and each process / thread executed in parallel will consume additional hardware resources. therefore, the essence of parallel execution is to shorten the execution time by consuming additional hardware resources. The additional hardware resource consumption here refers to the additional utilization of multiple CPU, memory, slave I _ Unix O channels on the database server, or even multiple database nodes in the RAC environment.

Here are some ways to turn on 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

Method 1 is to change the parallelism of the specified table to the default value, and method 2 is to change the parallelism of the specified table to n

View table EMP current parallelism is 1

Scott@TEST > select table_name,degree from user_tables where table_name='EMP';TABLE_NAME DEGREE---EMP 1

Want to access table EMP with default parallelism

Scott@TEST > alter table emp parallel;Table altered.scott@TEST > select table_name,degree from user_tables where table_name='EMP';TABLE_NAME DEGREE---EMP DEFAULTscott@TEST > set autotrace traceonlyscott@TEST > select * from emp 14 rows selected.Execution Plan---Plan hash value: 2873591275 Murray- -| 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 | Q1Laver 00 | P-> S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 14 | 1218 | 2 | (0) | 00:00:01 | Q1Person00 | PCWC | 4 | TABLE ACCESS FULL | EMP | 14 | 1218 | 2 (0) | 00:00:01 | Q1Power00 | PCWP | |-- -.

As can be seen from the above implementation plan, it is a full table scan of table EMP, PX.... It means walking in parallel.

The algorithm for the default parallelism is as follows:

Default parallelism = parallel_threads_per_cpu*cpu_count

If you want to enable 8 parallelism on the table, execute: alter table emp parallel 8

Scott@TEST > select table_name,degree from user_tables where table_name='EMP';TABLE_NAME DEGREE---EMP DEFAULTscott@TEST > alter table emp parallel 8 / table altered.scott@TEST > select table_name,degree from user_tables where table_name='EMP' TABLE_NAME DEGREE---EMP 8

2. Use parallel Hint

There are some parallel Hint that can be used to control whether parallelism is enabled and to specify parallelism.

1) / * + parallel (table [, degree]) * / # is used to specify the parallelism to access the specified table. If no parallelism degree is specified, the Oracle default parallelism is used.

2) / * + noparallel (table) * / # do not use parallel access to the specified table

3) / * + parallel_index (table [, index [, degree]]) * / # perform parallel range scanning on the specified partition index with a specified degree of parallelism

4) / * + no_parallel_index (table [, index]) * / # No parallel access is used for the specified partition index

5) / * + pq_distribute (table,out,in) * / # pass data to the specified table in the way specified by out/in, where the value of out/in can be any of the HASH/NONE/BROADCAST/PARTITION, such as / * + pq_distribute (table,none,partition) * /

Change the table EMP back to 1 parallelism

Scott@TEST > alter table emp noparallel;Table altered.scott@TEST > select table_name,degree from user_tables where table_name='EMP';TABLE_NAME DEGREE---EMP 1

Use parallel Hint to execute the previous SQL on

Scott@TEST > select / * + parallel (emp) * / * from emp 14 rows selected.Execution Plan---Plan hash value: 2873591275 Murray- -| 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 | Q1Laver 00 | P-> S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 14 | 1218 | 2 | (0) | 00:00:01 | Q1Person00 | PCWC | 4 | TABLE ACCESS FULL | EMP | 14 | 1218 | 2 (0) | 00:00:01 | Q1Power00 | PCWP | |--

As can be seen from the above implementation plan, it is 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 parallel queries or parallel DML are forcibly enabled, it means that from the point in time when the alter session command is executed to force parallelism, all subsequent SQL execution in this session will be executed in parallel. There are four ways to force parallelism in the current session

1) alter session parallel query

Force parallel queries to be enabled in the current session. No parallelism is specified. Oracle uses the default parallelism.

2) alter session parallel query parallel n

Forces parallel queries to be opened in the current session and specifies a degree of parallelism of n

3) alter session parallel dml

Force parallel DML to be enabled in the current session. No parallelism is specified. Oracle uses the default parallelism.

4) alter session parallel dml parallel n

Force parallel DML to be turned on in the current session, and specify a parallelism of n

Table EMP parallelism is still 1, force parallelism to be enabled in session:

Scott@TEST > select table_name,degree from user_tables where table_name='EMP';TABLE_NAME DEGREE---EMP 1scott@TEST > set autotrace traceonlyscott@TEST > alter session force parallel query;Session altered.scott@TEST > select * from emp 14 rows selected.Execution Plan---Plan hash value: 2873591275 Murray- -| 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 | Q1Laver 00 | P-> S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 14 | 1218 | 2 | (0) | 00:00:01 | Q1Person00 | PCWC | 4 | TABLE ACCESS FULL | EMP | 14 | 1218 | 2 (0) | 00:00:01 | Q1Power00 | PCWP | |-- -.

It can be seen from the implementation plan that we are going in parallel.

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

Scott@TEST > alter session disable parallel query;Session altered.scott@TEST > select * from emp 14 rows selected.Execution Plan---Plan hash value: 3956160932 Murray- -| 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 |- -.

4. Automatic parallelism of 11gR2

Oracle introduces Auto DOP in 11gR2. Automatic parallelism is controlled by the parameter parallel_degree_policy, and its default value is MANUAL, that is, automatic parallelism is not enabled by default. If automatic parallelism is turned on by changing the value of PARALLEL_DEGREE_POLICY, it is up to Oracle to automatically determine whether the subsequent SQL execution is serial or parallel, and how parallel the parallel execution is.

Scott@TEST > select table_name,degree from user_tables where table_name in ('EMP','EMP_TEMP') TABLE_NAME DEGREE -EMP 1EMP_TEMP 1scott@TEST > alter session set parallel_degree_policy=AUTO Session altered.scott@TEST > set autotrace traceonlyscott@TEST > select * from emp 14 rows selected.Execution Plan---Plan hash value: 3956160932 Murray- -| 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 |- -.Scott @ TEST > select * from emp_temp 1835008 rows selected.Execution Plan---Plan hash value: 2661083444 Murray- -| Id | Operation | Name | Rows | Bytes | 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 | Q1Mague 00 | P-> S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 1835K | 66m | 1683 (1) | 00:00:21 | Q1Mague 00 | | | PCWC | | 4 | TABLE ACCESS FULL | EMP_TEMP | 1835K | 66m | 1683 (1) | 00:00:21 | Q1Magin00 | PCWP | |- -.

From the output above, we can see that the parallelism of tables EMP and EMP_TEMP is 1, but the amount of data of the two tables is highly related. EMP has only 14 pieces of data and EMP_TEMP has 1835008 pieces of data. When executing, Oracle chooses different execution methods. EMP executes serially, while EMP_TEMP executes in parallel.

Refer to "SQL Optimization based on Oracle"

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.