在 Oracle 数据库中有物理读、逻辑读、一致性读、当前模式读等诸多概念，如果不理解或混淆这些概念的话，对深入理解一些知识无疑是一个障碍，但是这些概念确实挺让让人犯晕的。下面我们总结、学习一下这方面的知识点，捋一捋他们的关系和特点。
物理读（Physical Reads）
从磁盘读取数据块到内存的操作叫物理读，当 SGA 里的高速缓存（Cache Buffer）里面不存在这些数据块时，就会产生物理读；另外，像全表扫描、磁盘排序等操作也可能产生物理读，原因也是因为 Oracle 数据库需要访问的数据块较多，而有些数据块不在内存当中，需要从磁盘读取。
逻辑读（Logical Reads）
-
概念1：逻辑读指 Oracle 从内存读到的数据块数量。一般来说， logical reads = db block gets + consistent gets
-
概念2：逻辑读指的就是从 Buffer Cache 中读取数据块。按照访问数据块的模式不同，可以分为当前模式读和一致性读。
这两个概念本质是一样的，只是措辞不一样。
Oracle 性能调优中，逻辑读是个很重要的度量值，它不仅容易收集，而且能够告诉我们许多关于数据库引擎工作量的信息。
逻辑读在 Oracle 调优中有四个好处：
- 逻辑读是受制于 CPU 能力的操作，因而，很好的反映了 CPU 的使用情况。
- 逻辑读可能导致物理读，因而，通过减少逻辑读的数量，很可能会降低 I/O 操作次数。
- 逻辑读是受制于串行的操作，既然经常要考虑多用户负载的优化，最小化逻辑读将有利于避免扩展性问题。
- 逻辑读的数量可以通过 SQL 跟踪文件和动态性能视图在 SQL 语句以及执行计划级别获得。
数据块是 Oracle 最基本的读写单位，但用户所需要的数据，并不是整个数据块，而是数据块中的行或列。
当用户发出 SQL 语句时，该语句被解析执行完毕后，就开始了数据的抓取阶段，在此阶段，服务器进程会先将行所在的数据块从数据文件中读入 buffer cache，这个过程叫做物理读。物理读每读取一个块，就算一次物理读。
当块被送进 buffer cache 后，并不能立即将块传给用户，因为用户所需要的并不是整个块，而是块中的行。从 buffer cache 的块中读取行的过程，就是逻辑读。
为了完成一次逻辑读，服务器进程先要在 hash 表中查找块所在的 buffer cache 链。找到之后，需要在这个链上加一个 cache buffer chains 闩，加闩成功之后，就在这个链中寻找指定的块，并在块上加一个 pin。并释放 cache buffer chains 闩。然后就可以访问块中的行了。服务器进程不会将块中所有满足条件的行一次取出，而是根据你的抓取命令，每次取一定数量的行。这些行取出之后，会经由 PGA 传给客户端用户。行一旦从 buffer cache 中取出，会话要释放掉在块上所加的 pin，本次逻辑读就算结束，如果还要再抓取块中剩余的行，服务器进程要再次申请获得 cache bufffer 链闩，再次在块上加 pin，这就算是另外一次逻辑读。也就是说，服务器进程每申请一次 cache buffer chains 闩，就是一次逻辑读。而每次逻辑读所读取的行的数量，可以在抓取命令中进行设置。
一致性读（Consistant Get）
Oracle 是一个多用户系统。当一个会话开始读取数据还未结束读取之前，可能会有其他会话修改了它将要读取的数据。如果会话读取到修改后的数据，就会造成数据的不一致。一致性读就是为了保证数据的一致性。在 Buffer Cache 中的数据块上都会有最后一次修改数据块时的 SCN。
如果一个事务需要修改数据块中数据，会先在回滚段中保存一份修改前数据和 SCN 的数据块，然后再更新 Buffer Cache 中的数据块的数据及其 SCN，并标识其为“脏”数据。当其他进程读取数据块时，会先比较数据块上的 SCN 和进程自己的 SCN。如果数据块上的 SCN 小于等于进程本身的 SCN，则直接读取数据块上的数据；如果数据块上的 SCN 大于进程本身的 SCN，则会从回滚段中找出修改前的数据块读取数据。通常，普通查询都是一致性读。
Oracle 数据库中，consistent gets 在判断一段 SQL 的性能时非常有用。通常来讲，比较两段 SQL 的性能好坏不是看谁的执行时间短，而是看谁的 consistent gets 小。
当前模式读（DB Block Gets）
通常情况下 db block gets 可以理解为是 DML 操作才会产生的。
当前模式读即读取数据块是当前的最新数据。任何时候在 Buffer Cache 中都只有一份当前数据块。当前读通常发生在对数据进行修改、删除操作时。这时，进程会给数据加上行级锁，并且标识数据为“脏”数据。current mode 产生 db block gets，一般在 DML 操作时产生；query mode 产生 consistent gets（一致性读），一般在查询时产生。他们两个总和一般称为逻辑读（Logical Reads）。
物理读和一致性读案例
SQL> show user;
USER 为 "SCOTT"
SQL> create table test as select * from dba_objects;
表已创建。
SQL> alter session set sql_trace=true;
会话已更改。
SQL> set autotrace traceonly;
--第一次执行
SQL> select object_type, count(1) from test group by object_type;
已选择44行。
执行计划
----------------------------------------------------------
Plan hash value: 1435881708
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 88246 | 947K| 349 (2)| 00:00:05 |
| 1 | HASH GROUP BY | | 88246 | 947K| 349 (2)| 00:00:05 |
| 2 | TABLE ACCESS FULL| TEST | 88246 | 947K| 346 (1)| 00:00:05 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
10 recursive calls
0 db block gets
1319 consistent gets
1241 physical reads
0 redo size
1718 bytes sent via SQL*Net to client
541 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
44 rows processed
--第二次执行
SQL> select object_type, count(1) from test group by object_type;
已选择44行。
执行计划
----------------------------------------------------------
Plan hash value: 1435881708
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 88246 | 947K| 349 (2)| 00:00:05 |
| 1 | HASH GROUP BY | | 88246 | 947K| 349 (2)| 00:00:05 |
| 2 | TABLE ACCESS FULL| TEST | 88246 | 947K| 346 (1)| 00:00:05 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1246 consistent gets
0 physical reads
0 redo size
1718 bytes sent via SQL*Net to client
541 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
44 rows processed
SQL> set autotrace off
SQL> alter session set sql_trace =false;
会话已更改。
SQL> SELECT
T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' || P.SPID || '.trc' TRACE_FILE_NAME
FROM (
SELECT
P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# = 1
AND S.SID = M.SID
AND P.ADDR = S.PADDR
) P, (
SELECT
T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE V.NAME = 'thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))
) I, (
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest'
) T;
TRACE_FILE_NAME
-------------------------------------------------------------------------------------------------
D:\app\admin\diag\rdbms\orcl\orcl\trace/orcl_ora_9656.trc
根据执行计划可知： SQL 语句在第一次执行时，一致性读（consistent gets）为 1319， 物理读（physical reads）为 1241，当前模式读（db block gets）为 0； 再次执行同样的 SQL 语句后，物理读（physical reads）降低为 0 了，因为上一次查询，Oracle 已经将表 TEST 的所有数据块读取到 buffer cache 里面了。
先用 tkprof 工具格式化一下 trace 文件，然后再分析 out_9656.prf 文件。
D:\app\admin\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_9656.trc out_9656.prf aggregate=no;
专业术语说明：
- call：每次 SQL 语句的处理都分成三个部分 Parse：这步包括语法检查和语义检查（包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在）、以及将 SQL 语句转换、生成执行计划等。 Execute：这步是真正的由 Oracle 来执行语句。对于 insert、update、delete 操作，这步会修改数据，对于 select 操作，这步就只是确定选择的记录。 Fetch：返回查询语句中所获得的记录，这步只有 select 语句会被执行。
- count： 这个语句被 parse、execute、fetch 的次数。
- cpu：这个语句对于所有的 parse、execute、fetch 所消耗的 cpu 的时间，以秒为单位。
- elapsed：这个语句所有消耗在 parse、execute、fetch 的总的时间。
- disk：从磁盘上的数据文件中物理读取的数据块的数量。
- query：在一致性读模式下，一致性读的数量。
- current：在 current mode 下，即当前模式读下 db blocks gets 的数量。
- rows: 所有 SQL 语句返回的记录数，但是不包括子查询中返回的记录数目。对于 select 语句，返回记录是在 fetch 这一步骤，对于 insert、update、delete 操作，返回记录则是在 execute 这一步骤。
trac 文件分析
- 第一次执行 SQL 语句
SQL ID: d6byb5gv8sgd1 Plan Hash: 1435881708
select object_type, count(1)
from
test group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 0.04 0.16 908 1246 0 44
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.04 0.17 908 1247 0 44
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83
Number of plan statistics captured: 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.03 0.07 333 72 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.03 0.07 333 72 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
2 user SQL statements in session.
1 internal SQL statements in session.
3 SQL statements in session.
由上述结果可知： disk（物理读）= 908 + 333 = 1241；query（一致性读）= 1247 + 72 = 1319。
- 第二次执行 SQL 语句
SQL ID: d6byb5gv8sgd1 Plan Hash: 1435881708
select object_type, count(1)
from
test group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 0.01 0.01 0 1246 0 44
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.01 0.01 0 1246 0 44
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 83
Number of plan statistics captured: 1
Misses in library cache during parse: 0
1 user SQL statements in session.
0 internal SQL statements in session.
1 SQL statements in session.
由上述结果可知： disk（物理读）= 0；query（一致性读）= 1246。
当前模式读案例
当前模式读：用当前方式从数据库高速缓存区读取的总块数。
- 创建数据表
SQL> create table t (id number); 表已创建。
- 写入数据并查看 db block gets
SQL> set autotrace traceonly;
SQL> insert into t values(100);
已创建 1 行。
执行计划
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T | | | | |
---------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
8 db block gets
1 consistent gets
0 physical reads
0 redo size
837 bytes sent via SQL*Net to client
777 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> insert into t values(1001);
已创建 1 行。
执行计划
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T | | | | |
---------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
1 db block gets
1 consistent gets
0 physical reads
280 redo size
837 bytes sent via SQL*Net to client
778 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
从上述执行计划可知：第一次执行 insert 操作时，db block gets 等于 8，是因为从 Oracle 11g 开始，默认创建的表不会立即分配 segment，不会占用磁盘空间，只有当第一条数据 insert 时才会分配空间且在 Oracle 11g 中默认的 db_block_size 为 8k。
SQL> show parameter db_block_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192
第二次执行 insert 操作时，因为这次写入的内容只有一行，只发生在1个数据块上，所以 db block gets 等于 1。
一致性读的计算
一般一致性读 consistent gets ~= numrows/arraysize + blocks，确切的说是 consistent reads ~= ceil(numrows/arraysize) + Total Blocks - Unused Blocks ，需要注意这个不是绝对等于，而是约等于的关系。
--创建查看 Oracle 数据库对象空间使用情况的存储过程
SQL> conn / as sysdba
CREATE OR REPLACE PROCEDURE SHOW_SPACE(P_SEGNAME IN VARCHAR2,
P_OWNER IN VARCHAR2 DEFAULT USER,
P_TYPE IN VARCHAR2 DEFAULT 'TABLE',
P_PARTITION IN VARCHAR2 DEFAULT NULL) AS
L_FREE_BLKS NUMBER;
L_TOTAL_BLOCKS NUMBER;
L_TOTAL_BYTES NUMBER;
L_UNUSED_BLOCKS NUMBER;
L_UNUSED_BYTES NUMBER;
L_LASTUSEDEXTFILEID NUMBER;
L_LASTUSEDEXTBLOCKID NUMBER;
L_LAST_USED_BLOCK NUMBER;
L_SEGMENT_SPACE_MGMT VARCHAR2(255);
L_UNFORMATTED_BLOCKS NUMBER;
L_UNFORMATTED_BYTES NUMBER;
L_FS1_BLOCKS NUMBER;
L_FS1_BYTES NUMBER;
L_FS2_BLOCKS NUMBER;
L_FS2_BYTES NUMBER;
L_FS3_BLOCKS NUMBER;
L_FS3_BYTES NUMBER;
L_FS4_BLOCKS NUMBER;
L_FS4_BYTES NUMBER;
L_FULL_BLOCKS NUMBER;
L_FULL_BYTES NUMBER;
PROCEDURE P(P_LABEL IN VARCHAR2,
P_NUM IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(RPAD(P_LABEL, 40, '.') || TO_CHAR(P_NUM, '999,999,999,999'));
END;
BEGIN
EXECUTE IMMEDIATE '
select ts.segment_space_management from dba_segments seg,dba_tablespaces ts
where seg.segment_name = :p_segname and (:p_partition is null or seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name=ts.tablespace_name'
INTO L_SEGMENT_SPACE_MGMT
USING P_SEGNAME, P_PARTITION, P_PARTITION, P_OWNER;
IF L_SEGMENT_SPACE_MGMT = 'AUTO' THEN
DBMS_SPACE.SPACE_USAGE(P_OWNER,
P_SEGNAME,
P_TYPE,
L_UNFORMATTED_BLOCKS,
L_UNFORMATTED_BYTES,
L_FS1_BLOCKS,
L_FS1_BYTES,
L_FS2_BLOCKS,
L_FS2_BYTES,
L_FS3_BLOCKS,
L_FS3_BYTES,
L_FS4_BLOCKS,
L_FS4_BYTES,
L_FULL_BLOCKS,
L_FULL_BYTES,
P_PARTITION);
P('Unformatted Blocks ', L_UNFORMATTED_BLOCKS);
P('FS1 Blocks (0-25) ', L_FS1_BLOCKS);
P('FS2 Blocks (25-50) ', L_FS2_BLOCKS);
P('FS3 Blocks (50-75) ', L_FS3_BLOCKS);
P('FS4 Blocks (75-100) ', L_FS4_BLOCKS);
P('Full Blocks ', L_FULL_BLOCKS);
ELSE
DBMS_SPACE.FREE_BLOCKS(SEGMENT_OWNER => P_OWNER,
SEGMENT_NAME => P_SEGNAME,
SEGMENT_TYPE => P_TYPE,
FREELIST_GROUP_ID => 0,
FREE_BLKS => L_FREE_BLKS);
END IF;
DBMS_SPACE.UNUSED_SPACE(SEGMENT_OWNER => P_OWNER,
SEGMENT_NAME => P_SEGNAME,
SEGMENT_TYPE => P_TYPE,
PARTITION_NAME => P_PARTITION,
TOTAL_BLOCKS => L_TOTAL_BLOCKS,
TOTAL_BYTES => L_TOTAL_BYTES,
UNUSED_BLOCKS => L_UNUSED_BLOCKS,
UNUSED_BYTES => L_UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID => L_LASTUSEDEXTFILEID,
LAST_USED_EXTENT_BLOCK_ID => L_LASTUSEDEXTBLOCKID,
LAST_USED_BLOCK => L_LAST_USED_BLOCK);
P('Total Blocks ', L_TOTAL_BLOCKS);
P('Total Bytes ', L_TOTAL_BYTES);
P('Total MBytes ', TRUNC(L_TOTAL_BYTES / 1024 / 1024));
P('Unused Blocks', L_UNUSED_BLOCKS);
P('Unused Bytes ', L_UNUSED_BYTES);
P('Last Used Ext FileId', L_LASTUSEDEXTFILEID);
P('Last Used Ext BlockId', L_LASTUSEDEXTBLOCKID);
P('Last Used Block', L_LAST_USED_BLOCK);
END;
SQL> grant execute on show_space to scott;
SQL> conn scott/tiger;
SQL> create or replace synonym show_space for sys.show_space;
SQL> show user;
USER 为 "SCOTT"
SQL> create table test as select * from dba_objects;
表已创建。
SQL> exec dbms_stats.gather_table_stats(user, 'TEST');
PL/SQL 过程已成功完成。
SQL> set autotrace traceonly stat;
SQL> select * from test;
已选择86839行。
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
6952 consistent gets
0 physical reads
0 redo size
9988273 bytes sent via SQL*Net to client
64198 bytes received via SQL*Net from client
5791 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86839 rows processed
SQL> set autotrace off;
SQL> set serveroutput on;
SQL> exec show_space('TEST',USER);
Unformatted Blocks .................... 0
FS1 Blocks (0-25) .................... 0
FS2 Blocks (25-50) .................... 0
FS3 Blocks (50-75) .................... 0
FS4 Blocks (75-100) .................... 0
Full Blocks .................... 1,241
Total Blocks ........................... 1,280
Total Bytes ........................... 10,485,760
Total MBytes ........................... 10
Unused Blocks........................... 11
Unused Bytes ........................... 90,112
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 10,496
Last Used Block......................... 117
PL/SQL 过程已成功完成。
SQL> show arraysize ;
arraysize 15
SQL> select ceil(86839/15) + 1280 - 11 from dual;
CEIL(86839/15)+1280-11
----------------------
7059
参考资料
原创文章，转载请注明出处：http://www.opcoder.cn/article/43/