注册
dimp LOB 导入提速与 10 万行复现实验步骤
技术分享/ 文章详情 /

dimp LOB 导入提速与 10 万行复现实验步骤

dm-9527 2026/11/13 108 0 0

记录一次BLOB/CLOB 表的 dexp/dimp 导入测试的步骤与思路,供参考。

1. 采用的提速方案

优先实测以下组合,批次分别取 1000 / 5000 / 10000:

FAST_LOAD=Y LOB_NOT_FAST_LOAD=Y COMMIT_ROWS=5000 PARALLEL=1

官方手册说明:含 LOB 的表在其描述的 dmfldr 路径中逐行提交;LOB_NOT_FAST_LOAD=Y 让这类表改走普通 INSERT,按 COMMIT_ROWS 批量提交。仅导入 LOB 表时,也应比较 FAST_LOAD=N。如果现有命令已经使用 FAST_LOAD=N,增加 LOB_NOT_FAST_LOAD=Y 不会形成新的导入路径。[S1]

本实验不为回退后的 LOB 表增加 TABLE_PARALLEL、TASK_THREAD_NUMBER、BUFFER_NODE_SIZE:这些参数针对 dmfldr 路径。先固定 PARALLEL=1,避免混入并发因素;官网另有 LOB 并行时出现 -6524 的案例。[S1][S7]

组别 FAST_LOAD LOB_NOT_FAST_LOAD COMMIT_ROWS 用途
A_FAST Y N 5000 快速装载对照
B_INSERT N 不指定 5000 普通 INSERT 对照
C_LOB_5000 Y Y 5000 首选候选,与 A/B 比较
D_LOB_1000 Y Y 1000 较小事务批次
E_LOB_10000 Y Y 10000 较大事务批次

批次取值是本实验建议;提速幅度以本机校验通过后的中位耗时为准。若 A 更快,保留 A,不预设普通 INSERT 必然胜出。

2. 设置环境并记录版本

以数据库安装用户(如 dmdba)登录数据库服务器,在同一个 Bash 会话中依次执行后续代码块。修改下面 4 个配置项。使用与测试服务端配套的工具。

本实验在同一实例内创建两个用户和两个表空间。初始数据文件合计 16 GiB;源数据纯 LOB 载荷约 3.05 GiB,另有目标副本、导出文件、临时空间和事务日志开销。建议测试磁盘预留至少 50 GiB,并检查实际余量。表空间预分配用于减少导入计时中的文件扩展干扰。

# 以下命令使用 Bash。 bash set -euo pipefail # 修改这 4 项;数据文件目录须已存在且数据库服务进程可写。 export DM_HOME=/dm8 export LOB_DATA_DIR=/dm8/data/DAMENG export LOB_WORK=/data/dm_lob_bench_20261105 export LOB_ADMIN_CONN='SYSDBA/"YourSysdbaPassword"@127.0.0.1:5236' export LD_LIBRARY_PATH="$DM_HOME/bin:${LD_LIBRARY_PATH:-}" mkdir -p "$LOB_WORK" test -d "$LOB_DATA_DIR" test -w "$LOB_DATA_DIR" test -x "$DM_HOME/bin/disql" test -x "$DM_HOME/bin/dexp" test -x "$DM_HOME/bin/dimp" test -x /usr/bin/time df -h "$LOB_DATA_DIR" "$LOB_WORK" free -h "$DM_HOME/bin/dimp" HELP > "$LOB_WORK/dimp_help.txt" 2>&1 "$DM_HOME/bin/dexp" HELP > "$LOB_WORK/dexp_help.txt" 2>&1 # 当前工具缺少任一核心参数时停止,先核对工具与服务器版本。 for lob_param in FAST_LOAD LOB_NOT_FAST_LOAD COMMIT_ROWS DATA_ONLY REMAP_SCHEMA TABLE_EXISTS_ACTION do grep -q "$lob_param" "$LOB_WORK/dimp_help.txt" || { printf '当前 dimp HELP 中未找到参数:%s\n' "$lob_param" exit 1 } done cat > "$LOB_WORK/00_version.sql" <<'SQL' WHENEVER SQLERROR EXIT 1 ROLLBACK SET DEFINE OFF SELECT * FROM V$VERSION; EXIT SQL "$DM_HOME/bin/disql" "$LOB_ADMIN_CONN" "@$LOB_WORK/00_version.sql" \ > "$LOB_WORK/version.log" 2>&1 cat "$LOB_WORK/version.log"

