[20120903]關於Virtual index.txt


        virtual index沒有segment，如何去產生該虛擬索引的統計資訊，如何保證CBO的有效判斷。

做一個測試與學習看看：


1.測試環境:

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


create table t as select rownum id ,'test' name from dual connect by level <=1e4;

create index i_t_id on t(object_id) nosegment;

--EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id');

SQL> EXECUTE DBMS_STATS.gather_table_STATS (USER,'t');

PL/SQL procedure successfully completed.


2.檢視執行計劃:

SQL> explain plan for select * from t where id=:1 ;

Explained.


SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873


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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     1 |     9 |     8   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |     9 |     8   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"=TO_NUMBER(:1))

13 rows selected.


SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session altered.


SQL> explain plan for select * from t where id=:1 ;

Explained.


SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 4153437776


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

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |        |     1 |     9 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     1 |     9 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | I_T_ID |     1 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=TO_NUMBER(:1))

14 rows selected.


--可以透過設定引數_use_nosegment_indexes=true,來看看執行計劃是否有效！

退出！


3.看看如何分配空間的呢？

SQL> select object_id,data_object_id from dba_objects where wner=user and object_name='I_T_ID';

 OBJECT_ID DATA_OBJECT_ID

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

    106423         106423


SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106423;

      OBJ#     BLEVEL    LEAFCNT    DISTKEY    LBLKKEY    DBLKKEY     CLUFAC ANALYZETIME         SAMPLESIZE     ROWCNT

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

--沒有統計資訊！


SQL> select * from dba_indexes where wner=user and index_name='I_T_ID';

no rows selected


4.分析索引看看。


analyze index i_t_id delete statistics;

analyze index i_t_id validate structure;

validate index i_t_id;


SQL> validate index i_t_id;

Index analyzed.


SQL> select * from index_stats;

no rows selected


檢視文件發現：

procedure generate_stats

    (ownname varchar2, objname varchar2,

     organized number default 7,

     force boolean default FALSE);

--

-- This procedure generates object statistics from previously collected

-- statistics of related objects.  For fully populated

-- schemas, the gather procedures should be used instead when more

-- accurate statistics are desired.

-- The currently supported objects are b-tree and bitmap indexes.

--

--   ownname - schema of object

--   objname - name of object

--   organized - the amount of ordering associated between the index and

--     its undelrying table.  A heavily organized index would have consecutive

--     index keys referring to consecutive rows on disk for the table

--     (the same block).  A heavily disorganized index would have consecutive

--     keys referencing different table blocks on disk.  This parameter is

--     only used for b-tree indexes.

--     The number can be in the range of 0-10, with 0 representing a completely

--     organized index and 10 a completely disorganized one.

--   force - generate statistics even if it is locked

-- Exceptions:

--   ORA-20000: Unsupported object type of object does not exist

--   ORA-20001: Invalid option or invalid statistics

--   ORA-20005: object statistics are locked

--


http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm

GENERATE_STATS Procedure


        This procedure generates object statistics from previously collected statistics of related objects. The currently

supported objects are b-tree and bitmap indexes.


DBMS_STATS.GENERATE_STATS (

   ownname   VARCHAR2,

   objname   VARCHAR2,

   organized NUMBER DEFAULT 7);


Parameters


Table 103-33 GENERATE_STATS Procedure Parameters

Parameter         Description


ownname      Schema of object

objname      Name of object

organized

        Amount of ordering associated between the index and its underlying table. A heavily organized index would have consecutive

index keys referring to consecutive rows on disk for the table (the same block). A heavily disorganized index would have

consecutive keys referencing different table blocks on disk.


Usage Notes


        For fully populated schemas, the gather procedures should be used instead when more accurate statistics are desired.


Exceptions


ORA-20000: Unsupported object type of object does not exist.

ORA-20001: Invalid option or invalid statistics.


------

SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id');


PL/SQL procedure successfully completed.


SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106423;

      OBJ#     BLEVEL    LEAFCNT    DISTKEY    LBLKKEY    DBLKKEY     CLUFAC ANALYZETIME         SAMPLESIZE     ROWCNT

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

    106423          1         22      10000          1          1       4139 2012-09-03 09:00:29       2500      10000


--organized = 0 看看！

SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id',0);

PL/SQL procedure successfully completed.


SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106423;

      OBJ#     BLEVEL    LEAFCNT    DISTKEY    LBLKKEY    DBLKKEY     CLUFAC ANALYZETIME         SAMPLESIZE     ROWCNT

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

    106423          1         22      10000          1          1         24 2012-09-03 09:00:38       2500      10000


--organized = 10 看看！

SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id',10);

PL/SQL procedure successfully completed.


SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106423;

      OBJ#     BLEVEL    LEAFCNT    DISTKEY    LBLKKEY    DBLKKEY     CLUFAC ANALYZETIME         SAMPLESIZE     ROWCNT

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

    106423          1         22      10000          1          1       9997 2012-09-03 09:00:43       2500      10000


--說明 rganized=0 clufac最小，organized=10 clufac最大。其他引數一樣！


5.建立真實的索引看看:


