在Oracle 11g R2以及之前的版本,你的SQL執行可能被延遲,直到有充足的並行伺服器可用。
Oracle有一種輪換IO機制,叫做“直接路徑IO”,如果它判斷到繞過快取區直接執行IO會更快速的話, 它就會啟用。例如,Oracle在讀寫臨時段進行排序或者整理中間結果集時就會使用直接IO。從Oracle 11g開始,Oracle有時也優先利用直接路徑IO來處理序列表訪問,而不是正常的快取IO。
在執行並行查詢操作時,Oracle通常會使用直接路徑IO。透過使用直接路徑IO,Oracle可以避免建立快取記憶體競爭,並可以使IO更加最佳化地在並行程式之間分配。此外,對於執行全表掃描的並行操作,在快取記憶體找到匹配資料的機會相當低,因此快取記憶體幾乎沒有增加什麼價值。
在Oracle 10g以及更早的版本,並行查詢總是使用直接路徑IO,而序列查詢將總是使用快取IO。在11g中,Oracle可以對並行查詢利用快取IO(從11g R2以後的版本支援),而且序列查詢也可能利用直接路徑IO。然而,並行查詢仍然不太可能利用快取IO,因此,可能比序列查詢需要更高的IO成本。當然, 更高的IO成本將在所有並行程式之間共享,這樣整體效能仍可能更勝一籌。
並行的最佳化準則
從最佳化序列執行的SQL開始
一個最理想的並行計劃與最佳的序列計劃可能是有區別的。例如,並行處理通常從表或索引的掃描開始,而最佳序列計劃可能是基於索引查詢開始。然而,你應該確保你的查詢在進行並行最佳化之前先對序列執行進行最佳化,原因如下:
1、序列除錯的結構和方法主要針對索引和統計集合,而這些經常對好的並行除錯也非常關鍵。
2、如果請求並行執行的資源不可用,你的查詢可能被序列化(這取決於“PARALLEL_DEGREE_ POLICY”和“PARALLEL_MIN_PERCENT”的設定)。在這種情況下,你要確保你並行查詢的序列計劃足夠好。
缺少調優的SQL甚至可能變成更差的SQL,至少考慮到對其他使用者的影響時是這樣,這使它被允許消耗資料庫伺服器更多的CPU和IO資源。
在為並行執行最佳化SQL語句時,要從未序列執行SQL最佳化開始。
確保該SQL是合適的並行執行SQL
不是每個SQL都能從並行執行中獲益的。下面是一些例子,這些情況的SQL語句可能不應該被並行化。
1、序列執行時,執行時間很短的SQL語句。
2、可能在多個會話中高併發率執行的SQL語句。
3、基於索引查詢的SQL語句。非並行的索引查詢或者範圍掃描不能被並行化。然而,索引全掃描可以被並行化。在分割槽索引上的索引查詢也可以被並行化。
綜上3點,OLTP型別的查詢通常不適合並行化處理。
確保系統適合配置為並行執行
不是所有的SQL都適合並行執行,也不是所有的資料庫伺服器主機適合配置並行處理。在當今世界,大部分物理伺服器主機都滿足如下最小需求:多塊CPU和 跨多個物理驅動器的資料帶。然而,一些虛擬主機可能不滿足這些最小需求,而桌面計算機通常只有唯一的磁碟裝置,因此通常不適合調整為並行執行。
不要嘗試在那些不滿足最小需求(多塊CPU和跨多個磁碟驅動器的資料帶)的計算機系統上使用並行執行。
確保執行計劃的所有部分都被並行化了
在複雜的並行SQL語句中,很重要的一點是要確保該查詢執行的所有重要步驟都實現了並行。如果某複雜查詢的其中一個步驟是序列執行的,其他並行步驟可能 也不得不等待該序列步驟完成,這樣並行機制的優勢就完全喪失了。“PLAN_TABLE”表中的“OTHER_TAG”列用 “PARALLEL_FROM_SERIAL”標記指定了這樣一個步驟,“DBMS_XPLAN”在“IN-OUT”列中記錄了“S->P”。例如:在下面的例子中表“T1”是並行化的,但是表“T”不是。對兩個表的連線和“GROUP BY”包括許多並行操作,但是對“T”表的全表掃描不是並行化的,串到並(S->P)標記展示了“t”行被序列提取到後續並行操作中:
SQL> ALTER TABLE customers PARALLEL(DEGREE 4);
SQL> ALTER TABLE sales NOPARALLEL ;
SQL> explain plan for select /*+ ordered use_hash(t1) */ t.name,sum(t.id) from t,t1 where t.id=t1.object_id group by t.name;
SQL> 1* select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2575143521
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61 | 4758 | 22 (10)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 61 | 4758 | 22 (10)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 61 | 4758 | 22 (10)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 61 | 4758 | 22 (10)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | 61 | 4758 | 22 (10)| 00:00:01 | Q1,01 | P->P | HASH |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 | HASH GROUP BY | | 61 | 4758 | 22 (10)| 00:00:01 | Q1,01 | PCWP | |
|* 7 | HASH JOIN | | 61 | 4758 | 21 (5)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | 61 | 3965 | 12 (0)| 00:00:01 | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | 61 | 3965 | 12 (0)| 00:00:01 | | S->P | BROADCAST |
| 11 | TABLE ACCESS FULL | T | 61 | 3965 | 12 (0)| 00:00:01 | | | |
| 12 | PX BLOCK ITERATOR | | 52078 | 661K| 8 (0)| 00:00:01 | Q1,01 | PCWC | |
| 13 | INDEX FAST FULL SCAN| T1_IDX | 52078 | 661K| 8 (0)| 00:00:01 | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------
像前面這種情況,部分並行化執行計劃可能會導致兩方面效果都很差:消耗的時間並沒有改善,因為序列操作形成了整個執行的瓶頸。然而,該SQL還捆綁了並行伺服器程式,而且可能影響其他併發執行SQL的效能。
如果我們為表“t”設定一個預設的並行度,該序列瓶頸將會消失。對“t”表的全掃描現在是按並行執行了,而且“串到並S->P”瓶頸將被全並行的“併到並P->P”操作替代
在最佳化並行執行計劃時,要確保所有相關步驟都在並行執行:“DBMS_XPLAN”中的串到並S->P 標記或者“PLAN_TABLE”中的“PARALLEL_FROM_SERIAL”通常指示在並行計劃的某些方面存在序列瓶頸。
確保請求的DOP(Degree Of Parallel)是可實現的
超過調優限度增加DOP可能給系統增加額外負載,而不會提升效能。在最壞的情況下,超過調優限度增加DOP可以導致查詢執行時間減少。因此,設定合適的DOP對於資料庫整體的健康和並行查詢的效能最佳化都是非常重要的。
確保你請求或預期的DOP是現實的;過高的DOP可以導致資料庫伺服器負載過度,而不會提升SQL的效能。
監視實際DOP
你請求的DOP可能被最佳化,但是並不總是能成功。當多個並行查詢競爭有限的並行執行資源時,DOP可能會減少,或者SQL語句可能會以序列模式執行。
我們前面討論了Oracle如何決定實際DOP;最重要的引數“PARALLEL_MIN_PERCENT”,“PARALLEL_DEGREE_ POLICY”和“PARALLEL_ADAPTIVE_MULTI_USER”控制了Oracle改變DOP的方式,不管語句執行在降低並行,出錯終止,還是在現存資源不足以請求的DOP執行該語句時推遲到後續處理。
DOP的減少可以導致你的並行SQL表現出令人失望的效能。你應該監視查詢執行來看是否DOP的減少確實出現了。我們可以使用“V$PQ_TQSTAT”來度量實際DOP。見下例,
SQL> select dfo_number,tq_id,server_type,min(num_rows),max(num_rows),count(*) dop
from v$pq_tqstat
group by dfo_number,tq_id,server_type
order by dfo_number,tq_id,server_type
2 3 4 5 ;
DFO_NUMBER TQ_ID SERVER_TYPE MIN(NUM_ROWS) MAX(NUM_ROWS) DOP
---------- ---------- ------------------------------ ------------- ------------- ----------
1 0 Consumer 61 61 1
1 0 Producer 0 49 4
值得一提的是,由於操作間並行會有多組slave process通訊,例如組1scan資料給組2 order by, 該檢視中的server_type欄位中就把 組1定義為生產者(producer),組2定義為消費者(consumer)
我們還可以利用v$session中的PDML_STATUS,PDDL_STATUS,PQ_STATUS檢視當前會話的預設並行狀態
select PDML_STATUS,PDDL_STATUS,PQ_STATUS from v$session
另外,還可以利用如下方式查詢系統的並行狀態
當並行啟動時,Oracle中的並行伺候程式池會根據系統中的負荷和實際因素,確定分配出的並行程式數量。此時,我們可以透過檢視v$px_process來檢視程式池中的連線資訊。
select * from v$px_process;
注意:並行伺候程式是一種特殊的Server Process,本質上是一種可共享的slave程式。專用連線模式下,一般的Server Process與Client Process是“同生共死”的關係,終身服務於一個Client Process。而伺候slave程式是透過程式池進行管理的,一旦啟動初始化,就會在一定時間內駐留在系統中,等待下次並行處理到來。
此時,我們檢查v$process檢視,也可以找到對應的資訊。
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
對應的OS中,也存在相應的真實程式伺候。
$ 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
並行操作程式的資源消耗,透過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
如果你發現降級的並行導致了令人失望的效能,你可能想重新審查你的系統資源(記憶體,IO頻寬),排程並行SQL,或者重新檢查伺服器配置。可能的選擇包括:
重新排程SQL,以便它們不要併發執行。Oracle 11g R2可以自動排程SQL,只要你設定“PARALLEL_ DEGREE_POLICY”引數為“AUTO”。
調整並行配置引數,以支援更大的併發並行。你可以透過增加“PARALLEL_THREADS_PER_ CPU”或者“PARALLEL_MAX_SERVERS”的值來做到這一點。這裡的風險是並行執行的總量將會比你係統能支援的數量要多,這會導致SQL 效能退化。
增加你資料庫伺服器的效能。你可以增加CPU數量,RAC叢集中例項的數量,你磁碟陣列中磁碟的數量。
調整“PARALLEL_MIN_PERCENT ”引數,使SQL可以執行在較少的並行狀態下,而不是報錯。
參考至:《讓Oracle跑得更快》譚懷遠著
《讓Oracle跑得更快2》譚懷遠著
%E6%95%B0%E6%8D%AE%E5%BA%93/137.html
http://www.cnblogs.com/daduxiong/archive/2010/08/24/1807427.html
http://blog.csdn.net/tianlesoftware/article/details/5854583
http://space.itpub.net/17203031/viewspace-696389
本文轉自:http://czmmiao.iteye.com/blog/1487568