v$sql v$sqlarea v$sql_shared_cursor及遊標

1.v$sql和v$sqlarea的區別
v$sql和v$sqlarea從某種意義上具有父子關係。即v$sqlarea儲存的是父遊標的sql資訊，而v$sql儲存的是子游標的sql的資訊。在v$sqlarea裡面有一列VERSION_COUNT欄位，其中代表的就是對於此父遊標的子游標的數量，也就是在v$sql裡面的子游標的sql記錄的數量。在v$sql中有一列CHILD_NUMBER欄位，表示該字遊標的編號。可以說v$sqlarea和v$sql是一對多的父子關係。

2.父遊標和子游標
每種型別的dml語句都需要如下階段：
Create a Cursor         建立遊標
Parse the Statement     分析語句
Bind Any Variables      繫結變數
Run the Statement       執行語句
Close the Cursor        關閉遊標
當資料庫第一次對一條SQL語句進行硬解析的時候，會在庫快取中分配一些記憶體，並將新產生的父遊標儲存進去。與父遊標有關的關鍵資訊室這個SQL語句的文字，這個時候，會在v$sqlarea裡面插入一條記錄。那麼，在什麼情況下會產生子游標呢，當資料庫又碰到一條完全相同SQL語句，但是語句的執行計劃和執行環境發生了變化，比如由於繫結變數窺測而產生的不一致的執行計劃，由於SQL的初始化引數optimizer_mode的不同以及繫結變數分級的情況都會產生子游標，當產生子游標的時候，會在v$sql裡面插入一條記錄。並且v$sqlarea裡的VERSION_COUNT欄位的值會加1。
eg：
--多個SQL語句只有在它們的文字完全一致的情況下才能共享一個父遊標，這是最基本的要求。
--視窗1執行
sys/SYS>select * from t;

no rows selected

sys/SYS>select * from t;

no rows selected

sys/SYS>SELECT * from t;

no rows selected
--視窗2執行
sys/SYS>select hash_value,sql_text,executions from v$sqlarea where sql_text like '%from t';

HASH_VALUE SQL_TEXT                                 EXECUTIONS
---------- ---------------------------------------- ----------
3542409071 SELECT * from t                                   1
 520543201 select * from t                                   2
可以看到由於SQL文字不同產生了兩個父遊標。
sys/SYS>select HASH_VALUE,child_number,sql_text from v$sql where sql_text like '%from t';

HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------ ----------------------------------------
 520543201            0 select * from t
3542409071            0 SELECT * from t
在v$sql裡面也插入了兩條子游標的記錄，但是CHILD_NUMBER都是0，其實對應的就是父遊標。
現在再來看下由於optimizer_mode的不同而產生子游標的情況。
--視窗1執行
sys/SYS>alter session set optimizer_mode=all_rows;

Session altered.

sys/SYS>select * from tt;

no rows selected

sys/SYS>alter session set optimizer_mode=first_rows_10;

Session altered.

sys/SYS>select * from tt;

no rows selected

--視窗2執行

sys/SYS>select hash_value,sql_text,executions,VERSION_COUNT from v$sqlarea where sql_text like '%from tt';

HASH_VALUE SQL_TEXT                                 EXECUTIONS VERSION_COUNT
---------- ---------------------------------------- ---------- -------------
3762890390 select * from tt                                  2             2

sys/SYS>select HASH_VALUE,child_number,sql_text from v$sql where sql_text like '%from tt';

HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------ ----------------------------------------
3762890390            0 select * from tt
3762890390            1 select * from tt
可以看到，SQL文字是完全相同的，所以兩個子游標共享了一個父遊標。但是由於optimizer_mode的不同，所以生成了2個子遊標。
如果產生了子游標，那麼說明肯定產生了某種mismatch，那麼如何來檢視是何種原因產生了mismatch呢？這就要透過v$sql_shared_cursor了。
sys/SYS>select kglhdpar, address,auth_check_mismatch, translation_mismatch,OPTIMIZER_MISMATCH
  2  from v$sql_shared_cursor
  3  where kglhdpar in
  4  ( select address
  5    from v$sql
  6    where sql_text like '%from tt');

KGLHDPAR ADDRESS  A T O
-------- -------- - - -
89BB8948 89BB83CC N N N
89BB8948 89BB5E78 N N Y
可以看到OPTIMIZER_MISMATCH列第二行的值為Y，這說明了正是由於optimizer_mode的不同而產生了子游標。
最後，父遊標和子游標的意義何在？其實一切都是為了共享。以減少再次解析的資源浪費。