視圖 v$sql,v$sqlarea,$sqltext,v$sqltext_with_newlines 的差異

      視圖v$sql,v$sqlarea,v$sqltext,v$sqltext_with_newlines 是幾個經常容易混淆的視圖，主要是提供library cache中當前緩衝的sql語句的資訊。這幾個視圖都可以提供當前有關sql語句的具體資訊，但稍有差異。本文主要描述其差異並給出執行個體。

 

一、sql語句與遊標
      sql語句，這個沒什麼好說的，就是按照sql標準書寫的sql語句
      遊標，包含shared cursor，session cursor，簡單點來理解，一條sql語句對應一個或多個遊標，且一條sql語句至少解析為一個遊標。

      當任一sql語句被解析到shared_pool中之後，必定會產生相應的遊標，有下列三種情形，
          a、存在可完全共用的父遊標
          b、父遊標存在，但是由於執行環境的變化，不得不生存新的子遊標
          c、沒有父遊標存在，需要產生全新的遊標
      對於情形a，由於存在可共用的父遊標，也就是說v$sql中必定已經存在一個對應的sql遊標，我們可以查詢到，執行之後對應executions及相關列會發生變化。
      對於情形b或c，sql語句產生的遊標會被添加到v$sql視圖，也即是新增遊標(b為新增子遊標，c為新增父遊標)。

      注:在shared_pool由於aged out原則後的sql可能無法在該視圖查詢到，這個是另外一個話題。

 

二、視圖差異
1、v$sql視圖
      假定使用者A與使用者B都基於自身schema建立了表t
      使用者A發布查詢select * from t，此時共用池中產生一條與該語句的相關的sql遊標，在v$sql視圖體現(假定為首次執行)
      不久使用者B也發出select * from t的查詢，同上，v$sql中也對應有一條該語句的遊標
      為了便於理解，我們將v$sql視圖中的sql文本稱之為遊標，將v$sqlarea中的sql文本稱為sql語句

2、v$sqlarea
      對於上述情形
      此時v$sqlarea則是對視圖v$sql的一個彙總，也即是相當於對視圖v$sql使用了distinct關鍵字。
      儘管v$sql中出現了兩條select * from t，而v$sqlarea僅為一條
      v$sqlarea提供的是每條sql語句執行的匯總資訊

3、v$sqltext
      該視圖包括Shared pool中SQL語句的完整文本，但一條SQL語句是被分成多個塊來進行儲存的。
      對於比較短的sql語句，一個piece就搞定，對於比較長的sql語句則需要多個piece的結合來完整展現一條真正的sql語句。

4、v$sqltext_with_newlines
      該視圖用於完整儲存SQL語句所有字元，保留SQL語句的斷行符號和定位字元。

 

三、樣本說明

