15亿+条的记录,用sqlldr导入,9个多小时后遇到ORA-4030。
调整了一些参数后再进行,同样也是在9个多小时以ORA-4030结束。
Sweep [inc2][132427]: completed Trace dumping is performing id=[cdmp_20110810190110] Wed Aug 10 19:01:11 2011 Errors in file /u01/app/oracle/diag/rdbms/ddns_fs/peanut/incident/incdir_132426/peanut_ora_20743_i132426.trc: ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer) ORA-04030: out of process memory when trying to allocate 65560 bytes (klcalh:ld_hds,kdblc memory) Trace dumping is performing id=[cdmp_20110810190112] Wed Aug 10 19:02:08 2011 Sweep [inc2][132428]: completed Sweep [inc2][132426]: completed
调整的部分参数
ALTER SYSTEM SET parallel_servers_target=32 SCOPE=BOTH; Wed Aug 10 19:22:54 2011 ALTER SYSTEM SET open_cursors=1000 SCOPE=BOTH; Wed Aug 10 19:24:31 2011 ALTER SYSTEM SET processes=300 SCOPE=SPFILE; Wed Aug 10 19:24:46 2011 ALTER SYSTEM SET session_cached_cursors=100 SCOPE=SPFILE; Wed Aug 10 19:25:25 2011 ALTER SYSTEM SET sga_max_size='1G' SCOPE=SPFILE; Wed Aug 10 19:25:52 2011 ALTER SYSTEM SET sort_area_size=0 SCOPE=SPFILE;
[root@localhost ~]# cat /etc/sysctl.conf | grep shmmax kernel.shmmax = 68719476736 #kernel.shmmax = 536870912
[root@oray ~]# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 137216 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 137216 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited |