Subject: |
Export and Import of Table with LOB Columns (like CLOB and BLOB) has Slow Performance |
|
Doc ID: |
281461.1 |
Type: |
PROBLEM |
|
Modified Date : |
19-NOV-2008 |
Status: |
PUBLISHED |
Checked for relevance on 19-Nov-2008
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:
-- 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);
-- 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;
/
-- 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
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
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.
-- cleanup after running the test:
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.
FIX
---
Although the performance of the export cannot be improved directly, possible
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.
REFERENCE
---------Note 66046.1"Oracle8i: Example SQL Demonstrating use of LOBs in Oracle8"Note 66431.1"LOBS - Storage, Redo and Performance Issues"Note 159995.1"Different Behaviors of Lob and Lobindex Segments in 8.0, 8i
and 9i"Note 155477.1"Parameter DIRECT Conventional Path Export Versus Direct Path
Export"Bug 30062908.1.7.4: "EXPORT WITH CLOB COLUMNS IS VERY SLOW"
分享到:
相关推荐
Oracle数据库,更改表空间的命令,包括table、index、lob对象。
een cut at the first sentence boundary after 2.000 words) and the number of texts in each category varies (see table below). Further information about the t exts can be found in the LOB manual ...
分享一个来自官方的对Oracle LOB字段的性能调优。
Build a complete real-world line of business (LOB) solution from scratch, with distinct C++ library, QML user interface, and QtTest-driven unit-test projects. This is a suite of essential techniques...
MSDN Webcast 加速基于Windows Mobile的行业应用(Line of Business,LOB)的开发 Video
MSDN Webcast 加速基于Windows Mobile的行业应用(Line of Business,LOB)的开发 PPT
languages (C#, VB.NET, and so on), with no more of JavaScript • To implement advanced UI (XAML markup language) • To work with professional data access (WCF, RIA Services, Entity Framework, and so ...
oracle dbms_lob
real-world examples with all the background material and theory you need The concepts are explained with a practical LOB enterprise application that is gradually built through the course of this book...
Oracle的LOB(CLOB)大字段以及(SYS_LOB$$)清理.txt
The Text Importer and ODBC Importer can now also truncate a table before import, in addition to the "Delete" option. The truncate option is faster but cannot be rolled back. The Compile Invalid ...
sybase IQ LOB手册,这个功能能让你在IQ中村图片和视频
The Text Importer and ODBC Importer can now also truncate a table before import, in addition to the "Delete" option. The truncate option is faster but cannot be rolled back. The Compile Invalid ...
The Text Importer and ODBC Importer can now also truncate a table before import, in addition to the "Delete" option. The truncate option is faster but cannot be rolled back. The Compile Invalid ...
The Text Importer and ODBC Importer can now also truncate a table before import, in addition to the "Delete" option. The truncate option is faster but cannot be rolled back. The Compile Invalid ...
Oracle数据库中LOB的调优.pdf
Oracle LOB字段处理工具是一款简单的lob字段处理工具
通过spring存blob和clob数据到sybase数据库中,压缩包里包含了三种方式,(1)spring+hibernate,切面事务(aop),存lob数据,(2)spring+hibernate存lob数据,(3)spring+jdbc存lob数据。
LOB语料库是模仿Brown语料库的比例建立起来的英国英语语料库,其预料搜集自1961年英国英语出版物上的文本,共500篇,每篇大约2000个单词,合计100万单词。Brown语料库带词性标记,LOB语料库不带词性标记。
前端开源库-lobLOB,LOB API包装