1、建立示範環境SQL> select * from v$version where rownum<2;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionSQL> create table t ( x varchar2(30) primary key, y int );SQL> exec dbms_stats.set_table_stats('SCOTT','T', numrows => 1000000, numblks=>100000);SQL> alter system flush shared_pool;SQL> select sql_text from v$sql where upper(sql_text) like 'SELECT % T LOOK_FOR_ME%'  2  and upper(sql_text) not like '%FROM V$SQL%';no rows selected2、產生sql遊標SQL> declare  2     l_x_number      number;  3     l_x_string  varchar2(30);  4  begin  5     execute immediate 'alter session set optimizer_mode=all_rows';  6     for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;  7     for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;  8     execute immediate 'alter session set optimizer_mode=first_rows';  9     for x in (select * from t look_for_me where x = l_x_number) loop null; end loop; 10     for x in (select * from t look_for_me where x = l_x_string) loop null; end loop; 11  end; 12  /PL/SQL procedure successfully completed.3、分析 SQL> col sql_text format a55SQL> set linesize 180SQL> col plan_table_output format a80 truncateSQL> col sql_id new_val sql_idSQL> select sql_id, sql_text from v$sql where upper(sql_text) like 'SELECT % T LOOK_FOR_ME %B1_';SQL_ID        SQL_TEXT------------- -------------------------------------------------------1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B11qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B11qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B11qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1--從上面的查詢可知，對於上面的SQL語句我們得到了相同的SQL_ID。這是因為SQL_ID是由SQL文本hash得到的一個值--只要SQL文本相同(完全相同)，則SQL_ID一定是相同的。--我們從v$sql視圖裡邊查詢得到了四條相同sql_id的sql語句，也即是四個不同的遊標--為什麼同樣的sql文本產生了四個不同的遊標呢?這是因為:-- cursor 1) 使用ALL_ROWS 最佳化器模式, 綁定變數為number類型-- cursor 2) 使用ALL_ROWS 最佳化器模式, 綁定變數為varchar2類型-- cursor 3) 使用FIRST_ROWS 最佳化器模式, 綁定變數為number類型  -- cursor 4) 使用FIRST_ROWS 最佳化器模式,綁定變數為varchar2類型--查詢v$sql視圖SQL> select sql_id,loaded_versions,executions,optimizer_mode, plan_hash_value,child_number,child_address  2  from v$sql where sql_id = '&sql_id';old   2: from v$sql where sql_id = '&sql_id'new   2: from v$sql where sql_id = '1qqtru155tyz8'SQL_ID        LOADED_VERSIONS EXECUTIONS OPTIMIZER_ PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS------------- --------------- ---------- ---------- --------------- ------------ ----------------1qqtru155tyz8               1          1 ALL_ROWS        1601196873            0 00000000811110081qqtru155tyz8               1          1 ALL_ROWS        2572036781            1 00000000841B1DD81qqtru155tyz8               1          1 FIRST_ROWS      1601196873            2 00000000813D1A701qqtru155tyz8               1          1 FIRST_ROWS      2572036781            3 000000007FFE3370--從上面的查詢結果知，optimizer_mode不同，plan_hash_value的值不同，child_address的值也不同--尤其是child_address表明是pin到shared_pool中不同的位置--查看v$sqlarea視圖SQL> select sql_id,sql_text,version_count vs_cnt,loaded_versions ld_vs,executions ex_cnt  2  from v$sqlarea where sql_id = '&sql_id';old   2: from v$sqlarea where sql_id = '&sql_id'new   2: from v$sqlarea where sql_id = '1qqtru155tyz8'SQL_ID        SQL_TEXT                                        VS_CNT      LD_VS     EX_CNT------------- ------------------------------------------- ---------- ---------- ----------1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1            4          4          4--從上面的視圖可知，是sql_id的一個彙總，列出了version_count以及executions的總次數等--下面來看看每個sql對應的執行計畫--child_number為0的遊標，此時為父遊標SQL> select * from table(dbms_xplan.display_cursor('&sql_id',0));old   1: select * from table(dbms_xplan.display_cursor('&sql_id',0))new   1: select * from table(dbms_xplan.display_cursor('1qqtru155tyz8',0))PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID  1qqtru155tyz8, child number 0-------------------------------------SELECT * FROM T LOOK_FOR_ME WHERE X = :B1Plan hash value: 1601196873--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |       |       | 28616 (100)|          ||*  1 |  TABLE ACCESS FULL| T    | 10000 |   292K| 28616   (6)| 00:05:44 |--------------------------------------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(TO_NUMBER("X")=:B1)  -->存在謂詞轉換--下面是child_number為1的子遊標SQL> select * from table(dbms_xplan.display_cursor('&sql_id',1));old   1: select * from table(dbms_xplan.display_cursor('&sql_id',1))new   1: select * from table(dbms_xplan.display_cursor('1qqtru155tyz8',1))PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID  1qqtru155tyz8, child number 1-------------------------------------SELECT * FROM T LOOK_FOR_ME WHERE X = :B1Plan hash value: 2572036781--------------------------------------------------------------------------------| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)--------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|   1 |  TABLE ACCESS BY INDEX ROWID| T            |     1 |    30 |     2   (0)|*  2 |   INDEX UNIQUE SCAN         | SYS_C0011143 |     1 |       |     1   (0)--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("X"=:B1)   --從上面的兩個執行計畫中可以看出，因為綁定變數的類型不同，導致了sql語句產生了不同的執行計畫--且第一個執行計畫中使用了隱式轉換--下面是child_number為2的子遊標的執行計畫--Author : Robinson--Blog   : http://blog.csdn.net/robinson_0612SQL> select * from table(dbms_xplan.display_cursor('&sql_id',2));old   1: select * from table(dbms_xplan.display_cursor('&sql_id',2))new   1: select * from table(dbms_xplan.display_cursor('1qqtru155tyz8',2))PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID  1qqtru155tyz8, child number 2-------------------------------------SELECT * FROM T LOOK_FOR_ME WHERE X = :B1Plan hash value: 1601196873--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |       |       | 28616 (100)|          ||*  1 |  TABLE ACCESS FULL| T    | 10000 |   292K| 28616   (6)| 00:05:44 |--------------------------------------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(TO_NUMBER("X")=:B1)--下面是child_number為3的子遊標的執行計畫SQL> select * from table(dbms_xplan.display_cursor('&sql_id',3));old   1: select * from table(dbms_xplan.display_cursor('&sql_id',3))new   1: select * from table(dbms_xplan.display_cursor('1qqtru155tyz8',3))PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID  1qqtru155tyz8, child number 3-------------------------------------SELECT * FROM T LOOK_FOR_ME WHERE X = :B1Plan hash value: 2572036781--------------------------------------------------------------------------------| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)--------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|   1 |  TABLE ACCESS BY INDEX ROWID| T            |     1 |    30 |     2   (0)|*  2 |   INDEX UNIQUE SCAN         | SYS_C0011143 |     1 |       |     1   (0)--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("X"=:B1)--子遊標2與子遊標3也是使用了不同的執行計畫，這個原因與父遊標0，子遊標1的原因相同--子遊標2與父遊標0有相同的執行計畫，從Plan hash value的值可知--同樣，子遊標3與父遊標1也有相同的執行計畫，從Plan hash value的值可知--產生不同執行計畫的原因--v$sql_shared_cursor視圖記錄了那些不能共用子遊標的記錄並給給出原因，如下查詢SQL> SELECT child_number,bind_mismatch, optimizer_mode_mismatch   2  from v$sql_shared_cursor  3  where sql_id = '&SQL_ID';old   3: where sql_id = '&SQL_ID'new   3: where sql_id = '1qqtru155tyz8'CHILD_NUMBER B O------------ - -           0 N N           1 Y N           2 N Y           3 Y Y--從上面的查詢結果可知，遊標1與父遊標0是由於綁定變數不匹配而導致了不可共用子遊標--遊標2則是由於不同的執行環境，遊標3則是不同的執行環境與不匹配的綁定變數導致不可共用子遊標           --從上面的測試可以，父遊標相同，子遊標不同，執行計畫可能相同，也可能不相同 --下面的這個查詢也說明了這個問題，得到的是不同的PLAN_HASH_VALUE  SQL> select a.snap_id, a.sql_id, a.plan_hash_value,to_char(b.begin_interval_time,'yyyy-mm-dd hh24:mi:ss')  2  from dba_hist_sqlstat a, dba_hist_snapshot b   3  where a.snap_id = b.snap_id  4  and sql_id ='&sql_id';old   4: and sql_id ='&sql_id'new   4: and sql_id ='1qqtru155tyz8'   SNAP_ID SQL_ID        PLAN_HASH_VALUE TO_CHAR(B.BEGIN_INT---------- ------------- --------------- -------------------       275 1qqtru155tyz8      1601196873 2013-03-08 12:00:25       275 1qqtru155tyz8      2572036781 2013-03-08 12:00:25

