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的知識就介紹到這里了，希望本次的介紹能夠對您有所收獲！
【編輯推薦】
