Oracle平行計算

kunlunzhiying發表於2017-07-10
Oracle

【前言】現在CPU的發展已不僅朝著單個效能更好的方向了，而且還朝著多核數多核心的方向發展了。Oracle資料庫大部分也都是利用單執行緒的序列方式在執行。透過並行（Parallel）操作特性，充分應用CPU的多核心特點，提高對資料的操作效率，滿足在特定場景下對海量資料操作的需求。

OLTP系統最主要的核心還是資料的錄入操作，而這些應用的場景並不適合於平行計算的方式。對於OLAP業務場景更適合並行的操作，並行也成為資料庫倉庫調優利器；

【使用型別和場景】

Oracle並行處理（Parallel Processing）特性主要是針對SQL語句處理的並行。目前Oracle提供支援並行的操作包括如下型別：

  • 並行查詢操作；
  • 並行DDL，對資料物件的DDL操作；
  • 並行DML，進行並行的資料更新修改；

在具體的應用場景上，有如下場景：

  • 資料表、索引範圍掃描的查詢；
  • 批次增加、修改和刪除操作；
  • 資料表、索引物件的建立；
  • "DBMS_STATS"收集統計量；
  • 備份恢復管理器Recovery Manager的使用；


【設定並行和並行度】

  • Alter session force parallel query parallel n;（ALTER SESSION enable parallel query;） 單個SESSION裡面
  • Alter table tab1 parallel n; 單個TABLE
  • Select /*+parallel(tab n)*/ from tab; Hint設定單個SQL

優先順序：Hint > session > object


【並行度設定】並行度的設定是以多核CPU為核心的，所以並行度不能超過CPU的邏輯數量，Oracle官方文件介紹如下

If the PARALLEL clause is specified but no degree of parallelism is listed, the object gets the default DOP. Default parallelism uses a formula to determine the DOP based on the system configuration, as in the following:

  • For a single instance, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT
  • For an Oracle RAC configuration, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT



【實驗測試】

環境說明：

  • 資料庫資訊：

點選(此處)摺疊或開啟

  1. SQL> select * from v$version where rownum<2;
  2. BANNER
  3. -----------------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


  • CPU資訊

點選(此處)摺疊或開啟

  1. SQL> show parameter cpu
  2. NAME TYPE VALUE
  3. ------------------------------------ ----------- -------------
  4. cpu_count integer 8
  5. parallel_threads_per_cpu integer 1
  6. resource_manager_cpu_allocation integer 8

【實驗步驟】

正常情況下的執行計劃


點選(此處)摺疊或開啟

  1. SQL> alter session set STATISTICS_LEVEL=ALL;
  2. Session altered.
  3. SQL> select count(1) from edidc;
  4. COUNT(1)
  5. ----------
  6. 47262191

  8. SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'));

  10. PLAN_TABLE_OUTPUT
  11. --------------------------------------------------------------------------------
  12. SQL_ID 8h0snkxwm7x0w, child number 0
  13. -------------------------------------
  14. select count(1) from sapsr3.edidc

  16. Plan hash value: 2138151876

  18. ----------------------------------------------------------------------------------------------------------------------
  19. Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  20. -------------------------------------------------------------------------------------------------------------------
  21. | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:15.52| 351K|
  22. | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:15.52| 351K|
  23. | 2 | INDEX FAST FULL SCAN | EDIDC~1 | 1 | 46M | 47M |00:00:09.95| 351K|
  24. --------------------------------------------------------------------------------------------------------------------


修改session的方式


點選(此處)摺疊或開啟

  1. SQL> alter session set STATISTICS_LEVEL=ALL;
  2. SQL> Alter session force parallel query;
  3. SQL> select count(1) from edidc;
  4. COUNT(1)
  5. ----------
  6. 47262191
  7. SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'));
  8. -------------------------------------------------------------------------
  9. | Id | Operation | Name | E-Rows |
  10. -------------------------------------------------------------------------
  11. | 0 | SELECT STATEMENT | | |
  12. | 1 | SORT AGGREGATE | | 1 |
  13. | 2 | PX COORDINATOR | | |
  14. | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 |
  15. | 4 | SORT AGGREGATE | | 1 |
  16. | 5 | PX BLOCK ITERATOR | | 46M |
  17. |* 6 | INDEX FAST FULL SCAN| EDIDC~1 | 46M |
  18. ----------------------------------------------------------------------------
  19. Predicate Information (identified by operation id):
  20. ----------------------------------------------------------------------------
  21. 6 - access(:Z>=:Z AND :Z<=:Z)
  22. ------------------------------------------------------


