CLOB类型导致DBLink查询数据很慢

LuoLee.me

在实际维护过程中有用户通过DBLink查询远程数据库的数据，但是远程的那张表中含有CLOB列，进而导致一条select要执行十几分钟到四十分钟左右。

我在使用环境中也进行了测试，发现只要查询中含有CLOB列，就会很慢，不知是什么原因，同时不知如何能规避这种问题。

以下是我的实验过程：

1、在远程库中建表：
     create table T_LILO as select * from dba_objects;
     insert into T_LILO select * from T_LILO;
     commit;
2、在本地库上建DBLink：
     create public database link dblilo connect to lilo identified by lilo using 'dblink';
3、在本地建临时表：
     create global temporary table GT_LILO_TEMP (owner varchar2(200), name varchar2(200));
4、准备本地环境：
     LILO@orcl>set timing on
     LILO@orcl>set time on
     15:39:09 LILO@orcl>show user
     USER is "LILO"
     15:39:15 LILO@orcl>
5、插入远程库上的表：
     15:39:49 LILO@orcl>insert into GT_LILO_TEMP (owner, name) select a.owner,a.object_name from T_LILO@DBLILO a;

     205608 rows created.

     Elapsed: 00:00:05.96
     15:39:56 LILO@orcl>

  可以看到这里如果远程表中不含有CLOB类型列的话，只用了6s。

6、回滚刚刚的插入：
     15:40:12 LILO@orcl>rollback;

     Rollback complete.
7、分别在本地和远程增加CLOB类型列：
  本地临时表：
     alter table GT_LILO_TEMP add (C_LOB CLOB);
  远端表：
     alter table T_LILO add (C_LOB CLOB);
8、在远程表中插入CLOB类型列：
     SQL> insert into T_LILO(C_LOB) select object_name from T_LILO;

     102804 rows created.

     SQL> commit;
9、在本地临时表中插入含有CLOB类型的列：
     15:41:06 LILO@orcl>insert into GT_LILO_TEMP (owner, name,C_LOB) select a.owner,a.object_name,a.c_lob from T_LILO@DBLILO a;

     205608 rows created.

     Elapsed: 00:01:45.72
     15:42:53 LILO@orcl>

  从这里看，时间有1分45s，时间是之前没有CLOB列的17倍。

  不知道是否有什么办法可以优化CLOB类型通过DBLink的查询、插入呢？

低调小马哥

我觉得不是dblink的问题，首先clob类型的本身就比varchar2类型的耗费资源，实验如下：

1. SQL> select * from v$version;
   
2. 
   
3. BANNER
   
4. --------------------------------------------------------------------------------
   
5. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
   
6. create table t (id number,name varchar2(200));
   
7. insert into t select object_id,object_name from dba_objects;
   
8. commit;
   
9. create table t2 (id number,name clob);
   
10. insert into t2 select object_id,object_name from dba_objects;
    
11. commit;
    
12. SQL> set autot traceonly
    
13. SQL> select * from t;
    
14. 
    
15. 75348 rows selected.
    
16. 
    
17. 
    
18. Execution Plan
    
19. ----------------------------------------------------------
    
20. Plan hash value: 1601196873
    
21. 
    
22. --------------------------------------------------------------------------
    
23. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time         |
    
24. --------------------------------------------------------------------------
    
25. |   0 | SELECT STATEMENT  |         | 79315 |  8907K|   103   (1)| 00:00:02 |
    
26. |   1 |  TABLE ACCESS FULL| T         | 79315 |  8907K|   103   (1)| 00:00:02 |
    
27. --------------------------------------------------------------------------
    
28. 
    
29. Note
    
30. -----
    
31.    - dynamic sampling used for this statement (level=2)
    
32. 
    
33. 
    
34. Statistics
    
35. ----------------------------------------------------------
    
36.           4  recursive calls
    
37.           2  db block gets
    
38.        5459  consistent gets
    
39.           0  physical reads
    
40.           0  redo size
    
41.     3516951  bytes sent via SQL*Net to client
    
42.       55777  bytes received via SQL*Net from client
    
43.        5025  SQL*Net roundtrips to/from client
    
44.           0  sorts (memory)
    
