Oracle Virtual Index虚拟索引

转自

http://blog.itpub.net/17203031/viewspace-769304/

http://blog.itpub.net/17203031/viewspace-769391/

传统的性能优化和调整工作，大都是在系统上线之后，由运维团队进行的。当系统数据量积累到一定程度之后，原有一些隐藏的问题就不断出现。所以，在大数据量、应急场景下进行SQL调优，往往是运维团队经常遇到的问题。

 

添加索引是我们经常使用的性能优化手段。在遇到问题的时候，试一试添加索引，看看能不能改变执行计划，是我们分析和解决问题的过程手段。但是对于大数据表情况下，快速的创建索引是比较困难的事情。这个时候，我们可以利用Oracle的virtual index技术。

 

1、环境介绍和数据准备

Virtual Index出现的很早。笔者从9i时候的文档资料中，就可以看到virtual index的技术材料。我们还是选择Oracle 11gR2进行试验。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0  Production

我们创建数据表T作为实验对象，同时创建正常Index和虚拟Index。

SQL> show user;
User is "scott"


SQL> create table t as select * from dba_objects;
Table created

 
SQL> set timing on;
--创建一个普通索引

SQL> create index idx_t_owner on t(owner);
Index created
Executed in 0.687 seconds

SQL> select count(*) from t;
  COUNT(*)

----------

     72792

Executed in 0.015 seconds

我们创建virtual index，需要使用nosegment关键字。

SQL> create index idx_t_obj on t(object_id) nosegment;

Index created

Executed in 0.047 seconds


SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

Executed in 1.716 seconds

此处我们需要注意一个细节，同样是在7万多基础数据上面创建索引。nosegment虚拟索引使用的时间很短。

 

2、数据字典层面看virtual index

我们创建了虚拟索引idx_t_obj，又创建了作为参照的idx_t_owner。下面可以从数据字典的层面，去看看虚拟索引的内容信息。

Oracle所有索引信息都记录在dba_indexes视图中。

SQL> select index_name, index_type from dba_indexes where wner='SCOTT' and table_name='T';

INDEX_NAME                     INDEX_TYPE

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

IDX_T_OWNER                    NORMAL

Executed in 0.031 seconds

SQL> select segment_name from dba_segments where wner='SCOTT' and segment_name in ('IDX_T_OWNER','IDX_T_OBJ');

SEGMENT_NAME

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

IDX_T_OWNER

Executed in 0.062 seconds

 

我们从dba_indexes和dba_segments中，都只能看到普通索引idx_t_owner的信息。而创建的虚拟索引idx_t_obj没有踪迹。nosegment选项可以让我们猜测是没有索引段对象的创建过程。但是，作为字典的dba_indexes信息没有，就让人疑惑。

 

验证我们的想法，使用dbms_metadata.get_ddl方法，抽取到数据表t的字典定义。其中，我们看到了idx_t_obj的信息。

  CREATE INDEX "SCOTT"."IDX_T_OBJ" ON "SCOTT"."T" ("OBJECT_ID")

  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOSEGMENT ;


  CREATE INDEX "SCOTT"."IDX_T_OWNER" ON "SCOTT"."T" ("OWNER")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

相对于idx_t_owner，虚拟索引的定义全文显得很简单，只有nosegment很显眼。

那么，作为万物汇总的dba_objects中呢？

SQL> select owner,object_name, object_id, data_object_id, object_type from dba_objects where object_name in ('IDX_T_OWNER','IDX_T_OBJ');


OWNER OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

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

SCOTT IDX_T_OWNER          78019          78019 INDEX

SCOTT IDX_T_OBJ            78020          78020 INDEX

 

Executed in 0.047 seconds

在dba_objects中，我们找到idx_t_obj的信息，它依然被认为是一个索引。更重要的是，我们定位到了object_id和data_object_id，这两个分别为数据库对象的逻辑id和物理段id。

dba_indexes字典视图的基础数据表是ind$基表。其中定义了所有索引对象的信息。我们借助object_id去检查，发现了无法查询到的idx_t_obj对象记录。

