oracle數據庫優化一則

本次優化涉及到oracle數據庫大量insert 數據索引帶來的開銷，使用append、parallel的方式進行改善性能。

本次案例會用到2個hint，append 和parallel，環境是一個數據倉庫場景，業務數據處理具有單一性，非常適合通過並行提高單次大業務的速度。

/*+ append */以直接加載的方式將數據加載入庫，它是從HWM的位置開始插入，可能會造成空間浪費的。非歸檔模式下可以減少redo的產生;歸檔模式下，一般情況下數據庫會強製logging，歸檔減少不了。

對於OLTP類型的數據庫，除非隻用於做統計、報表類的表或索引，建議不對相關表或索引調置並行度。在數據庫有開啟並行查詢的情況下，在表或索引上存在默認並行度，將導致數據庫優先采用全表或全索引掃描的執行計劃，另外將生成多個並行子進程，對於OLTP類應用將反而降低相關SQL的執行效率。

   Oracle在並行處理時，會啟動多少個並行進程來同時執行任務，並行度越高， 並行進程越多，執行速度 會越快,默認是noparallel，如果我們設置並行度為default值，那麼此時的並行度是：

服務器CPU數*每個CPU啟用的線程數（PARALLEL_THREADS_PER_CPU），OLTP環境 所以一般建議使用Noparallel，或者將並行度設置為1，而不是default。

並行度hint的使用方法：

/*+ parallel(table_name n) */ 

/*+ no_parallel(table_name) */ 

testtb 表從dba_objects中取數86381 條數據，testtb 將作為數據加載到testtb1的數據來源，testtb 上麵沒有索引，在業務上使用insert into select 計算數據時，select段要首先控製其性能，這裏測試咱不涉及select段的性能。

SQL> create table testtb as select * from dba_objects where 1=0;

SQL> create table testtb1 as select * from dba_objects where 1=0;

SQL> insert into testtb1 select * from testtb;

345524 rows created.

Elapsed: 00:00:01.49

我們看到插入數據<2s鍾，此時增加一個索引

create index ind_OBJECT_NAME_testtb on testtb1(OBJECT_NAME);

SQL> insert into testtb1 select * from testtb;

345524 rows created.

Elapsed: 00:00:14.46 <<插入入數據將近15s

再增加索引

create index ind_OBJECT_ID_testtb on testtb1(OBJECT_ID);

SQL> insert into testtb1 select * from testtb;

345524 rows created.

Elapsed: 00:00:20.00<<插入入數據將近20s

再增加索引

create index ind_CREATED_testtb on testtb1(CREATED);

create index ind_CREATED_OBJECT_ID_testtb on testtb1(CREATED,OBJECT_ID);

SQL> insert into testtb1 select * from testtb;

345524 rows created.

Elapsed: 00:01:01.18<<插入入數據已經到60s以上

假如這些索引都是我們必須的，接下來該hint出場了

insert /*+Append parallel(8)*/ into testtb1 select * from testtb;

SQL> insert /*+Append parallel(8)*/ into testtb1 select * from testtb;

345524 rows created.

Elapsed: 00:00:21.43<<插入入數據21s

我們來看看執行過程

insert into testtb1 select * from testtb;

insert /*+Append parallel(8)*/ into testtb1 select * from testtb;

加hint提示後，select段使用的並行，但是load as select並沒有使用並行，也就是說，parallel隻對select段起作用了。

我們再來看看alter session enable parallel dml；

此時加載數據也有並行，用戶是使用pakage 和存儲過程執行業務流，

create or replace procedure proc_1 AUTHID CURRENT_USER parallel_enable as

begin

insert /*+Append parallel(2)*/ into testtb1 select * from testtb;

commit;

DBMS_OUTPUT.put_line('Haved insert OK!');

end;

/

通過10046事件跟蹤存儲過程的執行

oradebug setmypid;

oradebug unlimit;

oradebug event 10046 trace name context forever, level 12;

exec proc_1;

oradebug event 10046 trace name context off;

oradebug tracefile_name;

經過測試，存儲過程中的insert段是無法使用會話級並行的。這一點將在12c得到優化，hint中增加了enable_parallel_dml提示，同時這個參數包含了append的特性。

2、真實的場景

insert 語句已經使用了append 和parallel提示進行了優化，但執行1972s還是不能接受，查看插入的對象表，生產環境上該表的索引有8個，包含3個複合索引，測試環境雖然也有8個索引，但是插入速度比生產快，但兩者環境不同，不能按照具體數值比較，測試環境索引是優化後的，去掉了多列的複合索引，開發兄弟把生產的索引和測試環境比照進行了重建，當晚執行時間下降到了359s，因此索引一定要按需創建、優化創建。

這條sql執行273s，也是insert into ……select……的類型操作，select部分通過/*+ append parallel */提示從89s 縮減到8s。

六、總結

1. 建立必要的索引，廢棄的索引盡快清理

2. 建立不同數據的生命周期，按周期清理，曆史歸並等

3. 合理使用並行 /*+ parallel（8）*/