版本视图来源为官方动态性能视图说明;DIsql 出错退出语法见官方命令手册。[S5][S6]

3. 创建表空间、用户和含 LOB 的表

下面对象应在测试库首次创建;遇到同名对象会停止,不自动删除。用户采用普通权限管理环境的创建方式。表上仅保留 ID 非空约束,暂不建主键、二级索引或触发器,以隔离 LOB 数据装载成本。[S3]

cat > "$LOB_WORK/01_setup.sql" <<SQL WHENEVER SQLERROR EXIT 1 ROLLBACK SET DEFINE OFF CREATE TABLESPACE TS_LOB_SRC DATAFILE '${LOB_DATA_DIR}/TS_LOB_SRC.DBF' SIZE 8192 AUTOEXTEND ON NEXT 512 MAXSIZE 32768; CREATE TABLESPACE TS_LOB_DST DATAFILE '${LOB_DATA_DIR}/TS_LOB_DST.DBF' SIZE 8192 AUTOEXTEND ON NEXT 512 MAXSIZE 32768; CREATE USER LOB_SRC IDENTIFIED BY "LobSrc_2026#A"; ALTER USER LOB_SRC DEFAULT TABLESPACE TS_LOB_SRC DEFAULT INDEX TABLESPACE TS_LOB_SRC; GRANT RESOURCE TO LOB_SRC; CREATE USER LOB_DST IDENTIFIED BY "LobDst_2026#A"; ALTER USER LOB_DST DEFAULT TABLESPACE TS_LOB_DST DEFAULT INDEX TABLESPACE TS_LOB_DST; GRANT RESOURCE TO LOB_DST; CREATE TABLE LOB_SRC.T_LOB_TEST ( ID BIGINT NOT NULL, B_DATA BLOB, C_DATA CLOB ) STORAGE(ON TS_LOB_SRC); CREATE TABLE LOB_DST.T_LOB_TEST ( ID BIGINT NOT NULL, B_DATA BLOB, C_DATA CLOB ) STORAGE(ON TS_LOB_DST); EXIT SQL "$DM_HOME/bin/disql" "$LOB_ADMIN_CONN" "@$LOB_WORK/01_setup.sql" \ > "$LOB_WORK/setup.log" 2>&1 cat "$LOB_WORK/setup.log"

先检测 DBMS_LOB。仅当包不存在、检测报错时,执行注释中的初始化命令后重新检测;已有包无需重建。[S4]

cat > "$LOB_WORK/02_lob_package_check.sql" <<'SQL' WHENEVER SQLERROR EXIT 1 ROLLBACK SET DEFINE OFF DECLARE V_C CLOB; BEGIN DBMS_LOB.CREATETEMPORARY(V_C, TRUE); DBMS_LOB.WRITEAPPEND(V_C, 1, 'X'); DBMS_LOB.FREETEMPORARY(V_C); END; / EXIT SQL "$DM_HOME/bin/disql" "$LOB_ADMIN_CONN" "@$LOB_WORK/02_lob_package_check.sql" # 若确认 DBMS_LOB 尚未创建,使用 SYSDBA 在 DIsql 中执行: # CALL SP_CREATE_SYSTEM_PACKAGES(1, 'DBMS_LOB'); # 然后重新执行本节包检测。

4. 插入 100000 行,每行 BLOB/CLOB 各 16384 长度

每行 BLOB 为 16384 字节,CLOB 为 16384 个 ASCII 字符。BLOB 前 6 字节和 CLOB 前 12 字符随 ID 改变,用于检查行与 LOB 是否匹配。每批 1000 行,临时 LOB 在提交前释放,每批重新创建,避免跨事务继续填写同一临时 LOB。数据生成时间不计入导入耗时。[S4]

