oracle表中含有clob查询慢,CLOB类型导致DBLink查询数据很慢 - Oracle数据库管理 - Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusD...

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t (id number,name varchar2(200));

insert into t select object_id,object_name from dba_objects;

commit;

create table t2 (id number,name clob);

insert into t2 select object_id,object_name from dba_objects;

commit;

SQL> set autot traceonly

SQL> select * from t;

75348 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time         |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |         | 79315 |  8907K|   103   (1)| 00:00:02 |

|   1 |  TABLE ACCESS FULL| T         | 79315 |  8907K|   103   (1)| 00:00:02 |

--------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

4  recursive calls

2  db block gets

5459  consistent gets

0  physical reads

0  redo size

3516951  bytes sent via SQL*Net to client

55777  bytes received via SQL*Net from client

5025  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

75348  rows processed

SQL> select * from t2;

75351 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1513984157

--------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time         |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |         | 62603 |   120M|   273   (1)| 00:00:04 |

|   1 |  TABLE ACCESS FULL| T2         | 62603 |   120M|   273   (1)| 00:00:04 |

--------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

4  recursive calls

2  db block gets

75472  consistent gets

0  physical reads

0  redo size

51865980  bytes sent via SQL*Net to client

24928142  bytes received via SQL*Net from client

150704  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

75351  rows processed

从上可以看出，同样的数据，但clob的逻辑读高10几倍。

SQL> col segment_name for a20

select segment_name, sum(bytes)/1024/1024 Mb from user_extents where segment_name in ('T','T2')

2  group by segment_name;

SEGMENT_NAME                     MB

-------------------- ----------

T2                              8

T                              3