v$sql和v$sqlarea的区别

v$sql：

共享池库缓存中在子指针级别上的SQL统计信息，包含了所有用户执行过的所有SQL信息。不同用户、不同会话执行相同的SQL的语义、执行计划可能会不同，这些SQL的字面值相同，即具有相同的sql_id，通过不同的子指针编号child_number来区分。

v$sqlarea：

共享池库缓存中在父指针级别上的SQL统计信息。对于相同字面值的SQL语句仅以一行显示，忽略了相同SQL语句在执行会话、语义、执行计划上的不同，而版本计数version_count则反映了子指针的数量。

在Oracle 10g以前，v$sql和v$sqlarea的定义都是来自于同一张底层表x$kglcursor，而该表的定义又来自于另一张底层表x$kglob，v$sqlarea是在x$kglcursor表上对SQL字面值进行group by后的聚合。而从Oracle 10g开始，这两个视图来自于两个不同的底层表，v$sql(gv$sql）来自于x$kglcursor_child，而v$sqlarea（gv$sqlarea）则来自于x$kglcursor_child_sqlid，v$sqlarea也不再包含group by子句，这使得以前版本中查询v$sqlarea的性能问题得以缓解。并且，对于未执行的或者buffer_gets为0的SQL指针，从Oracle 10g开始在v$sql中被过滤掉不再列出。

以下用一个简单实验阐述其区别。

在用户cmes中创建一张和scott用户同样的表emp

conn cmes/cmes

create table emp as select * from scott.emp;

重启数据库，清理一下共享池中的信息

conn / as sysdba

startup force

cmes用户执行一个查询，通过autotrace观察一下执行计划和统计信息

conn cmes/cmes

set autot on

select * from emp;

执行计划

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    14 |  1218 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

   - 'PLAN_TABLE' is old version

   - dynamic sampling used for this statement (level=2)

统计信息

----------------------------------------------------------

         60  recursive calls

          0  db block gets

         49  consistent gets

          3  physical reads

          0  redo size

       1631  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          4  sorts (memory)

          0  sorts (disk)

         14  rows processed

这个查询统计显示，执行了3个磁盘物理读，49个内存一致性读。

查一下v$sql中记录的信息

col sql_text for a30

select sql_text, executions, optimizer_mode, optimizer_cost, disk_reads, buffer_gets, child_number, sql_id, address, hash_value from v$sql where sql_text='select * from emp';

SQL_TEXT                       EXECUTIONS OPTIMIZER_ OPTIMIZER_COST DISK_READS BUFFER_GETS CHILD_NUMBER SQL_ID        ADDRESS          HASH_VALUE

------------------------------ ---------- ---------- -------------- ---------- ----------- ------------ ------------- ---------------- ----------

select * from emp                       1 ALL_ROWS                3          3          49            0 a2dk8bdn0ujx7 000007FF05FE38B0 1745700775

记录的信息和autotrace显示的信息完全一致，child_number是子指针的编号。

再执行一次这个查询

select * from emp;

执行计划

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    14 |  1218 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

   - 'PLAN_TABLE' is old version

   - dynamic sampling used for this statement (level=2)

统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       1631  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

再查一下v$sql中记录的信息

col sql_text for a30

select sql_text, executions, optimizer_mode, optimizer_cost, disk_reads, buffer_gets, child_number, sql_id, address, hash_value from v$sql where sql_text='select * from emp';

SQL_TEXT                       EXECUTIONS OPTIMIZER_ OPTIMIZER_COST DISK_READS BUFFER_GETS CHILD_NUMBER SQL_ID        ADDRESS          HASH_VALUE

------------------------------ ---------- ---------- -------------- ---------- ----------- ------------ ------------- ---------------- ----------

select * from emp                       2 ALL_ROWS                3          3          53            0 a2dk8bdn0ujx7 000007FF05FE38B0 1745700775

执行次数变为2次，磁盘物理读未增加，因为数据已经在buffer中。内存读继续增加，和autotrace统计的情况一致。

看一下此时v$sqlarea中记录的信息

col sql_text for a30

select sql_text, executions, optimizer_mode, optimizer_cost, disk_reads, buffer_gets, version_count, sql_id, address, hash_value from v$sqlarea where sql_text='select * from emp';

SQL_TEXT                       EXECUTIONS OPTIMIZER_ OPTIMIZER_COST DISK_READS BUFFER_GETS VERSION_COUNT SQL_ID        ADDRESS          HASH_VALUE

------------------------------ ---------- ---------- -------------- ---------- ----------- ------------- ------------- ---------------- ----------

select * from emp                       2 ALL_ROWS                3          3          53             1 a2dk8bdn0ujx7 000007FF05FE38B0 1745700775

内容与v$sql一致，子指针计数version_count是1，即只有一个版本的相同SQL。

现在用scott用户执行同样的查询

conn scott/tiger

select * from emp;

查询v$sql中的统计信息

col sql_text for a30

select sql_text, executions, optimizer_mode, optimizer_cost, disk_reads, buffer_gets, child_number, sql_id, address, hash_value from v$sql where sql_text='select * from emp';

SQL_TEXT                       EXECUTIONS OPTIMIZER_ OPTIMIZER_COST DISK_READS BUFFER_GETS CHILD_NUMBER SQL_ID        ADDRESS          HASH_VALUE

------------------------------ ---------- ---------- -------------- ---------- ----------- ------------ ------------- ---------------- ----------

select * from emp                       2 ALL_ROWS                3          3          53            0 a2dk8bdn0ujx7 000007FF05FE38B0 1745700775

select * from emp                       1 ALL_ROWS                4         13          87            1 a2dk8bdn0ujx7 000007FF05FE38B0 1745700775

现在列出了两条相同字面值的SQL，子指针编号分别为0和1。

查询v$sqlarea中的统计信息

col sql_text for a30

select sql_text, executions, optimizer_mode, optimizer_cost, disk_reads, buffer_gets, version_count, sql_id, address, hash_value from v$sqlarea where sql_text='select * from emp';

SQL_TEXT                       EXECUTIONS OPTIMIZER_ OPTIMIZER_COST DISK_READS BUFFER_GETS VERSION_COUNT SQL_ID        ADDRESS          HASH_VALUE

------------------------------ ---------- ---------- -------------- ---------- ----------- ------------- ------------- ---------------- ----------

select * from emp                       3 ALL_ROWS                4         16         140             2 a2dk8bdn0ujx7 000007FF05FE38B0 1745700775

可以看到，相同字面值的SQL被合计为一条，版本计数version_count为2，即存在两个子指针。而执行次数、磁盘物理读次数、内存读次数便是它们的合计值，这就是v$sqlarea的聚合作用。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28974745/viewspace-2137954/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28974745/viewspace-2137954/