[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 levelSQL> 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

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下重複測試：

--------------------------------------------------------------------------------

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

NAME                                                                  VALUE

consistent gets                                                          37

consistent gets from cache                                     37

consistent gets from cache (fastpath)                   33

consistent gets - examinatio                                    4

consistent gets direct                                                 0

consistent gets                                                          39(+2)

consistent gets from cache                                     39(+2)

consistent gets from cache (fastpath)                   34(+1)

consistent gets - examination                                   4(+0)

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

補充: 如果執行這樣呢？select id,name from t1 where id=60 and rownum

 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 from cache                                     37

consistent gets from cache (fastpath)                  33

consistent gets - examination                                   4

15:09:28 SQL> select id,name from t1 where id=60 and rownum

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%';

consistent gets                                                           38

consistent gets from cache                                      38

consistent gets from cache (fastpath)                    33

consistent gets - examination                                     4

consistent gets direct                                                    0

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