v$sql和v$sqlarea视图

v$sql和v$sqlarea视图:

上文提到v$sqlareav$sql两个视图的不同之处在于,v$sql中为每一条SQL保留一个条目,而v$sqlarea中根据sql_text进行group by,通过version_count计算子指针的个数。下面对这个问题进行一点延伸探讨。

首先介绍一下v$sql视图,v$sql视图列举了共享SQL区(Shared SQL Area)中的SQL统计信息,这个视图中的信息未经分组,每个SQL指针都包含一条独立的记录。这个视图的主要字段如下:

ColumnDatatypeDescrption
SQL_TEXTVARCHAR2(1000)当前SQL指针的前1000个字符(也就是说这里记录的SQL是不完整的)
EXECUTIONSNUMBER执行次数
DISK_READSNUMBER这个子指针Disk Read的次数
BUFFER_GETSNUMBER这个子指针的Buffer Gets数量
OPTIMIZER_MODEVARCHAR2(10)SQL执行的优化器模式
OPTIMIZER_COSTNUMBERSQL执行成本
HASH_VALUENUMBER在Library Cache中父指针的Hash Value值

 

用前文应用的例子进行进一步说明,假定数据库中存在一个用户TQ,用户下存在一张EMP表(以下测试来自Oracle 10gR2数据库环境):

tq@NEI> create table emp as select * from scott.emp;
Table created.
tq@NEI> set autotrace on
tq@NEI> select count(*) from emp;
  COUNT(*)
----------
        14
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          7  consistent gets
          5  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

这个查询的统计信息显示,执行了5个物理读7个Consistent Gets,来看一下v$sql中记录的统计数据:

sys@NEI> select sql_text,executions,disk_reads,optimizer_mode,buffer_gets,hash_value
  2  from v$sql where sql_text='select count(*) from emp';
SQL_TEXT                       EXECUTIONS DISK_READS OPTIMIZER_ BUFFER_GETS HASH_VALUE
------------------------------ ---------- ---------- ---------- ----------- ----------
select count(*) from emp                1          5 ALL_ROWS             7 2295140356

记录的信息和AUTOTRACE显示的信息完全一致。在第一次执行时,这个SQL的HASH_VALUE被计算出来为2295140356,并且随之,这个SQL的父指针(Parent Cursor)在内存中被创建,一个子指针同时创建父指针可以被认为是Hash Value的相关信息,子指针可以被认为是SQL的元数据

再次执行这个查询,统计信息中的物理读(DISK_READS)不再增加,因为数据已经在Buffer中存在,而BUFFER_GETS继续增加。执行次数也变为2次

tq@NEI> select count(*) from emp;
  COUNT(*)
----------
        14
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
sys@NEI> select sql_text,executions,disk_reads,optimizer_mode,buffer_gets,hash_value
  2  from v$sql where sql_text='select count(*) from emp';                     
SQL_TEXT                       EXECUTIONS DISK_READS OPTIMIZER_ BUFFER_GETS HASH_VALUE
------------------------------ ---------- ---------- ---------- ----------- ----------
select count(*) from emp                2          5 ALL_ROWS            10 2295140356

 

v$sqlarea视图也是非常重要的一个视图,在Oracle 9iR2的文档中,Oracle这样定义这个视图:v$sqlarea列出了共享SQL区(Shared SQL Area)中的SQL统计信息,这些SQL按照SQL文本的不同,每条会记录一行统计数据注意这里所说的是“按照SQL文本”来进行区分,也就是说这个视图的信息可以看作是根据SQL_TEXT进行的一次汇总统计

v$sqlarea视图的主要字段如下:

ColumnDatatypeDescription
SQL_TEXTVARCHAR2(1000)当前指针的前1000个字符
VERSION_COUNTNUMBERCache中这个父指针下存在的子指针的数量
EXECUTIONSNUMBER总的执行次数,包含所有子指针执行次数的汇总
DISK_READSNUMBER所有子指针的Disk Reads总和
BUFFER_GETSNUMBER所有子指针的Buffer Gets总和
OPTIMIZER_MODEVARCHAR2(10)SQL执行的优化器模
HASH_VALUENUMBER父指针的Hash Value

 

通过前文可以知道,文本相同的SQL语句,在数据库中的意义可能完全不同。比如数据库中存在两个用户TQDBTAN,两个用户各拥有一张数据表EMP。

那么当两个用户发出一个查询select count(*) from emp时,这个查询访问的对象,返回的结果可能完全不同,TQ的查询访问的是TQ.EMP表,而DBTAN用户访问的则是DBTAN.EMP表。但是单从SQL_TEXT上来说,这两个SQL没有任何区别。

继续前面的测试,再来简单看一下以下的输出:

dbtan@NEI> create table emp as select * from scott.emp where rownum <9;
Table created.
dbtan@NEI> set autotrace on
dbtan@NEI> select count(*) from emp;
  COUNT(*)
----------
         8
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |     8 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          7  consistent gets
          5  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

现在v$sql中应该有了两条完全一样的SQL,但是各自查询的物理对象却是截然不同:

sys@NEI> select sql_text,executions,disk_reads,optimizer_mode,buffer_gets,hash_value
  2  from v$sql where sql_text='select count(*) from emp';
SQL_TEXT                       EXECUTIONS DISK_READS OPTIMIZER_ BUFFER_GETS HASH_VALUE
------------------------------ ---------- ---------- ---------- ----------- ----------
select count(*) from emp                3          5 ALL_ROWS            13 2295140356
select count(*) from emp                1          5 ALL_ROWS             7 2295140356

现在再来查询v$sqlarea视图,就可以看到这两个视图的不同:

sys@NEI> select sql_text,executions,disk_reads,buffer_gets,hash_value,version_count
  2  from v$sqlarea where sql_text='select count(*) from emp';
SQL_TEXT                       EXECUTIONS DISK_READS BUFFER_GETS HASH_VALUE VERSION_COUNT
------------------------------ ---------- ---------- ----------- ---------- -------------
select count(*) from emp                4         10          20 2295140356             2

在这个视图中,Oracle将v$sql中的sql_text相同的2个子指针合并起来,执行次数等信息也都进行了累计,version_count也显示为2,这就是v$sqlarea的聚合作用。

- The End -