sqlldr 的**direct方式**, 数据入库用时44秒 .
sqlldr 的**非direct方式**, 数据入库用时28秒.
如何解决多出的10多秒,如何做到direct方式比非direct入库要快?
据说oracle官方声称direct方式在1小时内能入100G数据。如何能做到?
###现在入库每秒3.6M,如何做到20M以上??
##十分感谢 。
表已经设置了nologging
###要入库的文件 157M 000001_0.dat
tdlte_mro_pnn_grid50_cell_day1.ctl 文件内容:
UNRECOVERABLE
load data
infile '/home/day_to_ora/20180829/MRO_TDLTE_MRO_PNN_GRID50_CELL_DAY1/000001_0.dat'
append INTO TABLE TDLTE_MRO_PNN_GRID50_CELL_DAY1
Fields terminated by x'01'
trailing nullcols (
SDATE DATE "YYYY-MM-DD" ,
CITY "replace(:CITY,'NULL')" ,
SC_ECI "replace(:SC_ECI,'NULL')" ,
GRIDX "replace(:GRIDX,'NULL')" ,
GRIDY "replace(:GRIDY,'NULL')" ,
NUM_HOURS "replace(:NUM_HOURS,'NULL')" ,
MAX_AVGRSRP "replace(:MAX_AVGRSRP,'NULL')" ,
MAX_MAXRSRP "replace(:MAX_MAXRSRP,'NULL')" ,
AVG_SCRSRP "replace(:AVG_SCRSRP,'NULL')" ,
MAX_SCRSRP "replace(:MAX_SCRSRP,'NULL')" ,
MIN_SCRSRP "replace(:MIN_SCRSRP,'NULL')" ,
RSRP_SAMPLES "replace(:RSRP_SAMPLES,'NULL')" ,
AVG_SCRSRQ "replace(:AVG_SCRSRQ,'NULL')" ,
MAX_SCRSRQ "replace(:MAX_SCRSRQ,'NULL')" ,
MIN_SCRSRQ "replace(:MIN_SCRSRQ,'NULL')" ,
RSRQ_SAMPLES "replace(:RSRQ_SAMPLES,'NULL')" ,
AVG_ULSINR "replace(:AVG_ULSINR,'NULL')" ,
MAX_ULSINR "replace(:MAX_ULSINR,'NULL')" ,
MIN_ULSINR "replace(:MIN_ULSINR,'NULL')" ,
ULSINR_SAMPLES "replace(:ULSINR_SAMPLES,'NULL')" ,
ULSINR_0 "replace(:ULSINR_0,'NULL')" ,
AVG_DLSINR "replace(:AVG_DLSINR,'NULL')" ,
MAX_DLSINR "replace(:MAX_DLSINR,'NULL')" ,
MIN_DLSINR "replace(:MIN_DLSINR,'NULL')" ,
DLSINR_SAMPLES "replace(:DLSINR_SAMPLES,'NULL')" ,
DLSINR_N3 "replace(:DLSINR_N3,'NULL')" ,
DLSINR_0 "replace(:DLSINR_0,'NULL')" ,
DLSINR_3 "replace(:DLSINR_3,'NULL')" ,
DLSINR_10 "replace(:DLSINR_10,'NULL')" ,
DLSINR_15 "replace(:DLSINR_15,'NULL')" ,
DLSINR_20 "replace(:DLSINR_20,'NULL')" ,
DLSINR_25 "replace(:DLSINR_25,'NULL')" ,
DLSINR_B25 "replace(:DLSINR_B25,'NULL')" ,
POOR_RSRP105_SINRN3 "replace(:POOR_RSRP105_SINRN3,'NULL')" ,
POOR_RSRP105_SINR0 "replace(:POOR_RSRP105_SINR0,'NULL')" ,
POOR_RSRP105_SINR3 "replace(:POOR_RSRP105_SINR3,'NULL')" ,
POOR_RSRP110_SINRN3 "replace(:POOR_RSRP110_SINRN3,'NULL')" ,
POOR_RSRP110_SINR0 "replace(:POOR_RSRP110_SINR0,'NULL')" ,
POOR_RSRP110_SINR3 "replace(:POOR_RSRP110_SINR3,'NULL')" ,
POOR_RSRP115_SINRN3 "replace(:POOR_RSRP115_SINRN3,'NULL')" ,
POOR_RSRP115_SINR0 "replace(:POOR_RSRP115_SINR0,'NULL')" ,
POOR_RSRP115_SINR3 "replace(:POOR_RSRP115_SINR3,'NULL')" ,
POORCOVERAGE120 "replace(:POORCOVERAGE120,'NULL')" ,
POORCOVERAGE115 "replace(:POORCOVERAGE115,'NULL')" ,
POORCOVERAGE110 "replace(:POORCOVERAGE110,'NULL')" ,
POORCOVERAGE105 "replace(:POORCOVERAGE105,'NULL')" ,
POORCOVERAGE100 "replace(:POORCOVERAGE100,'NULL')" ,
POORCOVERAGE95 "replace(:POORCOVERAGE95,'NULL')" ,
POORCOVERAGE90 "replace(:POORCOVERAGE90,'NULL')" ,
POORCOVERAGE85 "replace(:POORCOVERAGE85,'NULL')" ,
POORCOVERAGE80 "replace(:POORCOVERAGE80,'NULL')" ,
POORCOVERAGE75 "replace(:POORCOVERAGE75,'NULL')" ,
POORCOVERAGE70 "replace(:POORCOVERAGE70,'NULL')" ,
POORCOVERAGE65 "replace(:POORCOVERAGE65,'NULL')" ,
POORCOVERAGE60 "replace(:POORCOVERAGE60,'NULL')" ,
POORCOVERAGE40 "replace(:POORCOVERAGE40,'NULL')" ,
AVG_GRID90_OVERLAPDU6 "replace(:AVG_GRID90_OVERLAPDU6,'NULL')" ,
AVG_GRID100_OVERLAPDU6 "replace(:AVG_GRID100_OVERLAPDU6,'NULL')" ,
AVG_GRID105_OVERLAPDU6 "replace(:AVG_GRID105_OVERLAPDU6,'NULL')" ,
AVG_GRID110_OVERLAPDU6 "replace(:AVG_GRID110_OVERLAPDU6,'NULL')" ,
AVG_GRID115_OVERLAPDU6 "replace(:AVG_GRID115_OVERLAPDU6,'NULL')" ,
GRID_CMCC_OVERLAP6NUM_90 "replace(:GRID_CMCC_OVERLAP6NUM_90,'NULL')" ,
GRID_CMCC_OVERLAP6NUM_100 "replace(:GRID_CMCC_OVERLAP6NUM_100,'NULL')" ,
GRID_CMCC_OVERLAP6NUM_105 "replace(:GRID_CMCC_OVERLAP6NUM_105,'NULL')" ,
GRID_CMCC_OVERLAP6NUM_110 "replace(:GRID_CMCC_OVERLAP6NUM_110,'NULL')" ,
GRID_CMCC_OVERLAP6NUM_115 "replace(:GRID_CMCC_OVERLAP6NUM_115,'NULL')" ,
GRID_CELL_SAMPLE_CELLNUM "replace(:GRID_CELL_SAMPLE_CELLNUM,'NULL')" ,
GRID_CELL_SAMPLES "replace(:GRID_CELL_SAMPLES,'NULL')" ,
RSRQ_N10 "replace(:RSRQ_N10,'NULL')" ,
RSRQ_N12 "replace(:RSRQ_N12,'NULL')" ,
RSRQ_N13 "replace(:RSRQ_N13,'NULL')" ,
RSRQ_N14 "replace(:RSRQ_N14,'NULL')" ,
RSRQ_N16 "replace(:RSRQ_N16,'NULL')" ,
RSRQ_N18 "replace(:RSRQ_N18,'NULL')" ,
MOD3NUM "replace(:MOD3NUM,'NULL')" ,
AVG_TA "replace(:AVG_TA,'NULL')" ,
MAX_TA "replace(:MAX_TA,'NULL')" ,
MIN_TA "replace(:MIN_TA,'NULL')" ,
TA_SAMPLES "replace(:TA_SAMPLES,'NULL')" ,
FREQ "replace(:FREQ,'NULL')" ,
EARFCN "replace(:EARFCN,'NULL')" ,
ENODEB "replace(:ENODEB,'NULL')" ,
SCENCATEGORY "replace(:SCENCATEGORY,'NULL')" ,
CELL_SAMPLES "replace(:CELL_SAMPLES,'NULL')" ,
CELL_SAMPLES_PROP "replace(:CELL_SAMPLES_PROP,'NULL')" ,
DATA_THP_KPI "replace(:DATA_THP_KPI,'NULL')" ,
DATA_THP_XDR "replace(:DATA_THP_XDR,'NULL')" ,
DATA_THP_RFD "replace(:DATA_THP_RFD,'NULL')" )
sqlldr 'xxxx/xIxx*xxx@192.xx.xx.xx:1521/ejb' control=/home/day_to_ora/20180829/MRO_TDLTE_MRO_PNN_GRID50_CELL_DAY1/tdlte_mro_pnn_grid50_cell_day2.ctl log=/home/day_to_ora/20180829/MRO_TDLTE_MRO_PNN_GRID50_CELL_DAY1/log.log bad=/home/day_to_ora/20180829/MRO_TDLTE_MRO_PNN_GRID50_CELL_DAY1/bad.bad multithreading=true direct=true parallel=true bindsize=75000000 skip_index_maintenance=true skip_unusable_indexes=true columnarrayrows=600000 readsize=750000000 streamsize=1750000000 date_cache=1000 silent=header,feedback
real 0m44.696s
user 0m19.164s
sys 0m1.434s
Table TDLTE_MRO_PNN_GRID50_CELL_DAY1:
464590 Rows successfully loaded.
0 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 : 1
Hits : 464589
Misses : 0
Bind array size not used in direct path.
Column array rows : 600000
Stream buffer bytes:16777216
Read buffer bytes:750000000
Total logical records skipped: 0
Total logical records read: 464590
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 16
Run began on Sun Feb 23 18:14:46 2020
Run ended on Sun Feb 23 18:15:31 2020
Elapsed time was: 00:00:44.55
CPU time was: 00:00:20.31
##以下为非direct方式:
sqlldr 'xxxx/xIxx*xxx@192.xx.xx.xx:1521/ejb' control=/home/day_to_ora/20180829/MRO_TDLTE_MRO_PNN_GRID50_CELL_DAY1/tdlte_mro_pnn_grid50_cell_day2.ctl log=/home/day_to_ora/20180829/MRO_TDLTE_MRO_PNN_GRID50_CELL_DAY1/log.log bad=/home/day_to_ora/20180829/MRO_TDLTE_MRO_PNN_GRID50_CELL_DAY1/bad.bad parallel=true bindsize=607286400 readsize=607286400 streamsize=607286400 rows=200000 silent=header,fe
edback
value used for ROWS parameter changed from 200000 to 65534
Table TDLTE_MRO_PNN_GRID50_CELL_DAY1:
464590 Rows successfully loaded.
0 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: 1487883936 bytes(65534 rows)
Read buffer bytes:607286400
Total logical records skipped: 0
Total logical records read: 464590
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Sun Feb 23 16:37:01 2020
Run ended on Sun Feb 23 16:37:30 2020
Elapsed time was: 00:00:28.75
CPU time was: 00:00:22.22