clob加||隐式转换造成的性能问题
来源:互联网 发布:编程tc 是什么 编辑:程序博客网 时间:2024/06/04 19:15
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)
- clob加||隐式转换造成的性能问题
- filter造成的性能问题
- 隐式转换造成数据库性能急剧下降解决
- varchar2转换clob问题
- 【Oracle】 Blob转Clob的字符UTF16转换UTF8问题
- Dynamic Parameters造成的linked server性能问题
- oracle的clob转换varchar2
- weblogic的Clob类型转换
- oracle的clob转换varchar2
- oracle的clob转换varchar2
- Clob转换
- Union Clob字段的问题
- CLOB处理的一些问题.
- CLOB数据类型的显示问题
- clob转换成String的方法总结
- 一个隐式转换引起的性能故障问题的解决过程
- 代码里直接返回对象造成的可读性、扩展性以及性能问题
- 为什么忘记 commit 也会造成 select 查询的性能问题
- 机器学习实战matplotlib画图出错
- 过的发
- 网络爬虫基本原理
- 操作系统学习----进程
- 微信API和SDK的调查
- clob加||隐式转换造成的性能问题
- C++学习札记20140318
- 请求的种类
- Xcode5 统计单元测试覆盖率(仅限XCTest)
- 第三周作业
- jq 选择器大全
- ceph基础研究(2)
- eclipse导入工程出现大红叹号
- 放的地方