四、小結
    a、本文討論了v$sql,v$sqlarea,v$sqltext以及v$sqltext_with_newlines幾個視圖的差異
    b、需要記住的是v$sql儲存所有遊標，v$sqlarea等同於使用了distinct關鍵字，僅保留sql語句。v$sqltext提供完整的sql語句
    c、硬解析通常是由於不可共用的父遊標造成的，如經常變動的SQL語句，或動態SQL或未使用綁定變數等
    d、與父遊標SQL文本完全一致的情形下，多個相同的SQL語句可以共用一個父遊標
    e、SQL文本、執行環境完全一致的情形下，子遊標能夠被共用，否則如果執行環境不一致則產生新的子遊標
    f、父遊標相同，子遊標不同，執行計畫可能相同，也可能不相同

 

五、相關參考
    父遊標、子遊標及共用遊標
    綁定變數及其優缺點
    Oracle 硬解析與軟解析
    Oracle 綁定變數窺探
    Oracle自適應共用遊標
    Whats the difference between the v$sql* views

 

六、更多參考:

有關Oracle RAC請參考
     使用crs_setperm修改RAC資源的所有者及許可權
     使用crs_profile管理RAC資源設定檔
     RAC 資料庫的啟動與關閉
     再說 Oracle RAC services
     Services in Oracle Database 10g
     Migrate datbase from single instance to Oracle RAC
     Oracle RAC 串連到指定執行個體
     Oracle RAC 負載平衡測試(結合伺服器端與用戶端)
     Oracle RAC 伺服器端串連負載平衡(Load Balance)
     Oracle RAC 用戶端串連負載平衡(Load Balance)
     ORACLE RAC 下非預設連接埠監聽配置(listener.ora tnsnames.ora)
     ORACLE RAC 監聽配置 (listener.ora tnsnames.ora)
     配置 RAC 負載平衡與容錯移轉
     CRS-1006 , CRS-0215 故障一例 
     基於Linux (RHEL 5.5) 安裝Oracle 10g RAC
     使用 runcluvfy 校正Oracle RAC安裝環境