cat > "$LOB_WORK/03_generate.sql" <<'SQL' WHENEVER SQLERROR EXIT 1 ROLLBACK SET DEFINE OFF SET TIMING ON DECLARE V_BLOB BLOB; V_CLOB CLOB; V_RAW VARBINARY(1024); V_TEXT VARCHAR2(1024); V_ID_RAW VARBINARY(6); V_ID_TEXT VARCHAR2(12); V_ID BIGINT; BEGIN V_RAW := HEXTORAW(RPAD('AB', 2048, 'AB')); V_TEXT := RPAD('C', 1024, 'C'); FOR BATCH_NO IN 1..100 LOOP DBMS_LOB.CREATETEMPORARY(V_BLOB, TRUE); DBMS_LOB.CREATETEMPORARY(V_CLOB, TRUE); FOR CHUNK_NO IN 1..16 LOOP DBMS_LOB.WRITEAPPEND(V_BLOB, 1024, V_RAW); DBMS_LOB.WRITEAPPEND(V_CLOB, 1024, V_TEXT); END LOOP; FOR ROW_NO IN 1..1000 LOOP V_ID := (BATCH_NO - 1) * 1000 + ROW_NO; V_ID_TEXT := LPAD(TO_CHAR(V_ID), 12, '0'); V_ID_RAW := HEXTORAW(V_ID_TEXT); DBMS_LOB.WRITE(V_BLOB, 6, 1, V_ID_RAW); DBMS_LOB.WRITE(V_CLOB, 12, 1, V_ID_TEXT); INSERT INTO LOB_SRC.T_LOB_TEST(ID, B_DATA, C_DATA) VALUES(V_ID, V_BLOB, V_CLOB); END LOOP; DBMS_LOB.FREETEMPORARY(V_BLOB); DBMS_LOB.FREETEMPORARY(V_CLOB); COMMIT; END LOOP; END; / SELECT COUNT(*) AS ROW_COUNT, COUNT(DISTINCT ID) AS DISTINCT_IDS, MIN(ID) AS MIN_ID, MAX(ID) AS MAX_ID, MIN(DBMS_LOB.GETLENGTH(B_DATA)) AS B_MIN_BYTES, MAX(DBMS_LOB.GETLENGTH(B_DATA)) AS B_MAX_BYTES, MIN(DBMS_LOB.GETLENGTH(C_DATA)) AS C_MIN_CHARS, MAX(DBMS_LOB.GETLENGTH(C_DATA)) AS C_MAX_CHARS FROM LOB_SRC.T_LOB_TEST; DECLARE E_BAD_SOURCE EXCEPTION FOR -20001, 'SOURCE_DATA_CHECK_FAILED'; V_ROWS BIGINT; V_DISTINCT BIGINT; V_MIN BIGINT; V_MAX BIGINT; V_BAD BIGINT; BEGIN SELECT COUNT(*), COUNT(DISTINCT ID), MIN(ID), MAX(ID) INTO V_ROWS, V_DISTINCT, V_MIN, V_MAX FROM LOB_SRC.T_LOB_TEST; SELECT COUNT(*) INTO V_BAD FROM LOB_SRC.T_LOB_TEST WHERE B_DATA IS NULL OR C_DATA IS NULL OR NVL(DBMS_LOB.GETLENGTH(B_DATA), 0) <> 16384 OR NVL(DBMS_LOB.GETLENGTH(C_DATA), 0) <> 16384 OR NVL(RAWTOHEX(DBMS_LOB.SUBSTR(B_DATA, 6, 1)), 'X') <> LPAD(TO_CHAR(ID), 12, '0') OR NVL(DBMS_LOB.SUBSTR(C_DATA, 12, 1), 'X') <> LPAD(TO_CHAR(ID), 12, '0'); IF V_ROWS <> 100000 OR V_DISTINCT <> 100000 OR NVL(V_MIN, 0) <> 1 OR NVL(V_MAX, 0) <> 100000 OR V_BAD <> 0 THEN RAISE E_BAD_SOURCE; END IF; END; / EXIT SQL "$DM_HOME/bin/disql" "$LOB_ADMIN_CONN" "@$LOB_WORK/03_generate.sql" \ > "$LOB_WORK/generate.log" 2>&1 cat "$LOB_WORK/generate.log"

