如何加速Oracle大批量資料處理____Oracle

 

一、提高DML操作的辦法：

　　簡單說來：

　　１、暫停索引，更新後恢複.避免在更新的過程中涉及到索引的重建.

　　２、批次更新，每更新一些記錄後及時進行提交動作.避免大量佔用復原段和或暫存資料表空間.

　　３、建立一臨時的大的資料表空間用來應對這些更新動作．

　　4、批次更新，每更新一些記錄後及時進行提交動作.避免大量佔用復原段和或暫存資料表空間.

　　5、建立一臨時的大的資料表空間用來應對這些更新動作．

　　6、加大排序緩衝區

　　alter session set sort_area_size=100000000;

　　insert into tableb select * from tablea;

　　commit;

　　如果UPDATE的是索引欄位，就會涉及到索引的重建，暫停索引不會提高多少的速度，反而有可能降低UPDATE速度，

　　因為在更新是索引可以提高資料的查詢速度，重建索引引起的速度降低影響不大。

　　ORACLE最佳化修改參數最多也只能把效能提高15%，大部分都是SQL語句的最佳化。

　　update總體來說比insert要慢 ：

　　幾點建議：

　　1、如果更新的資料量接近整個表，就不應該使用index而應該採用全表掃描

　　2、減少不必要的index，因為update表通常需要update index

　　3、如果你的伺服器有多個cpu，採用parellel hint，可以大幅度的提高效率

　　另外，建表的參數非常重要，對於更新非常頻繁的表，建議加大PCTFREE的值，以保證資料區塊中有足夠的空間用於UPDATE， 從而降低CHAINED_ROWS。

　　二、各種批量DML操作：

　　（1）、oracle批量拷貝：

　　set arraysize 20

　　set copycommit 5000

　　copy from username/password@oraclename append table_name1

　　using select * from table_name2;

（2）、常規插入方式：

　　insert into t1 select * from t;

　　為了提高速度可以使用下面方法，來減少插入過程中產生的日誌:

　　alter table t1 nologging;

　　insert into t1 select * from t;

　　commit;

　　(3)、CTAS方式：

　　create table t1

　　as

　　select * from t;

　　為了提高速度可以使用下面方法，來減少插入過程中產生的日誌，並且可以制定並行度:

　　create table t1 nologging parallel(degree 2) as select * from t;

　　（4）、Direct-Path插入：

　　insert /*+append*/ into t1 select * from t;

　　commit;

為了提高速度可以使用下面方法，來減少插入過程中產生的日誌：

　　alter table t1 nologging;

　　insert /*+append*/ into t1 select * from t;

　　Direct-Path插入特點：

　　1、 append只在insert … select …中起作用，像insert /*+ append */ into t values(…)這類的語句是不起作用的。在update、delete操作中，append也不起作用。

　　2、 Direct-Path會使資料庫不記錄直接路徑匯入的資料的重做日誌，會對恢複帶來麻煩。

　　3、 Direct-Path直接在表段的高水位線以上的空白資料區塊中寫資料，不會重用高水位線以下的空間，會對空間的使用造成一定的浪費，對查詢的效能也會造 成一定的影響。而常規插入會優先考慮使用高水位線之下有空閑空間存在的資料區塊。因此理論上Direct-Path插入會比常規插入速度更快，因為 Direct-Path直接使用新資料區塊，而常規插入要遍曆freelist擷取可用空閑資料區塊，如果同 nologging 配合，這種速度優勢會更加明顯。

　　4、 以append方式插入記錄後，要執行commit，才能對錶進行查詢。否則會出現錯誤：ORA-12838: 無法在並行模式下修改之後讀/修改對象。

　　5、 用append匯入資料後，如果沒有提交或者復原，在其他會話中任何對該表的DML都會被阻塞（不會報錯），但對該表的查詢可以正常執行。

　　6、在歸檔模式下，要把表設定為nologging，然後以append方式大量新增記錄，才會顯著減少redo數量。在非歸檔模式下，不必設 置表的 nologging屬性，即可減少redo數量。如果表上有索引，則append方式大量新增記錄，不會減少索引上產生的redo數量，索引上的redo 數量可能比表的redo數量還要大。

　　7、 資料直接插入資料檔案，繞過buffer cache並且忽略了參考完整性約束。

　　8、 不管表是否在nologging 下，只要是 direct  insert，就不會對資料內容產生undo。

　　9、 Oracle在Direct-Path INSERT 操作末尾，對具有索引的表執行索引維護，這樣就避免了在drop掉索引後，再rebuild。

　　10、Direct-Path INSERT比常規的插入需要更多的空間。因為它將資料插入在高水位之上。並行插入非分區表需要更多的空間，因為它需要為每一個並行線程建立臨時段。

　　11、在插入期間，資料庫在表上獲得獨佔鎖定，使用者不能在表上執行並行插入、更新或者刪除操作，並行的索引建立和build也不被允許。但卻可以並行查詢，但查詢返回的是插入之前的結果集。

　　（5）、並行DML：

　　如果你的伺服器有多個cpu，採用parellel hint，可以大幅度的提高效率

　　ALTER SESSION ENABLE PARALLEL DML;

　　INSERT /*+ PARALLEL(tableA, 2) */INTO tableA

　　SELECT * FROM tableB;

為了提高速度可以使用下面方法，來減少插入過程中產生的日誌：

　　INSERT /*+ PARALLEL(tableA, 2) */INTO tableA NOLOGGING

　　SELECT * FROM tableB;

　　oracle預設並不會開啟PDML，對DML語句必須手工啟用。即需要執行

　　alter table enable parallel dml命令。

　　並行DML特點：

　　1、在並行DML模式中，預設的就是DIRECT-PATH插入，為了運行並行DML模式，必須滿足以下條件：

　　a、必須是Oracle企業版；

　　b、必須在session中使並行DML生效，執行以下sql語句：

　　ALTER SESSION { ENABLE | FORCE } PARALLEL DML;

　　c、必須指定table的並行屬性，在建立的時候或者其他時候，或者在insert操作時使用“PARALLEL”提示。

　　d、為了使Direct-Path Insert模式失效，在INSERT語句中指定“NOAPPEND”提示，覆蓋並行DML模式。

　　2、並行Direct-Path INSERT到分區表：

　　類似於serial Direct-Path INSERT，每個並行操作分配給一個或者多個分區，每個並行操作插入資料到各自的分區段的高水位標誌之上，commit之後，使用者就能看到更新的資料。

　　3、並行Direct-Path INSERT到非分區表：

　　每個並存執行分配一個新的臨時段，並插入資料到臨時段。當commit運行後，並存執行協調者合并新的臨時段到主表段，使用者就能看到更新的資料。

　　4、Direct-Path INSERT可以使用Log或者不使用Log。

　　5、另外不得不說的是，並行不是一個可擴充的特性，只有在資料倉儲或作為DBA等少數人的工具在批量資料操作時利於充分利用資源，而在OLTP 環境下使用並行需要非常謹慎。事實上PDML還是有比較多的限制的，例如不支援觸發器，參考條件約束，進階複製和分散式交易等特性，同時也會帶來額外的空間占 用，PDDL同 樣是如此。