update对consistent gets的影响
来源：互联网 发布：锐捷云课堂软件 编辑：程序博客网 时间：2024/05/19 13:24
chao_ping:
经过别的session Update之后,select 语句的buffer gets 会变大,这是可以理解的.
因为Oracle需要从别的Block里面得到他需要的Block的之前的映像。
但是,这里的这个参数就好像有点南里理解:
SQL> select * from t where object_id=100;
OBJECT_ID OWNER
---------- ------------------------------
100 cc
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=14 Bytes= 420)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=14 ytes
=420)
2 1 INDEX (RANGE SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 rd= 14)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
3 physical reads
0 redo size
424 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
另外开一个session, 把这个表全部都UPdate掉. 在这边再次select:
SQL> /
OBJECT_ID OWNER
---------- ------------------------------
100 cc
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=14 Bytes= 420)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=14 Bytes =420)
2 1 INDEX (RANGE SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card= 14)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
557 consistent gets
0 physical reads
52 redo size
424 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
OBJECT_ID OWNER
---------- ------------------------------
100 cc
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=14 Bytes= 420)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=14 Bytes =420)
2 1 INDEX (RANGE SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card= 14)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
OBJECT_ID OWNER
---------- ------------------------------
100 cc
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=14 Bytes= 420)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=14 Bytes =420)
2 1 INDEX (RANGE SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card= 14)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from t;
COUNT(*)
----------
6996
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1307)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
20 consistent gets
0 physical reads
0 redo size
368 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
这里如果update 只是很少记录的话,那么这边的consistent gets地数量也会小很多.
这个时可以理解的.
update 整个表,为什么检索这条记录,Buffer gets会增加这么多?
标被另外sesision update 之后:
SQL> /
COUNT(*)
----------
6996
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1307)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
7033 consistent gets
0 physical reads
832 redo size
368 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
那个session commit 之后:
SQL> /
COUNT(*)
----------
6996
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1307)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
21 consistent gets
0 physical reads
60 redo size
368 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
但是，如果即使update 其他整个表（除了object_id=100)对应的记录所在地lock,
SQL> update t set owner='c' where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)<>17470;
6380 rows updated.
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=4 Bytes=9
6)
1 0 UPDATE OF 'T'
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=4 Bytes=96)
Statistics
----------------------------------------------------------
0 recursive calls
6530 db block gets
19 consistent gets
0 physical reads
1561860 redo size
622 bytes sent via SQL*Net to client
577 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6380 rows processed
这个select 的成本还是不变
SQL> select * from t where object_id=100;
OBJECT_ID OWNER
---------- ------------------------------
100 zc
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=1 Bytes=3
0)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=
30)
2 1 INDEX (RANGE SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card=
1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
436 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
如果只是update 改记录所在地Block的某一个记录，那么buffer gets增加为2：
SQL> update t set owner='c' where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=17470 and rownum=1;
1 row updated.
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=2
4)
1 0 UPDATE OF 'T'
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=24)
Statistics
----------------------------------------------------------
37 recursive calls
2 db block gets
8 consistent gets
0 physical reads
384 redo size
623 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
OBJECT_ID OWNER
---------- ------------------------------
100 zc
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=1 Bytes=3
0)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=
30)
2 1 INDEX (RANGE SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card=
1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
52 redo size
436 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
如果update 了这个记录所在地Block的全部记录，那么consisteng gets 就会是何update 整个表所在地记录一样的结果：
SQL> update t set owner='c' where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=17470 ;
616 rows updated.
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=2
4)
1 0 UPDATE OF 'T'
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=24)
Statistics
----------------------------------------------------------
0 recursive calls
630 db block gets
19 consistent gets
0 physical reads
150724 redo size
624 bytes sent via SQL*Net to client
576 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
616 rows processed
那么select 的成本：
SQL> /
OBJECT_ID OWNER
---------- ------------------------------
100 zc
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=1 Bytes=3
0)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=
30)
2 1 INDEX (RANGE SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card=
1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
622 consistent gets
0 physical reads
52 redo size
436 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
biti_rainy 回复： buffer gets?
因为 产生 consistent gets 的时候决定于 CR block 的生成 ,select count(*) from x$bh where state = 3; 这里的数量的变化代表着 CR block 重构的生成 ,对于oracle来说，若查询到一条记录，发现已经被更改，于是拷贝当前block到新的地方，从回滚段取回before image 再rollback而重构block，这样的block的 state 为3 ,对于block中每一条记录都会去重构一次( 发现被修改，拷贝到新的block(CR)，然后row--->ITL --> transaction table ---> undo record --->rollback新的blcok(CR)），所以跟一个块中的记录数有关。
但这里要注意，虽然重构了很多CR，但是在我的曾经的测试中发现一次select中一个block的CR block似乎,不超过 3 blocks .也就是说同一个 DBA 的 cache buffer chain 下的 CR 类型的blocks不超过3，我估计是为了节约内存和降低 该 latch(cache buffer chain) 上由于搜索所等待的时间的一种策略。因为 CR block一旦产生后就没有用处了(实际上这个数是一个oracle隐藏参数控制的)
测试如下
SQL> create table test(a number);
Table created.
SQL> insert into test select rownum from t where rownum < 1001;
1000 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t;
COUNT(*)
----------
154986
SQL> update test set a = 0 where a = 1;
1 row updated.
SQL> update test set a = 0 where a < 100;
99 rows updated.
SQL>
通过打开不同的sqlplus反复执行下面的语句观察 后面 的 x$bh 查询表的变化
SQL> select * from test where a < 101;
100 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
110 consistent gets
0 physical reads
52 redo size
2475 bytes sent via SQL*Net to client
1091 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
SQL>
SQL> select ts#,file#,dbablk,state,obj from x$bh where state = 3 order by 1,2,3,4;
TS# FILE# DBABLK STATE OBJ
---------- ---------- ---------- ---------- ----------
0 1 133 3 4294967294
0 1 925 3 6
0 1 930 3 6
0 1 11579 3 10
0 1 11579 3 10
0 1 27273 3 34
0 1 33837 3 6
0 1 34264 3 8
0 1 34264 3 8
0 1 34264 3 8
0 1 34264 3 8
TS# FILE# DBABLK STATE OBJ
---------- ---------- ---------- ---------- ----------
0 1 34268 3 8
0 1 34268 3 8
0 1 34268 3 8
0 1 34268 3 8
0 1 34768 3 2
0 1 34768 3 2
0 1 34788 3 18
2 3 3 3 24825
2 3 3 3 24825
2 3 3 3 24825
2 3 3 3 24825
TS# FILE# DBABLK STATE OBJ
---------- ---------- ---------- ---------- ----------
2 3 3 3 24825
2 3 643 3 27155
2 3 643 3 27155
2 3 643 3 27155
2 3 643 3 27155
27 rows selected.
- update对consistent gets的影响
- ++++update对consistent gets的影响
- Consistent gets 的理解
- 对'Consistent Gets',''Physical Reads'和'DB Block Gets'的理解和解释
- 对'Consistent Gets',''Physical Reads'和'DB Block Gets'的理解和解释
- 对'Consistent Gets',''Physical Reads'和'DB Block Gets'的理解和解释
- 浅谈consistent gets的计算
- Consistent gets
- consistent gets
- Oracle 有关 Consistent gets 的测试
- Oracle 有关 Consistent gets 的测试
- Oracle 有关 Consistent gets 的测试
- 关于 db block gets,consistent gets,physical reads的概念
- 关于 db block gets,consistent gets,physical reads的概念
- 【mysql】关于字符串索引对update的影响
- sqlite3 update/insert/insert or replace对触发器的影响
- 测试delete_trigger对update语句效率的影响
- oracle中null 值对update的影响。
- 关于 cursor_sharing = similar
- 9i rman block recover
- List of the Most Common Data Type Mappings
- v$waitstat 和 save undo segment header /save undo block
- 2004.07.12,Mon - 万事俱备
- update对consistent gets的影响
- 如果在shared_pool中keep cursor(sql)
- 工作流系统中组织模型应用解决方案
- Asp.net连接Sql数据库的语法例子
- 如何知道哪些表是FTS
- 如何查找隐藏参数(x$ksppi and x$ksppsv)
- CTS文章、活动目录［转贴］
- 无聊之通过修改数据字典为表换owner
- Step by Step with Duwamish
