[20111229]理解consistent gets*相關資訊[補充]

1.補充一點資料:
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> create table t1 as select rownum id ,'test' name  from dual connect by level<=100;
SQL> alter table t1 add constraint pk_t1 PRIMARY KEY(id) USING INDEX(CREATE INDEX i_t1_id ON t1(id,name)) ENABLE ;
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'T1', estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');

SQL> select index_name from user_indexes where table_name='T1';

INDEX_NAME
------------------------------
I_T1_ID

2.
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

--失望!oracle 看來還是不夠聰明,執行計劃依舊是索引範圍掃描。看來要想實現一個邏輯讀，只能使用IOT表。

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9r3x00whhy46y, 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.

3.在11Gr2下重複測試：

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 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 - examinatio                                    4
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                                                          39(+2)
consistent gets from cache                                     39(+2)
consistent gets from cache (fastpath)                   34(+1)
consistent gets - examination                                   4(+0)
consistent gets direct                                                  0

--與10g一樣，不過11G增加一個統計資訊consistent gets from cache (fastpath).並且有1個讀,以後研究看看。

補充: 如果執行這樣呢？select id,name from t1 where id=60 and rownum<=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                                                          37
consistent gets from cache                                     37
consistent gets from cache (fastpath)                  33
consistent gets - examination                                   4
consistent gets direct                                                  0

15:09:28 SQL> select id,name from t1 where id=60 and rownum<=1;
        ID NAME
---------- ----
        60 test

15:09:54 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                                                           38
consistent gets from cache                                      38
consistent gets from cache (fastpath)                    33
consistent gets - examination                                     4
consistent gets direct                                                    0

邏輯讀僅僅為1，不過一般情況下不會這樣寫。