Oracle SQL优化—— 从执行计划和统计信息比较分页查询效率

先看一张执行计划和统计信息的比较图（下图）

这是针对于同一张数据表TAB_TEST_1采用不同的分页查询SQL执行后，获得的执行计划和统计数据，从中可以看出什么问题吗？

显然，左边的执行计划无论是Rows、Bytes、Cost（%CPU），都远远大于右边的执行计划，并且根本就不是一个数量级。

再观察统计数据，左边的执行计划中统计：查询产生了 5868个 consistent gets；5827个 physical reads；而右边的执行计划中查询仅仅产生了 95个 consistent gets；23个 physical reads。

在从执行计划中可以看出，无论左边的查询，还是右边的查询，都对TAB_TEST_1表做了全表扫描（TABLE ACCESS FULL），即FULL TABLE SCAN。

这时我们看到的结果。显而易见，右边的分页查询性能要优于左边的分页查询。

为什么呢？

先解释执行计划中的几个基本信息：

什么是逻辑读（session logical reads）？什么是物理读（physical reads）？

所谓逻辑读（session logical reads），是包含从共享内存（即缓存）和进程私有内存中读取数据块的次数，一般理解为它的值等于“db block gets”+“consistent gets”。

从在内存找不到查询所需要的数据块时，就会产生物理读。

所谓物理读（physical reads），表示从物理磁盘读取到的数据块数。包含了直接从磁盘读取的数据块数，从磁盘读入缓存中数据块数以及从磁盘读入进程私有内存的数据块数。

提示：查询中应尽量减少物理读，从而提高查询的性能。

什么是一致性获取读（consistent gets）？

一致性获取读（consistent gets）：不仅包含了对数据块的读取次数，还可能会包含对回滚数据块的读取次数。

consistent_gets是从回滚段中读到的前映（或叫读取一致性影象）, 看见的数据是查询开始的时间点的，所以若存在block在查询开始后发生了变化的情况，则必须产生 before image 然后读数据，这就是一致读的含义。

一般查询就是表示 consistent gets (query mode)，因为查询要保证所获取的数据的时间点的一致性，所以叫一致读，即使是从当前buffer获得的数据，也叫 consistent gets ，这仅仅表达一种模式一种期望，并不表示真实的是从当前buffer 获得还是从回滚段获取数据产生的 bufore image 。

什么是当前模式数据块获取（db block gets）？

（当前模式）数据块获取：以当前模式读取的数据块数。这种模式下读取的数据块没有一致性要求，只需要读取当前最新内容，通常用于数据修改（增、删、该）。

db block gets 又叫current mode , 不管这个块上的数据是否可能存在 before image ,也就是说不管是否存在回滚数据，只看见当前最新块的数据，即使别人正在更新，也看不见别人更新状态的数据，比如DML的时候就不需要看见别人更改前的数据，而是看见正在更改的，当然同时，若操作相同数据则被LOCK住。也就是说一次查询中看见的数据可能不在同一个时间点上，比如一个大的dml，当dml 开始更新一个非常大的表后，这个表更新的过程中，有一个进程去把该表末尾的一个记录更新了，然后这个大更新抵达该记录的时候会被阻塞的，若该进程事物提交，则大更新会覆盖该事务的更新，也就是说，这个大更新所看见的数据是当前的，不具有时间点的一致性，所以叫 current mode。

consistent gets consistent_gets是从回滚段中读到的前映（或叫读取一致性影象）, 看见的数据是查询开始的时间点的，所以若存在block在查询开始后发生了变化的情况，则必须产生 before image 然后读数据，这就是一致读的含义。一般查询就是表示 consistent gets (query mode)，因为查询要保证所获取的数据的时间点的一致性，所以叫一致读，即使是从当前buffer获得的数据，也叫 consistent gets ，这仅仅表达一种模式一种期望，并不表示真实的是从当前buffer 获得还是从回滚段获取数据产生的 bufore image 。

physical reads： Total number of data blocks read from disk. This number equals the value of “physical reads direct” plus all reads into buffer cache. (Physical Reads:实例启动后，从磁盘读到Buffer Cache数据块数量) 物理读就是从磁盘上读取数据块的数量，其产生的主要原因是：

（1）在数据库高速缓存中不存在这些块

（2）全表扫描

（3）磁盘排序

它们三者之间的关系大致可概括为：逻辑读指的是Oracle从内存读到的数据块数量。一般来说是’consistent gets’ + ‘db block gets’。前面说过，当在内存中找不到所需的数据块的话就需要从磁盘中获取，于是就产生了’physical reads’。

解释完统计信息之后，我们来看分页SQL的写法：

第一条分页查询SQL如下（产生左边的执行计划）：

SQL> SELECT * FROM
  2  (
  3  SELECT A.*, ROWNUM RN
  4  FROM (SELECT * FROM tab_test_1) A
  5  )
  6  WHERE RN BETWEEN 21 AND 40;

SQL分析：select * from table （目标表是：TAB_TEST_1）这个条件存在查询的第三层，Orcale无法将第三层的查询条件推到最内层，所以最内层和中间层返回的数据都是所有满足条件的数据，数据过滤在外层才完成，效率明显较低。

第二条分页查询SQL如下（产生右边的执行计划）：

SQL> SELECT * FROM
  2  (
  3  SELECT A.*, ROWNUM RN
  4  FROM (SELECT * FROM tab_test_1) A
  5  WHERE ROWNUM <= 40
  6  )
  7  WHERE RN >= 21;

SQL分析：select * from table（目标表是：TAB_TEST_1）表示不进行分页的原始语句， rownum >= 21 控制分页查询的每页范围。Orcale可以将外层查询条件推到内层查询中，提高查询效率；所以 rownum <= 40 可以被推到内层查询中，一旦Oracle查询结果超过 rownum 限制条件，查询结果就终止，所以此分页查询格式效率较高。

结果真的是这样吗？

我们的样例数据表TAB_TEST_1有多少行记录呢？有285184条记录，大约28.5万条。（不是很多）

SQL> select count(*) from tab_test_1;

  COUNT(*)
----------
    285184

已选择 1 行。

当然实际业务中，我们不会去查第199981条到第200000条之间的记录。知都不知道是第N页了。（当然，你可以计算出来！）

但是如果查询范围设置为这样，会出现什么结果？

看下面的例子：

第一条SQL语句查询范围为 199981 到 200000

SQL> SELECT * FROM
  2  (
  3  SELECT A.*, ROWNUM RN
  4  FROM (SELECT * FROM tab_test_1) A
  5  )
  6  WHERE RN BETWEEN 199981 AND 200000;

已选择 20 行。

已用时间:  00: 00: 01.93

执行计划
----------------------------------------------------------
Plan hash value: 2614887213

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