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,
Quote: | |
|
这个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:
Quote: | |
|
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.