oracle延迟块清除
2024-08-30 20:20:18
oracle在执行一些DML操作时,会在block上有活动事务的标志,如果一个事务commit后,由于某些block在commit之前已经写回datafile, 或者事务影响到的block数过多,则commit的时候只会清理undo segment header中的事务标志信息,data block上的事务标志不会清除,否则代价过高。那么在一些读取这些block时,需要将这些事务标志进行清除,就是延迟块清除
实验如下:
SQL> drop table t cascade constraints; Table dropped.
--创建一张表t
SQL> create table t as select * from dba_objects where 1=2; Table created. SQL> select count(*) from t; COUNT(*)
----------
0
--采用直接路径插入的方式,即不经过buffer cache
SQL> insert /*+ append */ into t select * from dba_objects; 87023 rows created. SQL> set autotrace on;
SQL> commit; --提交,此时新插入数据的block上的事务标志并没有清除 Commit complete. SQL> select count(*) from t; COUNT(*)
----------
87023 Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522 -------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 339 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 67743 | 339 (1)| 00:00:05 |
------------------------------------------------------------------- Note
-----
- dynamic sampling used for this statement (level=2) Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
1249 consistent gets
1241 physical reads
168 redo size --查询即产生了redo,查询导致了data block上进行事务清除
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed SQL> / COUNT(*)
----------
87023 Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522 -------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 339 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 67743 | 339 (1)| 00:00:05 |
------------------------------------------------------------------- Note
-----
- dynamic sampling used for this statement (level=2) Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1246 consistent gets
0 physical reads
0 redo size --再次查询不产生redo,事务已清除完毕
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed SQL> truncate table t; Table truncated. SQL> select count(*) from t; COUNT(*)
----------
0 Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522 -------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------- Note
-----
- dynamic sampling used for this statement (level=2) Statistics
----------------------------------------------------------
6 recursive calls
1 db block gets
13 consistent gets
0 physical reads
96 redo size --truncate表之后查询,同样也产生了延迟块清除
525 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
最新文章
- asp.net(c#) Color颜色的转换
- Windows 10磁盘占用100%解决办法
- asp.net mvc在Model中控制日期格式
- [转]通过继承ConfigurationSection,在web.config中增加自定义配置
- Wisdombud.CommonTool及其应用
- eclipse 汉化
- ecshop的广告链接地址打开有问题修改affiche.php为普通链接
- COB(Chip On Board)的製程簡單介紹
- MYSQLinsert速度过慢
- 手游 ui布局
- [转载] 详细讲解Hadoop中的简单数据库HBase
- JAVA WEB快速入门之从编写一个JSP WEB网站了解JSP WEB网站的基本结构、调试、部署
- Kubernetes 中的渐进式交付:蓝绿部署和金丝雀部署
- Linux上 发布.Net Core
- CodeForces834D DP + 线段树
- springboot整合activiMQ
- C#创建自定义Object对象
- 第9章 应用层(1)_域名系统DNS
- [vue]计算和侦听属性(computed&;watch)
- ZT android -- 蓝牙 bluetooth (一) 入门