update对consistent gets的影响

 

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:

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：

Quote:

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.