收获不止Oracle读书笔记之一：体验物理体系之旅

最近在读这本书，梁老师写的真是太好了，让我有种醍醐灌顶的感觉，对思维有很大指导性

今天读了物理体系这张，把自己总结的写下来，分享给大家。

本书京东和当当等网站有售，强烈推荐购买纸质书。支持正版，支持知识产权。

如有侵犯作者权利，请联系我删除。谢谢。

首先我们来看一下Oracle的物理体系是什么样子的，如下图所示：

用户请求的发起经历顺序一般是从 PGA→SGA→数据文件 或者 PGA→SGA

我们以下的例子一条简单的SQL语句来拉开序幕：

select object_name from t wher object_id=29;

我们对这句SQL语句来对物理体系进行分析；

Ⅰ：PGA不同于SGA，是仅供用户使用的私有内存空间，这个区域的具体作用有三点：1.保存用户的登录信息，如会话属性 绑定变量等；2.保存用户的权限信息等；3.当发起的指令需要排序的时候，PGA正处于这个排序去，如果在内存中可以放下排序的尺寸就在内存PGA区完成；

只要该Session不断开连接，下次系统不用去硬盘中读取数据，而直接从PGA内存区去读取；

另外该SQL还会立即匹配成一条唯一的HASH值，接下来该SQL指令进入SGA区进行处理

Ⅱ：首先敲开SGA区共享池的大门，准备登门拜访，如下图所示：

共享池的大门打开了，该SQL先在房间内查询是否什么地方有存储过这个sQL指令的身份工证(就是那个唯一的HASH值)，如果没有就辛苦了，首先要查询自己的语句语法是否正确、语义是否正确、是否有权限 、在这些都没有问题的情况下生成这条语句的身份证，唯一的HASH值就被存储起来了；

接下来就要开始进行解析，在object_id列有索引的情况下，是用索引读取更高效呢，还是用全表扫描更高效？Oracle要做出选择。

这里我们插入Oracle处理的依据：实际上很简单，就是把两种方式都估算一遍，看哪个代价(COST)更低就用哪种。好比上街买菜一样，有的白菜摊卖5毛一斤，有的卖6毛一斤，那就买5毛的。

假设Oracle认定使用索引的代价更低，只要5毛钱，于是Oracle就选用了索引读执行计划而放弃了需要6毛钱的全表扫描方式，接下来这个索引读的执行计划就立即被存储起来，并且和之前存储的该SQL的身份证(唯一的HASH值)对应在一起。

Ⅲ：接下来，该SQL指令好比钦差大臣一样，手持“索引读获取某条数据”这个圣旨，继续往前走，他这要是去哪里呢？原来是直奔“数据缓冲区”府去宣读圣旨了，如下图所示：

Ⅳ：数据缓冲区开门跪谢天恩后，立即要根据ID列上的索引从t表中查找object_id值为29的宝物，但是所要的东西府内找不到，怎么办呢？

数据缓冲区府只好传令下去，八百里加急赶去偏远的Database军营的数据文件去查找皇帝要的东西(当然必须用索引读的方式去查找，这是圣旨，违抗不得），如果查找到了，就带回数据缓冲区府，由钦差大臣展现给皇上，如果找不到，也就只有就此复命了。如图所示：

这样一条最普通的SQL指令的前后经历就完了。

第二部分我们用实例来实验下：

进入sqlplus环境之下如下指令：

create table t as select * from all_objects;

create index idx_object_id on t(object_id);

set autotrace on

set linesize 1000

set timing on

select object_name from t where object_id=29;

执行计划如下：

OBJECT_NAME

------------------------------

C_COBJ#

已用时间:  00: 00: 00.04

执行计划

----------------------------------------------------------

Plan hash value: 2041828949

---------------------------------------------------------------------------

-------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost

)| Time     |

---------------------------------------------------------------------------

-------------

|   0 | SELECT STATEMENT            |               |     1 |    30 |     2

)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |    30 |     2

)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |     1 |       |     1

)| 00:00:01 |

---------------------------------------------------------------------------

-------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=29)

Note

-----

   - dynamic sampling used for this statement

统计信息

----------------------------------------------------------

         52  recursive calls

          0  db block gets

         82  consistent gets

          4  physical reads

          0  redo size

        416  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

接下来我们再执行一次这条语句：

OBJECT_NAME

------------------------------

C_COBJ#

已用时间:  00: 00: 00.00

执行计划

----------------------------------------------------------

Plan hash value: 2041828949

--------------------------------------------------------------------------------

-------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU

)| Time     |

--------------------------------------------------------------------------------

-------------

|   0 | SELECT STATEMENT            |               |     1 |    30 |     2   (0

)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |    30 |     2   (0

)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |     1 |       |     1   (0

)| 00:00:01 |

--------------------------------------------------------------------------------

-------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=29)

Note

-----

   - dynamic sampling used for this statement

统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        416  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

对比两次执行时间：

第二次比第一快了3倍

接下来来看统计信息：

首次执行产生了52次递归调用、82次逻辑读、4次物理读。

而第2次递归调用为0、逻辑读为4次、物理读为0.

这里我们重点把第2次执行的差异解释下：

①用户首次执行该SQL指令时，该指令从磁盘中获取用户连接信息和相关权限信息，并保存在PGA内存里。当用户再次执行该指令时，由于SESSION之前未被中断重连，所以连接信息和相关权限信息就可以在PGA内存里直接读取，避免了物理读取。

②首次执行该SQL指令结束后，SGA内存区的共享池里已经保存了该SQL唯一指令的HASH值，并保留了语法语义检查及相关解析动作的劳动成果，当再次执行该SQL时，由于该指令的HASH值和共享池里保存的匹配了，所以之前的硬解析就无须再做，不仅跳过了相关语法语义检查，对于该选取哪种执行计划也无须考虑直接拿来主义就好了。

③首次执行该SQL指令时，数据一般不存在SGA的数据缓冲区，只能从磁盘中读取，不可避免产生了物理读，但是由于获取后会保存在数据缓冲区里，再次执行就直接从数据缓冲区里获取了，完全避免了物理读，首次执行物理读为4次，第2次为0，数据全在缓存里，效率当然高的多了。

总体来说，Oracle的体系结构设计是有玄机的，这种设计保证了再次执行的效率能大大提升，是非常有意义的。