jingyu.wang 2020-02-23 19:30 采纳率: 0%
浏览 527
已结题

oracle12.1 sqlldr 的direct方式导数据比“非direct方式导数据慢”，如何解决？

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

  • 写回答

2条回答 默认 最新

  • jingyu.wang 2021-04-20 11:19
    关注

    我自己找到了答案，我结个题吧， 单线程 direct方式确实比 “非 direct 方式”慢，但如果多线程入的话(并发多个sqlldr) ， direct方式要比非  direct方式要快很多。原因是 direct方式 是利用客户机直接把文件做成oracle可用的块文件，直接写到服务器硬盘上，所以用的是 客户机的cpu和内存，但如果用非 direct方式 ，就是在用insert插入数据，会利用oracle服务器的cpu与内存。当并发量大的情况下，会大量占用服务器的cpu与内存，导致整个服务器性能下降。所以 direct方式 的瓶颈是网络与客户机的cpu与内存。

    评论

报告相同问题？

悬赏问题

  • ¥15 基于卷积神经网络的声纹识别
  • ¥15 Python中的request，如何使用ssr节点，通过代理requests网页。本人在泰国，需要用大陆ip才能玩网页游戏，合法合规。
  • ¥100 为什么这个恒流源电路不能恒流？
  • ¥15 有偿求跨组件数据流路径图
  • ¥15 写一个方法checkPerson，入参实体类Person，出参布尔值
  • ¥15 我想咨询一下路面纹理三维点云数据处理的一些问题，上传的坐标文件里是怎么对无序点进行编号的，以及xy坐标在处理的时候是进行整体模型分片处理的吗
  • ¥15 CSAPPattacklab
  • ¥15 一直显示正在等待HID—ISP
  • ¥15 Python turtle 画图
  • ¥15 stm32开发clion时遇到的编译问题