v$sql v$sqlarea和父遊標 子游標記載01

這個雙休去了常州，blog好久沒更新了，又偷懶了。今早群裡在聊繫結變數，其實對於繫結變數自己也是糊里糊塗的，對於實質的還遠遠理解不夠，pub的老謝給了我們一篇帖子，細細讀下去還是受益匪淺啊。

http://www.itpub.net/thread-965919-1-1.html

在oltp事務處理系統中，繫結變數和索引可以說是這個系統中比較重要的兩部分，對於索引自己慢慢也有了一定的理解，在索引那些事中自己還是整理了一些心得的。從老謝的帖子中讓自己以前的有些錯誤理解糾正了，記得看楊老大的blog中關於分頁查詢的分析也讓自己恍然大悟，（看過老楊大的物化檢視，大師如此認真，後輩們真是太不勤奮了。）對於oracle的學習需要的更多是認真吧！

這裡摘要一下老謝的帖子一些資訊：

SQL> select * from tt where id=1;

ID NAME
---------- ----------------------------------------
1 test

SQL> select * from tt where id=2;

ID NAME
---------- ----------------------------------------
2 test

SQL> variable i number;
SQL> exec :i :=1;

PL/SQL 過程已成功完成。

SQL> select *from tt where id=:i;

ID NAME
---------- ----------------------------------------
1 test

SQL> exec :i :=2;

PL/SQL 過程已成功完成。

SQL> select *from tt where id=:i;

ID NAME
---------- ----------------------------------------
2 test

SQL> print i;

I
----------
2

SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from t
t where id=%';

SQL_TEXT PARSE_CALLS
------------------------------------------------------------ -----------
select * from tt where id=2 1
select * from tt where id=1 1
select * from tt where id=:i 2
SQL>從上面試驗發現繫結變數i的使用使查詢id=1和id=2的sqlselect *from tt where id=:i得以重複使用，從而避免了hard parse，這裡的PARSE_CALLS＝2包括了一次soft parse.

從上面可以推翻自己以前記載的靜態sql都會使用繫結變數的錯誤理解。繫結變數的使用跟靜態和動態sql沒有絕對關係！

這裡順便提一下關於v$sql檢視的一些column的含義：

Hash_value: sql語句的hash值，這個hash值用來判斷sql語句是否可以共享使用。

Address:sql語句在sga的地址。

Executions： 執行次數

Optimizer_mode：sql執行的最佳化器模式

Buffer_gets：子指標的buffer gets數量

Disk_reads：子指標disk read的次數

Optimizer_cost：sql執行的cost成本

Hash_value:在library cache父指標的hash value值

End_of_fetch_count:完整執行sql的次數

Loads:物件被裝載或過載的次數

Row_processes:sql語句返回的行數

Cpu_time:分析 執行和提取所消耗的時間（微秒）

Elapsed_time:客觀上分析 執行 提取所消耗的時間

Sharable_memory:cursor使用的共享記憶體總數

Persistent_memory:cursor使用的常駐記憶體總數

Runtime_memory：cursor使用的記憶體總數

V$sqlarea檢視：

V$sqlarea檢視和v$sql檢視大致結構相同，其中的v$sqlarea有列VERSION_COUNT欄位，其中代表的就是對於此父遊標的子游標的數量，也就是在v$sql裡面的子游標的sql記錄的數量。在v$sql中有一列CHILD_NUMBER欄位，表示該字遊標的編號. v$sqlarea列出了共享SQL區（Shared SQL Area）中的SQL統計資訊，這些SQL按照SQL文字的不同，每條會記錄一行統計資料。

父遊標和子游標有點糊塗了，自己也不能說到很好的表達來描述，還是直接看例子吧！

SQL> alter system flush shared_pool;

System altered

SQL> conn ashuang/ashuang;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as ashuang

SQL> select count(1) from test01;

COUNT(1)

----------

23

SQL> conn test01/test01;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as test

SQL> select count(1) from test01;

COUNT(1)

----------

23

SQL> col sql_text for a40;

SQL> select sql_text,executions,loads,version_count from v$sqlarea where sql_text like '%select count(1) from test01%';

SQL_TEXT EXECUTIONS LOADS VERSION_COUNT

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

select count(1) from test01 1 1 2

select sql_text,executions,loads,versio 1 1 1

n_count from v$sqlarea where sql_text li

ke '%select count(1) from test01%'

SQL> select sql_text,executions,loads,child_number,parsing_schema_name from v$sql where sql_text like '%select count(1) from test01%';

SQL_TEXT EXECUTIONS LOADS CHILD_NUMBER PARSING_SCHEMA_NAME

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

select count(1) from test01 1 1 0 ASHUANG

select sql_text,executions,loads,versio 1 1 0 TEST

n_count from v$sqlarea where sql_text li

ke '%select count(1) from test01%'

select sql_text,executions,loads,child_ 1 1 0 TEST

number,parsing_schema_name from v$sql wh

ere sql_text like '%select count(1) from

test01%'

在查詢過程中雖然sql語句的文字相同，但是實際我們查詢的表卻是不同的使用者，sql的執行計劃等都不相同，實際就產生了兩個子游標儲存相應的sql的執行計劃和解析樹等。

當一條sql語句出現時，oracle會在庫快取中分配記憶體，將父遊標儲存進去，同時v$sqlarea中會記在父遊標一條記錄，然後如果同一條sql語句出現時，此時如果使用者不同或者optimizer_mode不同 繫結量表分級的情況可能導致執行計劃不同，從而生成多個子遊標。

上面的實驗中是用多個使用者的來驗證子游標的存在，其實在不同的optimizer_mode下同一個使用者的同一個sql也會產生多個子遊標。

這裡就不貼出繁瑣的程式碼了，說下測試的思路：

Select count(1) from test01;

Alter session set optimizer_mode=fistr_rows；

然後再次查詢Select count(1) from test01;可以明顯的看出會生產兩個子游標。

那麼父遊標與子游標的最終意義了，由於一個sql語句中需要建立遊標，然後分析語句執行查詢，需要一個遊標來指定sql的相關記憶體地址從而來判斷是否能共享sql語句，也就是減小不必要的硬解析啊！

哎準備整理繫結變數的，不知不覺中說到了v$sql和v$sqlarea檢視和父子游標去了，後續繼續更新！

[@more@]