[20111228]理解consistent gets*相关信息
| SQL> select * from v$version;
BANNER SQL> select * from v$statname where name like 'consistent%'; |
可以发现在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 levelSQL> 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发现如下链接：
http://richardfoote.wordpress.com/2009/05/13/indexes-and-small-tables-part-v-its-no-game/
摘要如下:
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'));
SQL_ID 329djm0w66kfk, child number 0
-------------------------------------
select id,name from t1 where id=60
Plan hash value: 4406210
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
|* 1 | INDEX RANGE SCAN| I_T1_ID | 1 |
---------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
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主键上建立非唯一索引。