作者:dbtan |【转载时请以超链接形式标明文章出处作者信息】


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 -