记录一次BLOB/CLOB 表的 dexp/dimp 导入测试的步骤与思路,供参考。
优先实测以下组合,批次分别取 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 必然胜出。
以数据库安装用户(如 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]
下面对象应在测试库首次创建;遇到同名对象会停止,不自动删除。用户采用普通权限管理环境的创建方式。表上仅保留 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');
# 然后重新执行本节包检测。
每行 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 长度分布与内容复核。
以 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"
脚本每轮先清空 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 进程耗时,含连接及文件读取等开销;不包含清表、导出、造数和校验。性能结果反映重复导入场景,不等同于严格冷缓存结果。
若 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 开始;批次扩大后若更慢,采用较小批次。
文章
阅读量
获赞
