問答榜上引發的Oracle并行的探究（一）

原創布衣&凡塵 2022-10-16

2747

一、背景

??在沖刺“問答榜”的時候發現在了一個關于并行的問題，自己對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并行的探究（二）: http://m.sunline.cc/db/521304

???????????????????文章推薦

Oracle：	URL
《Oracle 自動收集統計信息機制》	http://m.sunline.cc/db/403670
《Oracle_索引重建—優化索引碎片》	http://m.sunline.cc/db/399543
《DBA_TAB_MODIFICATIONS表的刷新策略測試》	http://m.sunline.cc/db/414692
《FY_Recover_Data.dbf》	http://m.sunline.cc/doc/74682
《Oracle RAC 集群遷移文件操作.pdf》	http://m.sunline.cc/doc/72985
《Oracle Date 字段索引使用測試.dbf》	http://m.sunline.cc/doc/72521
《Oracle 診斷案例：因應用死循環導致的CPU過高》	http://m.sunline.cc/db/483047
《Oracle 慢SQL監控腳本》	http://m.sunline.cc/db/479620
《Oracle 慢SQL監控測試及監控腳本.pdf》	http://m.sunline.cc/doc/76068
《Oracle 腳本實現簡單的審計功能》	http://m.sunline.cc/db/450052
《記錄一起索引rebuild與收集統計信息的事故》	http://m.sunline.cc/db/408934
《RAC DG刪除備庫redo時報ORA-01623》	http://m.sunline.cc/db/515939
《ASH報告發現：os thread startup 等待事件分析》	http://m.sunline.cc/db/521146
《問答榜上引發的Oracle并行的探究（一）》	http://m.sunline.cc/db/521260
《問答榜上引發的Oracle并行的探究（二）》	http://m.sunline.cc/db/521304

歡迎贊賞支持或留言指正

墨力計劃故障處理 oracle并行 ora-12841 ora-12838

「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」

關注作者

特色一级强游戏,海奥华预言免费阅读,51漫画兑换码,美女裸体无遮挡永久免费观看网站,lubuntu线路检测入口