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.
- The End -