(oracle)逻辑读异常(主键查询)
浏览:1228次 出处信息
今天早上在查看各库情况时,发现以primary key查询,竟然消耗了5301个,按理应该是4个逻辑读或以上一点!
以下是代码片段: select t_id, cat_id, t_top1, t_top2, t_top3, t_top4, t_top5, t_top6, t_top7, t_top8, t_top9, t_top10, t_top11, t_top12, t_top13, t_top14 from t where t_id = :1 and cat_id=:2 ------------------------------------------------------------------------------------------ |SELECT STATEMENT |---------- 149755262 ----------| | | 4 | |TABLE ACCESS BY INDEX ROWID |T | 1 | 108 | 4 | | INDEX UNIQUE SCAN |PK_T_ID | 1 | | 3 | ------------------------------------------------------------------------------------------ buffer_gets=5301.07 |
随后我随机取了几个值代入,奇怪的事,逻辑读正常了!这时没有删除T的sql在跑,但早上发生的那种情况,发现删除T的sql在跑。
怀疑和一致读有关,为了维护一致读,sql要不停得读取undo(oracle可能采用递归调用)。
下面测试来证实我的猜测:
以下是代码片段: SQL> !tbsql index tmp_lx_t Column CONSTRAINT_NAME C COLUMN_NAME POSITION STATUS VALIDATED ------------------------------ - ------------------------------ ---------- -------- ------------- PK_T P ID 1 ENABLED VALIDATED |
正常情况下:
以下是代码片段: SQL> select * from tmp_lx_t where id = 1178961568; -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 204 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TMP_LX_T | 1 | 204 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_T | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1580 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
异常情况:
session 1:
以下是代码片段: SQL> delete from tmp_lx_t; 表尽量大一点,延长delete操作时间,效果更明显! |
session 2:
以下是代码片段: SQL> select * from tmp_lx_t where id = 1178961568; -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 204 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TMP_LX_t | 1 | 204 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_T | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17 consistent gets 0 physical reads 64 redo size 1580 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
逻辑读发生明显的变化,go on
以下是代码片段: SQL> select * from tmp_lx_t where id = 1178961568; -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 204 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TMP_LX_t | 1 | 204 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_T | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 38 consistent gets 0 physical reads 64 redo size 1580 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
逻辑读不停再发生变化!!!
小结:在删除表中记录时,删除的记录刚好是符合查询条件的记录,oracle为了构造一致性读,必须读取undo的信息,所以导致逻辑读异常偏高!!!
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
扫一扫订阅我的微信号:IT技术博客大学习
后一篇:为什么Oracle不使用我的索引?! >>
文章信息
- 作者:structured data & unstructured data 来源: structured data & unstructured data
- 标签: 逻辑读
- 发布时间:2010-02-23 22:25:16
近3天十大热文
- [55] Oracle MTS模式下 进程地址与会话信
- [55] IOS安全–浅谈关于IOS加固的几种方法
- [54] 如何拿下简短的域名
- [53] android 开发入门
- [52] Go Reflect 性能
- [52] 图书馆的世界纪录
- [49] 【社会化设计】自我(self)部分――欢迎区
- [49] 读书笔记-壹百度:百度十年千倍的29条法则
- [38] 程序员技术练级攻略
- [32] 视觉调整-设计师 vs. 逻辑