SQL> select obj#, ts#, file#, block#, bo# from ind$ where obj# in (78019, 78020);
      OBJ#        TS#      FILE#     BLOCK#        BO#

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

     78019          4          4       1586      78017

     78020          4          0          0      78017

Executed in 0.015 seconds

SQL> select owner, object_name from dba_objects where object_id=78017;

OWNER OBJECT_NAME

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

SCOTT T
Executed in 0.016 seconds

 

我们可以从bo#编号，确定的确是数据表scott.t的索引对象。那么，我们思考一个问题，既然ind$中存在对应记录，为什么dba_indexes不能检索到这个信息呢？

通过抽取dba_indexes的源码信息，我们可以猜到端倪。

from sys.ts$ ts, sys.seg$ s,

     sys.user$ iu, sys.obj$ io, sys.user$ u, sys.ind$ i, sys.obj$ o,

     sys.user$ itu, sys.obj$ ito, sys.deferred_stg$ ds

where u.user# = o.owner#

  and o.obj# = i.obj#

  and i.bo# = io.obj#

  and io.owner# = iu.user#

  and bitand(i.flags, 4096) = 0

  and bitand(o.flags, 128) = 0

  and i.ts# = ts.ts# (+)

  and i.file# = s.file# (+)

  and i.block# = s.block# (+)

  and i.ts# = s.ts# (+)

  and i.obj# = ds.obj# (+)

  and i.indmethod# = ito.obj# (+)

  and ito.owner# = itu.user# (+);

虽然虚拟索引是没有段的，在seg$中必然没有对应记录。但是SQL语句中对于这个条件定义的是外连接。也就是说，即使没有段结构，索引也能显示出来。

疑点就落在对一些列flag标记的bitand操作上了。我们检查一下ind$的基础flags取值，就可以知道原因了。

SQL> select obj#, bitand(flags, 4096) from ind$ where obj# in (78019, 78020);
      OBJ# BITAND(FLAGS,4096)

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

     78019                  0

     78020               4096


Executed in 0.016 seconds

看来，虽然ind$中包括信息，但是不显示出来，也是Oracle的一个本意。

下面我们继续来看virtual index的实际工作效果。

3、“不成功”的实验

Virtual index的特点就是没有段segment结构的支持，在数据字典的基表中存在痕迹。那么，它对于我们的执行计划有什么样的影响呢？

这里我们需要区分两个概念，就是执行计划SEP的生成和执行。Oracle优化器是一个独立的组件，是可以单独进行工作的。同时，Oracle执行计划真正的情况，是从Shared Pool中抽取出来的。Virtual index没有segment结构支持，所以根本不可能实际去执行，即使优化器命令走virtual index路径。那么，我们从执行计划和实际执行两个角度看问题。

首先，我们不做任何额外的配置，看看在virtual index存在的情况下，默认情况下会给我们带来什么。

--反映Oracle Optimizer的判定；

SQL> explain plan for select * from t where object_id = 10000;
Explained
Executed in 0.016 seconds

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 |    97 |   273   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    97 |   273   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=10000)

13 rows selected

Executed in 0.109 seconds

Explain plan for是优化器单独工作，SQL是不真正执行的！看来virtual index不会在这个时候影响优化器。

那么，运行时如何？我们先执行SQL，从shared pool中抽取sql_id信息。

SQL> select /*+demo*/count(*) from t where object_id=10000;
  COUNT(*)
----------
         1
Executed in 0.078 seconds

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/count(*)%';

SQL_ID        EXECUTIONS VERSION_COUNT
------------- ---------- -------------
d2s9wnt37f4g7          1             1

利用dbms_xplan包进行抽取。

