[20120903]关于Virtual index.txt

  1. 云栖社区>
  2. 博客>
  3. 正文

[20120903]关于Virtual index.txt

lfreeali 2012-09-03 15:35:04 浏览657
展开阅读全文
[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
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
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ----------
    106423
--没有统计信息!

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)
-----------
        101

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"

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)
-----------
        101

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"=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

 

网友评论

登录后评论
0/500
评论
lfreeali
+ 关注