CLOB column over DBLink takes long time

From: Luo Li <lilogohard_at_gmail.com>
Date: Sat, 20 Jul 2013 13:34:03 +0800
Message-ID: <CAEJ23UcNppWqAyQ_iBQQ6x+Hh4cizT0bEfRKPprb7jPz4+FSxQ_at_mail.gmail.com>

Hello!
I have a question about insert/select clob column over dblink, it takes me a very long time.
Anybody meets this problem or has an advise?

Below is my test:

1. Create table in the remote:

   create table T_LILO as select * from dba_objects; insert into T_LILO select * from T_LILO; commit;

2. Create a dblink in local db:

   create public database link dblilo connect to lilo identified by lilo using 'dblink';

3. Create a temporary table in local:

   create global temporary table GT_LILO_TEMP (owner varchar2(200), name varchar2(200));

4. Prepare the environment:

   LILO_at_orcl>set timing on
   LILO_at_orcl>set time on 15:39:09 LILO_at_orcl>show user USER is "LILO" 15:39:15 LILO_at_orcl>

5. Insert to the local table from dblink:

   15:39:49 LILO_at_orcl>insert into GT_LILO_TEMP (owner, name) select a.owner,a.object_name from T_LILO_at_DBLILO a;

   205608 rows created.
   Elapsed: 00:00:05.96 15:39:56 LILO_at_orcl>

   If the table haven't CLOB column, it takes 6s.

6. Rollback the temporary table:

   15:40:12 LILO_at_orcl>rollback;
   Rollback complete.

7. Add an column on the LOCAL temporary table:

   alter table GT_LILO_TEMP add (C_LOB CLOB);

  Add a CLOB column in the REMOTE table:

   alter table T_LILO add (C_LOB CLOB);

 Fill the C_LOB column in the remote table T_LILO:

   SQL> insert into T_LILO(C_LOB) select object_name from T_LILO;    102804 rows created.
   SQL> commit;

8. Insert into the table adding CLOB column:

   15:41:06 LILO_at_orcl>insert into GT_LILO_TEMP (owner, name,C_LOB) select a.owner,a.object_name,a.c_lob from T_LILO_at_DBLILO a;

   205608 rows created.
   Elapsed: 00:01:45.72 15:42:53 LILO_at_orcl>

*We can see the insert takes 1mins and 45s,so 1m45s VS 6s! it's surprising to me why the CLOB cosumes so long time.*

Is there any way to improving the performance CLOB through DBLink?

Thanks very much.

BLOG: http://luolee.me

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jul 20 2013 - 07:34:03 CEST