SQL> select * from table(dbms_xplan.display_cursor(sql_id => 'd2s9wnt37f4g7'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  d2s9wnt37f4g7, child number 0
-------------------------------------
select /*+demo*/count(*) from t where object_id=10000
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     5 |   273   (1)| 00:00:04 

---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"=10000)

19 rows selected

实际执行的SQL中，也没有执行virtual index路径。所以：在默认的情况下，virtual index既不会参与单独的Optimizer决定，也不会生成与virtual index有关的真实执行计划来执行。

4、“受到影响”的优化器

要让virtual index起作用，需要调整一个Oracle隐含参数_use_nosegment_indexes。默认这个参数取值为false，表示不开启nosegment indexes功能。

我们可以在instance和session两个level去设置这个参数。

SQL> alter　session set "_use_nosegment_indexes" = true;
Session altered
Executed in 0 seconds


我们再来看刚刚的实验。
--explain plan for命令

SQL> explain plan for select * from t where object_id = 10000;
Explained
Executed in 0.016 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2999300365
--------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    97 |     2   (0)| 0
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |     1 |    97 |     2   (0)| 0
|*  2 |   INDEX RANGE SCAN          | IDX_T_OBJ |     1 |       |     1   (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=10000)
14 rows selected
Executed in 0.078 seconds

 我们发现，单独调用optimizer工作的时候，virtual index路径被走到了。那么，真实执行呢？

--真正去执行一下
SQL> select /*+demo_2*/count(*) from t where object_id=10000;

  COUNT(*)
----------
         1

Executed in 0.015 seconds


SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo_2*/count(*)%';
SQL_ID        EXECUTIONS VERSION_COUNT
------------- ---------- -------------
8gbx9grs6cga2          1             1

Executed in 0.016 seconds

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '8gbx9grs6cga2'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8gbx9grs6cga2, child number 0
---------------------------------------------------------------------------------
select /*+demo_2*/count(*) from t where object_id=10000
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     5 |   273   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   2 - filter("OBJECT_ID"=10000)

19 rows selected
Executed in 0.078 seconds

从实际情况看，设置了隐含参数后，单独CBO进行执行计划判定的时候，是会考虑nosegment索引的。但是，在真正执行的时候，还是不会考虑virtual index，因为这个索引并不存在，也不能支持真正执行。

5、CBO or RBO

此时，笔者想到一个问题。Oracle CBO在工作的时候，索引路径只是执行计划的一种“可选路径”。究竟是FTS（Full Table Scan）还是Index Path，取决于统计量计算出的成本值。

那么，virtual index在工作的时候，没有段结构与之对应，统计量也必然有一些不完全。那么，Oracle在生成执行计划的时候，是否进行CBO判定呢？

一个最简单的方法，就是偏移列索引路径判定。

--构造偏移列
SQL> update t set wner='SYS' where owner <> 'SCOTT';
72773 rows updated
Executed in 7.628 seconds

SQL> commit;
Commit complete

Executed in 0 seconds

删除原有的owner列一般索引，创建nosegment索引。
SQL> drop index idx_t_owner;
Index dropped
Executed in 0.234 seconds

SQL> create index idx_t_owner on t(owner) nosegment;
Index created
Executed in 0.078 seconds

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
Executed in 1.123 seconds

--不存在index正式结论；
SQL> select count(*) from dba_indexes where wner='SCOTT' and index_name='IDX_T_OWNRE';
  COUNT(*)
----------
         0
Executed in 0.015 seconds

测试对owner列的选择执行计划。

SQL> alter　session set "_use_nosegment_indexes" = true;
Session altered

Executed in 0 seconds
--小数值执行计划
SQL> explain plan for select * from t where wner='SCOTT';
Explained
Executed in 0.015 seconds

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 151678715
--------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    13 |  1235 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |    13 |  1235 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |    13 |       |     1   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SCOTT')
14 rows selected
Executed in 0.031 seconds


--大数值偏移执行计划
SQL> explain plan for select * from t where wner='SYS';
Explained

Executed in 0 seconds

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  |      | 72772 |  6751K|   273   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 72772 |  6751K|   273   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------
   1 - filter("OWNER"='SYS')

13 rows selected
Executed in 0.063 seconds

看来，在工作中的确是CBO成本运算。对于一些不存在的统计值，Oracle可能是选择一个默认值来定义计算。

6、结论

Oracle Virtual Index是一个研究工具，是我们在投产环境上继续SQL优化方案研究时候的不错工具。

它既满足了让我们创建索引，看执行计划效果的需求。同时也不会消耗很多的索引build资源。