并行执行的本质就是以额外的硬件消耗来换取目标SQL执行时间的缩短。当我们在对目标SQL进行优化时,使用统计信息,Hint,改写SQL等方法都不能缩短目标SQL的执行时间时,这个时候就需要考虑使用并行了…
1、默认并行
"""oracle数据库默认情况下不开启并行"""
SCOTT@TNS_PDB01>show parameter parallel_degree_policy;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string MANUAL
SCOTT@TNS_PDB01>show parameter parallel_threads_per_cpu;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 1
SCOTT@TNS_PDB01>show parameter cpu_count;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 1
oracle数据库默认并行度 = cpu_count * parallel_threads_per_cpu
2、如何开启并行
1)开启并行查询
SCOTT@TNS_PDB01>create table t1 as select * from dba_objects;
Table created.
SCOTT@TNS_PDB01>select count(*) from t1;
COUNT(*)
----------
72509
SCOTT@TNS_PDB01>select table_name,degree from dba_tables where table_name = 'T1' and owner = 'SCOTT';
TABLE_NAME DEGREE
--------------- ----------
T1 1
"""表T1开启并行,并行度为默认并行度"""
SCOTT@TNS_PDB01>alter table t1 parallel;
Table altered.
SCOTT@TNS_PDB01>select table_name,degree from dba_tables where table_name = 'T1' and owner = 'SCOTT';
TABLE_NAME DEGREE
--------------- ----------
T1 DEFAULT
SCOTT@TNS_PDB01>select slave_name,status,sessions from v$pq_slave;
SLAV STATUS SESSIONS
---- ---------- ----------
P000 IDLE 5
P001 IDLE 5
SCOTT@TNS_PDB01>select count(*) from t1;
COUNT(*)
----------
72509
SCOTT@TNS_PDB01>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
------------------------------------------------------------ ------------- ------------- ----------
select count(*) from t1 5bc0v4my7dvr5 1 1
SCOTT@TNS_PDB01>select * from table(dbms_xplan.display_cursor(sql_id => '5bc0v4my7dvr5',cursor_child_no => 0,format => 'advanced'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID 5bc0v4my7dvr5, child number 0
-------------------------------------
select count(*) from t1
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 393 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 72509 | 393 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| T1 | 72509 | 393 (1)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT()[22]
2 - SYS_OP_MSR()[10]
3 - (#keys=0) SYS_OP_MSR()[10]
4 - (#keys=0) SYS_OP_MSR()[10]
5 - (rowset=1019)
6 - (rowset=1019)
Note
-----
"""可以看出默认并行度为1"""
- automatic DOP: Computed Degree of Parallelism is 1 because of degree limit
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h></
f></q>
64 rows selected.
SCOTT@TNS_PDB01>select slave_name,status,sessions from v$pq_slave;
SLAV STATUS SESSIONS
---- ---------- ----------
P000 IDLE 5
P001 IDLE 5
"""开启表T1并行度,并执行并行度为2"""
SCOTT@TNS_PDB01>alter table t1 parallel 2;
Table altered.
SCOTT@TNS_PDB01>select table_name,degree from dba_tables where table_name = 'T1' and owner = 'SCOTT';
TABLE_NAME DEGREE
--------------- ----------
T1 2
SCOTT@TNS_PDB01>select count(*) from t1;
COUNT(*)
----------
72509
SCOTT@TNS_PDB01>select * from table(dbms_xplan.display_cursor(sql_id => null,cursor_child_no => null,format => 'advanced'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID 5bc0v4my7dvr5, child number 0
-------------------------------------
select count(*) from t1
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 218 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 72509 | 218 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| T1 | 72509 | 218 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT()[22]
2 - SYS_OP_MSR()[10]
3 - (#keys=0) SYS_OP_MSR()[10]
4 - (#keys=0) SYS_OP_MSR()[10]
5 - (rowset=1019)
6 - (rowset=1019)
Note
-----
"""可以看出并行度为2"""
- Degree of Parallelism is 2 because of table property
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h></
f></q>
64 rows selected.
"""查询表V$PQ_SLAVE查看并行会话增加了2个"""
SCOTT@TNS_PDB01>select slave_name,status,sessions from v$pq_slave;
SLAV STATUS SESSIONS
---- ---------- ----------
P000 IDLE 5
P001 IDLE 5
SCOTT@TNS_PDB01>select slave_name,status,sessions from v$pq_slave;
SLAV STATUS SESSIONS
---- ---------- ----------
P000 IDLE 6
P001 IDLE 6
"""使用HINT来使用并行查询"""
SCOTT@TNS_PDB01>alter table t1 noparallel;
Table altered.
SCOTT@TNS_PDB01>select table_name,degree from dba_tables where table_name = 'T1' and owner = 'SCOTT';
TABLE_NAME DEGREE
--------------- ----------
T1 1
SCOTT@TNS_PDB01>select /*+ parallel(t1 4) */count(*) from t1;
COUNT(*)
----------
72509
SCOTT@TNS_PDB01>select * from table(dbms_xplan.display_cursor(sql_id => null,cursor_child_no => null,format => 'advanced'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID 9prj7yaw3xvx6, child number 0
-------------------------------------
select /*+ parallel(t1 4) */count(*) from t1
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 109 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 72509 | 109 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| T1 | 72509 | 109 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT()[22]
2 - SYS_OP_MSR()[10]
3 - (#keys=0) SYS_OP_MSR()[10]
4 - (#keys=0) SYS_OP_MSR()[10]
5 - (rowset=1019)
6 - (rowset=1019)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
6 - SEL$1 / T1@SEL$1
- parallel(t1 4)
Note
-----
"""并行度为4"""
- Degree of Parallelism is 4 because of table property
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T1]]></t><s><![CDATA[SEL$1]]></s></h></
f></q>
71 rows selected.
SCOTT@TNS_PDB01>select slave_name,status,sessions from v$pq_slave;
SLAV STATUS SESSIONS
---- ---------- ----------
P000 IDLE 6
P001 IDLE 6
SCOTT@TNS_PDB01>select slave_name,status,sessions from v$pq_slave;
SLAV STATUS SESSIONS
---- ---------- ----------
P000 IDLE 7
P001 IDLE 7
P002 IDLE 4
P003 IDLE 4
2)开启并行DML
默认情况下,即使你开启了并行如果不开并行DML的话,执行DML操作也不会使用并行,开启DML并行命令
alter session force parallel dml;
alter session enable parallel dml;
alter session disable parallel dml;
3、并行相关参数
1)、PARALLEL_ADAPTIVE_MULTI_USER
参数决定是否开启自适应并行。默认为不开启(一般不会开启,该参数受系统IO、CPU等资源影响,进而导致SQL执行时间不稳定)
2)、PARALLEL_DEGREE_LIMIT
决定了并行度的上限(默认为值为CPU)
3)、PARALLEL_MAX_SERVERS
参数决定系统能启动的最大并行子进程数。
4)、PARALLEL_MIN_SERVERS
参数决定系统默认启动的最小并行子进程数。并行执行完后不会消失12c默认为8。
5)、PARALLEL_DEGREE_POLICY
参数决定系统是否开启并行默认值为MANUAL,即不开启并行。系统一般也不会设置为LIMIT或者AUTO(对资源消耗影响太大了)。
6)、PARALLEL_SERVERS_TARGET
当目标SQL的并行度和系统现有活动的并行子进程数量的和小于PARALLEL_SERVERS_TARGET后,Oracle会从排队中将这个SQL取出,并以SQL所需的实际的并行度执行它。
7)、PAEALLEL_MIN_PERCENT
当目标SQL以用户执行的并行度执行时,系统可用的并行子进程的总数必须大于等于该SQL中指定的并行度和PARALLEL_MIN_PERCENT的剩积。默认值为0,表示总是并行立即执行。为1表示总是以串行来执行目标SQL。
8)、PARLLEL_MAX_SERVERS
系统可用并行进程数受该参数影响。默认为20。
9)、PARALLEL_THREADS_PER_CPU
表示在并行执行的过程中一颗单核CPU能同时处理的并行子进程的数量,默认是2。
10)、PARALLEL_FORCE_LOCAL
参数parallel_force_local控制并行子程序是否跨节点(默认为FALSE,跨节点)
SYS@lissen>show parameter parallel_force_local;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_force_local boolean FALSE