在10g中引入了数据泵Data Pump导入导出工具，DataPump的工作流如下图:
我们在使用Data Pump工具时经常会遇到著名的ORA-04031/ORA-04030错误，主要影响DataPump的内存组件有PGA和SGA中的共享池Shared Pool、流池Streams Pool。Expdp/Impdp对shared Pool的开销主要体现在其运行过程中需要调用一系列的包体PACKGE BODY，它们包括:
PACKAGE_NAME TYPE SHARABLE_MEM ---------------------------------------- -------------------- ------------ SYS.KUPM$MCP PACKAGE BODY 425448 SYS.KUPW$WORKER PACKAGE BODY 386000 SYS.DBMS_METADATA_INT PACKAGE BODY 325856 SYS.DBMS_REPCAT_UTL PACKAGE BODY 269064 SYS.DBMS_METADATA PACKAGE BODY 226624 SYS.DBMS_DATAPUMP PACKAGE BODY 192888 SYS.DBMS_PRVTAQIS PACKAGE BODY 147288 SYS.DBMS_PRVTAQIM PACKAGE BODY 142680 SYS.KUPF$FILE PACKAGE BODY 142008 SYS.DBMS_METADATA_UTIL PACKAGE BODY 115224 SYS.KUPD$DATA PACKAGE BODY 109400 SYS.DBMS_LOGREP_EXP PACKAGE BODY 102648 SYS.DBMS_SCHED_MAIN_EXPORT PACKAGE BODY 86816 SYS.DBMS_SYS_SQL PACKAGE BODY 72280 SYS.DBMS_AW PACKAGE BODY 68128 SYS.DBMS_SQL PACKAGE BODY 68064 SYS.DBMS_DM_EXP_INTERNAL PACKAGE BODY 57040 SYS.DBMS_AW_EXP PACKAGE BODY 52256 SYS.KUPC$QUE_INT PACKAGE BODY 52088 SYS.DBMS_CUBE_EXP PACKAGE BODY 48432 SYS.KUPV$FT_INT PACKAGE BODY 47184 SYS.DBMS_LOGREP_UTIL PACKAGE BODY 45856 SYS.DBMS_CDC_EXPDP PACKAGE BODY 44616 SYS.DBMS_EXPORT_EXTENSION PACKAGE BODY 38728 SYS.DBMS_CDC_UTILITY PACKAGE BODY 37712 SYS.KUPV$FT PACKAGE BODY 34536 SYS.DBMS_DM_MODEL_EXP PACKAGE BODY 33904 SYS.DBMS_AQ PACKAGE BODY 33512 SYS.DBMS_IJOB PACKAGE BODY 33488 SYS.DBMS_AQ_SYS_EXP_INTERNAL PACKAGE BODY 29464 SYS.DBMS_FILE_GROUP_EXP PACKAGE BODY 29440 SYS.KUPD$DATA_INT PACKAGE BODY 29424 SYS.DBMS_RULE_EXP_RL_INTERNAL PACKAGE BODY 29400 SYS.KUPP$PROC PACKAGE BODY 25368 SYS.DBMS_AQ_IMPORT_INTERNAL PACKAGE BODY 25352 SYS.DBMS_AQADM PACKAGE BODY 25320 SYS.DBMS_ODCI PACKAGE BODY 21200 SYS.UTL_XML PACKAGE BODY 21200 SYS.DBMS_CDC_DPUTIL PACKAGE BODY 21192 SYS.DBMS_STREAMS_DATAPUMP_UTIL PACKAGE BODY 21144 SYS.KUPF$FILE_INT PACKAGE BODY 17104 SYS.DBMS_SESSION PACKAGE BODY 17048 SYS.DBMS_AQ_SYS_EXP_ACTIONS PACKAGE BODY 17048 SYS.DBMS_STREAMS_DATAPUMP PACKAGE BODY 17032 SYS.KUPC$QUEUE_INT PACKAGE BODY 17032 SYS.DBMS_RULE_ADM PACKAGE BODY 17032 SYS.DBMS_LOCK PACKAGE BODY 17032 SYS.DBMS_RULEADM_INTERNAL PACKAGE BODY 12952 SYS.DBMS_TRANSFORM_EXIMP_INTERNAL PACKAGE BODY 12952 SYS.DBMS_REFRESH_EXP_SITES PACKAGE BODY 12952 SYS.DBMS_REPCAT_RGT_EXP PACKAGE BODY 12936 SYS.UTL_RAW PACKAGE BODY 12936 SYS.DBMS_FLASHBACK PACKAGE BODY 12936 SYS.DBMS_TRANSFORM_EXIMP PACKAGE BODY 12936 SYS.DBMS_SCHED_JOB_EXPORT PACKAGE BODY 12936 SYS.DBMS_REFRESH_EXP_LWM PACKAGE BODY 12936 SYS.DBMS_SCHED_EXPORT_CALLOUTS PACKAGE BODY 8856 SYS.DBMS_AQ_EXP_QUEUE_TABLES PACKAGE BODY 8856 SYS.DBMS_DEFER_IMPORT_INTERNAL PACKAGE BODY 8856 SYS.DBMS_INTERNAL_SAFE_SCN PACKAGE BODY 8856 SYS.DBMS_AQ_EXP_INDEX_TABLES PACKAGE BODY 8856 SYS.DBMS_AQ_EXP_HISTORY_TABLES PACKAGE BODY 8856 SYS.DBMS_AQ_EXP_SIGNATURE_TABLES PACKAGE BODY 8856 SYS.DBMS_AQ_EXP_DEQUEUELOG_TABLES PACKAGE BODY 8856 SYS.DBMS_AQ_EXP_SUBSCRIBER_TABLES PACKAGE BODY 8856 SYS.DBMS_AQ_EXP_CMT_TIME_TABLES PACKAGE BODY 8856 SYS.DBMS_AQ_EXP_TIMEMGR_TABLES PACKAGE BODY 8856 SYS.DBMS_RULE_EXP_RULES PACKAGE BODY 8840 SYS.DBMS_RULE_EXP_UTLI PACKAGE BODY 8840 SYS.DBMS_AQADM_INV PACKAGE BODY 8840 SYS.DBMS_ZHELP_IR PACKAGE BODY 8840 SYS.KUPC$QUEUE PACKAGE BODY 8840 SYS.DBMS_AQ_EXP_ZECURITY PACKAGE BODY 8840 SYS.KUPU$UTILITIES_INT PACKAGE BODY 8840 SYS.KUPU$UTILITIES PACKAGE BODY 8840 SYS.DBMS_REPCAT_EXP PACKAGE BODY 8840 SYS.DBMS_CDC_EXPVDP PACKAGE BODY 8840 total<<10MB
DataPump内部利用高级队列 Advanced Queue技术，所以也会用到流池Streams Pool，与之相关的Streams Pool组件包括:
POOL NAME BYTES ------------ -------------------------- ---------- streams pool sob_kgqmrec 19584 streams pool Sender info 17616 streams pool recov_kgqbtctx 11904 streams pool kwqbcqini:spilledovermsgs 3168 streams pool kgqbt_alloc_block 2096 streams pool recov_kgqmsub 1608 streams pool kwqbsinfy:bqg 1232 streams pool recov_kgqmctx 1104 streams pool kwqbsinfy:mpr 1088 streams pool kwqbsinfy:sta 768 streams pool kgqmsub 584 streams pool fixed allocation callback 448 streams pool kwqbsinfy:cco 376 streams pool image handles 288 streams pool kwqbsinfy:bms 256 streams pool name_kgqmsub 256 streams pool spilled:kwqbl 256 streams pool deqtree_kgqmctx 144 streams pool substree_kgqmctx 144 streams pool kgqmdm_fl_1 144 streams pool time manager index 144 streams pool msgtree_kgqmctx 144
当Streams Pool分配过小同样可能引发Expdp/Impdp因ORA-04031 (“streams pool”, …)错误而意外终止，详见<EXPDP Fails With ORA-04031 (“streams pool”, …) [ID 457724.1]>
此外DataPump还可能从Large Pool中分配PX msg pool作为并行进程通信池，但是这种内存开销很小。
DataPump对PGA的消耗主要体现在koh-kghu sessi sub-heap上，已知的Bug 10404544(ORA – 4030 DURING EXPDP)、7681160(EXPDP FAILS WITH ORA-4030 WHEN SELECT FROM SYS.KU$_PROCACT_SCHEMA_VIEW)说明该子堆sub-heap(和另一个sub-heap kxs-heap-w)在10.2.0.4上使用expdp时可能引发内存泄露memory-leak。我们来具体看一下DataPump Manager DM00的PGA使用情况:
SQL> oradebug setospid 5278;
Oracle pid: 51, Unix process pid: 5278, image: oracle@rh2.oracle.com (DM00)
SQL> oradebug dump heapdump 536870917;
Statement processed.
SQL> oradebug tracefile_name;
/s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_dm00_5278.trc
SQL> select pga_alloc_mem / 1024 / 1024, pga_used_mem / 1024 / 1024, pname
2 from v$process
3 where addr = '00000000DCBC51F8'
4 /
PGA_ALLOC_MEM/1024/1024 PGA_USED_MEM/1024/1024 PNAME
----------------------- ---------------------- -----
8.06555557 7.25846481 DM00
[oracle@rh2 ~]$ egrep "HEAP DUMP heap name|Total heap size|Permanent space"
/s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_dm00_5278.trc
HEAP DUMP heap name="session heap" desc=0x7fae4fc167f8
Total heap size = 4517512
Permanent space = 192664
HEAP DUMP heap name="koh-kghu sessi" desc=0x7fae4f9db150
Total heap size = 362416
Permanent space = 80
HEAP DUMP heap name="koh-kghu sessi" desc=0x7fae4fc47fb0
Total heap size = 336104
Permanent space = 80
HEAP DUMP heap name="koh-kghu sessi" desc=0x7fae4f984660
Total heap size = 156792
Permanent space = 80
HEAP DUMP heap name="koh-kghu sessi" desc=0x7fae4f988c88
Total heap size = 154912
Permanent space = 80
HEAP DUMP heap name="Alloc environm" desc=0x7fae4fc3e090
Total heap size = 129512
Permanent space = 416
HEAP DUMP heap name="Alloc statemen" desc=0x7fae4f8c91f8
Total heap size = 10264
Permanent space = 696
HEAP DUMP heap name="Alloc server h" desc=0x7fae4f9f3650
Total heap size = 10192
Permanent space = 1056
HEAP DUMP heap name="Alloc server h" desc=0x7fae4fc4fed0
Total heap size = 9912
Permanent space = 1488
HEAP DUMP heap name="Alloc server h" desc=0x7fae4f8e4690
Total heap size = 9080
Permanent space = 560
HEAP DUMP heap name="Alloc server h" desc=0x7fae4f8aeda0
Total heap size = 8992
Permanent space = 568
HEAP DUMP heap name="pga heap" desc=0xb7c8ba0
Total heap size = 2353064
Permanent space = 654544
HEAP DUMP heap name="KFK_IO_SUBHEAP" desc=0x7fae4fdfaa98
Total heap size = 730640
Permanent space = 80
HEAP DUMP heap name="koh-kghu call " desc=0x7fae4fb650d0
Total heap size = 246080
Permanent space = 80
HEAP DUMP heap name="diag pga" desc=0x7fae500347e0
Total heap size = 70816
Permanent space = 13472
HEAP DUMP heap name="Alloc environm" desc=0x7fae4fdd15c8
Total heap size = 68064
Permanent space = 1336
HEAP DUMP heap name="Alloc server h" desc=0x7fae4fb50ee0
Total heap size = 12272
Permanent space = 2096
HEAP DUMP heap name="Alloc server h" desc=0x7fae4fb66c00
Total heap size = 11784
Permanent space = 1856
HEAP DUMP heap name="Alloc server h" desc=0x7fae4fc14fc0
Total heap size = 9816
Permanent space = 1392
HEAP DUMP heap name="Alloc server h" desc=0x7fae4fc10068
Total heap size = 9664
Permanent space = 1240
HEAP DUMP heap name="PLS PGA hp" desc=0x7fae4fdeeab8
Total heap size = 46784
Permanent space = 80
HEAP DUMP heap name="top call heap" desc=0xb7ce3c0
Total heap size = 458584
Permanent space = 1920
HEAP DUMP heap name="callheap" desc=0xb7cd578
Total heap size = 343104
Permanent space = 65536
HEAP DUMP heap name="callheap" desc=0xb7cd4c0
Total heap size = 21616
Permanent space = 968
HEAP DUMP heap name="kti call subhe" desc=0x7fae4fdee018
Total heap size = 20584
Permanent space = 80
HEAP DUMP heap name="callheap" desc=0x7fae4f6bd108
Total heap size = 8952
Permanent space = 200
HEAP DUMP heap name="callheap" desc=0x7fae4f6bd030
Total heap size = 2072
Permanent space = 1672
HEAP DUMP heap name="top uga heap" desc=0xb7ce5e0
Total heap size = 4520496
Permanent space = 80
HEAP DUMP heap name="session heap" desc=0x7fae4fc167f8
Total heap size = 4517512
Permanent space = 192664
HEAP DUMP heap name="koh-kghu sessi" desc=0x7fae4f9db150
Total heap size = 362416
Permanent space = 80
HEAP DUMP heap name="koh-kghu sessi" desc=0x7fae4fc47fb0
Total heap size = 336104
Permanent space = 80
HEAP DUMP heap name="koh-kghu sessi" desc=0x7fae4f984660
Total heap size = 156792
Permanent space = 80
HEAP DUMP heap name="koh-kghu sessi" desc=0x7fae4f988c88
Total heap size = 154912
Permanent space = 80
HEAP DUMP heap name="Alloc environm" desc=0x7fae4fc3e090
Total heap size = 129512
Permanent space = 416
HEAP DUMP heap name="Alloc statemen" desc=0x7fae4f8c91f8
Total heap size = 10264
Permanent space = 696
HEAP DUMP heap name="Alloc server h" desc=0x7fae4f9f3650
Total heap size = 10192
Permanent space = 1056
HEAP DUMP heap name="Alloc server h" desc=0x7fae4fc4fed0
Total heap size = 9912
Permanent space = 1488
HEAP DUMP heap name="Alloc server h" desc=0x7fae4f8e4690
Total heap size = 9080
Permanent space = 560
HEAP DUMP heap name="Alloc server h" desc=0x7fae4f8aeda0
Total heap size = 8992
Permanent space = 568
一般遇到这类memory leak的问题，Oracle内部会使用一个名叫heap.awk的dump分析工具(类似于ass.awk)来找出问题子堆(problematic sub-heap),我们可以使用图形化的免费工具Membai来替代heap.awk。
总结：
DataPump工具Expdp/Impdp需要从PGA和SGA的Shared Pool、Streams Pool和Large Pool分配必要的内存。为了避免Expdp/Impdp出现ORA-04031/ORA-04030错误，我们有必要在自动管理模式下设置合理的pga_aggregate_target和sga_target(抑或者memory_target)内存初始化参数，如果使用手动的SGA管理的话，那么有必要保证shared_pool_size的设置适宜，对于Streams Pool和Large Pool一般设置为150MB大小。
Comment