在一個表上建立索引後，會導致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 編輯 ]

在一個表上建立索引後，會導致library cache裡的與此物件有關的遊標被清理出去。

相關文章