问答榜上引发的Oracle并行的探究（一）

原创布衣&凡尘 2022-10-16

1697

一、背景

在冲刺“问答榜”的时候发现在了一个关于并行的问题，自己对Oracle的并行也是一知半解的状态，于是自己趁这个机会也充充电。

小小吐槽一下：）
最近的“问答榜”竞争的太激烈了，我这都翻到了2022-01-05的问题了！不得不增加自己的知识储备了，

二、简介：并行

并行执行的原理概括起来就是“分而治之（Divide and Conquer）”,把一个大任务拆分成多个小的子任务，并把该任务的执行方式由一个单进程/线程依次顺序执行改成由多个进程/线程同时并发执行，而且每个子进程/线程只执行拆分后的任务。
并行执行的本质就是以额外的硬件资源消耗来换取执行时间的缩短。硬件资源利用得越好，越高效，并行执行的时间就会越短。

注意：并行执行并不一定会缩短执行时间，它并不适合所有的场景。

三、并行真正发挥作用的条件：

1.非常有效率的执行计划，如果执行计划本身非常差，使用并行可能并不能改善多少语句的执行效率。
2.数据库系统有着充足的资源可用。
3.工作量的分配没有明显的倾斜，根据短板理论，如果某一个PX slave干了很多活，那么最大的瓶颈就是它。

四、Oracle里能够并行执行的操作：

测试表：
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;

1、并行查询

单表并行

-- 开启2个并行：
14:13:37 SQL> select /*+ parallel(2) */ count(*) from t1;
  COUNT(*)
----------
     76833
Elapsed: 00:00:00.19
Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |   166   (0)| 00:00:01 |        |      |            |
|   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 |          | 75850 |   166   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       | 75850 |   166   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

多表关联

14:23:38 SQL> select  /*+ parallel(2) */t1.owner,t1.object_name,t2.status 
from t1,t2 
where t1.object_id=t2.object_id and t1.owner='SCOTT';

11 rows selected.
Elapsed: 00:00:00.27
Execution Plan
----------------------------------------------------------
Plan hash value: 3350059367
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |    12 |  1368 |   334   (1)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |    12 |  1368 |   334   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN            |          |    12 |  1368 |   334   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          |    12 |  1152 |   167   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND BROADCAST  | :TQ10000 |    12 |  1152 |   167   (1)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|   6 |       PX BLOCK ITERATOR |          |    12 |  1152 |   167   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|*  7 |        TABLE ACCESS FULL| T1       |    12 |  1152 |   167   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |     PX BLOCK ITERATOR   |          | 93976 |  1651K|   167   (1)| 00:00:01 |  Q1,01 | PCWC |            |
|*  9 |      TABLE ACCESS FULL  | T2       | 93976 |  1651K|   167   (1)| 00:00:01 |  Q1,01 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

2、并行DDL

Oracle数据库的DDL语句如果并行执行，通常情况下都可以缩短执行时间。

准备数据相同的2张表：t1、t3

14:29:35 SQL> select count(*) from t1;
  COUNT(*)
----------
   4917312
14:29:41 SQL> create table t3 as select * from t1;
Table created.
14:31:01 SQL> select count(*) from t3;
  COUNT(*)
----------
   4917312

在表T1上串行创建索引IDX_T1：耗时：25.93s

SQL> create index idx_t1 on t1(object_name,object_id,data_object_id);
Index created.
Elapsed: 00:00:25.93

在表T3上8个并行创建索引IDX_T3：耗时：18.34s

SQL>  create index idx_t3 on t3(object_name,object_id,data_object_id) parallel 8;
Index created.
Elapsed: 00:00:11.64

并行比串行提升：55%

SQL> select (25.93-11.64)/25.93*100 from dual;
(25.93-11.64)/25.93*100
-----------------------
             55.1099113

注：Oracle 在并行执行完DDL语句后，可能会导致相关对像默认并行度的变化：
Oracle 在访问索引IDX_T3的时候，CBO可能会考虑并行执行，这可能会引发一系列的问题。
所以在并行执行完DDL语句后通常应该将相关对象的并行度调整为：1

SQL> select index_name,degree from dba_indexes where index_name like 'IDX_T%';
INDEX_NAME                     DEGREE
------------------------------ ----------------------------------------
IDX_T3                         8   <--- 8个并行创建的索引，并行度为：8
IDX_T1                         1   <--- 串行创建索引，并行度为：1

将索引IDX_T3并行度调整为：1

SQL> alter index idx_t3 noparallel;
Index altered.
Elapsed: 00:00:00.01
SQL>  select index_name,degree from dba_indexes where index_name like 'IDX_T3';
INDEX_NAME                     DEGREE
------------------------------ ----------------------------------------
IDX_T3                         1

3、并行DML

Oracle数据库的DML语句如果并行执行，通常情况下都可以缩短执行时间。

示例：

-- 串行：
SQL> update t1 set object_name='CUIHUA1';
4917312 rows updated.
Elapsed: 00:02:40.80
Execution Plan
----------------------------------------------------------
Plan hash value: 2927627013
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |  4785K|   301M| 18999   (1)| 00:03:48 |
|   1 |  UPDATE            | T1   |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |  4785K|   301M| 18999   (1)| 00:03:48 |
---------------------------------------------------------------------------
-- 并行：启用并行Hint
SQL> update /*+ parallel(4) */ t1 set object_name='CUIHUA1';
4917312 rows updated.
Elapsed: 00:02:49.69
Execution Plan
----------------------------------------------------------
Plan hash value: 121765358
---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |  4785K|   301M|  5271   (1)| 00:00:11 |        |      |            |
|   1 |  UPDATE               | T1       |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |  4785K|   301M|  5271   (1)| 00:00:11 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |  4785K|   301M|  5271   (1)| 00:00:11 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T1       |  4785K|   301M|  5271   (1)| 00:00:11 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------

