虚拟索引-性能优化之利器

去客户现场做性能优化,往往因为生产环境的原因,只能把性能差的SQL拿出来跑执行计划,然后对其进行分析.往往我们给出的判断是创建一个索引就会有性能提升.可是有些刁钻的客户就会问,能走上索引么?最后会有多大的提升?因为是生产环境,客户不会让你随便在上面做测试.所以这类的问题往往都无法回答,因为我不是系统,我不知道会有多大的提升.其实Oracle针对这个问题已经在9i之后的版本推出了虚拟索引的功能,只是用的人很少罢了.那么虚拟索引是个什么意思呢?它就是在数据字典里面有index的定义,而没有真实的index segment.因为数据字典里面有这个信息,我们就能骗到优化器,优化器就会在访问PATH的时候选择索引.优化器最终会生成执行计划,那么我们就可以看到我们建立的索引究竟有没有走,走索引花费的COST以及时间.虽然大部分这个时间是不准的,但是客户想要一个数字的情况下,貌似也成了我们的唯一选择.
下面先来看看虚拟索引的用法,参照了metalink的demo.Virtual Indexes [ID 1401046.1]
1、先创建一个test表,用于测试.

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

2、在Test表查看某个值,生成执行计划.

SQL> explain plan for select * from test where object_name='STANDARD';
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 297 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 12 | 2484 | 297 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='STANDARD')
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.

3.创建我们的虚拟索引

SQL> create index test_index on test (object_name) nosegment;
Index created.

在创建索引的时候一定要指定nosegment选项,这样就不会有真实的数据.
4.查询下数据字典

SQL> select segment_name from dba_segments where segment_name='TEST_INDEX';
no rows selected

SQL> select object_name,object_type from dba_objects where object_name='TEST_INDEX';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
TEST_INDEX INDEX

SQL> exec dbms_stats.gather_index_stats(OWNNAME=>'T1',indname=>'TEST_INDEX');
PL/SQL procedure successfully completed.

SQL> select index_name from dba_indexes where index_name='TEST_INDEX';
no rows selected

可以看到其实在数据字典这个索引是存在的,但是在segment这样的视图是没有的.dba_indexes视图里面也没有,所以收集统计信息也是没用的.
5.验证是否走我们的索引

SQL> explain plan for select * from test where object_name='STANDARD';
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 297 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 12 | 2484 | 297 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='STANDARD')
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.

可以看到这里我们并没有选择使用索引.
6.要使用虚拟索引,确保_USE_NOSEGMENT_INDEXES设置成true.

SQL> explain plan for select * from test where object_name='STANDARD';
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2627321457
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 12 | 2484 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_INDEX | 294 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='STANDARD')
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected.

这里我们看到最终优化器使用了我们建立的索引.有了这些数据,我们就可以告诉客户他想要的得到的答案,所以虚拟索引真是一个好的测试方法.
有几点需要注意的:
1、虚拟索引可以分析,这点我们已经试过。
2、不能使用rebuild语句重建虚拟索引,会报“ORA-08114: can not alter a fake index”。
3、可以像正常索引一样删除。
4、当然该功能也是有些bug的。

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *