consistent gets :consistent_gets是从回滚段中读到的前映(或叫读取一致性影象), 看见的数据是查询开始的时间点的,所以若存在block在查询开始后发生了变化的情况,则必须产生 before image 然后读数据,这就是一致读的含义
查询就是表示 consistent gets (query mode),因为查询要保证所获取的数据的时间点的一致性,所以叫一致读,即使是从当前 buffer 获得的数据,也叫 consistent gets ,这仅仅表达一种模式一种期望,并不表示真实的是从 当前buffer 获得 还是从回滚段获取数据产生的 bufore image 。
db block gets : current mode , 不管这个块上的数据是否可能存在 before image ,也就是说不管是否存在回滚中数据可以 回滚,只看见当前最新块的数据,即使别人正在更新,也看见别人更新状态的数据,比如dml的时候就不需要看见别人更改前的数据,而是看见正在更改的,当然 同时,若操作相同数据则被lock住。也就是说一次查询中看见的数据可能不在同一个时间点上,比如一个大的dml,当dml 开始更新一个非常大的表后,这个表更新的过程中,有一个进程去把该表末尾的一个记录更新了,然后这个大更新抵达该记录的时候会被阻塞的,若该进程事物提 交,则大更新会覆盖该事务的更新,也就是说,这个大更新所看见的数据是当前的,不具有时间点的一致性,所以叫 current mode,个人认为db block gets这个词用的不好, 容易让人误解. 如果改成inconsistent gets可能会更准确一些
别人blog写的。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> set autot
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autot traceonly stat
SQL> select * from t;
已选择798945行。
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets --这个为0
63677 consistent gets
11118 physical reads
0 redo size
85740332 bytes sent via SQL*Net to client
586267 bytes received via SQL*Net from client
53264 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
798945 rows processed
SQL> select * from t;
已选择798945行。
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
69587 consistent gets --全是一致性得到,因为已经在Buffer_cache中
0 physical reads
0 redo size
85740332 bytes sent via SQL*Net to client
586267 bytes received via SQL*Net from client
53264 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
798945 rows processed
SQL> select * from t for update;
已选择798945行。
统计信息
----------------------------------------------------------
476 recursive calls
812439 db block gets --更新时,产生这个
884932 consistent gets
5 physical reads
160912940 redo size
73057198 bytes sent via SQL*Net to client
586267 bytes received via SQL*Net from client
53264 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
798945 rows processed
SQL> rollback;
回退已完成。
SQL> alter sytem flush buffer_cache;
alter sytem flush buffer_cache
*
第 1 行出现错误:
ORA-00940: 无效的 ALTER 命令
SQL> alter system flush buffer_cache;
系统已更改。
SQL> select * from t for update;
已选择798945行。
统计信息
----------------------------------------------------------
476 recursive calls
812437 db block gets
884836 consistent gets
11122 physical reads
160903012 redo size
73057198 bytes sent via SQL*Net to client
586267 bytes received via SQL*Net from client
53264 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
798945 rows processed
SQL> alter system flush buffer_cache;
系统已更改。
SQL> desc t;
名称 是否为空? 类型
----------------------------------------- -------- ---------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> update t set owner='a';
已更新798945行。
统计信息
----------------------------------------------------------
4564 recursive calls
7008787 db block gets
1123165 consistent gets
13434 physical reads
555747124 redo size
683 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
798945 rows processed
SQL> select * from t;
已选择798945行。
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
63677 consistent gets
0 physical reads
0 redo size
82886810 bytes sent via SQL*Net to client
586267 bytes received via SQL*Net from client
53264 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
798945 rows processed
可以理解 为:
consistent gets : 是由 查询语句产生的, 不管所查的数据是否需要构造前镜像,都要算进去
db block gets: 是由update,delete,select for update 产生的
相关推荐
查看该SQL的response time(db block gets/consistent gets/physical reads/sorts (disk)) 7:说说你对索引的认识(索引的结构、对dml影响、为什么提高查询性能) b-tree index/bitmap index/function index/...
oracle数据库 oracle数据库的性能调整 性能调整
arraysize对consistent gets的影响
Consistent Hashing and Random Trees
开源项目-lafikl-consistent.zip,lafikl/consistent: a package for Consistent Hashing and Consistent Hashing With Bounded Loads.
开源项目-buraksezer-consistent.zip,go中有界负载的一致散列
Nginx:一致性哈希(第三方模块ngx_http_consistent_hash):ngx_http_consistent_hash-master.zip
Consistent-hashing: Go中的散列环实现
Consistent Global States of Distributed Systems
python库。 资源全名:ConsistentHashing-0.1.9.tar.gz
Applications of self-consistent field theory in polymer systems.pdf
CVPR2015-Cross-modality Consistent Regression for Joint Visual-Textual Sentiment Analysis.pdf 全文
analysis, we propose a novel, real-time EKF-based VIO algorithm, which achieves consistent estimation by (i) ensuring the correct observability properties of its linearized system model, and (ii) ...
libconhash is a consistent hashing libraray, which can be compiled both on Windows and Linux platform. High performance, easy to use, and easy to scale according to node's processing capacity.
目标检测论文 Consistent Optimization for Single-Shot Object Detection.pdf
Jemter测试MQ的插件 JMeter-Rabbit-AMQP在github上17年便停止更新了,不支持rabbitmq的交换机类型“x-consistent-hash”,为此我更改了源码使其支持"x-consistent-hash
Building reliable distributed systems at a worldwide scale demands trade-offs between consistency and availability.
this the problem of consistent labeling of objects when seen in multiple cameras. We employ a novel approach of finding the limits of field of view (FOV) of each camera as visible in the other cameras...
资源分类:Python库 所属语言:Python 资源全名:jump_consistent_hash-3.1.1-cp27-cp27m-win_amd64.whl 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
Cockroach provides snapshot isolation (SI) and serializable snapshot isolation (SSI) semantics, allowing externally consistent, lock-free reads and writes--both from a historical snapshot timestamp ...