预期:ROW_COUNT=DISTINCT_IDS=100000,ID 范围 1~100000,四个 LOB 长度结果均为 16384。本测试采用可重复载荷且不启用导出压缩;测试通过后,应再用实际业务的 LOB 长度分布与内容复核。

5. dexp 导出

以 SYSDBA 导出源表,导出后不再修改源数据。所有导入组使用同一份 dmp 文件。[S2]

/usr/bin/time -p -o "$LOB_WORK/export.time" \ "$DM_HOME/bin/dexp" \ "USERID=$LOB_ADMIN_CONN" \ "DIRECTORY=$LOB_WORK" \ FILE=lob_100000.dmp \ TABLES=LOB_SRC.T_LOB_TEST \ ROWS=Y COMPRESS=N PARALLEL=1 \ LOG=export_lob.log \ > "$LOB_WORK/export.out" 2>&1 cat "$LOB_WORK/export.out" cat "$LOB_WORK/export.time" ls -lh "$LOB_WORK/lob_100000.dmp"

6. dimp 对照实验:5 组各运行 3 次

脚本每轮先清空 LOB_DST.T_LOB_TEST,然后导入,最后完整校验。源表保持不动。TABLES 使用导出文件中的源表名;REMAP_SCHEMA 指向目标用户。预建空表后采用 DATA_ONLY=Y TABLE_EXISTS_ACTION=APPEND。[S1]

校验同时覆盖行数、ID 唯一性和范围、每个 LOB 长度,以及按 ID 关联后的 BLOB/CLOB 全量内容比较。DBMS_LOB.COMPARE 返回 0 表示相等;对 NULL 单独判错。校验放在导入计时之外。[S4]

