[20111229]Consistent gets from cache (fastpath).txt
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select * from v$statname where NAME like 'consistent gets%';
STATISTIC# NAME CLASS STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
67 consistent gets 8 4162191256
68 consistent gets from cache 8 2839918855
69 consistent gets from cache (fastpath) 8 2173751438
70 consistent gets - examination 8 1701530557
71 consistent gets direct 8 420374750
如果对比10g可以发现，11G增加一个consistent gets from cache (fastpath)统计，google发现如下链接:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:580696400346550153
http://afatkulin.blogspot.com/2009/01/consistent-gets-from-cache-fastpath.html
1.重复测试:
create table t1 nologging as select rownum id ,a.* from all_objects a where rownum create table t2 nologging as select * from t1;
create index idx_t1_id on t1(id);
exec dbms_stats.gather_table_stats(user,'T1');
exec dbms_stats.gather_table_stats(user,'T2');
2.在11GR2下执行如下：
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 37
consistent gets from cache 37
consistent gets from cache (fastpath) 33
consistent gets - examination 4
consistent gets direct 0
SQL> select/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id;
COUNT(*)
----------
10000
10:26:20 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 324(+287)
consistent gets from cache 324(+287)
consistent gets from cache (fastpath) 298(+265)
consistent gets - examination 5(+1)
consistent gets direct 0
执行计划如下:
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 518mw211s3mmv, child number 0
-------------------------------------
select/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id
Plan hash value: 3211355954
--------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | NESTED LOOPS | | 10000 |
| 3 | TABLE ACCESS FULL| T2 | 10000 |
|* 4 | INDEX RANGE SCAN | IDX_T1_ID | 1 |
--------------------------------------------------
3.在10G下执行:
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 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/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id;
COUNT(*)
----------
10000
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 10208(+10157)
consistent gets from cache 10208(+10157)
consistent gets - examination 6(+1)
consistent gets direct 0
--可以发现逻辑读在10g下增加许多，在11G下仅仅287个逻辑读。11g下逻辑读明显减少。
4.对比执行计划：
11G:
SQL> select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id;
COUNT(*)
----------
10000
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 877z7w0x4d3rx, child number 0
-------------------------------------
select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id
Plan hash value: 3211355954
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 287 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 287 |
| 2 | NESTED LOOPS | | 1 | 10000 | 10000 |00:00:00.03 | 287 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 10000 | 10000 |00:00:00.01 | 139 |
|* 4 | INDEX RANGE SCAN | IDX_T1_ID | 10000 | 1 | 10000 |00:00:00.02 | 148 |
-------------------------------------------------------------------------------------------
10G:
SQL> select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id;
COUNT(*)
----------
10000
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST OUTLINE PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 877z7w0x4d3rx, child number 0
-------------------------------------
select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id =t2.id
Plan hash value: 3211355954
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.09 | 10157 |
| 2 | NESTED LOOPS | | 1 | 10000 | 10000 |00:00:00.12 | 10157 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 10000 | 10000 |00:00:00.01 | 135 |
|* 4 | INDEX RANGE SCAN | IDX_T1_ID | 10000 | 1 | 10000 |00:00:00.07 | 10022 |
-------------------------------------------------------------------------------------------
--可以发现执行计划一样的,差别主要在扫描IDX_T1_ID索引上的buffers，可以知道11G改进了一些东西。因为T2插入是按照的id号的顺序插入的，在扫描t1的IDX_T1_ID索引时id=1,id=2.....应该在同一索引数据块上。
试想一下，如果T2表插入的数据不是有序的，逻辑读没有这么少了。
5.建立表T3,打乱id的顺序。重复以上的测试：
11G:
SQL> create table t3 nologging as select * from t1 order by dbms_random.random;
SQL> exec dbms_stats.gather_table_stats(user,'T3');
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 37
consistent gets from cache 37
consistent gets from cache (fastpath) 33
consistent gets - examination 4
consistent gets direct 0
SQL> select/*+use_nl(t3 t1) gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id;
COUNT(*)
----------
10000
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 10198
consistent gets from cache 10198
consistent gets from cache (fastpath) 10193
consistent gets - examination 5
consistent gets direct 0
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cwdqmh7ryywx8, child number 0
-------------------------------------
select/*+use_nl(t3 t1) gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id
Plan hash value: 1864576943
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 10161 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 10161 |
| 2 | NESTED LOOPS | | 1 | 10000 | 10000 |00:00:00.05 | 10161 |
| 3 | TABLE ACCESS FULL| T3 | 1 | 10794 | 10000 |00:00:00.01 | 139 |
|* 4 | INDEX RANGE SCAN | IDX_T1_ID | 10000 | 1 | 10000 |00:00:00.04 | 10022 |
-------------------------------------------------------------------------------------------
10g下：
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/*+use_nl(t3 t1) gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id;
COUNT(*)
----------
10000
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 10208
consistent gets from cach 10208
consistent gets - examinaton 6
consistent gets direct 0
cache buster issue application index and duplicate .library file
