clob加||隐式转换造成的性能有关问题
日期：2014-05-16 浏览次数：20562 次
clob加||隐式转换造成的性能问题
SQL> insert into t_clob values(1,'3,4,5,6,77,88,99,10,222');
SQL> begin
for i in 1 .. 1000 loop
insert into t_clob values(i,'3,4,99,71,18,91,89,'||i);
end loop;
commit;
end;
/
SQL> set autotrace traceonly
SQL> alter session set events '10046 trace name context forever ,level 12';
SQL> select *
from t_clob
where cb like ',4,'
or cb like ',16'
or cb like ',91';
已用时间: 00: 00: 00.23
执行计划
----------------------------------------------------------
Plan hash value: 3459655851
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2015 | 39 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_CLOB | 1 | 2015 | 39 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CB" LIKE ',4,' OR "CB" LIKE ',16' OR "CB" LIKE ',91')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
820 recursive calls
0 db block gets
175 consistent gets
7 physical reads
0 redo size
416 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select *
from t_clob
where ',' || cb like ',4'
or ',' || cb like ',16'
or ',' || cb like ',91';
未选定行
已用时间: 00: 00: 00.50
执行计划
----------------------------------------------------------
Plan hash value: 3459655851
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2015 | 39 (0)| 00:00:01 |
|* 1 | TABLE ACCE
CLOB在隐式类型转换的时候，会消耗这么多的current mode read和 consistent read(同时也会引起db block change,db block gets 的飙升)，也就是CPU飙升。
SQL> drop table t_clob;
SQL> create table t_clob(id number,cb clob);SQL> insert into t_clob values(1,'3,4,5,6,77,88,99,10,222');
SQL> begin
for i in 1 .. 1000 loop
insert into t_clob values(i,'3,4,99,71,18,91,89,'||i);
end loop;
commit;
end;
/
SQL> set autotrace traceonly
SQL> alter session set events '10046 trace name context forever ,level 12';
SQL> select *
from t_clob
where cb like ',4,'
or cb like ',16'
or cb like ',91';
已用时间: 00: 00: 00.23
执行计划
----------------------------------------------------------
Plan hash value: 3459655851
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2015 | 39 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_CLOB | 1 | 2015 | 39 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CB" LIKE ',4,' OR "CB" LIKE ',16' OR "CB" LIKE ',91')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
820 recursive calls
0 db block gets
175 consistent gets
7 physical reads
0 redo size
416 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select *
from t_clob
where ',' || cb like ',4'
or ',' || cb like ',16'
or ',' || cb like ',91';
未选定行
已用时间: 00: 00: 00.50
执行计划
----------------------------------------------------------
Plan hash value: 3459655851
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2015 | 39 (0)| 00:00:01 |
|* 1 | TABLE ACCE
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
