代码改变世界

ORACLE 物理读 逻辑读 一致性读 当前模式读总结浅析

2016-10-08 23:37  潇湘隐者  阅读(8970)  评论(5)  编辑  收藏  举报

     在ORACLE数据库中有物理读（Physical Reads）、逻辑读（Logical Reads）、一致性读（Consistant Get）、当前模式读（DB Block Gets）等诸多概念，如果不理解或混淆这些概念的话，对你深入理解一些知识无疑是一个障碍，但是这些概念确实挺让让人犯晕的。下面我们总结、学习一下这方面的知识点。捋一捋他们的关系和特点，希望对你有所帮助。

 

物理读（Physical Reads）

 

从磁盘读取数据块到内存的操作叫物理读，当SGA里的高速缓存（Cache Buffer）里面不存在这些数据块时，就会产生物理读，另外。像全表扫描、磁盘排序等操作也可能产生物理读，原因也是因为ORACLE数据库需要访问的数据块较多，而有些数据块不在内存当中，需要从磁盘读取。

 

逻辑读（Logical Reads）

 

概念1：逻辑读指ORACLE从内存读到的数据块数量。一般来说， logical reads = db block gets + consistent gets

概念2：逻辑读指的就是从Buffer Cache中读取数据块。按照访问数据块的模式不同，可以分为当前模式读（Current Read）和一致性读（Consistent Read）。　

这两个概念本质是一样的，只是措辞不一样。

 

一致性读（Consistant Get）

 

ORACLE是一个多用户系统。当一个会话开始读取数据还未结束读取之前，可能会有其他会话修改了它将要读取的数据。如果会话读取到修改后的数据，就会造成数据的不一致。一致性读就是为了保证数据的一致性。在Buffer Cache中的数据块上都会有最后一次修改数据块时的SCN。如果一个事务需要修改数据块中数据，会先在回滚段中保存一份修改前数据和SCN的数据块，然后再更新Buffer Cache中的数据块的数据及其SCN，并标识其为“脏”数据。当其他进程读取数据块时，会先比较数据块上的SCN和进程自己的SCN。如果数据块上的SCN小于等于进程本身的SCN，则直接读取数据块上的数据；如果数据块上的SCN大于进程本身的SCN，则会从回滚段中找出修改前的数据块读取数据。通常，普通查询都是一致性读。

 

当前模式读（DB Block Gets）

 

个人觉得当前模式读（db block gets）是最难理解的一个概念，通常情况下db block gets 可以理解为是DML操作才会产生的.

当前模式读（db block gets）即读取数据块是当前的最新数据。任何时候在Buffer Cache中都只有一份当前数据块。当前读通常发生在对数据进行修改、删除操作时。这时，进程会给数据加上行级锁，并且标识数据为“脏”数据。current mode产生db block gets，一般在DML操作时产生，query mode产生consistent gets（一致性读），一般在查询时产生。他们两个总和一般称为逻辑读，logical read。

有个有意思的现象，在ask tom或一些资料中，你会发现Oracle 8i在SELECT查询当中还能看到db block gets，但是ORACLE 10以及以上版本在SELECT语句中db block gets一般为0。

了解完了概念，如果你还是有一些疑问和不解，那我们结合实际例子来理解一下这些概念吧。如下所示:

SQL> show user;
USER is "SYS"
 
SQL> create table test
  2  as
  3  select * from dba_objects;
 
Table created.
 
SQL> alter session set sql_trace=true;
 
System altered.
 
SQL> set autotrace on;
SQL> select object_type, count(1) from test 
  2  group by object_type;
 
OBJECT_TYPE           COUNT(1)
------------------- ----------
EDITION                      1
INDEX PARTITION            264
CONSUMER GROUP              25
SEQUENCE                   223
TABLE PARTITION            240
SCHEDULE                     3
QUEUE                       35
RULE                         1
JAVA DATA                  328
...............................
...............................
 
