v$sql檢視和v$sqlarea檢視的構建

edwardking888發表於2010-04-13
SQL

通過v$fixed_view_definition檢視，可以查詢得到v$sql檢視和v$sqlarea檢視的構建語句：

select view_definition from v$fixed_view_definition where view_name='GV$SQL';
select view_definition from v$fixed_view_definition where view_name='GV$SQLAREA';

GV$SQL的定義結構如下（Oracle 10gR2環境）：

select inst_id,kglnaobj,kglfnobj,kglobt03,
  kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16,
  kglobt08+kglob t11,kglobt10,kglobt01,decode(kglobhs6,0,0,1),
  decode(kglhdlmd,0,0,1),kglhdlkc,kglobt04,kglobt05,kglobt48,kglobt35,
  kglobpc6,kglhdldc,
  substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19),kglhdivc,kglobt12,
  kglobt13,kglobwdw,kglobt14,kglobwap,kglobwcc,kglobwcl,kglobwui,kglobt42,
  kglobt43,kglobt15,kglobt02,
  decode(kglobt32, 0, 'NONE', 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE',
          4, 'CHOOSE', 'UNKNOWN'),
  kglobtn0,kglobcce,kglobcceh,kglobt17,kglobt18,kglobts4,kglhdkmk,kglhdpar,
  kglobtp0,kglnahsh,kglobt46,kglobt30,kglobt09,kglobts5,kglobt48,kglobts0,
  kglobt19,kglobts1,kglobt20,kglobt21,kglobts2,kglobt06,kglobt07,
  decode(kglobt28, 0, to_number(NULL), kglobt28),kglhdadr,kglobt29,
  decode(bita nd(kglobt00,64),64, 'Y', 'N'),
  decode(kglobsta, 1, 'VALID', 2, 'VALID_AUTH_ERROR', 3, 'VALID_COMPILE_ERROR',
          4, 'VALID_UNAUTH', 5, 'INVALID_UNAUTH', 6, 'INVALID'),
  kglobt31,
  substr(to_char(kglobtt0,'YYYY-MM-DD/HH24:MI:SS'),1,19),
  decode(kglobt33, 1, 'Y', 'N'),kglhdclt,kglobts3,kglobt44,kglobt45,kglobt47,
  kglobt49,kglobcla,kglobcbca
from x$kglcursor_child

而GV$SQLAREA的檢視結構如下（Oracle 10gR2環境）：

