问答榜上引发的oracle并行的探究（一） -m6米乐安卓版下载

一、背景

  在冲刺“问答榜”的时候发现在了一个关于并行的问题，自己对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

                   文章推荐