有關Oracle 網路設定相關基礎以及概念性的問題請參考：
     配置非預設連接埠的動態服務註冊
     配置sqlnet.ora限制IP訪問Oracle
     Oracle 監聽器日誌配置與管理
     設定 Oracle 監聽器密碼(LISTENER)
     配置ORACLE 用戶端串連到資料庫

有關基於使用者管理的備份和備份恢複的概念請參考
     Oracle 冷備份
     Oracle 熱備份
     Oracle 備份恢複概念
     Oracle 執行個體恢複
     Oracle 基於使用者管理恢複的處理
     SYSTEM 資料表空間管理及備份恢複
     SYSAUX資料表空間管理及恢複
     Oracle 基於備份控制檔案的恢複(unsing backup controlfile)

有關RMAN的備份恢複與管理請參考
     RMAN 概述及其體繫結構
     RMAN 配置、監控與管理
     RMAN 備份詳解
     RMAN 還原與恢複
     RMAN catalog 的建立和使用
     基於catalog 建立RMAN儲存指令碼
     基於catalog 的RMAN 備份與恢複
     RMAN 備份路徑困惑
     使用RMAN實現異機備份恢複(WIN平台)
     使用RMAN遷移檔案系統資料庫到ASM
     linux 下RMAN備份shell指令碼
     使用RMAN遷移資料庫到異機

有關ORACLE體繫結構請參考
     Oracle 資料表空間與資料檔案
     Oracle 密碼檔案
     Oracle 參數檔案
     Oracle 聯機重做記錄檔(ONLINE LOG FILE)
     Oracle 控制檔案(CONTROLFILE)
     Oracle 歸檔日誌
     Oracle 復原(ROLLBACK)和撤銷(UNDO)
     Oracle 資料庫執行個體啟動關閉過程
     Oracle 10g SGA 的自動化管理
     Oracle 執行個體和Oracle資料庫(Oracle體繫結構)