v$sql视图和v$sqlarea视图的构建

通过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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8183550/viewspace-659588/