Sql最佳化(三) 關於oracle的併發

Oracle的併發技術可以將一個大任務分解為多個小任務由多個程式共同完成。合理地使用併發可以充分利用系統資源，提高效率。
一、 併發的種類
Parallel query
Parallel DML(PDML)
Parallel DDL
Parallel recovery

[@more@]

二、 適用場合
適用parallel的兩個條件
1）大的任務，如全表掃描大表
這和日常生活中的經驗是一樣的，小任務自己完成都比派發任務省事
2）系統有足夠的資源(cpu/io)
換句話說，併發是在系統資源充足、使用者少的系統上，為了充分利用系統資源以提高任務處理速度而設計的一種技術。以下是幾種場景：
1）OLTP系統 有大量使用者和session，如果每個session使用併發查詢將導致系統崩潰。但也有例外例如計費系統月底或下班後沒有或使用者很少訪問，執行批處理程式，此時可使用併發提高速度
2）資料倉儲系統 通常可使用併發查詢、PDML等併發，注意有些資料倉儲系統也提供給大量使用者訪問，這種系統有某些OLTP特性，應慎用併發
3）無論是OLTP還是資料倉儲，維護期間使用parallel ddl和PDML對管理員來說是非常有用的

三、 Parallel query
使用併發查詢的方法：
1）修改表屬性
Alter table big_table parallel 4;
Alter table big_table parallel ;由oracle根據系統資源情況決定。這是推薦的.Oracle根據cpu數目乘以parallel threads per cpu引數（default 2），例如4cpu的機器，oracle決定parallel數目為8
2）使用hint , select * /*+ PARALLEL(emp,12) */ …

四、 PDML
例子：
ALTER TABLE emp PARALLEL (10);
ALTER SESSION ENABLE PARALLEL DML;
INSERT INTO emp
SELECT * FROM t_emp;
COMMIT;
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(emp,12) */ INTO emp
SELECT /*+ PARALLEL(t_emp,12) */ * FROM t_emp;
COMMIT;
注意：使用parallel後，insert select * 語句自動就使用direct-load了，此時不再需要使用append hint（ /*+APPEND */）
PDML的限制：
不支援有trigger的表,在上面做PDML，能成功，但忽略了併發性
不支援某些約束，例如self-referential integrity。原因是PDML分為多個獨立的session去修改資料，無法保證某些完整性；容易引起死鎖已經其他鎖問題
一個session使用了PDML，在commit/rollback之前，另一個session無法再使用PDML
Advanced replication不支援（因為使用了trigger）
Deferred constraints（約束的deferred模式指修改操作在提交時才去驗證是否滿足約束條件）不支援
分散式事務不支援
Clustered tables不支援
當違反這些限制，PDML要麼報錯，要麼忽略並行度

五、 併發與空間浪費
Parallel DDL以及某些PDML依賴於direct path load，即繞過databuffer直接寫資料檔案。
例如，create table as select ，insert /*+APPEND */,
這會形成空間浪費，例如倒入1010M資料,每個extent 100m，direct path load會新分配100m 的extent來存放資料（如果有小於100m的extent，常規insert可以用這些空間）。假設10個併發，每個併發倒入101M資料，會建立2個extent,則總共會建立20個extent,則形成990m空間浪費。一方面浪費了空間（如果表建立之後有常規insert，則能使用這些空間），另一方面全表掃描時會搜尋這些空的extent，這也降低了全表掃描的速度。

表空間的extent管理有兩種方式，unform size，則每個extent大小相同，autoallocate是oracle根據內部機制決定extent大小，更靈活
Uniform 方式不支援extent trimming，而autoallocate在parallel ddl中用到extent trimming，減少了空間浪費。
因此在頻繁使用parallel DDL操作的表空間上，要麼減少uniform size每個extent的大小，要麼使用autoallocate ，以減少空間浪費。

六、 併發DIY-儲存過程的併發
以下是一個常見任務：掃描全表，修改資料，再寫入新的表
如果一個程式處理太慢，我們通常會自己將資料劃分，然後開多個程式呼叫。
使用11gr2 內建的併發包：DBMS_PARALLLEL_EXECUTE，大大簡化了這一過程
（11gr2之前，沒有內建的併發程式包，需要手工按照rowid或主鍵劃分大表，然後透過dbms_job或dbms_schedule併發呼叫。）

我們以前兩天***的一個程式為例，看看如何使用這一併發技術（本例較簡單，不見得需要使用這樣技術，僅僅作為例子來說明）
程式的目的是刪除bmf中orig_bill_ref_no like '18%'的記錄，本來一句sql可以完成，由於資料量太大,系統回滾段不足。因此開發人員準備分多個程式執行
declare
cursor c1
is select orig_bill_ref_no from bmf where orig_bill_ref_no like '18%'
and mod(account_no, 5) = 0; （將資料分為5段）
begin
for r1 in c1 loop
delete from bmf where orig_bill_ref_no = r1.orig_bill_ref_no;
commit;
end loop;
commit;
end;
/
這樣的寫法會有什麼問題呢，很快就遇到snapshot too old錯誤了。原因是select開啟bmf遊標，同時修改bmf並commit資料，由於查詢一致性要求，開啟的遊標要看到的是bmf修改之前的情況，這是從undo去讀的，因此一旦時間超出undo_retention，undo資訊過期，就報snapshot too old了。

使用ora11g提供的併發包的寫法：
1) 建立過程serial過程，用來被多個併發執行緒呼叫
create or replace
procedure serial( p_lo_rid in rowid, p_hi_rid in rowid )
is
begin
delete from bmf
where rowid between p_lo_rid and p_hi_rid and orig_bill_ref_no like '15%';
end;
/

2) 按照rowid將表劃分為多個chunk，供執行緒呼叫
begin
dbms_parallel_execute.create_task('PROCESS BIG TABLE');
dbms_parallel_execute.create_chunks_by_rowid
( task_name => 'PROCESS BIG TABLE',
table_owner => 'LUW',
table_name => 'BMF',
by_row => false, --不按行記錄數而按block數
chunk_size => 2000 );
end;
/

select *
from (
select chunk_id, status, start_rowid, end_rowid
from dba_parallel_execute_chunks
where task_name = 'PROCESS BIG TABLE'
order by chunk_id
)
where rownum <= 5
/
3) 發起併發任務，按照第2步對錶的劃分來分配並執行任務
begin
dbms_parallel_execute.run_task
( task_name => 'PROCESS BIG TABLE',
sql_stmt => 'begin serial( :start_id, :end_id ); end;',
language_flag => DBMS_SQL.NATIVE,
parallel_level => 4 );
end;
/
4) 刪除併發作業
begin
dbms_parallel_execute.drop_task('process big table' );
end;
/

那麼使用併發和簡單的delete相比，速度怎樣呢
使用併發：
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.07
直接delete:
delete from bmf where orig_bill_ref_no like '15%';
403525 rows deleted.
Elapsed: 00:00:08.12

這說明使用併發提高了速度，更別說對回滾段的空間要求也少了。