用hint的方式進行


點選(此處)摺疊或開啟

  1. SQL> select /*+ PARALLEL(4) */ count(1) from edidc;
  2. COUNT(1)
  3. ----------
  4. 47262191

  6. SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'));
  7. PLAN_TABLE_OUTPUT
  8. --------------------------------------------------------------------------------
  9. SQL_ID gr4rp3q9c4qu3, child number 0
  10. -------------------------------------
  11. select /*+ PARALLEL(4) */ count(1) from sapsr3.edidc

  13. Plan hash value: 152749150

  15. -------------------------------------------------------------------------------------------------------------------------------------
  16. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
  17. -------------------------------------------------------------------------------------------------------------------------------------------
  18. | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:13.86 | 25 | 0 |
  19. | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:13.86 | 25 | 0 |
  20. | 2 | PX COORDINATOR | | 1 | | 4 |00:00:13.86 | 25 | 0 |
  21. | 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |
  22. | 4 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:52.76 | 357K | 351K |
  23. | 5 | PX BLOCK ITERATOR | | 4 | 46M | 47M |00:00:41.69 | 357K | 351K |
  24. |* 6 | INDEX FAST FULL SCAN | EDIDC~1 | 136 | 46M | 47M |00:00:20.42 | 357K | 351K |
  25. --------------------------------------------------------------------------------------------------------------------------------------
  26. Predicate Information (identified by operation id):
  27. ---------------------------------------------------
  28. 6 - access(:Z>=:Z AND :Z<=:Z)
  29. PLAN_TABLE_OUTPUT
  30. --------------------------------------------------------------------------------


修改table的方式


點選(此處)摺疊或開啟

  1. SQL> ALTER TABLE edidc PARALLEL 2; #設定完成後需要更新統計資訊

  3. SQL> SELECT TABLE_NAME, degree FROM dba_tables WHERE TABLE_NAME='EDIDC';
  4. TABLE_NAME DEGREE
  5. ------------------------------ ------------------------------
  6. EDIDC 2
  7. SQL> alter session set STATISTICS_LEVEL=ALL;
  8. Session altered.
  9. SQL> select count(1) from edidc;
  10. COUNT(1)
  11. ----------
  12. 47262191

  14. SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'));
  15. --------------------------------------------------------------------------------------------------------------------------
  16. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
  17. -------------------------------------------------------------------------------------------------------------------------
  18. | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:12.93 | 25 | 0 |
  19. | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:12.93 | 25 | 0 |
  20. | 2 | PX COORDINATOR | | 1 | | 8 |00:00:12.93 | 25 | 0 |
  21. | 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |
  22. | 4 | SORT AGGREGATE | | 8 | 1 | 8 |00:01:39.17 | 359K| 351K|
  23. | 5 | PX BLOCK ITERATOR | | 8 | 46M | 47M|00:01:23.50 | 359K| 351K|
  24. |* 6 | INDEX FAST FULL SCAN | EDIDC~1 | 164 | 46M| 47M|00:00:54.01 | 359K| 351K|
  25. ------------------------------------------------------------------------------------------------------------------------
  26. Predicate Information (identified by operation id):
  27. ---------------------------------------------------
  28. 6 - access(:Z>=:Z AND :Z<=:Z)
  29. PLAN_TABLE_OUTPUT
  30. ---------------------------------------------------

  32. SQL>ALTER TABLE TABLE_NAME NOPARALLEL; 取消並行


以上是針對查詢的操作，同樣也是適用於DML操作的；

並行的使用並是簡單的以上的幾個語句的套用，在OLTP系統中使用需謹慎，用好了是一把調優的利器，用不好可能反而會被利器所傷。本文件也是是針對並行的一篇基礎文章，後續會針對更深入的應用繼續說明。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2141817/，如需轉載，請註明出處，否則將追究法律責任。

相關文章