cat > "$LOB_WORK/04_truncate_target.sql" <<'SQL' WHENEVER SQLERROR EXIT 1 ROLLBACK SET DEFINE OFF TRUNCATE TABLE LOB_DST.T_LOB_TEST; EXIT SQL cat > "$LOB_WORK/05_verify.sql" <<'SQL' WHENEVER SQLERROR EXIT 1 ROLLBACK SET DEFINE OFF SELECT COUNT(*) AS ROW_COUNT, COUNT(DISTINCT ID) AS DISTINCT_IDS, MIN(ID) AS MIN_ID, MAX(ID) AS MAX_ID, MIN(DBMS_LOB.GETLENGTH(B_DATA)) AS B_MIN_BYTES, MAX(DBMS_LOB.GETLENGTH(B_DATA)) AS B_MAX_BYTES, MIN(DBMS_LOB.GETLENGTH(C_DATA)) AS C_MIN_CHARS, MAX(DBMS_LOB.GETLENGTH(C_DATA)) AS C_MAX_CHARS FROM LOB_DST.T_LOB_TEST; DECLARE E_BAD_TARGET EXCEPTION FOR -20002, 'TARGET_DATA_CHECK_FAILED'; V_ROWS BIGINT; V_DISTINCT BIGINT; V_MIN BIGINT; V_MAX BIGINT; V_BAD BIGINT; V_DIFF BIGINT; BEGIN SELECT COUNT(*), COUNT(DISTINCT ID), MIN(ID), MAX(ID) INTO V_ROWS, V_DISTINCT, V_MIN, V_MAX FROM LOB_DST.T_LOB_TEST; SELECT COUNT(*) INTO V_BAD FROM LOB_DST.T_LOB_TEST WHERE B_DATA IS NULL OR C_DATA IS NULL OR NVL(DBMS_LOB.GETLENGTH(B_DATA), 0) <> 16384 OR NVL(DBMS_LOB.GETLENGTH(C_DATA), 0) <> 16384; IF V_ROWS <> 100000 OR V_DISTINCT <> 100000 OR NVL(V_MIN, 0) <> 1 OR NVL(V_MAX, 0) <> 100000 OR V_BAD <> 0 THEN RAISE E_BAD_TARGET; END IF; SELECT COUNT(*) INTO V_DIFF FROM LOB_SRC.T_LOB_TEST S JOIN LOB_DST.T_LOB_TEST D ON D.ID = S.ID WHERE D.B_DATA IS NULL OR D.C_DATA IS NULL OR NVL(DBMS_LOB.COMPARE(S.B_DATA, D.B_DATA), 1) <> 0 OR NVL(DBMS_LOB.COMPARE(S.C_DATA, D.C_DATA), 1) <> 0; IF V_DIFF <> 0 THEN RAISE E_BAD_TARGET; END IF; END; / SELECT 'VERIFY_OK' AS RESULT FROM DUAL; EXIT SQL cat > "$LOB_WORK/run_bench.sh" <<'BASH' #!/usr/bin/env bash set -euo pipefail : "${DM_HOME:?请先执行环境配置}" : "${LOB_WORK:?请先执行环境配置}" : "${LOB_ADMIN_CONN:?请先执行环境配置}" run_case() { local round="$1" case_name="$2" local -a lob_options local tag="${case_name}_r${round}" case "$case_name" in A_FAST) lob_options=(FAST_LOAD=Y LOB_NOT_FAST_LOAD=N COMMIT_ROWS=5000 TABLE_PARALLEL=1) ;; B_INSERT) lob_options=(FAST_LOAD=N COMMIT_ROWS=5000) ;; C_LOB_5000) lob_options=(FAST_LOAD=Y LOB_NOT_FAST_LOAD=Y COMMIT_ROWS=5000 TABLE_PARALLEL=1) ;; D_LOB_1000) lob_options=(FAST_LOAD=Y LOB_NOT_FAST_LOAD=Y COMMIT_ROWS=1000 TABLE_PARALLEL=1) ;; E_LOB_10000) lob_options=(FAST_LOAD=Y LOB_NOT_FAST_LOAD=Y COMMIT_ROWS=10000 TABLE_PARALLEL=1) ;; *) return 1 ;; esac "$DM_HOME/bin/disql" "$LOB_ADMIN_CONN" "@$LOB_WORK/04_truncate_target.sql" \ > "$LOB_WORK/${tag}_truncate.out" 2>&1 printf '开始导入:%s\n' "$tag" /usr/bin/time -p -o "$LOB_WORK/${tag}.time" \ "$DM_HOME/bin/dimp" \ "USERID=$LOB_ADMIN_CONN" \ "DIRECTORY=$LOB_WORK" \ FILE=lob_100000.dmp \ TABLES=LOB_SRC.T_LOB_TEST \ REMAP_SCHEMA=LOB_SRC:LOB_DST \ DATA_ONLY=Y TABLE_EXISTS_ACTION=APPEND \ PARALLEL=1 FEEDBACK=0 \ "${lob_options[@]}" \ "LOG=${tag}.log" \ > "$LOB_WORK/${tag}.out" 2>&1 # 即使导入工具只给警告,行数或内容不正确也会在这里失败停止。 "$DM_HOME/bin/disql" "$LOB_ADMIN_CONN" "@$LOB_WORK/05_verify.sql" \ > "$LOB_WORK/${tag}_verify.out" 2>&1 grep -q 'VERIFY_OK' "$LOB_WORK/${tag}_verify.out" # 只把校验成功的结果纳入统计。 local elapsed elapsed=$(awk '$1=="real" {print $2}' "$LOB_WORK/${tag}.time") test -n "$elapsed" printf '%s,%s,%s\n' "$case_name" "$round" "$elapsed" >> "$LOB_WORK/results.csv" printf '完成并通过内容校验:%s,耗时 %s 秒\n' "$tag" "$elapsed" } printf 'case,round,elapsed_seconds\n' > "$LOB_WORK/results.csv" rm -f "$LOB_WORK/median.csv" # 改变执行顺序,降低首次读取和缓存带来的固定顺序偏差。 for round in 1 2 3 do case "$round" in 1) cases=(A_FAST B_INSERT C_LOB_5000 D_LOB_1000 E_LOB_10000) ;; 2) cases=(E_LOB_10000 D_LOB_1000 C_LOB_5000 B_INSERT A_FAST) ;; 3) cases=(C_LOB_5000 A_FAST E_LOB_10000 B_INSERT D_LOB_1000) ;; esac for case_name in "${cases[@]}" do run_case "$round" "$case_name" done done # 5 组各 3 次,排序后取第 2 次耗时作为中位数。 awk -F, 'NR>1 {print $1, $3}' "$LOB_WORK/results.csv" \ | LC_ALL=C sort -k1,1 -k2,2n \ | awk ' BEGIN {print "case,median_seconds"} $1 != prev {prev=$1; n=0} {n++; if(n==2) print $1 "," $2} ' > "$LOB_WORK/median.csv" cat "$LOB_WORK/results.csv" cat "$LOB_WORK/median.csv" BASH chmod 700 "$LOB_WORK/run_bench.sh" bash "$LOB_WORK/run_bench.sh"

