Oracle数据库consistent gets使用的特例

Oracle数据库中，consistent gets在判断一段SQL的性能时非常有用，通常来讲比较两段SQL的性能好坏不是看谁的执行时间短，而是看谁的consistent gets小。不过这也不是绝对的，下面这个例子就是一个反例。

反例子如下：

ETL@RACTEST> create table test( a int);  
Table created. Elapsed: 00:00:00.05  
ETL@RACTEST> ETL@RACTEST> begin  
  2  for i in 1..10000 loop  
  3  insert into test values (i);  
  4  end loop;  
  5  end;  
  6  / PL/SQL procedure successfully completed. Elapsed: 00:00:00.44  
ETL@RACTEST> set autot trace  
ETL@RACTEST> ETL@RACTEST> select * from test;  
10000 rows selected. Elapsed: 00:00:00.05 Execution Plan Plan hash value: 1357081020 -------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | |   0 | SELECT STATEMENT  |      | 10000 |   126K|     6   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS FULL| TEST | 10000 |   126K|     6   (0)| 00:00:01 |  
-------------------------------------------------------------------------- Note    - dynamic sampling used for this statement  
Statistics           0  recursive calls  
          0  db block gets  
        690  consistent gets  
          0  physical reads  
          0  redo size  
     214231  bytes sent via SQL*Net to client  
       7791  bytes received via SQL*Net from client  
        668  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
      10000  rows processed 可以看到select *读了690个内存块。 ETL@RACTEST> select * from test order by 1; 10000 rows selected. Elapsed: 00:00:00.04 Execution Plan Plan hash value: 2007178810 --------------------------------------------------------------------  
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | |   0 | SELECT STATEMENT   |      | 10000 |   126K|     7  (15)| 00:00:01 |  
|   1 |  SORT ORDER BY     |      | 10000 |   126K|     7  (15)| 00:00:01 |  
|   2 |   TABLE ACCESS FULL| TEST | 10000 |   126K|     6   (0)| 00:00:01 |  
--------------------------------------------------------------------------- Note    - dynamic sampling used for this statement  
Statistics           0  recursive calls  
          0  db block gets  
         23  consistent gets  
          0  physical reads  
          0  redo size  
     174288  bytes sent via SQL*Net to client  
       7791  bytes received via SQL*Net from client  
        668  SQL*Net roundtrips to/from client  
          1  sorts (memory)  
          0  sorts (disk)  
      10000  rows processed

再看一下order by，竟然只有23个逻辑读！

1. select * from test;

2. select * from test order by 1;

第1个SQL比第2个SQL效率高是毋庸置疑的。但是为什么第2个SQL的consistent gets如此之少，我起初也是百思不得其解，最终我在ASK TOM中找到了答案。

原因：

一：通常情况下，不在logical RAM buffer中的数据要通过physical reads来读取，而physical reads后通常会紧跟着一个consistent gets。因此一般情况下consistent gets是要比physical reads大的。但是有一个特例，如果physical reads得到的数据直接用于HASH或者SORT，则只记为physical reads不记为consistent gets。所以加上order by后有可能physical reads多但consistent gets少。不过这个原因不是我这里现象产生的原因，因为我这个实验里根本没有physical reads。

二：arraysize的影响。arraysize是指读取数据时一次读取得到的行数。这个值默认为15，使用show arraysize命令可以查看。一个数据块例如有100条记录，那么并不是读取这个块一次就能取到所有数据，以arraysize=15为例，就要有100/15=7次consistent gets。把arraysize设置得大一点可以降低consistent gets，不过有时候可能会消耗更多的资源。如果我们做select count(0) from test;操作，那么Oracle会把arraysize暂时设为test的行数，因此consistent gets会很少：

代码如下：

ETL@RACTEST> select count(0) from test; Elapsed: 00:00:00.00 Execution Plan Plan hash value: 1950795681 --------------  
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     | |   0 | SELECT STATEMENT   |      |     1 |     6   (0)| 00:00:01 |  
|   1 |  SORT AGGREGATE    |      |     1 |            |          |  
|   2 |   TABLE ACCESS FULL| TEST | 10000 |     6   (0)| 00:00:01 |  
------------------------------------------------------------------- Note    - dynamic sampling used for this statement  
Statistics           0  recursive calls  
          0  db block gets  
         23  consistent gets  
          0  physical reads  
          0  redo size  
        515  bytes sent via SQL*Net to client  
        465  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
          1  rows processed

可以看到select count(0)只需要23个逻辑读。一共10000条数据，10000/15=666.667 ，好，667+23=690！和第1个SQL的consistent gets竟然惊人的一致！这不是巧合，这就是consistent gets的计算公式。我们还可以发现select count(0)和第2个SQL的consistent gets竟然也惊人地一致，都是23！

TOM的解释是：
在select * from test order by 1;时，Oracle也把arraysize临时设为test表的行数，它把所有数据先全部取出来放到sort区做排序，而在sort区的读取就不算在consistent gets里了。所以虽然第2个SQL和select count(0)的consistent gets相同，但它的效率一定比select count(0)低，我们看执行计划里的COST便可以得知，第2个SQL的COST为7，select count(0)的COST为6，第1个SQL的COST也为6。（COST相同并不代表执行效率完全相同）

关于Oracle数据库consistent gets的知识就介绍到这里了，希望本次的介绍能够对您有所收获！

【编辑推荐】