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

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

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

我们先用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

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

上面例子，让我们了解了物理读、一致性读，那么接下来看看当前模式读（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

一致性读如何计算呢？

关于一致性读如何计算呢？ 我查了一下资料，一般一致性读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

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...................