大对象对数据库导出/导入性能的影响
来源:互联网 发布:比较好的外贸鞋淘宝店 编辑:程序博客网 时间:2024/04/29 14:47
Export and Import of Table with LOB Columns (like CLOB and BLOB) has Slow Performance
Doc ID:Note:281461.1Type:PROBLEM Last Revision Date:02-SEP-2004Status:PUBLISHED
The information in this article applies to:
- Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 10.2.0.0
- Oracle Server - Personal Edition - Version: 8.1.7.0 to 10.2.0.0
- Oracle Server - Standard Edition - Version: 8.1.7.0 to 10.2.0.0
This problem can occur on any platform.
SYMPTOMS
--------
An export or import of a table with a Large Object (LOB) column,
has a slower performance than an export or import of a table without LOB
columns:
has a slower performance than an export or import of a table without LOB
columns:
-- create two tables: TESTTAB1 with a VARCHAR2 column, and TESTTAB2 with a
-- CLOB column:
connect / as sysdba
create table scott.testtab1 (nr number, txt varchar2(2000));
create table scott.testtab2 (nr number, txt clob);
-- CLOB column:
connect / as sysdba
create table scott.testtab1 (nr number, txt varchar2(2000));
create table scott.testtab2 (nr number, txt clob);
-- populate both tables with the same 500,000 rows:
declare
x varchar2(50);
begin
for i in 1..500000 loop
x := 'This is a line with the number: ' || i;
insert into scott.testtab1 values(i,x);
insert into scott.testtab2 values(i,x);
commit;
end loop;
end;
/
declare
x varchar2(50);
begin
for i in 1..500000 loop
x := 'This is a line with the number: ' || i;
insert into scott.testtab1 values(i,x);
insert into scott.testtab2 values(i,x);
commit;
end loop;
end;
/
-- export both tables:
% exp system/manager file=exp_testtab1.dmp tables=scott.testtab1 direct=y
% exp system/manager file=exp_testtab1a.dmp tables=scott.testtab1
% exp system/manager file=exp_testtab2.dmp tables=scott.testtab2
% exp system/manager file=exp_testtab1.dmp tables=scott.testtab1 direct=y
% exp system/manager file=exp_testtab1a.dmp tables=scott.testtab1
% exp system/manager file=exp_testtab2.dmp tables=scott.testtab2
No CLOB No CLOB With CLOB
DIRECT CONVENTIONAL column
------------ ------------ ------------
8.1.7.4.0 0:13 0:20 7:49
9.2.0.4.0 0:14 0:18 7:37
9.2.0.5.0 0:12 0:15 7:03
10.1.0.2.0 0:16 0:31 7:15
DIRECT CONVENTIONAL column
------------ ------------ ------------
8.1.7.4.0 0:13 0:20 7:49
9.2.0.4.0 0:14 0:18 7:37
9.2.0.5.0 0:12 0:15 7:03
10.1.0.2.0 0:16 0:31 7:15
Note:
These performance results should not be considered as a benchmark
of the performance between different Oracle versions, as the test
databases were located on different machines with different hardware,
and the databases had a different parameter configuration.
The main objective of these results is to give an indication of the
difference in the time that is needed to export a table with a LOB
column, and a table without a LOB column.
These performance results should not be considered as a benchmark
of the performance between different Oracle versions, as the test
databases were located on different machines with different hardware,
and the databases had a different parameter configuration.
The main objective of these results is to give an indication of the
difference in the time that is needed to export a table with a LOB
column, and a table without a LOB column.
-- cleanup after running the test:
connect / as sysdba
drop table scott.testtab1;
drop table scott.testtab2;
connect / as sysdba
drop table scott.testtab1;
drop table scott.testtab2;
CHANGES
-------
You recently created tables that have Large Object (LOB) columns.
CAUSE
-----
This is expected behavior. The rows of a table with a LOB column are fetched
one row at a time. Also note that rows in tables that contain objects and
LOBs will be exported using conventional path, even if direct path was
specified.
Also during import, the rows of tables containing LOB columns are inserted
individually.
one row at a time. Also note that rows in tables that contain objects and
LOBs will be exported using conventional path, even if direct path was
specified.
Also during import, the rows of tables containing LOB columns are inserted
individually.
FIX
---
Although the performance of the export cannot be improved directly, possible
alternative solutions are:
alternative solutions are:
1. If not required, do not use LOB columns.
or:
2. Use Transport Tablespace export instead of full/user/table level export.
or:
3. Upgrade to Oracle10g and use Export DataPump and Import DataPump.
or:
2. Use Transport Tablespace export instead of full/user/table level export.
or:
3. Upgrade to Oracle10g and use Export DataPump and Import DataPump.
我的测试:
=======================================
Tue Jan 29 10:26:06 CST 2008
Export: Release 9.2.0.6.0 - Production on Tue Jan 29 10:26:06 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
Current user changed to SCOTT
. . exporting table TESTTAB1 500000 rows exported
Export terminated successfully without warnings.
Tue Jan 29 10:26:11 CST 2008
Current user changed to SCOTT
. . exporting table TESTTAB1 500000 rows exported
Export terminated successfully without warnings.
Tue Jan 29 10:26:11 CST 2008
=======================================
50万条记录,无lob对象,direct路径导出,耗时5秒
=======================================
Export: Release 9.2.0.6.0 - Production on Tue Jan 29 10:26:11 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table TESTTAB1 500000 rows exported
Export terminated successfully without warnings.
Tue Jan 29 10:26:18 CST 2008
Current user changed to SCOTT
. . exporting table TESTTAB1 500000 rows exported
Export terminated successfully without warnings.
Tue Jan 29 10:26:18 CST 2008
=======================================
50万条记录,无lob对象,Conventional路径导出,耗时7秒
=======================================
Export: Release 9.2.0.6.0 - Production on Tue Jan 29 10:26:18 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table TESTTAB2 500000 rows exported
Export terminated successfully without warnings.
Tue Jan 29 10:29:19 CST 2008
Current user changed to SCOTT
. . exporting table TESTTAB2 500000 rows exported
Export terminated successfully without warnings.
Tue Jan 29 10:29:19 CST 2008
=======================================
50万条记录,含lob对象,只能Conventional路径导出,耗时181秒
- 大对象对数据库导出/导入性能的影响
- 临时对象对软件性能的影响
- AutoGrowth 对数据库性能的影响
- 浅谈索引对数据库性能的影响
- 数据库参数对mysql性能的影响
- exp/imp导出导入功能对job的的影响
- secure_file_priv 配置项对数据导入导出的影响
- extended-insert对mysqldump及导入性能的影响
- java对mysql数据库的导入导出
- oracle的对数据库导入和导出
- c#数据库对文本文件的导入导出
- 数据库的连接数对应用系统性能的影响
- 重复的index,对"数据库性能"有一定的影响。
- SQL条件的顺序对数据库性能的影响
- 高速缓存和连接池对访问数据库性能的影响
- 高速缓存和连接池对数据库性能的影响
- 触发器(TRIGGER)对DB2数据库系统性能的影响
- 高速缓存和连接池对访问数据库性能的影响
- 集合划分问题
- 10 个有趣的 Linux 命令
- AspectJ ログ
- Codeforces Round #116 (Div. 2, ACM-ICPC Rules) E - Cubes
- Win7下安装VC6
- 大对象对数据库导出/导入性能的影响
- Codeforces Round #116 (Div. 2, ACM-ICPC Rules) C - Letter
- Android LOG机制的实现
- AspectJ GUIアプリの国際化
- linux下GTK+配置
- XAF之DashboardViewItem管理
- 有关ucenter同步的原理(个人总结的一点浅薄见解)
- 【译】构建可扩展的Java EE应用(二)
- ArcEngine Runtime 10 安装后在 VS2010 上未能找到 ArcGIS 的引用