SQL> create index i_t_id on t(id) ;

create index i_t_id on t(id)

ERROR at line 1:

ORA-00955: name is already used by an existing object


SQL> create index i_t_id1 on t(id) ;

Index created.


--可以這樣！


SQL> column object_name format a10

SQL> select object_name,object_id,data_object_id from dba_objects where wner=user and object_name like 'I_T_ID%';

OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID

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

I_T_ID1        106424         106424

I_T_ID         106423         106423


SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106424;

      OBJ#     BLEVEL    LEAFCNT    DISTKEY    LBLKKEY    DBLKKEY     CLUFAC ANALYZETIME         SAMPLESIZE     ROWCNT

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

    106424          1         21      10000          1          1         20 2012-09-03 09:02:18      10000      10000

SQL> validate index i_t_id1;


Index analyzed.


SQL> @i

    HEIGHT     BLOCKS NAME          LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS

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

         2         32 I_T_ID1         10000         21      149801       7996         20          1         220       8028           0               0         10000


MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

                1      175944     150021         86            1                    3          0            0              0                0

6.拿真實的索引測試看看：執行DBMS_STATS.GENERATE_STATS。

SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id1');


PL/SQL procedure successfully completed.


SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106424;

      OBJ#     BLEVEL    LEAFCNT    DISTKEY    LBLKKEY    DBLKKEY     CLUFAC ANALYZETIME         SAMPLESIZE     ROWCNT

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

    106424          1         22      10000          1          1       4139 2012-09-03 10:41:43      10000      10000


SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);


PL/SQL procedure successfully completed.


SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106424;

      OBJ#     BLEVEL    LEAFCNT    DISTKEY    LBLKKEY    DBLKKEY     CLUFAC ANALYZETIME         SAMPLESIZE     ROWCNT

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

    106424          1         21      10000          1          1         20 2012-09-03 10:42:17      10000      10000


--說明可以修改clufac因子。並且狀態看不出是手工修改的。


SQL> select index_name,user_stats from dba_indexes where wner=user and table_name like 'T';

INDEX_NAME                     USE

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

I_T_ID1                        NO


SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id1');


PL/SQL procedure successfully completed.


SQL> select index_name,user_stats from dba_indexes where wner=user and table_name like 'T';

INDEX_NAME                     USE

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

I_T_ID1                        NO


7.為什麼看不建I_T_ID索引呢？

SQL> select obj#,ts#,file#,block#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj# in (106423,106424);

      OBJ#        TS#      FILE#     BLOCK#     BLEVEL    LEAFCNT    DISTKEY    LBLKKEY    DBLKKEY     CLUFAC ANALYZETIME         SAMPLESIZE     ROWCNT

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

    106423          4          0          0          1         22      10000          1          1       4139 2012-09-03 10:41:29       2500      10000

    106424          4          4        570          1         21      10000          1          1         20 2012-09-03 10:55:34      10000      10000


--file#,block#=0! obj#=106423


8.看看改變Clustering Factor的情況：

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);

PL/SQL procedure successfully completed.


SQL> select count(name) from t where id between 50 and  150;

COUNT(NAME)

-----------


SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  gnfqbm1wfph1m, child number 0

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

select count(name) from t where id between 50 and  150


Plan hash value: 1534616770


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

| Id  | Operation                    | Name    | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT             |         |        |     3 (100)|

|   1 |  SORT AGGREGATE              |         |      1 |            |

|   2 |   TABLE ACCESS BY INDEX ROWID| T       |    102 |     3   (0)|

|*  3 |    INDEX RANGE SCAN          | I_T_ID1 |    102 |     2   (0)|

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


Predicate Information (identified by operation id):

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


   3 - access("ID">=50 AND "ID"<=150)


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


26 rows selected.

SQL> alter system flush shared_pool;


System altered.


SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id1');


PL/SQL procedure successfully completed.


SQL> select count(name) from t where id between 50 and  150;

COUNT(NAME)

-----------


SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  gnfqbm1wfph1m, child number 0

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

select count(name) from t where id between 50 and  150


Plan hash value: 2966233522


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

| Id  | Operation          | Name | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT   |      |        |     8 (100)|

|   1 |  SORT AGGREGATE    |      |      1 |            |

|*  2 |   TABLE ACCESS FULL| T    |    102 |     8   (0)|

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


Predicate Information (identified by operation id):

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


   2 - filter(("ID"<=150 AND "ID">=50))


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

25 rows selected.


--執行計劃發生變化，因為Clustering Factor發生了變化：


SQL> select obj#,ts#,file#,block#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj# in (106423,106424);

      OBJ#        TS#      FILE#     BLOCK#     BLEVEL    LEAFCNT    DISTKEY    LBLKKEY    DBLKKEY     CLUFAC ANALYZETIME         SAMPLESIZE     ROWCNT

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

    106423          4          0          0          1         22      10000          1          1       4139 2012-09-03 10:41:29       2500      10000

    106424          4          4        570          1         22      10000          1          1       4139 2012-09-03 11:12:14      10000      10000

[20120903]關於Virtual index.txt

相關文章