用直接路徑(direct-path)insert提升效能的兩種方法,direct-pathinsert
1、傳統串列insert方式
常見的insert方式有兩種:
(1) insert into table_name values(....)
(2) insert into target_table select* from source_table
上面這兩種常規的插入式,預設都是在串列方式下的插入,會將insert的資料寫入buffer_cache,插入前檢查表中是否有block中存有空閑空間可以追加插入,並寫入redo log和資料的undo。
2、直接路徑(direct-path)insert優點與使用要點2.1、Direct-path insert 方式的優點
(1) 可以將insert資料跳過buffer_cahce,省掉了buffer block的格式化與DBWR操作,直接從PGA寫入磁碟
(2) 不檢查表中現有的block是否有空閑空間,直接在表的高水位線(HWM)以上插入
(3) 如果在資料庫處於非歸檔模式下,或者是資料就處于歸檔模式,表級處於nologging狀態下,只有少量的空間資訊redo寫入、不寫入資料undo(因為要復原時可以直接回退到高水線即可,而不需要針對insert產生delete的復原記錄),所以在特定的情況下,直接路徑(direct-path)的insert方式,在效能上遠遠快於常規的串列插入方式。
2.2、使用direct-path insert需要特別注意的要點2.2.1 DB非force loggging模式下direct-path insert對redo與undo的寫入影響
如果在資料庫處于歸檔模式,以及表處於logging模式下,直接路徑(direct-path)效能提升會大打折扣,因為,雖然direct-path能生效,但是仍然會記錄下完整的redo和undo。
也就是說,在歸檔模式下,還需要將表改成nologging模式,才不會寫資料的redo和undo。
2.2.2 DB force logging模式下direct-pathinsert對redo的寫入影響
Note: If the database or tablespace is in FORCE LOGGING mode, then direct-path INSERT always logs, regardless of the logging setting. |
如果資料庫或資料表空間在forcelogging模式,則direct-path insert總是會寫日誌,無論logging如何設定。
3、使用直接路徑(direct-path)insert的方法3.1 方法一:使用/*+ APPEND */ hint方式
以下為ORACLE官方技術資料對APPENDhint的說明:
APPEND hint: Instructs the optimizer to use direct-path INSERT (data is appended to the end of the table, regardless of whether there is free space in blocks below the high watermark) |
3.1.1 資料庫非歸檔模式下使用/*+ APPEND*/ hint方式
當資料庫處於非歸檔模式下,不管表為logging模式還是nologging模式,使用/*+APPEND */ hint,既可以使用direct-path,還將不記錄redo和undo
用法如下:
INSERT /*+ APPEND */ INTO new_object SELECT * FROM dba_objects; |
3.1.2 資料庫處于歸模模式下使用/*+APPEND */ hint方式
當資料庫處于歸模模式下,若表為logging模式,即便使用/*+APPEND */ hint,雖然direct-path可以起到作用,但是insert操作仍然會寫redo記錄,就算你在insert語句上加nologging也不會有效果,redo日誌與undo照寫不誤。
需要通修改表或修改索引,或修改資料表空間的no-logging模式來達到不寫redo與undo的效果
以下為從metalink(文檔ID166727.1)中找到的技術資料:
The APPEND hint is required for using serial direct-load INSERT. Direct-load INSERT operations can be done without logging of redo information in case the database is in ARCHIVELOG mode. Redo information generation is suppressed by setting no-logging mode for the table, partition, or index into which data will be inserted by using an ALTER TABLE, ALTER INDEX, or ALTER TABLESPACE command. |
用法如下:
Alter table new_object nologging; INSERT /*+ APPEND */ INTO new_object SELECT * FROM dba_objects; |
3.2 方法二:DML並行模式的方式
DML並行模式下,direct-path插入方式是預設的,當然,在DML並行模式下如果想不使用direct-path插入,可以通過加noappendhint實現。以下是DML並行模式下的direct-path插入:
並行DML的前提條件:
(1)ORACLE版本為Oracle Enterprise Edition
(2)操作的會話開啟並行DML
(3)下面三項要求必須滿足一項:
1)目標表上開啟並行屬性(DEGREE)
2)插入語句中指定並行提示(/*+ parallel n */)
3)有設定PARALLEL_DEGREE_POLICY參數的值為AUTO
以資料庫為非歸檔模式用法為例(注意歸檔模式,還需將表改成nologging模式): (1)alter session enable parallel dml; 語句還有選項有::ALTER SESSION { ENABLE | FORCE } PARALLEL DML; (2)alter table new_object_directpath parallel 8; (3)insert /*+PARALLEL(new_object_directpath, 8) */into new_object_directpathnologging select * from new_object_old; |
4、歸檔模式下傳統串列方式與direct-path方式insert效能對比
環境說明:
源表名 |
test_dba_objects |
源表行數 |
1630104 |
源表segment大小 |
184MB |
操作步驟與效能對比結果如下:
傳統串列insert方式 |
APPEND hint的direct-path insert方式 |
DML並行的direct-path insert方式 |
(1)建表與修改設定 SQL>create table new_object_directpath as select * from test_dba_objects where 1=2 SQL>alter table new_object_directpath nologging SQL> SET TIMING ON Elapsed: 00:00:00.54 (2)insert耗時 SQL> insert into new_object_directpath nologgingselect * from test_dba_objects; 1630104 rows created.
Elapsed: 00:00:12.43 未產生資料redo與undo |
(1)建表與修改設定 SQL>create table new_object_directpath as select * from test_dba_objects where 1=2 SQL>alter table new_object_directpath nologging SQL> SET TIMING ON Elapsed: 00:00:00.54 (2)insert耗時 SQL> insert /*+APPEND */ into new_object_directpath select * from test_dba_objects; 1630104 rows created. Elapsed: 00:00:05.83 未產生資料redo與undo |
SQL>create table new_object_directpath as select * from test_dba_objects where 1=2 SQL>alter table new_object_directpath nologging SQL> SET TIMING ON Elapsed: 00:00:00.54 (2)修改表的並行模式 SQL> alter table new_object_directpath parallel 8; (3) insert耗時 SQL> insert /*+parallel (new_object_directpath,8) */ into new_object_directpath select * from test_dba_objects; 1630104 rows created. Elapsed: 00:00:05.61 未產生資料redo與undo |
本文作者:黎俊傑(網名:踩點),從事”系統架構、作業系統、存放裝置、資料庫、中介軟體、應用程式“六個層面系統性的效能最佳化工作
歡迎加入 系統效能最佳化專業群,共同探討效能最佳化技術。群號:258187244