45.           0  sorts (disk)
    
46.       75348  rows processed
    
47. SQL> select * from t2;
    
48. 
    
49. 75351 rows selected.
    
50. 
    
51. 
    
52. Execution Plan
    
53. ----------------------------------------------------------
    
54. Plan hash value: 1513984157
    
55. 
    
56. --------------------------------------------------------------------------
    
57. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time         |
    
58. --------------------------------------------------------------------------
    
59. |   0 | SELECT STATEMENT  |         | 62603 |   120M|   273   (1)| 00:00:04 |
    
60. |   1 |  TABLE ACCESS FULL| T2         | 62603 |   120M|   273   (1)| 00:00:04 |
    
61. --------------------------------------------------------------------------
    
62. 
    
63. Note
    
64. -----
    
65.    - dynamic sampling used for this statement (level=2)
    
66. 
    
67. 
    
68. Statistics
    
69. ----------------------------------------------------------
    
70.           4  recursive calls
    
71.           2  db block gets
    
72.       75472  consistent gets
    
73.           0  physical reads
    
74.           0  redo size
    
75.    51865980  bytes sent via SQL*Net to client
    
76.    24928142  bytes received via SQL*Net from client
    
77.      150704  SQL*Net roundtrips to/from client
    
78.           0  sorts (memory)
    
79.           0  sorts (disk)
    
80.       75351  rows processed
    
81. 从上可以看出，同样的数据，但clob的逻辑读高10几倍。
    
82. SQL> col segment_name for a20
    
83. select segment_name, sum(bytes)/1024/1024 Mb from user_extents where segment_name in ('T','T2')
    
84.   2  group by segment_name;
    
85. 
    
86. SEGMENT_NAME                     MB
    
87. -------------------- ----------
    
88. T2                              8
    
89. T                              3

复制代码

占用空间也不同，而且默认clob类型是in row存放的，因此我觉得可能需要读更多的数据块。

1. SQL> select * from user_lobs where table_name='T2';
   
2. 
   
3. TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING ENCRYPT COMPRESSION DEDUPLICATION   IN_ROW FORMAT          PARTITIONED SECUREFILE SEGMENT_CREATED RETENTION_TYPE RETENTION_VALUE
   
4. ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------- ------- ----------- --------------- ------ --------------- ----------- ---------- --------------- -------------- ---------------
   
5. T2                             NAME                                                                             SYS_LOB0000088496C00002$$      USERS                          SYS_IL0000088496C00002$$             8192                   900            NO         YES     NONE    NONE        NONE            YES    ENDIAN NEUTRAL  NO          NO         YES             YES            
   
6. 
   

复制代码

用to_char转换下，看看逻辑读：

1. SQL> select id,to_char(name) from t2;
   
2. 
   
3. 75351 rows selected.
   
4. 
   
5. 
   
6. Execution Plan
   
7. ----------------------------------------------------------
   
8. Plan hash value: 1513984157
   
9. 
   
10. --------------------------------------------------------------------------
    
11. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time         |
    
12. --------------------------------------------------------------------------
    
13. |   0 | SELECT STATEMENT  |         | 62603 |   120M|   273   (1)| 00:00:04 |
    
14. |   1 |  TABLE ACCESS FULL| T2         | 62603 |   120M|   273   (1)| 00:00:04 |
    
15. --------------------------------------------------------------------------
    
16. 
    
17. Note
    
18. -----
    
19.    - dynamic sampling used for this statement (level=2)
    
20. 
    
21. 
    
22. Statistics
    
23. ----------------------------------------------------------
    
24.           4  recursive calls
    
25.           2  db block gets
    
26.        6051  consistent gets
    
27.           0  physical reads
    
28.           0  redo size
    
29.     3215696  bytes sent via SQL*Net to client
    
30.       55777  bytes received via SQL*Net from client
    
31.        5025  SQL*Net roundtrips to/from client
    
32.           0  sorts (memory)
    
33.           0  sorts (disk)
    
34.       75351  rows processed

复制代码

可以看出to_char转换后逻辑读和t1表的逻辑读差不多，至于为什么会这样，我还要进一步摸索。
希望有大神给出更深入分析。