–启用并行Hint执行上述更新操作后发现执行时间反而增加了9s左右。从执行计划上面来看，更新操作的并行部分全部发生在全表扫描T1的部分，而真正的UPDATE 则是发生在P->S（表示PARALLEL_TO_SERIAL）之后，即这里启用并行Hint的更新操作是一个伪并行更新操作。在这个伪并行更新操作里，真正的更新操作并没有并行执行，实际的并行操作全部发生在并行扫描表T1上，更新操作成为了上述SQL在并行执行扫描表T1后的瓶颈，因此执行速度反而更慢了。

现在我们执行真正的并行更新：

......前面事务未结束
SQL> alter session force parallel dml;
ERROR:
ORA-12841: Cannot alter the session parallel DML state within a transaction
Elapsed: 00:00:00.03
-- 结束事务，重启事务
SQL> commit;
Commit complete.
Elapsed: 00:00:00.04
SQL> alter session force parallel dml;
Session altered.
Elapsed: 00:00:00.00
SQL> update t1 set object_name='CUIHUA1';
4917312 rows updated.
Elapsed: 00:01:22.97
SQL> commit;

– 串行的：00:02:40.80，原并行：00:02:49.69，真实并行： 00:01:22.97，整整提高了1分20秒左右
– 注：alter session force parallel dml;执行完一个dml语句后需要结束事务（commit/rollback）,
否则会报：ORA-12841: Cannot alter the session parallel DML state within a transaction
所以set autotrace on 无法与alter session force parallel dml 一起使用。

SQL> set autotrace on
SQL>  update t1 set object_name='CUIHUA1';
4917312 rows updated.
Elapsed: 00:01:22.43
Execution Plan
---------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel
SP2-0612: Error generating AUTOTRACE EXPLAIN report

– 查看执行计划：

--  用于刚刚执行过的SQL，真实的执行计划
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------
SQL_ID  12nxhwc2ugdm6, child number 1
-------------------------------------
update t1 set object_name='CUIHUA1'
Plan hash value: 3991856572
---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |       |       |   439 (100)|          |        |      |            |
|   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |  4785K|   301M|   439   (1)| 00:00:06 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    UPDATE             | T1       |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |          |  4785K|   301M|   439   (1)| 00:00:06 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| T1       |  4785K|   301M|   439   (1)| 00:00:06 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------

– 从执行计划可以看到，更新操作是真正的并行执行的，并行部分不仅发生在全表扫描表T1的部分，而且也发生在更新部分。所对应的是PCWP(表示PARALLEL_COMBINED_WITH_PARENT)，这就表明上述更新操作确实是在并发执行。
– 知识小点：
除了“alter session force parallel dml;”可以真正并行执行DML操作之外，“alter session enable parallel dml;”+并行Hint的dml语句联合使用也同样可以达到真正并行执行DML操作的目的。
注意：仅仅修改表的并行度仅使用并行Hint ,都不能真正并行执行DML.

alter session enable parallel dml;+并行Hint联合使用示例：

SQL> alter session enable parallel dml;
Session altered.
Elapsed: 00:00:00.01
SQL> update /*+ parallel(4) */ t1 set object_name='CUIHUA1';
4917312 rows updated.
Elapsed: 00:01:31.87
-- 执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
SQL_ID  2wajx9wh66udc, child number 0
-------------------------------------
 update /*+ parallel(4) */ t1 set object_name='CUIHUA1'
Plan hash value: 3991856572
---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |       |       |  5271 (100)|          |        |      |            |
|   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |  4785K|   301M|  5271   (1)| 00:01:04 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    UPDATE             | T1       |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |          |  4785K|   301M|  5271   (1)| 00:01:04 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| T1       |  4785K|   301M|  5271   (1)| 00:01:04 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------

未完待续

问答榜上引发的Oracle并行的探究（二）: https://www.modb.pro/db/521304

文章推荐

Oracle：	URL
《Oracle 自动收集统计信息机制》	https://www.modb.pro/db/403670
《Oracle_索引重建—优化索引碎片》	https://www.modb.pro/db/399543
《DBA_TAB_MODIFICATIONS表的刷新策略测试》	https://www.modb.pro/db/414692
《FY_Recover_Data.dbf》	https://www.modb.pro/doc/74682
《Oracle RAC 集群迁移文件操作.pdf》	https://www.modb.pro/doc/72985
《Oracle Date 字段索引使用测试.dbf》	https://www.modb.pro/doc/72521
《Oracle 诊断案例：因应用死循环导致的CPU过高》	https://www.modb.pro/db/483047
《Oracle 慢SQL监控脚本》	https://www.modb.pro/db/479620
《Oracle 慢SQL监控测试及监控脚本.pdf》	https://www.modb.pro/doc/76068
《Oracle 脚本实现简单的审计功能》	https://www.modb.pro/db/450052
《记录一起索引rebuild与收集统计信息的事故》	https://www.modb.pro/db/408934
《RAC DG删除备库redo时报ORA-01623》	https://www.modb.pro/db/515939
《ASH报告发现：os thread startup 等待事件分析》	https://www.modb.pro/db/521146
《问答榜上引发的Oracle并行的探究（一）》	https://www.modb.pro/db/521260
《问答榜上引发的Oracle并行的探究（二）》	https://www.modb.pro/db/521304

欢迎赞赏支持或留言指正

墨力计划故障处理 oracle并行 ora-12841 ora-12838

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