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博客 ” ，链接：https://blog.itpub.net/28974745/viewspace-2137954/ ，如需转载，请注明出处，否则将追究法律责任。
