db block gets 的含义

rchsh

scott@ORA9>update t set owner='rchsh' where  rownum<2;

1 row updated.


Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=5)
   1    0   UPDATE OF 'T'
   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'T' (Cost=10 Card=6363 Bytes=31815)




Statistics
----------------------------------------------------------
          0  recursive calls
          6  db block gets
         25  consistent gets
          0  physical reads
        556  redo size
       1015  bytes sent via SQL*Net to client
       1055  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

又不明白了，为什么update 存在consistent gets呢？是不是因为update 还是要将数据读到buffer cache 里面修改，这个读也要保证一致性吗？

rchsh

scott@ORA9>select * from t where rownum<2;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=82)
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=10 Card=6363 Bytes=521766)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1391  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott@ORA9>update t set owner='rchsh' where  rownum<2;

1 row updated.


Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=5)
   1    0   UPDATE OF 'T'
   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'T' (Cost=10 Card=6363 Bytes=31815)




Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
          4  consistent gets
          0  physical reads
        252  redo size
       1016  bytes sent via SQL*Net to client
       1055  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott@ORA9>/
在T表修改一条记录和select 一条记录发现2者的consistent gets 都是4而update 的db block gets 为1，意思是不是对于update 的时候先是select 数据然后再修改的，所以update 的consistent gets 还是4，而update 的时候对buffer cache 访问的次数是1所以db block gets 是1

luhuibiao89

不错，