[20111228]理解consistent gets*相關資訊

[20111228]理解consistent gets*相關資訊

SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi PL/SQL Release 10.2.0.3.0 - Production CORE    10.2.0.3.0      Production TNS for Linux: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production SQL> select * from v$statname where name like 'consistent%'; STATISTIC# NAME                                                                  CLASS    STAT_ID ---------- ---------------------------------------------------------------- ---------- ----------         50 consistent gets                                                           8 4162191256         51 consistent gets from cache                                      8 2839918855         52 consistent gets - examination                                  8 1701530557         53 consistent gets direct                                                 8  420374750         60 consistent changes                                                    8 3876379665

 
可以發現在v$statname中，consistent gets有幾個。
一致性讀:consistent gets
    Oracle是一個多使用者系統。當一個會話開始讀取資料還未結束讀取之前，可能會有其他會話修改它將要讀取的資料。如果會話讀取到修改後的資料，就會造成資料的不一致。一致性讀就是為了保證資料的一致性。在Buffer Cache中的資料塊上都會有最後一次修改資料塊時的 SCN。如果一個事務需要修改資料塊中資料，會先在回滾段中儲存一份修改前資料和SCN的資料塊，然後再更新 Buffer Cache中的資料塊的資料及其SCN，並標識其為“髒”資料。當其他程式讀取資料塊時，會先比較資料塊上的 SCN和自己的 SCN。如果資料塊上的 SCN小於等於程式本身的 SCN，則直接讀取資料塊上的資料；如果資料塊上的SCN大於程式本身的SCN，則會從回滾段中找出修改前的資料塊讀取資料。通常，普通查詢都是一致性讀。

consistent gets from cache 很容易理解，就是從cache讀取（記憶體).