说明:TABLE_PARALLEL=1 在 A 组固定快速装载线程数,在 C/D/E 组不作为普通 INSERT 提速手段。总计导入 15 次;中途失败时查看对应 .out、.log、_verify.out,排除问题后重新开始本节测试。

查看警告和错误(须结合上下文判断,不能只凭工具退出码):

grep -Ein 'warning|error|警告|错误' "$LOB_WORK"/*.out "$LOB_WORK"/*.log || true cat "$LOB_WORK/median.csv"

通过条件:每次目标行数和去重 ID 数均为 100000,ID 范围和 LOB 长度正确,完整内容比较通过,导入日志无未解释的警告或错误。real 是整个 dimp 进程耗时,含连接及文件读取等开销;不包含清表、导出、造数和校验。性能结果反映重复导入场景,不等同于严格冷缓存结果。

7. 根据结果采用命令

若 C/D/E 组最快,将下面 COMMIT_ROWS 改为其胜出批次。以下命令可独立复跑一次,仍仅清空本实验的目标表:[S1]

"$DM_HOME/bin/disql" "$LOB_ADMIN_CONN" "@$LOB_WORK/04_truncate_target.sql" /usr/bin/time -p \ "$DM_HOME/bin/dimp" \ "USERID=$LOB_ADMIN_CONN" \ "DIRECTORY=$LOB_WORK" \ FILE=lob_100000.dmp \ TABLES=LOB_SRC.T_LOB_TEST \ REMAP_SCHEMA=LOB_SRC:LOB_DST \ DATA_ONLY=Y TABLE_EXISTS_ACTION=APPEND \ FAST_LOAD=Y LOB_NOT_FAST_LOAD=Y \ COMMIT_ROWS=5000 PARALLEL=1 FEEDBACK=0 \ LOG=imp_selected.log "$DM_HOME/bin/disql" "$LOB_ADMIN_CONN" "@$LOB_WORK/05_verify.sql"

若 B 组胜出,将上述导入参数改为 FAST_LOAD=N COMMIT_ROWS=5000,移除 LOB_NOT_FAST_LOAD;若 A 胜出,使用 FAST_LOAD=Y LOB_NOT_FAST_LOAD=N TABLE_PARALLEL=1 COMMIT_ROWS=5000。迁移实际业务表时保留并验证原有约束、索引、触发器和权限;本实验的 DATA_ONLY=Y 以目标结构已经准备完成为前提。

批次 10000 对本数据约对应 312.5 MiB 的原始 LOB 载荷,不代表客户端实际占用同等内存。实际 LOB 更大时,优先从 1000 开始;批次扩大后若更慢,采用较小批次。

评论
后发表回复

作者

文章

阅读量

获赞

扫一扫
联系客服