SQLLDR直接載入幾個引數的測試
比較好的一篇關於增強sqlldr效能的文章
1.幾個檔案的準備:
create table L5M.load_01 as
select 1 as u_id ,a.* from (
select * from all_tables where 1=0 )a;
[oracle@qht108 sqlldr]$ cat para.txt
userid=l5m/l5m
control='/home/oracle/sqlldr/control.txt'
data='/home/oracle/sqlldr/data.txt'
log='/home/oracle/sqlldr/log.txt'
bad='/home/oracle/sqlldr/bad.txt'
#errors=0
direct=true
[oracle@qht108 sqlldr]$ cat control.txt
load data
append into table LOAD_01
fields terminated by ',' TRAILING NULLCOLS
(u_id recnum,OWNER,TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME,IOT_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,FREELISTS,FREELIST_GROUPS,LOGGING,BACKED_UP,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS,DEGREE,INSTANCES,CACHE,TABLE_LOCK,SAMPLE_SIZE,LAST_ANALYZED date 'yyyy-mm-dd HH24:mi:ss)',PARTITIONED,IOT_TYPE,TEMPORARY,SECONDARY,NESTED,BUFFER_POOL,ROW_MOVEMENT,GLOBAL_STATS,USER_STATS,DURATION,SKIP_CORRUPT,MONITORING,CLUSTER_OWNER,DEPENDENCIES,COMPRESSION,DROPPED)
data.txt檔案是將all_tables的資料導成txt檔生成的,複製了N遍後有232M.
[oracle@qht108 sqlldr]$ tail -5 data.txt
SYS,SCHEDULER$_WINDOW_GROUP,SYSTEM,,,VALID,10,40,1,255,65536,,1,2147483645,,1,1,YES,N,1,1,0,0,0,7,0,0, 1, 1, N,ENABLED,1,2008-2-17 2:02:38,NO,,N,N,NO,DEFAULT,DISABLED,YES,NO,,DISABLED,YES,,DISABLED,DISABLED,NO
SYS,SCHEDULER$_WINGRP_MEMBER,SYSTEM,,,VALID,10,40,1,255,65536,,1,2147483645,,1,1,YES,N,2,1,0,0,0,8,0,0, 1, 1, N,ENABLED,2,2008-2-17 2:02:38,NO,,N,N,NO,DEFAULT,DISABLED,YES,NO,,DISABLED,YES,,DISABLED,DISABLED,NO
SYS,SCHEDULER$_SCHEDULE,SYSTEM,,,VALID,10,40,1,255,65536,,1,2147483645,,1,1,YES,N,1,1,0,0,0,50,0,0, 1, 1, N,ENABLED,1,2008-2-17 2:02:38,NO,,N,N,NO,DEFAULT,DISABLED,YES,NO,,DISABLED,YES,,DISABLED,DISABLED,NO
SYS,SCHEDULER$_CHAIN,SYSTEM,,,VALID,10,40,1,255,65536,,1,2147483645,,1,1,YES,N,0,0,0,0,0,0,0,0, 1, 1, N,ENABLED,0,2008-2-17 2:02:37,NO,,N,N,NO,DEFAULT,DISABLED,YES,NO,,DISABLED,YES,,DISABLED,DISABLED,NO
SYS,SCHEDULER$_STEP,SYSTEM,,,VALID,10,40,1,255,6
[oracle@qht108 sqlldr]$ du -h data.txt
232M data.txt
建立redo_size檢視,便於檢視redosize,當然這裡取的系統的整個redo,由於sqlldr沒有辦法抓取到v$mystat的資料.
create or replace view redo_size
as
select value
from v$sysstat, v$statname
where v$sysstat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
2.直接用上面的引數檔案進行load的結果.
[oracle@qht108 sqlldr]$ sqlldr parfile=para.txt
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Jul 16 12:34:31 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Load completed - logical record count 311379.
檢視loading時的日誌,用了1分17.92秒
[oracle@qht108 sqlldr]$ tail -29 log.txt
Table LOAD_01:
1123237 Rows successfully loaded.
4 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Date cache:
Max Size: 1000
Entries : 83
Hits : 1055798
Misses : 0
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 1123241
Total logical records rejected: 4
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 233
Total stream buffers loaded by SQL*Loader load thread: 924
Run began on Wed Jul 16 13:48:58 2008
Run ended on Wed Jul 16 13:50:16 2008
Elapsed time was: 00:01:17.92
CPU time was: 00:00:15.66
3.加個索引看看
SQL>truncate table l5m.load_01;
SQL>create index l5m.i_load01 on l5m.load_01(u_id,owner,table_name);
SQL>create index l5m.i_load02 on l5m.load_01(tablespace_name,logging);
SQL> select * from redo_size;
VALUE
----------
47991260
檢視loading時的日誌,用了2分55秒,整整比不加索引慢了一倍多
[oracle@qht108 sqlldr]$ tail -29 log.txt
Table LOAD_01:
1123237 Rows successfully loaded.
4 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Date cache:
Max Size: 1000
Entries : 83
Hits : 1055798
Misses : 0
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 1123241
Total logical records rejected: 4
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 233
Total stream buffers loaded by SQL*Loader load thread: 924
Run began on Wed Jul 16 14:47:02 2008
Run ended on Wed Jul 16 14:49:57 2008
Elapsed time was: 00:02:55.14
CPU time was: 00:00:16.60
SQL> select * from redo_size;
VALUE
----------
48791144
SQL> select 48791144-47991260 from dual;
48791144-47991260
-----------------
799884
4.測試ROWS和bindsize引數,
加了Rows引數,傳統載入時必須注意bindsize引數是多少,否則sqlldr會自動按bindsize的值來除每行的資料量,得到一個新的rows替換你的引數.直接載入時不受這個限制.
4.1 測一下傳統載入的情況
引數檔案改了一下:
[oracle@qht108 sqlldr]$ cat para.txt
userid=l5m/l5m
control='/home/oracle/sqlldr/control.txt'
data='/home/oracle/sqlldr/data.txt'
log='/home/oracle/sqlldr/log.txt'
bad='/home/oracle/sqlldr/bad.txt'
rows=300
bindsize=2560000
SQL> select * from redo_size;
VALUE
----------
604331212
log.txt的部分記錄如下:
注意自動將rows=300按照bindsize 2560000計算得出202 rows.
Table LOAD_01:
1123237 Rows successfully loaded.
4 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 2558330 bytes(202 rows)
Read buffer bytes: 2560000
Total logical records skipped: 0
Total logical records read: 1123241
Total logical records rejected: 4
Total logical records discarded: 0
Run began on Thu Jul 17 16:19:25 2008
Run ended on Thu Jul 17 16:27:52 2008
Elapsed time was: 00:08:27.76
CPU time was: 00:01:02.26
redo生成的比較大,傳統載入嘛,可以理解.
SQL> select * from redo_size;
VALUE
----------
1211475560
SQL> select 1211475560-604331212 from dual;
1211475560-604331212
--------------------
607144348
4.2 試試直接載入,將rows設為10000
[oracle@qht108 sqlldr]$ cat para.txt
userid=l5m/l5m
control='/home/oracle/sqlldr/control.txt'
data='/home/oracle/sqlldr/data.txt'
log='/home/oracle/sqlldr/log.txt'
bad='/home/oracle/sqlldr/bad.txt'
rows=10000
direct=true
SQL>truncate table l5m.load_01;
SQL> select * from redo_size;
VALUE
----------
1213797372
[oracle@qht108 sqlldr]$ sqlldr parfile=para.txt
SQL*Loader: Release 10.2.0.4.0 - Production on Thu Jul 17 16:44:42 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Save data point reached - logical record count 10000.
Save data point reached - logical record count 20000.
Save data point reached - logical record count 30000.
Save data point reached - logical record count 40000.
Save data point reached - logical record count 50000.
Save data point reached - logical record count 60000.
Save data point reached - logical record count 70000.
Save data point reached - logical record count 80000.
....
可10000行Save一次的,看到不是commit.
commit和save的不同,可以參考:
http://www.itpub.net/thread-1022543-2-2.html
log.txt部分內容如下:
The following index(es) on table LOAD_01 were processed:
index L5M.I_LOAD01 loaded successfully with 1123237 keys
index L5M.I_LOAD02 loaded successfully with 985224 keys
Table LOAD_01:
1123237 Rows successfully loaded.
4 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Date cache:
Max Size: 1000
Entries : 83
Hits : 1055798
Misses : 0
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 1123241
Total logical records rejected: 4
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 345
Total stream buffers loaded by SQL*Loader load thread: 822
Run began on Thu Jul 17 16:44:42 2008
Run ended on Thu Jul 17 16:48:15 2008
Elapsed time was: 00:03:33.79
CPU time was: 00:00:20.74
SQL> select * from redo_size;
VALUE
----------
1214767672
SQL> select 1214767672-1213797372 from dual;
1214767672-1213797372
---------------------
970300
5.測試unrecoverable引數.(索引沒有刪除,下面的測試都在不刪除索引的前提下進行)
要注意:將unrecoverable加到控制檔案的load data上面,而非加到引數檔案.
[oracle@qht108 sqlldr]$ cat control.txt
unrecoverable
load data
append into table LOAD_01
fields terminated by ',' TRAILING NULLCOLS
(u_id recnum,OWNER,TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME,IOT_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,FREELISTS,FREELIST_GROUPS,LOGGING,BACKED_UP,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS,DEGREE,INSTANCES,CACHE,TABLE_LOCK,SAMPLE_SIZE,LAST_ANALYZED date 'yyyy-mm-dd HH24:mi:ss)',PARTITIONED,IOT_TYPE,TEMPORARY,SECONDARY,NESTED,BUFFER_POOL,ROW_MOVEMENT,GLOBAL_STATS,USER_STATS,DURATION,SKIP_CORRUPT,MONITORING,CLUSTER_OWNER,DEPENDENCIES,COMPRESSION,DROPPED)
SQL>truncate table l5m.load_01
SQL> select * from redo_size;
VALUE
----------
49505816
檢視loading時的日誌,用了3分04秒,速度比不加還慢點,這個和Donald 的結論有點不太對,不過不用太計較,Donald 的結論也只是快5%,而我慢了5%,應該和server的效能有關.
[oracle@qht108 sqlldr]$ tail -29 log.txt
Table LOAD_01:
1123237 Rows successfully loaded.
4 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Date cache:
Max Size: 1000
Entries : 83
Hits : 1055798
Misses : 0
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 1123241
Total logical records rejected: 4
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 233
Total stream buffers loaded by SQL*Loader load thread: 924
Run began on Wed Jul 16 15:22:02 2008
Run ended on Wed Jul 16 15:25:06 2008
Elapsed time was: 00:03:04.00
CPU time was: 00:00:23.29
現在我們關心的是redo有沒有更少一點,也產生了799580的redo,看來和沒加也差不到哪兒去.
SQL> select * from redo_size;
VALUE
----------
50305396
SQL> select 50305396-49505816 from dual;
50305396-49505816
-----------------
799580
6.測試skip_index_maintenance引數.
[oracle@qht108 sqlldr]$ echo "skip_index_maintenance=true" >> para.txt
[oracle@qht108 sqlldr]$ cat para.txt
userid=l5m/l5m
control='/home/oracle/sqlldr/control.txt'
data='/home/oracle/sqlldr/data.txt'
log='/home/oracle/sqlldr/log.txt'
bad='/home/oracle/sqlldr/bad.txt'
#errors=0
direct=true
skip_index_maintenance=true
SQL>truncate table l5m.load_01
SQL> select * from redo_size;
VALUE
----------
51052076
日誌中看到只用了1分17秒,和沒有索引的情況是一樣的,且出現SKIP_INDEX_MAINTENANCE option requested的資訊.
The following index(es) on table LOAD_01 were processed:
index L5M.I_LOAD01 was made unusable due to:
SKIP_INDEX_MAINTENANCE option requested
index L5M.I_LOAD02 was made unusable due to:
SKIP_INDEX_MAINTENANCE option requested
Table LOAD_01:
1123237 Rows successfully loaded.
4 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Date cache:
Max Size: 1000
Entries : 83
Hits : 1055798
Misses : 0
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 1123241
Total logical records rejected: 4
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 233
Total stream buffers loaded by SQL*Loader load thread: 924
Run began on Wed Jul 16 16:01:13 2008
Run ended on Wed Jul 16 16:02:30 2008
Elapsed time was: 00:01:16.96
CPU time was: 00:00:16.16
下面看一下產生的redo大小及index的狀態.
由於skip了建立index的redo,產生的redo少多了.
SQL> select * from redo_size;
VALUE
----------
51459748
SQL> select 51459748-51052076 from dual;
51459748-51052076
-----------------
407672
SQL> select index_name,status from dba_indexes
2 where owner='L5M' and table_name='LOAD_01';
INDEX_NAME STATUS
------------------------------ --------
I_LOAD01 UNUSABLE
I_LOAD02 UNUSABLE
SQL> select segment_name,blocks from dba_segments where owner='L5M' and segment_type='INDEX';
SEGMENT_NAME BLOCKS
-------------------- ----------
I_LOAD02 8
I_LOAD01 8
可以看出索引沒有用,必須rebuild才可以.
SQL> alter index l5m.i_load01 rebuild;
Index altered.
SQL> alter index l5m.i_load02 rebuild;
Index altered.
SQL> select index_name,status from dba_indexes
2 where owner='L5M' and table_name='LOAD_01';
INDEX_NAME STATUS
------------------------------ --------
I_LOAD01 VALID
I_LOAD02 VALID
SQL> select segment_name,blocks from dba_segments where owner='L5M' and segment_type='INDEX';
SEGMENT_NAME BLOCKS
-------------------- ----------
I_LOAD02 3072
I_LOAD01 6016
7.測試skip_unusable_indexes引數
將此引數分別設為true和flase,並沒有發現有何不同,呵.
8.測試commit_discontinued引數
該引數預設為false,表示當load被異外中止後,已load的資料是不是自動提,經過測試改為TRUE後,中止sqlldr資料會自動提交,預設的false不會提交.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-1007571/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQLLDR直接載入能否分批提交?SQL
- 測試TOM=SQLLDR載入日期資料SQL
- 測試TOM==SQLLDR載入固定格式資料SQL
- 測試TOM=SQLLDR使用函式載入資料SQL函式
- 測試TOM=SQLLDR載入內嵌換行符資料SQL
- Sqlldr效能引數SQL
- 幾個SQLLDR的典型案例SQL
- 測試oracle sqlldrOracleSQL
- sqlldr批量匯入匯出資料測試SQL
- sqlldr批次匯入匯出資料測試SQL
- 【sqlldr載入資料】SQL
- Oracle sqlldr工具功能測試OracleSQL
- sqlldr效能調優測試SQL
- 並行相關的幾個引數並行
- sqlldr載入效能問題的排查SQL
- ORACLE 的載入工具SQLLDR應用OracleSQL
- 測試TOM=SQLLDR生成外部表SQL
- 《安全測試常用的幾個工具》
- innodb的幾個記憶體引數記憶體
- EBS密碼安全的幾個引數密碼
- 幾個引數配置的計算公式公式
- 【測試】Android Studio 相關下載及引數Android
- Oracle JDBC ResultSet引數測試OracleJDBC
- 介面測試-引數校驗
- 測試TOM=SQLLDR使用CASE語句SQL
- 測試TOM=SQLLDR函式使用1SQL函式
- 關於INLIST ITERATOR的幾個測試
- web測試的幾個隱藏點Web
- [轉載]基於頻譜分析儀的濾波器引數測試
- 伺服器中的幾個重要引數伺服器
- 幾個重要的 ASM Disk Groups 引數ASM
- Oracle幾個初始化引數Oracle
- weblogic幾個優化引數Web優化
- Python的unittest做引數化測試Python
- remote_os_authent引數測試！REM
- sqlldr載入會產生redo嗎？SQL
- 軟體效能測試的幾個階段
- 聊聊Oracle Optimizer相關的幾個引數（中）Oracle