SELECT   a.SID,          DECODE (b.CLASS,1, 'User',2, 'Redo',4, 'Enqueue',                  8, 'Cache',16, 'OS',32, 'ParallelServer',                  64, 'SQL',128, 'Debug',                  72, 'SQL & Cache', 40, 'ParallelServer & Cache'                 ) CLASS,          b.NAME, a.VALUE     FROM v$sesstat a, v$statname b    WHERE (a.statistic# = b.statistic#) AND NAME IN ('consistent gets', 'consistent gets from cache') ORDER BY 1;

在正式的伺服器上，兩個數值一樣！

其他表示什麼呢?consistent gets - examination?

1.建立測試環境:
SQL> create table t1 as select rownum id ,'test' name  from dual connect by level<=100;
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'T1', estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');

2.測試如下(無索引):
A.無索引
SQL> column name format a30
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          51
consistent gets from cache                                     51
consistent gets - examination                                   5
consistent gets direct                                                  0

SQL> select * from t1 where id=60;
        ID NAME
---------- ----
        60 test

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          55(+4)
consistent gets from cache                                     55(+4)
consistent gets - examination                                 5(+0)
consistent gets direct                                                    0

--最好先執行select * from t1 where id=60;多次，避免不必要的遞迴語句。以下都是一樣。

3.測試2(建立非唯一索引):
--如何理解consistent gets - examination呢？
B.建立索引,非唯一索引:
SQL> create index i_t1_id on t1(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'T1', estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME                                                                            VALUE
---------------------------------------------------------------- ----------
consistent gets                                                                    51
consistent gets from cache                                               51
consistent gets - examination                                             5
consistent gets direct                                                           0

SQL> select * from t1 where id=60;
        ID NAME
---------- ----
        60 test

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          54(+3)
consistent gets from cache                                     54(+3)
consistent gets - examination                                   5(+0)
consistent gets direct                                                    0

--可以發現走索引可以減少一個邏輯讀，對於這個小表。

4.測試3(建立唯一索引):
SQL> drop index i_t1_id;
SQL> create unique index i_t1_id on t1(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'T1', estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                                    51
consistent gets from cache                                               51
consistent gets - examination                                             5
consistent gets direct                                                           0

SQL> select * from t1 where id=60;
        ID NAME
---------- ----
        60 test

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          53(+2)
consistent gets from cache                                     53(+2)
consistent gets - examination                                   7(+2)
consistent gets direct                                                       0

--可以發現建立唯一索引還可以減少1個邏輯讀，而且consistent gets - examination增加2，也就是這兩個一致讀都是consistent gets - examination貢獻的。

google發現如下連結：


摘要如下:

    We notice we have indeed only performed the 2 consistent gets. But we also notice another significant difference, that being both consistent gets are now the "cheaper" consistent gets – examination.

    This means that the latches required to now perform. this select statement via the Unique index is just 2, down from 6 for the Non-unique index and 8 from the FTS.

    Generally during a consistent get, Oracle needs to grab the cache buffers chain latch so it can pin the specific block in memory and then grab the latch again so that it can subsequently unpin the block once it's finished processing the block.Each of these accesses to the latch and the subsequently pin/unpinning of the block requires CPU and is a possible sourceof contention within the database.

    For some operations that only require a very very quick "read and get out of there" type operation and/or on blocks that are unlikely to change within a given point of time, Oracle uses a cheaper consistent get operation which doesn't actually require the block to be pinned. There's no point in pinning the block as it's only going to be read and accessed for a short time (shorter than might otherwise be required when processing a block in memory) and the block is unlikely to change anyways.

    So for these operations, Oracle uses a cheaper consistent get called a consistent gets – examination. These consistent gets examinations only need grab the cache buffers chain latch before quickly reading the block and releasing the latch once
the read operation is complete. Therefore it only needs to grab and release the cache buffer chains latch the once without having to pin/unpin the block, which means less CPU and less latch contention overall.
--為什麼是1次latch使用consistent gets - examination?

    Now this isn't particularly well documented. Often discussions mention reads of undo blocks as being candidates for consistent gets examinations as these reads are likely to be both relatively quick and a specific undo block is unlikely to change as only one transaction can actually update an undo block at a given time.

    Getting back to indexes, reads of index root blocks are another candidate mentioned as again a read of an index root block is going to be very quick and an index root block is unlikely to change at a given point of time.

    However, what is not well documented at all is the fact that any block accessed during an index Unique Scan is accessed via a consistent get – examination, including the consistent get associated with reading the table block as well. This is because again, any such read operation is going to be relatively quick as the most that ever needs to be read is the one index related entry and the one table row.

    The net result is that now accessing a row from a small table via a Unique index requires only 2 latch accesses vs. the initial FTS example which required 8 latch gets as none of the FTS consistent gets are examinations.

    Now you might say that these are all very small numbers, that 4 consistent reads isn't that much, that 8 latches isn't  really that huge a number and reducing 8 latches down to 2 latches isn't really going to be that noticeable. Yes it is effectively a 75% reduction but it's a 75% reduction of not very much.

    And if you're discussing a single read of a single small lookup table you would likely be right.

    But what if the small table is accessed frequently by the application, perhaps many 1,000s of times per minute. What if you have many such small tables, often used in small join operations by your OLTP applications. What if you have large numbers of users in a large application with many many such small table accesses. This effectively 75% saving can potentially become very significant, both in terms of the reduction in CPU load and also in the reduction of latch contention, which in turn can further reduce CPU loads.

    A small improvement multiplied by a large amount can indeed make a difference …

    However, I have one more step to go yet in further improving the efficiency of these small table lookups via an index.

    One which can reduce the overall overheads by yet another 50% …

5.測試4(建立唯一索引,但是包括name欄位):
SQL> drop index i_t1_id;
SQL> create unique index i_t1_id on t1(id,name);
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'T1', estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                               51
consistent gets from cache                                          51
consistent gets - examination                                        5
consistent gets direct                                                       0

SQL> select id,name from t1 where id=60;

        ID NAME
---------- ----
        60 test

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                           VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          53(+2)
consistent gets from cache                                     53(+2)
consistent gets - examination                                   5(+0)
consistent gets direct                                                       0

--奇怪,為什麼consistent gets - examination沒有變化呢？

--檢視執行計劃如下:
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
   1 - access("ID"=60)
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
23 rows selected.

    可以發現執行計劃是INDEX RANGE SCAN,2個邏輯讀也說明問題。如果修改語句如下：
select id,name from t1 where id=60 and name='test';

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME                                                                            VALUE
---------------------------------------------------------------- ----------
consistent gets                                                                    51
consistent gets from cache                                               51
consistent gets - examination                                             5
consistent gets direct                                                            0

SQL> select id,name from t1 where id=60 and name='test';
        ID NAME
---------- ----
        60 test

SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                          52(+1)
consistent gets from cache                                     52(+1)
consistent gets - examination                                   6(+1)
consistent gets direct                                                    0

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  ast84z2xbwdbk, child number 0
-------------------------------------
select id,name from t1 where id=60 and name='test'
Plan hash value: 3064003808
----------------------------------------------
| Id  | Operation         | Name    | E-Rows |
----------------------------------------------
|*  1 |  INDEX UNIQUE SCAN| I_T1_ID |      1 |
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"=60 AND "NAME"='test')
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
23 rows selected.


6.總結如下：
以前我自己對於小表建立很多索引覺得沒有必要，現在看來不是，而且資料模型也很重要。比如上面的表你可以建立id主鍵上建立非唯一索引。