Oracle并行操作之并行查詢實(shí)例解析
Oracle數(shù)據(jù)庫(kù)的并行操作特性，其本質(zhì)上就是強(qiáng)行榨取除數(shù)據(jù)庫(kù)服務(wù)器空閑資源（主要是CPU資源），對(duì)一些高負(fù)荷大數(shù)據(jù)量數(shù)據(jù)進(jìn)行分治處理。并行操作是一種非確定性的優(yōu)化策略，在選擇的時(shí)候需要小心對(duì)待。目前，使用并行操作特性的主要有下面幾個(gè)方面：
Parallel Query：并行查詢，使用多個(gè)操作系統(tǒng)級(jí)別的Server Process來同時(shí)完成一個(gè)SQL查詢；
Parallel DML：并行DML操作。類似于Parallel Query。當(dāng)要對(duì)大數(shù)據(jù)量表進(jìn)行DML操作，如insert、update和delete的時(shí)候，可以考慮使用；
Parallel DDL：并行DDL操作。如進(jìn)行大容量數(shù)據(jù)表構(gòu)建、索引rebuild等操作；
Parallel Recovery，并行恢復(fù)。當(dāng)數(shù)據(jù)庫(kù)實(shí)例崩潰重新啟動(dòng)，或者進(jìn)行存儲(chǔ)介質(zhì)恢復(fù)的時(shí)候，可以啟動(dòng)并行恢復(fù)技術(shù)。從而達(dá)到減少恢復(fù)時(shí)間的目的；
Procedural Parallel，過程代碼并行化。對(duì)我們編寫的代碼片段、存儲(chǔ)過程或者函數(shù)，可以實(shí)現(xiàn)執(zhí)行的并行化，從而加快執(zhí)行效率；
1、并行查詢Parallel Query
Oracle數(shù)據(jù)庫(kù)的并行查詢是比較基礎(chǔ)的技術(shù)，也是OLAP和Oracle Data Warehouse經(jīng)常使用的一種并行技術(shù)。同本系列前面一直強(qiáng)調(diào)的要素相同，在確定使用并行技術(shù)之前，要確定軟硬件的一些先決條件：
任務(wù)task必要條件。備選進(jìn)行并行操作的任務(wù)task必須是一個(gè)大任務(wù)作業(yè)，比如，長(zhǎng)時(shí)間的查詢。任務(wù)時(shí)間通常可以以分鐘、小時(shí)進(jìn)行計(jì)數(shù)。只有這樣的任務(wù)和需要，才值得讓我們冒險(xiǎn)使用并行操作方案；
資源閑置條件。只有在數(shù)據(jù)庫(kù)服務(wù)器資源存在閑置的時(shí)候，才可以考慮進(jìn)行并行處理。如果經(jīng)常性的繁忙，貿(mào)然使用并行只能加劇資源的爭(zhēng)用。
并行操作最大的風(fēng)險(xiǎn)在于并行爭(zhēng)用引起的效率不升反降。所以，要在確定兩個(gè)前提之后，再進(jìn)行并行規(guī)劃處理。
2、環(huán)境準(zhǔn)備
首先，準(zhǔn)備實(shí)驗(yàn)環(huán)境。由于筆者使用的一般家用PC虛擬機(jī)，所以并行度和存儲(chǔ)量不能反映真實(shí)條件需求，見諒。
SQL> select * from v$version where rownum<2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production SQL> select count(*) from t; COUNT(*) ---------- 1160704
選擇11gR2服務(wù)器環(huán)境，數(shù)據(jù)表T總數(shù)據(jù)量超過一百萬。
首先，我們觀察一下不使用并行的執(zhí)行情況。
//提取出使用游標(biāo)信息；
SQL> select sql_text, sql_id, version_count from v$sqlarea where sql_text like 'select count(*) from t%';
SQL_TEXT SQL_ID VERSION_COUNT
------------------------------ ------------- -------------
select count(*) from t 2jkn7rpsbj64t 2
SQL> select * from table(dbms_xplan.display_cursor('2jkn7rpsbj64t',format => 'advanced', cursor_child_no => 0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2jkn7rpsbj64t, child number 0
-------------------------------------
select count(*) from t
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4464 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1160K| 4464 (1)| 00:00:54 |
-------------------------------------------------------------------
該執(zhí)行計(jì)劃中沒有使用并行特性，進(jìn)行全表掃描。執(zhí)行時(shí)間為54s。
3、并行查詢計(jì)劃
首先，我們?cè)O(shè)置相應(yīng)的并行度。設(shè)置并行度有兩種方式，一種是使用hint加在特定的SQL語(yǔ)句上。另一種是對(duì)大對(duì)象設(shè)置并行度屬性。
前者的優(yōu)點(diǎn)是帶有一定的強(qiáng)制性和針對(duì)性。就是指定特定的SQL語(yǔ)句進(jìn)行并行處理。這樣的優(yōu)點(diǎn)是易于控制并行度，缺點(diǎn)是帶有很強(qiáng)的強(qiáng)制力，當(dāng)數(shù)據(jù)量偏小的時(shí)候，使用并行優(yōu)勢(shì)不大。而且如果是顯示指定并行度，又會(huì)帶來移植伸縮性差的缺點(diǎn)。
后者通過對(duì)象的屬性指定并行度。就將并行作為一種執(zhí)行手段，提供給優(yōu)化器進(jìn)行選擇。這樣，CBO會(huì)根據(jù)系統(tǒng)中資源的情況和數(shù)據(jù)的實(shí)際，進(jìn)行執(zhí)行計(jì)劃生成。計(jì)劃中可能是并行，也可能不是并行。這樣的優(yōu)點(diǎn)是將并行與否交予優(yōu)化器CBO去判斷，缺點(diǎn)是并行的濫用風(fēng)險(xiǎn)。
此處，筆者設(shè)置自動(dòng)確定并行度的方式。
SQL> alter table t parallel; Table altered SQL> select count(*) from t; COUNT(*) ---------- 1160704
當(dāng)啟動(dòng)查詢時(shí)，Oracle中的并行伺候進(jìn)程池會(huì)根據(jù)系統(tǒng)中的負(fù)荷和實(shí)際因素，確定分配出的并行進(jìn)程數(shù)量。此時(shí)，我們可以通過視圖v$px_process來查看進(jìn)程池中的連接信息。
SQL> select * from v$px_process; SERVER_NAME STATUS PID SPID SID SERIAL# ----------- --------- ---------- ------------------------ ---------- ---------- P000 AVAILABLE 25 5776 P001 AVAILABLE 26 5778
注意，并行伺候進(jìn)程是一種特殊的Server Process，本質(zhì)上是一種可共享的slave進(jìn)程。專用連接模式下，一般的Server Process與Client Process是“同生共死”的關(guān)系，終身服務(wù)于一個(gè)Client Process。而伺候slave進(jìn)程是通過進(jìn)程池進(jìn)行管理的，一旦啟動(dòng)初始化，就會(huì)在一定時(shí)間內(nèi)駐留在系統(tǒng)中，等待下次并行處理到來。
此時(shí)，我們檢查v$process視圖，也可以找到對(duì)應(yīng)的信息。
SQL> select * from v$process; PID SPID PNAME USERNAME SERIAL# PROGRAM -------- ---------- ------------------------ ----- --------------- ---------- ------------------------------- 25 5776 P000 oracle 13 oracle@oracle11g (P000) 26 5778 P001 oracle 6 oracle@oracle11g (P001) （篇幅由于原因，予以省略……） 32 rows selected
對(duì)應(yīng)的OS中，也存在相應(yīng)的真實(shí)進(jìn)程伺候。
[oracle@oracle11g ~]$ ps -ef | grep oracle （篇幅由于原因，予以省略……） oracle 5700 1 0 17:29 ? 00:00:02 oraclewilson (LOCAL=NO) oracle 5723 1 0 17:33 ? 00:00:00 ora_smco_wilson oracle 5764 1 2 17:40 ? 00:00:05 oraclewilson (LOCAL=NO) oracle 5774 1 0 17:42 ? 00:00:00 oraclewilson (LOCAL=NO) oracle 5776 1 0 17:43 ? 00:00:00 ora_p000_wilson oracle 5778 1 0 17:43 ? 00:00:00 ora_p001_wilson oracle 5820 1 1 17:44 ? 00:00:00 ora_w000_wilson
由于此時(shí)查詢已經(jīng)結(jié)束，對(duì)應(yīng)的并行會(huì)話信息，已經(jīng)消失不可見。
SQL> select * from v$px_session; SADDR SID SERIAL# QCSID QCSERIAL# -------- ---------- ---------- ---------- ----------
但是，如果任務(wù)的時(shí)間長(zhǎng)，是可以捕獲到對(duì)應(yīng)信息的。
從上面的情況看，我們執(zhí)行一個(gè)并行操作時(shí)，Oracle會(huì)從伺候進(jìn)程池中獲取到對(duì)應(yīng)的并行進(jìn)程，來進(jìn)行操作。當(dāng)操作完成后，伺候進(jìn)程還會(huì)等待一定時(shí)間，之后回收。
并行操作進(jìn)程的資源消耗，通過v$px_sysstat視圖查看。
SQL> col statistic for a30; SQL> select * from v$px_process_sysstat; STATISTIC VALUE ------------------------------ ---------- Servers In Use 0 Servers Available 0 Servers Started 2 Servers Shutdown 2 Servers Highwater 2 Servers Cleaned Up 0 Server Sessions 6 Memory Chunks Allocated 4 Memory Chunks Freed 0 Memory Chunks Current 4 Memory Chunks HWM 4 Buffers Allocated 30 Buffers Freed 30 Buffers Current 0 Buffers HWM 8 15 rows selected
下面，我們檢查一下執(zhí)行計(jì)劃信息。
SQL> set pagesize 10000;
SQL> select * from table(dbms_xplan.display_cursor('2jkn7rpsbj64t',format => 'advanced',cursor_child
_no => 1));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 2jkn7rpsbj64t, child number 1
-------------------------------------
select count(*) from t
Plan hash value: 3126468333
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2478 (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 | | 1160K| 2478 (1)| 00:00:30 | Q1,00 | PCWC |
|* 6 | TABLE ACCESS FULL| T | 1160K| 2478 (1)| 00:00:30 | Q1,00 | PCWP |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
從執(zhí)行計(jì)劃的條件（6 - access(:Z>=:Z AND :Z<=:Z)）中，我們可以看到任務(wù)分配，之后分別進(jìn)行全表掃描。最后排序計(jì)算count，合并結(jié)果的過程。
4、結(jié)論
Oracle Parallel Query是經(jīng)常使用到的一種并行操作技術(shù)。相對(duì)于DDL、DML等類型操作，并行查詢更可以作為系統(tǒng)功能的一個(gè)步驟來進(jìn)行。
進(jìn)行并行查詢最大的風(fēng)險(xiǎn)就是并行濫用和失控的出現(xiàn)。這也是Oracle一直致力解決的問題。在Oracle11gR2中，引入了Parallel Statement Queuing（PSQ）技術(shù)特性。通常，只要并行伺候池允許，Oracle會(huì)引入盡可能多的并行進(jìn)程進(jìn)行操作。PSQ技術(shù)的出現(xiàn)，就是從資源角度加入了并行控制。
當(dāng)系統(tǒng)繁忙的時(shí)候，PSQ會(huì)將一些要進(jìn)行的并行操作進(jìn)入等待狀態(tài)，防止并行環(huán)境的惡化。當(dāng)環(huán)境好轉(zhuǎn)之后，等待隊(duì)列中的并行語(yǔ)句就進(jìn)入執(zhí)行狀態(tài)。這個(gè)特性就可以有效的防止并行濫用的出現(xiàn)。
相關(guān)文章
oracle插入字符串?dāng)?shù)據(jù)時(shí)字符串中有''單引號(hào)問題
這篇文章主要介紹了oracle插入字符串?dāng)?shù)據(jù)時(shí)字符串中有'單引號(hào)問題的相關(guān)資料,需要的朋友可以參考下2017-04-04
Oracle回滾段的概念，用法和規(guī)劃及問題的解決...2007-03-03
Oracle通過時(shí)間(分鐘)計(jì)算有幾天幾小時(shí)幾分鐘的方法
這篇文章主要介紹了Oracle通過時(shí)間(分鐘)計(jì)算有幾天幾小時(shí)幾分鐘的方法,涉及Oracle時(shí)間操作的技巧,需要的朋友可以參考下2015-03-03
Oracle不像SQLServer那樣在存儲(chǔ)過程中用Select就可以返回結(jié)果集，而是通過Out型的參數(shù)進(jìn)行結(jié)果集返回的2012-07-07
解決Oracle刪除重復(fù)數(shù)據(jù)只留一條的方法詳解
本篇文章是對(duì)Oracle刪除重復(fù)數(shù)據(jù)只留一條的解決方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-05-05
表空間使用情況包括：查看表空間的名稱及大小/查看表空間物理文件的名稱及大小/查看回滾段名稱及大小等等感興趣的你可以參考下本文2013-03-03
這篇文章主要介紹了新手學(xué)oracle常見的疑問，第一，索引范圍掃描，第二，索引唯一掃描,需要的朋友可以參考下2015-07-07
本文沒有對(duì)PL/SQL中的東西有什么說明，只是簡(jiǎn)單的舉了幾個(gè)例子，希望對(duì)大家有用！2009-08-08
oracle 使用sql獲取數(shù)據(jù)庫(kù)表、表的字段的多種方法
使用sql獲取數(shù)據(jù)庫(kù)表，表的字段的方法有很多，本文整理了幾個(gè)常用且實(shí)用的方法，需要的朋友可以參考下2013-11-11
oracle中如何查詢所有用戶表的表名、主鍵名稱、索引及外鍵等
在與Oracle進(jìn)行數(shù)據(jù)庫(kù)操作時(shí),往往會(huì)需要查詢數(shù)據(jù)庫(kù)表列表,以便對(duì)其進(jìn)行操作,這篇文章主要給大家介紹了關(guān)于oracle中如何查詢所有用戶表的表名、主鍵名稱、索引及外鍵等的相關(guān)資料,需要的朋友可以參考下2023-12-12