43 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 75101 |   806K|   284   (2)| 00:00:04 |
|   1 |  HASH GROUP BY     |      | 75101 |   806K|   284   (2)| 00:00:04 |
|   2 |   TABLE ACCESS FULL| TEST | 75101 |   806K|   281   (1)| 00:00:04 |
---------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
       1109  consistent gets
       1029  physical reads
          0  redo size
       1694  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         43  rows processed
 
SQL> select object_type, count(1) from test 
  2  group by object_type;
 
OBJECT_TYPE           COUNT(1)
------------------- ----------
EDITION                      1
INDEX PARTITION            264
CONSUMER GROUP              25
SEQUENCE                   223
TABLE PARTITION            240
..............................
..............................
 
43 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 75101 |   806K|   284   (2)| 00:00:04 |
|   1 |  HASH GROUP BY     |      | 75101 |   806K|   284   (2)| 00:00:04 |
|   2 |   TABLE ACCESS FULL| TEST | 75101 |   806K|   281   (1)| 00:00:04 |
---------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1034  consistent gets
          0  physical reads
          0  redo size
       1694  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         43  rows processed
 
SQL> set autotrace off
SQL> alter session set sql_trace =false;
 
Session altered.
 
SQL> SELECT T.value 
  2         || '/' 
  3         || Lower(Rtrim(I.INSTANCE, Chr(0))) 
  4         || '_ora_' 
  5         || P.spid 
  6         || '.trc' TRACE_FILE_NAME 
  7  FROM   (SELECT P.spid 
  8          FROM   v$mystat M, 
  9                 v$session S, 
 10                 v$process P 
 11          WHERE  M.statistic# = 1 
 12                 AND S.sid = M.sid 
 13                 AND P.addr = S.paddr) P, 
 14         (SELECT T.INSTANCE 
 15          FROM   v$thread T, 
 16                 v$parameter V 
 17          WHERE  V.name = 'thread' 
 18                 AND ( V.value = 0 
 19                        OR T.thread# = To_number(V.value) )) I, 
 20         (SELECT value 
 21          FROM   v$parameter 
 22          WHERE  name = 'user_dump_dest') T;
 
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/gsp/gsp/trace/gsp_ora_24900.trc

clip_image001

 

如上截图所示， SQL语句第一次执行时，一致性读（consistent gets）为1109， 物理读（physical reads）为1029，当前模式读（db block gets）为0. 如果你再执行一次上面SQL语句，你会发现物理读（physical reads）会降低为0了，因为上一次查询，ORACLE已经将表test的所有数据块读取到buffer cache里面了。当然生产环境实际情况会复杂很多。

 

clip_image002

 

我们先用tkprof工具格式化一下trace文件，然后我们分析一下 out_24900.prf文件。

 

[oracle@DB-Server trace]$ tkprof gsp_ora_24900.trc out_24900.prf aggregate=no;

TKPROF: Release 11.2.0.1.0 - Development on Thu Sep 22 10:12:15 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

 

在分析之前，我们先了解一下一些概念、术语

count    = number of times OCI procedure was executed

cpu      = cpu time in seconds executing

elapsed  = elapsed time in seconds executing

disk     = number of physical reads of buffers from disk                   # 物理读

query    = number of buffers gotten for consistent read                    # 一致性读

current  = number of buffers gotten in current mode (usually for update)   # 当前模式读

rows     = number of rows processed by the fetch or execute call

 

 

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模式下，即当前模式读下db blocks gets的数量。

rows    : 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。 

 

如下截图所示（图1与图2本是连接在一起的，由于太长，分开截图，两张图片有相同部分），由于我们实验过程中，并没有采集统计信息，你会看到trac文件里面有一个动态采样（如果你在创建表，做一次统计信息收集，结果会有一些差别），另外，物理读和一致性读如下，跟上面的执行计划中的数据一致。

 

disk(物理读)      = 747+282 = 1029

query(一致性读)   = 1035+74 = 1109

clip_image003

clip_image004

 

继续分析格式化的prf文件，我们会看到第二次查询的query(一致性读)为1034， disk(物理读)为0

 

clip_image005

 

上面例子，让我们了解了物理读、一致性读，那么接下来看看当前模式读（db block gets）的例子

SQL> create table t
  2  ( id  number(10)
  3  );
 
Table created.
 
SQL> set autotrace on;
SQL> insert into t
  2  values(1000);
 
1 row created.
 
 
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |   100 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | T    |       |       |            |          |
---------------------------------------------------------------------------------
 
Statistics
----------------------------------------------------------
          1  recursive calls
          7  db block gets
          1  consistent gets
          0  physical reads
        748  redo size
        836  bytes sent via SQL*Net to client
        783  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
  2  values(1001);
 
1 row created.
 
 
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |   100 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | T    |       |       |            |          |
---------------------------------------------------------------------------------
 
Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
        308  redo size
        837  bytes sent via SQL*Net to client
        783  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
 

clip_image006

 

一致性读如何计算呢？

 

关于一致性读如何计算呢？ 我查了一下资料，一般一致性读consistent gets ~= numrows/arraysize + blocks ，确切的说是consistent reads计算 ~=ceil(获取行数(card)/arraysize)+used blocks， 而且这个不是绝对等于，而是约等于的关系。 但是这个不是官方资料，而是asktom和一些技术博客的介绍，我们来验证看看吧

 
SQL> exec dbms_stats.gather_table_stats(user, 'TEST');
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> set autotrace traceonly stat
SQL> select * from test;
 
72271 rows selected.
 
 
Statistics
----------------------------------------------------------
        448  recursive calls
          0  db block gets
       5846  consistent gets
       1031  physical reads
          0  redo size
    8296071  bytes sent via SQL*Net to client
      53521  bytes received via SQL*Net from client
       4820  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      72271  rows processed
SQL> /
 
72271 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5789  consistent gets
          0  physical reads
          0  redo size
    8296071  bytes sent via SQL*Net to client
      53521  bytes received via SQL*Net from client
       4820  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72271  rows processed
 

clip_image007

SQL> set autotrace off;
SQL> set serveroutput on;
SQL> exec show_space('TEST',USER);
Free Blocks.............................               0
Total Blocks............................           1,152
Total Bytes.............................       9,437,184
Total MBytes............................               9
Unused Blocks...........................             121
Unused Bytes............................         991,232
Last Used Ext FileId....................               1
Last Used Ext BlockId...................          89,344
Last Used Block.........................               7
 
PL/SQL procedure successfully completed.
 
SQL> show arraysize ;
arraysize 15
SQL> select ceil(72271/15) + 1152-121 from dual;
 
CEIL(72271/15)+1152-121
-----------------------
                   5850
 
SQL> SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(ROWID)) AS blocks FROM TEST;
 
    BLOCKS
----------
      1030
 
SQL> SELECT  ceil(72271/15) + 1030 FROM DUAL;
 
CEIL(72271/15)+1030
-------------------
               5849

clip_image008

 

不管是5849还是5850，都和5879 或5846有一点的出入？也就是说上面那个公式不能用等于号，关于这个，其实在https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:880343948514 里面，你会看到这里介绍的也是一个约等于关系，而不是一个绝对等于的关系。在这里我想深入一点，无奈知识有限。 从上面的公式， 我们可以看到一致性读跟arraysize的关系是蛮大的。那么我们来测试验证一下，先将araraysize调整为50

SQL> set autotrace off;
SQL> set arraysize 50
SQL> set autotrace traceonly stat;
SQL> select * from test;
 
72271 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2456  consistent gets
          0  physical reads
          0  redo size
    7668743  bytes sent via SQL*Net to client
      16418  bytes received via SQL*Net from client
       1447  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72271  rows processed
 
SQL>

clip_image009

SQL> SELECT  ceil(72271/50) + 1030 FROM DUAL;
 
CEIL(72271/50)+1030
-------------------
               2476
 
SQL>

 

如上所示，一致性读从5789降为了2456，有兴趣的可以做一下实验。另外，由于在Oracle中，取数据最后都是从buffer cache中取，所以每出现一个physical reads必然会出现一次 logical reads. 也就是说物理读（physical reads）一定小于逻辑读（logical reads=db block gets + consistent gets），也就是说物理读一定小于一致性读，但是也有物理读大于逻辑读的情况，具体参考官方文档 Why Physical Read is greater than Logical Read in Tkprof (文档 ID 211700.1)

PURPOSE

In some circumstances, you can find that tkprof report shows more physical reads than logical reads, which isn't the current result as the physical reads are normally included in logical reads.

SCOPE & APPLICATION

This article will be useful for the DBA's and customers who are concerned by the tuning of Requests.

Why Physical reads are greater than Logical reads

Sometimes, you can find the following content in tkprof report:

· Physical Reads = Disk (total)

· Logical Reads = Query (total) + Current (total)

call

count

cpu

elapsed

disk

query

current

rows

-------

------

--------

----------

----------

----------

----------

----------

Parse

1

0.67

1.10

0

0

0

0

Execute

1

0.00

0.00

0

0

0

0

Fetch

2202

167.48

678.70

579441

283473

17418

33014

-------

------

--------

----------

----------

----------

----------

----------

total

2204

168.15

679.81

579441

283473

17418

33014

The 'disk' column is then greater than the 'query' + 'current' columns. This isn't usual.

To find the root cause of the problem, you must generate a 10046 event trace file level 8 and check for direct read waits in it.

In 10046 raw trace, you will find "direct path read" and "direct path write" waits like the example below:

WAIT #1: nam='direct path read' ela= 10076 p1=4 p2=29035 p3=1

with P1 = file#, P2 = start block#, P3 = num blocks

The "direct path read" waits account explains the difference between logical and physical reads.

In Oracle 9.2 and above, TKProf will print waits associated with each SQL statement in the output file.

Explanation:

The reason for more physical reads than logical reads is due to the number of direct reads block access. Direct path reads are generally used by Oracle when reading directly into PGA memory (as opposed to into the buffer cache).

They may happen on different actions:

· Sort IO on disk.

· Read by parallel Query Slaves when scanning is done.

· Blocks read by anticipation (readahead).

Such reads are done without loading blocks into the Buffer Cache. They can be single or multiblock reads.

Utilizing Direct Path Reads in this manner prevents the Oracle Buffer cache from beeing overloaded.

Oracle uses this optimisation when it considers that its not necessary to share the blocks between different sessions.

 

最后我们来看一个，热表上一致性读飙涨的案例，其实这个是Oracle 9i&10g编程艺术:深入数据库体系结构这本书籍里面的一个案例，我们在此重演一遍，希望能加深大家对一致性读的理解，首先准备测试数据环境

SQL> show user;
USER is "TEST"
SQL> create table t( x int);
 
Table created.
 
SQL> insert into t values(1);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec dbms_stats.gather_table_stats(user, 'T');
 
PL/SQL procedure successfully completed.
 
 
SQL> set autotrace on statistics;
SQL> select * from t;
 
         X
----------
         1
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        523  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>

clip_image010

 

如上所示，一般情况下一致性读为7，但是我们在一个会话窗口准备执行下面SQL，频繁修改表T 

 
SQL> begin
  2   for i in 1 .. 100000
  3   loop
  4     update t set x=x+1;
  5     commit;
  6   end loop;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.

 

在上面会话执行的同时，我们在另外一个会话窗口马上执行下面SQL，你会看到一致性读飙涨。

SQL> alter session set isolation_level=serializable;
 
Session altered.
 
SQL> set autotrace on statistics;
SQL> select * from t;
 
         X
----------
         1
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      23681  consistent gets
          0  physical reads
          0  redo size
        523  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

clip_image011

 

将会话设置为使用SERIALIZABLE 隔离级别，这样无论在会话中运行多少次查询，都将得到事务开始时刻的查询结果。具体分析不在此画蛇添足，大家感兴趣的可以去看看Oracle 9i&10g编程艺术:深入数据库体系结构。

 

参考资料：

https://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm#i13945

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:880343948514

Oracle 9i&10g编程艺术:深入数据库体系结构