在一个表上建立索引后，会导致library cache里的与此对象有关的游标被清理出去。...-优快云博客

本文通过实验探讨了在Oracle数据库中创建索引后对librarycache的影响及执行计划的变化。实验证明，创建索引会导致原有游标被清理，并引发硬解析，即使SQL语句不变也会采用新的执行计划。

在一个表上建立索引后，会导致library cache里的与此对象有关的游标被清理出去。

library cache里的游标总是期待能被重用，只有父游标与子游标同时被重用，才能被叫做一次软解析。

我今天的实验，来自于一个疑惑，先看如下实验：
SQL> create table t as select * from dba_objects;

表已创建。

SQL> select * from t where wner='SCOTT';

已选择8行。

已用时间:  00: 00: 00.61
SQL> select * from table(dbms_xplan.display_cursor(null,null,'last'));



--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    |    | 194 (100)|       |
|*  1 |  TABLE ACCESS FULL| T | 67 | 11859 | 194 (2)| 00:00:03 |
--------------------------------------------------------------------------


已选择22行。

已用时间:  00: 00: 00.21
SQL> select sql_text ,sql_id from v$sql where sql_text like 'select * from t where%';
SQL_TEXT                               SQL_ID
---------------------------------------- -------------
select * from t where wner='SCOTT'    5ytkakbws70wb

SQL> create index ind_t on t(owner);

索引已创建。

已用时间:  00: 00: 00.27
SQL> select * from t where wner='SCOTT';

已选择8行。

已用时间:  00: 00: 00.06
SQL> select * from table(dbms_xplan.display_cursor(null,null,'last'));

Plan hash value: 4013845416

-------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    |    |    2 (100)|       |
| 1 |  TABLE ACCESS BY INDEX ROWID| T    |    8 |  1416 |    2 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | IND_T |    8 |    |    1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
已选择23行。

已用时间:  00: 00: 00.04

实验现象：
1）第一次执行语句的时候，由于字段owner上没有索引，因此执行计划采用了全表扫描
2）创建了索引后，再次执行这个语句（完全一样的语句），执行计划已经改用了索引扫描。

疑问：
前后执行的sql语句是一样的，而且这个sql语句在创建索引前，还保留在library cache里，第二次执行的时候，按照我一开始的想法，会发生软解析，重用之前的解析计划，可是结果相反，解析计划已经发生了改变。改为了索引扫描。

那问题出哪了呢？实验步骤里，创建索引前，我是确认过此语句还是在library cache里的，为什么创建完索引，下次再执行这个语句，就发生了硬解析呢？莫非创建完索引，这个语句被淘汰出library cache里了？

接着实验：

SQL> select * from t where wner='NCSI';
未选定行
已用时间:  00: 00: 00.02
SQL> select * from t where wner='NCSITEST';
未选定行
已用时间:  00: 00: 00.03
SQL> select * from t where wner='MM';
未选定行
已用时间:  00: 00: 00.02
SQL> select * from t where wner='SCOTT';
未选定行
已用时间:  00: 00: 00.02

SQL> select sql_text ,sql_id from v$sql where sql_text like 'select * from t where owner%';
SQL_TEXT                               SQL_ID
---------------------------------------- -------------
select * from t where wner='MM'       cvd5kqv65dqsd
select * from t where wner='SCOTT'    5ytkakbws70wb
select * from t where wner='NCSITEST' 41wtwt0byb380
select * from t where wner='NCSI'    7yn8110s175ha
已用时间:  00: 00: 00.04
SQL> create index ind_t on t(owner);
索引已创建。
已用时间:  00: 00: 00.23
SQL> select sql_text ,sql_id from v$sql where sql_text like 'select * from t where owner%';
未选定行
已用时间:  00: 00: 00.03

实验结论：果然和猜测的一样。在一个表上创建完索引后，会导致共享池中的有关此对象的游标被out出library cache。如果下次执行有关这个对象的语句，会导致硬解析。

[ 本帖最后由 wei-xh 于 2010-6-6 17:13 编辑 ]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-664511/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-664511/