db block get和consistent read get

Buffer Modes

When a client requests data, Oracle Database retrieves buffers from the database buffer cache in either of the following modes:

• Current mode

  A current mode get, also called a db block get, is a retrieval of a block as it currently appears in the buffer cache. For example, if an uncommitted transaction has updated two rows in a block, then a current mode get retrieves the block with these uncommitted rows. The database uses db block gets most frequently during modification statements, which must update only the current version of the block.

• Consistent mode

  A consistent read get is a retrieval of a read-consistent version of a block. This retrieval may use undo data. For example, if an uncommitted transaction has updated two rows in a block, and if a query in a separate session requests the block, then the database uses undo data to create a read-consistent version of this block (called a consistent read clone) that does not include the uncommitted updates. Typically, a query retrieves blocks in consistent mode.

这里我们主要讨论db block get发生的情况：
1.创建模拟数据：
SQL> create user test identified by test;

用户已创建。

SQL> grant connect,resource to test;

授权成功。

SQL>
SQL>
SQL> connect test/test
已连接。
SQL> create table test(id number);

表已创建。

SQL> insert into test values (1);

已创建 1 行。

SQL> insert into test values (2);

已创建 1 行。

SQL> commit;

提交完成。

SQL>
SQL>
SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),id from test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)         ID
------------------------------------ ----------
                                 174          1
                                 174          2
创建了一张test表，插入了两条记录，两条记录都存储在相同的块。

2.用sys用户执行如下两个脚本，使得test用户也能跟踪统计信息：
SQL> @?/rdbms/admin/utlxplan.sql

表已创建。

SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
第 1 行出现错误:
ORA-01919: 角色 'PLUSTRACE' 不存在

SQL> create role plustrace;

角色已创建。

SQL>
SQL> grant select on v_$sesstat to plustrace;

授权成功。

SQL> grant select on v_$statname to plustrace;

授权成功。

SQL> grant select on v_$mystat to plustrace;

授权成功。

SQL> grant plustrace to dba with admin option;

授权成功。

SQL>
SQL> set echo off
SQL> grant plustrace to test;

授权成功。

3.模拟db block gets发生情况，db block gets多发生在执行DML语句的时候。
场景1：事务A读取了一个块到内存中，这时事务B也需要读取这个块来进行修改，事务B不需要从磁盘上读取，直接从事务A之前读取到内存中的块来读取，如果事务B操作的数据对应的块和事务A选择的数据是在同一个块上就会发生db block gets。

会话1：
SQL> set autotrace trace statistics;
SQL> select * from test where id=1;

统计信息
----------------------------------------------------------
        268  recursive calls
          0  db block gets
         46  consistent gets
         17  physical reads
          0  redo size
        417  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed
会话2：
SQL> set autotrace trace statistics;
SQL> update test set id=6 where id=2;

已更新 1 行。

统计信息
----------------------------------------------------------
          6  recursive calls
          3  db block gets
         20  consistent gets
          2  physical reads
          0  redo size
        671  bytes sent via SQL*Net to client
        600  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

场景2：如果事务A修改某条数据，从磁盘中读到了内存，并未提交，这时事务B也修改了表的一条数据，数据所在的块和事务A修改的数据在相同的块上。事务B不会从磁盘读取这个块，而是直接中事务A读取到内存中的块读取，这时也会发生db block gets，db block gets的发生产生了脏读，也就说事务A没有提交的数据也读取到事务B的块中来了，但是由于事务B与事务A修改的不是同一条数据，所以脏读并不会造成数据的不一致。db block gets这也是提高SQL执行效率的处理方式。
先用sys用户清空shared_pool和buffer_cache：
SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

会话1：
SQL> set autotrace trace statistics;
SQL> update test set id=5 where id=1;

已更新 1 行。

统计信息
----------------------------------------------------------
        239  recursive calls
          3  db block gets
         48  consistent gets
         11  physical reads
          0  redo size
        674  bytes sent via SQL*Net to client
        600  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

会话2：
SQL> set autotrace trace statistics;
SQL> update test set id=6 where id=2;

已更新 1 行。

统计信息
----------------------------------------------------------
          4  recursive calls
          3  db block gets
         17  consistent gets
          0  physical reads
        476  redo size
        670  bytes sent via SQL*Net to client
        599  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-697963/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23135684/viewspace-697963/