select inst_id,kglnaobj,kglfnobj,kglobt03,
  kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6,
  kglobt08+kglobt11,k globt10,kglobt01,kglobccc,kglobclc,kglhdlmd,kglhdlkc,
  kglobt04,kglobt05,kglobt48,kglobt35,kglobpc6,kglhdldc,
  substr(to_c har(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19),
  kglhdivc,kglobt12,kglobt13,kglobwdw,kglobt14,kglobwap,kglobwcc,kglobwcl,
  kglobwui,kglobt42,kglobt43,kglobt15,kglobt02,
  decode(kglobt32, 0, 'NONE', 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE',
          4, 'CHOOSE', 'UNKNOWN'),
  kglobtn0,kglobcce,kglobcceh,kglobt17,kglobt18,kglobts4,kglhdkmk,kglhdpar,
  kglnahsh,kglobt46,kglobt30,kglobts0,kglobt19,kglobts1,kglobt20,kglobt21,
  kglobts2,kglobt06,kglobt07,
  decode(kglobt28, 0, NULL, kglobt28),kglhdadr,
  decode(bitand(kglobt00,64),64, 'Y', 'N'),
  decode(kglobsta, 1, 'VALID', 2, 'VALID_AUTH_ERROR', 3, 'VALID_COMPILE_ERROR',
          4, 'VALID_UNAUTH', 5, 'INVALID_UNAUTH', 6 , 'INVALID'),
  kglobt31,kglobtt0,decode(kglobt33, 1, 'Y', 'N'),kglhdclt,kglobts3,kglobt44,
  kglobt45,kglobt47,kglobt49,kgl obcla,kglobcbca
from x$kglcursor_child_sqlid
where kglobt02 != 0

在Oracle 10g中，這兩個檢視來自兩個獨立的底層X$表，v$sqlarea檢視也不再包含Group by子句，這使得以前版本中查詢v$sqlarea的效能得以緩解。那麼version_count和v$sql的差異應該就來自底層x$表的變更，在v$sqlarea中增加了一個“!=0”的條件過濾（kglobt02對應檢視中的COMMAND_TYPE定義）。

能夠發現，所有的子指標在x$kglob（[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject）表中全部存在，也就是說，觀察到的變化是由於底層表的變更導致不同過濾演算法導致的。在我的一個測試環境中，這樣的情況也可以看到：

sys@NEI> select a.sql_id,a.version_count,a.hash_value,count(*)
  2    from v$sqlarea a ,v$sql b
  3    where a.version_count >10 and a.hash_value=b.hash_value
  4    group by a.sql_id,a.version_count,a.hash_value
  5    order by 4;
SQL_ID                     VERSION_COUNT HASH_VALUE   COUNT(*)
-------------------------- ------------- ---------- ----------
4gb7r5dm6hnzs                         22 1718113272          1

以其中一個SQL為例進行進一步分析：

sys@NEI> select sql_id,hash_value,buffer_gets,executions,parsing_user_id
  2     from v$sql
  3     where hash_value = 1718113272
  4     order by buffer_gets desc;

SQL_ID                     HASH_VALUE BUFFER_GETS EXECUTIONS PARSING_USER_ID
-------------------------- ---------- ----------- ---------- ---------------
4gb7r5dm6hnzs              1718113272          22        956               0

注意到，所有的SQL都是執行過的。而從x$kglob中查詢得到的SQL或者Oracle 9i的v$sql檢視中查詢得到的SQL包含未執行或者buffer_gets為0的SQL指標，這部分在Oracle 10g中被從v$sqlarea中過濾了出去：

sys@NEI> select kglobt03,kglnahsh,kglobt14,kglhdexc,kglobt17
  2    from x$kglob
  3    where kglnahsh = 1718113272
  4    order by kglobt14 desc,4;
KGLOBT03                     KGLNAHSH   KGLOBT14   KGLHDEXC   KGLOBT17
-------------------------- ---------- ---------- ---------- ----------
4gb7r5dm6hnzs              1718113272         22        956          0
4gb7r5dm6hnzs              1718113272          0          0 2147483644
4gb7r5dm6hnzs              1718113272          0         10          0
4gb7r5dm6hnzs              1718113272          0         10          0
4gb7r5dm6hnzs              1718113272          0         10          0
4gb7r5dm6hnzs              1718113272          0         10          0
4gb7r5dm6hnzs              1718113272          0         10          0
4gb7r5dm6hnzs              1718113272          0         10          0
4gb7r5dm6hnzs              1718113272          0         10          0
4gb7r5dm6hnzs              1718113272          0         10          0
4gb7r5dm6hnzs              1718113272          0         10          0
4gb7r5dm6hnzs              1718113272          0         10          0
4gb7r5dm6hnzs              1718113272          0         12          0
4gb7r5dm6hnzs              1718113272          0         16          0
4gb7r5dm6hnzs              1718113272          0         16          0
4gb7r5dm6hnzs              1718113272          0         16          0
4gb7r5dm6hnzs              1718113272          0         16          0
4gb7r5dm6hnzs              1718113272          0         16          0
4gb7r5dm6hnzs              1718113272          0         16          0
4gb7r5dm6hnzs              1718113272          0         16          0
4gb7r5dm6hnzs              1718113272          0         16          0
4gb7r5dm6hnzs              1718113272          0         16          0
4gb7r5dm6hnzs              1718113272          0         16          0
23 rows selected.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8183550/viewspace-659588/，如需轉載，請註明出處，否則將追究法律責任。

相關文章