- 相關(guān)推薦
Oracle并行DML操作知識(shí)
DML包含物理的拷貝存儲(chǔ)，DML是發(fā)布管理的基礎(chǔ)。下面一起來(lái)看看Oracle的并行DML操作知識(shí)，希望能幫助到大家!
對(duì)大部分的OLTP系統(tǒng)而言，并行DML(PDML)的應(yīng)用場(chǎng)景不多。大多數(shù)的PDML操作集中在下面幾個(gè)場(chǎng)景下：
ü 系統(tǒng)移植，從舊系統(tǒng)中導(dǎo)入原始數(shù)據(jù)和基礎(chǔ)數(shù)據(jù);
ü 數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)Data Warehouse定期進(jìn)行大批量原始數(shù)據(jù)導(dǎo)入和清洗;
ü 借助一些專(zhuān)門(mén)的工具，如sql loader，進(jìn)行數(shù)據(jù)海量導(dǎo)入;
本篇主要介紹并行DML操作的一些細(xì)節(jié)和注意方面。
1、環(huán)境準(zhǔn)備
Oracle并行操作前提兩個(gè)條件，其一是盈余的軟硬件資源，其二是海量的大數(shù)據(jù)量操作。
//操作系統(tǒng)和DB環(huán)境
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> show parameter cpu_count;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
cpu_count integer 4
SQL>
//數(shù)據(jù)環(huán)境
SQL> select count(*) from t;
COUNT(*)
----------
10039808
Executed in 4.072 seconds
2、并行統(tǒng)計(jì)量收集
為了實(shí)現(xiàn)CBO的正常工作，我們通常要保證Oracle數(shù)據(jù)字典中保留有關(guān)于數(shù)據(jù)表完全的統(tǒng)計(jì)信息描述。統(tǒng)計(jì)信息包括數(shù)據(jù)行數(shù)、取值分布、離散程度等等指標(biāo)。收集統(tǒng)計(jì)量是一項(xiàng)比較重要的工作。當(dāng)數(shù)據(jù)表很大的時(shí)候，即使使用了比例抽樣的方法，進(jìn)行匯總統(tǒng)計(jì)的數(shù)據(jù)量也是很大。所以這種場(chǎng)合下，是可以應(yīng)用到并行技術(shù)的。
在目前的Oracle版本中，通常是使用dbms_stats包進(jìn)行統(tǒng)計(jì)量收集。相對(duì)于過(guò)去的analyze table xxx命令，dbms_stats包對(duì)于統(tǒng)計(jì)量收集更加完全，應(yīng)對(duì)分區(qū)狀況更好。在dbms_stats方法中，存在參數(shù)degree，表示并行度，可以直接指定希望的收集并行度。
--收集統(tǒng)計(jì)量，指定并行度
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,degree => 7);
PL/SQL procedure successfully completed
Executed in 15.32 seconds
系統(tǒng)使用15.32s的時(shí)間完成了收集。
在收集過(guò)程中，我們觀察v$px_session和v$px_process兩個(gè)視圖的狀態(tài)。檢查并行伺服進(jìn)程池的狀況。
SQL> select * from v$px_process;
SERVER_NAME STATUS PID SPID SID SERIAL#
----------- --------- ---------- ------------------------ ---------- ----------
P006 IN USE 100 19070982 35 50729
P001 IN USE 65 13107452 178 35585
P002 IN USE 73 9633888 184 25268
P003 IN USE 85 22478986 223 33339
P000 IN USE 63 18743314 500 16029
P004 IN USE 95 14221380 509 26446
P005 IN USE 99 23068708 510 20895
7 rows selected
系統(tǒng)依據(jù)并行度要求，分配了7個(gè)進(jìn)程進(jìn)行操作。
//并行會(huì)話信息
SQL> select * from v$px_session;
SADDR SID SERIAL# QCSID QCSERIAL# DEGREE REQ_DEGREE
---------------- ---------- ---------- ---------- ---------- ---------- ----------
070000007D2BA680 500 16029 324 26152 7 7
070000007FE7EC70 178 35585 324 26152 7 7
070000007FE6D5D0 184 25268 324 26152 7 7
070000007FDFC2C0 223 33339 324 26152 7 7
070000007D2A0490 509 26446 324 26152 7 7
070000007D29D620 510 20895 324 26152 7 7
070000007FC94480 35 50729 324 26152 7 7
070000007D12FB00 324 26152 324
(篇幅原因，有截取結(jié)果……)
8 rows selected
注意，在請(qǐng)求了并行度degree=7的情況下，Oracle根據(jù)CPU數(shù)量分配了7個(gè)并行slave進(jìn)程進(jìn)行操作。會(huì)話層面，七個(gè)slave進(jìn)程分別對(duì)應(yīng)七個(gè)會(huì)話信息進(jìn)行并行操作。同時(shí)，存在一個(gè)額外會(huì)話(sid=324)，充當(dāng)全局協(xié)調(diào)者coordinator的角色。v$px_session中的qcsid字段含義為“Session serial number of the parallel coordinator”，就是并行操作中扮演協(xié)調(diào)者角色的進(jìn)程。
如果不使用并行收集，只是簡(jiǎn)單的串行收集，我們查看一下效率情況。
//指定串行
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,degree => 1);
PL/SQL procedure successfully completed
Executed in 46.816 seconds
效果清晰可見(jiàn)，從原來(lái)的15s多的收集時(shí)間，放大為47s左右，幾乎是三倍的損耗。
結(jié)論：對(duì)于統(tǒng)計(jì)量收集而言，如果作業(yè)時(shí)間可以避開(kāi)業(yè)務(wù)高峰時(shí)間窗口，進(jìn)行并行操作收集統(tǒng)計(jì)量還是一個(gè)不錯(cuò)的選擇。
3、并行insert操作
下面進(jìn)行并行insert操作，我們選擇使用hint來(lái)進(jìn)行并行控制。
//開(kāi)啟PDML的開(kāi)關(guān)
SQL> alter session enable parallel dml;
Session altered
Executed in 0.016 seconds
使用hint，開(kāi)啟8個(gè)并行度進(jìn)行insert操作。
--并行insert
SQL> insert /*+ parallel(t,8) */ into t select * from t;
10039808 rows inserted
Executed in 76.238 seconds
運(yùn)行過(guò)程中，出現(xiàn)的并行操作過(guò)程如下。
//開(kāi)啟8個(gè)并行度;
SQL> select * from v$px_session;
SADDR SID SERIAL# QCSID QCSERIAL#
---------------- ---------- ---------- ---------- ----------
070000007FFF52E0 361 3123 324 26152
070000007FE84950 176 50028 324 26152
070000007FE7EC70 178 35508 324 26152
070000007FE0AAF0 218 5994 324 26152
070000007D29D620 510 20829 324 26152
070000007D2A0490 509 26391 324 26152
070000007FC94480 35 50615 324 26152
070000007FFFAFC0 359 32516 324 26152
070000007D12FB00 324 26152 324
9 rows selected
SQL> select * from v$px_process;
SERVER_NAME STATUS PID SPID SID SERIAL#
----------- --------- ---------- ------------------------ ---------- ----------
P006 IN USE 100 19005590 35 50615
P001 IN USE 69 19398710 176 50028
P002 IN USE 73 9633968 178 35508
P003 IN USE 85 23068694 218 5994
P007 IN USE 102 18743298 359 32516
P000 IN USE 66 14221352 361 3123
P005 IN USE 99 21233884 509 26391
P004 IN USE 95 19071188 510 20829
8 rows selected
此時(shí)，我們嘗試抽取出執(zhí)行計(jì)劃。
//從shared_pool中嘗試獲取到指定的記錄;
SQL> select sql_text, sql_id, version_count from v$sqlarea where sql_text like 'insert /*+ parallel(t,8) */%';
SQL_TEXT SQL_ID VERSION_COUNT
-------------------------------------------------- ------------- -------------
insert /*+ parallel(t,8) */ into t select * from t 67wymm0jhw3gv 2
Executed in 0.234 seconds
利用sql_id，嘗試抽取出shared_pool中的執(zhí)行計(jì)劃。
//抽取出執(zhí)行計(jì)劃，篇幅原因，有刪節(jié)……
SQL> select * from table(dbms_xplan.display_cursor('67wymm0jhw3gv',format => 'advanced',cursor_child_no => 1));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 67wymm0jhw3gv, child number 1
-------------------------------------
insert /*+ parallel(t,8) */ into t select * from t
Plan hash value: 4064487821
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 2718 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 5019K| 469M| 2718 (1)| 00:00:33 | Q1,00 | P->S | Q
| 3 | LOAD AS SELECT | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 5019K| 469M| 2718 (1)| 00:00:33 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| T | 5019K| 469M| 2718 (1)| 00:00:33 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 8 because of degree limit
已選擇66行。
已用時(shí)間: 00: 00: 00.40
如果不使用并行操作，進(jìn)行如此規(guī)模的insert操作，會(huì)如何呢?
//使用noparallel的hint進(jìn)行并行抑制;
SQL> insert /*+ noparallel */ into t select * from t;
10039808 rows inserted
Executed in 87.813 seconds
對(duì)應(yīng)的執(zhí)行計(jì)劃如下：
SQL> select sql_text, sql_id, version_count from v$sqlarea where sql_text like 'insert /*+ noparallel */%';
SQL_TEXT SQL_ID VERSION_COUNT
-------------------------------------------------- ------------- -------------
insert /*+ noparallel */ into t select * from t 9u0xcrr3bcjs1 1
Executed in 0.234 seconds
SQL> select * from table(dbms_xplan.display_cursor('9u0xcrr3bcjs1',format => 'advanced',cursor_child_no => 0));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 9u0xcrr3bcjs1, child number 0
-------------------------------------
insert /*+ noparallel */ into t select * from t
Plan hash value: 2153619298
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 19601 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | TABLE ACCESS FULL | T | 5019K| 469M| 19601 (1)| 00:03:56 |
---------------------------------------------------------------------------------
4、結(jié)論
本篇對(duì)PDML進(jìn)行了簡(jiǎn)單的介紹，包括使用方法和并行度設(shè)置。由于篇幅原因，只介紹了并行insert和并行統(tǒng)計(jì)量的收集。并行update和delete本質(zhì)相同，就不加以累述了。
最后，并行操作是一種帶有特殊性的操作，絕對(duì)不要將其輕易作為經(jīng)常性無(wú)監(jiān)管下的操作。
【Oracle并行DML操作知識(shí)】相關(guān)文章：
ORACLE數(shù)據(jù)庫(kù)操作基本語(yǔ)句03-06
Oracle復(fù)習(xí)知識(shí)點(diǎn)匯總01-23
Oracle數(shù)據(jù)庫(kù)基本知識(shí)03-31
oracle數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)01-21
Oracle認(rèn)證：ORACLE綁定變量BINDPEEKING03-08
Oracle數(shù)據(jù)庫(kù)知識(shí)點(diǎn)：SQLPLUS介紹03-30
Oracle數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)：SELECT語(yǔ)句01-23