mysql clob 性能_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)

本文原创发布php中文网，转载请注明出处，感谢您的尊重！