淺談consistent gets的計算

首先介紹一下什麼是consistent gets，我摘引一段官方的定義，就不做自己的解釋了：
The consistent gets Oracle metric is the number of times a consistent read (a logical RAM buffer I/O) was requested to get data from a data block.
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相同並不代表執行效率完全相同）

consistent reads計算=ceil(獲取行數(card)/arraysize)+used blocks(FTS的話就是HWM下BLOCK)+1
分析:ceil(num_rows/arraysize) 例如取100行 每次顯示到 螢幕10行 需要取10次,oracle 訪問buffer cache 中相應的 hash chain 搜尋需要的buffer時需要 持有 cache

buffers chains latch取完資料後釋放,再取時再獲取,這樣需要獲取10次才夠顯示完100行, cache buffers chains latch每獲取一次就是一次邏輯讀 (對於select來說就是).
+1 是多加一次segment header block scan
好了，現在明白了吧，這第二個原因就是我的實驗現象產生的原因！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23071790/viewspace-695436/，如需轉載，請註明出處，否則將追究法律責任。