ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Export and Import of Table with LOB Columns has Slow Performance

Export and Import of Table with LOB Columns has Slow Performance

原创 Linux操作系统 作者:lhl1212 时间:2009-02-11 17:44:58 0 删除 编辑
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 3006290   8.1.7.4: "EXPORT WITH CLOB COLUMNS IS VERY SLOW"

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/611609/viewspace-548745/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 吞吐量与IOPS
下一篇: tns 连接问题
请登录后发表评论 登录
全部评论

注册时间:2008-12-08

  • 博文量
    252
  • 访问量
    497757