clob加||隐式转换造成的性能问题

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

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 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
统计信息
----------------------------------------------------------
5 recursive calls
84084 db block gets
18057 consistent gets
0 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
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> alter session set events '10046 trace name context off';
会话已更改。
已用时间: 00: 00: 00.00

SQL> set autotrace off

10046 trace的结果：

select *
from t_clob
where cb like ',4,'
or cb like ',16'
or cb like ',91'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 7 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.01 0 16 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 0.01 0 23 0 0


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL T_CLOB (cr=16 pr=0 pw=0 time=12823 us)

select *
from t_clob
where ',' || cb like ',4'
or ',' || cb like ',16'
or ',' || cb like ',91'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 7 15 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.21 0.20 0 9025 42027 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.21 0.24 0 9032 42042 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL T_CLOB (cr=9025 pr=0 pw=0 time=207946 us)