ITPub博客

首页 > 数据库 > Oracle > v$sql和v$sqlarea的区别

v$sql和v$sqlarea的区别

原创 Oracle 作者:llnnmc 时间:2017-04-25 15:16:02 0 删除 编辑

v$sql

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


v$sqlarea

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


Oracle 10g以前,v$sqlv$sqlarea的定义都是来自于同一张底层表x$kglcursor,而该表的定义又来自于另一张底层表x$kglobv$sqlarea是在x$kglcursor表上对SQL字面值进行group by后的聚合。而从Oracle 10g开始,这两个视图来自于两个不同的底层表,v$sql(gv$sql)来自于x$kglcursor_child,而v$sqlareagv$sqlarea)则来自于x$kglcursor_child_sqlid,v$sqlarea也不再包含group by子句,这使得以前版本中查询v$sqlarea的性能问题得以缓解。并且,对于未执行的或者buffer_gets0SQL指针,从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_count1,即只有一个版本的相同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,子指针编号分别为01


查询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_count2,即存在两个子指针。而执行次数、磁盘物理读次数、内存读次数便是它们的合计值,这就是v$sqlarea的聚合作用。

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

下一篇: Oracle错误号检索
请登录后发表评论 登录
全部评论

注册时间:2016-12-29

  • 博文量
    90
  • 访问量
    57814