v$sql,v$sqlarea,v$sqltext区别

v$sqltext
存储的是完整的SQL,SQL被分割

SQL> desc v$sqltext
Name                                      Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS                                            RAW(4) ---------
HASH_VALUE                                         NUMBER ---------   和 address 一起唯一标志一条sql
COMMAND_TYPE                                     NUMBER
PIECE                                           NUMBER ----------   分片之后的顺序编号
SQL_TEXT                                        VARCHAR2(64) --------------   注意长度



v$sqlarea ---------   存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息
SQL> desc v$sqlarea
Name                                      Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT                                        VARCHAR2(1000)
SHARABLE_MEM                                     NUMBER
PERSISTENT_MEM                                  NUMBER
RUNTIME_MEM                                     NUMBER
SORTS                                           NUMBER
VERSION_COUNT                                      NUMBER
LOADED_VERSIONS                                  NUMBER
OPEN_VERSIONS                                      NUMBER
USERS_OPENING                                      NUMBER
FETCHES                                            NUMBER
EXECUTIONS                                         NUMBER
USERS_EXECUTING                                  NUMBER
LOADS                                           NUMBER
FIRST_LOAD_TIME                                  VARCHAR2(38)
INVALIDATIONS                                      NUMBER
PARSE_CALLS                                     NUMBER
DISK_READS                                         NUMBER
BUFFER_GETS                                     NUMBER
ROWS_PROCESSED                                  NUMBER
COMMAND_TYPE                                     NUMBER
OPTIMIZER_MODE                                  VARCHAR2(25)
PARSING_USER_ID                                  NUMBER
PARSING_SCHEMA_ID                               NUMBER
KEPT_VERSIONS                                      NUMBER
ADDRESS                                            RAW(4)
HASH_VALUE                                         NUMBER
MODULE                                           VARCHAR2(64)
MODULE_HASH                                     NUMBER
ACTION                                           VARCHAR2(64)
ACTION_HASH                                     NUMBER
SERIALIZABLE_ABORTS                                NUMBER
CPU_TIME                                        NUMBER
ELAPSED_TIME                                     NUMBER
IS_OBSOLETE                                     VARCHAR2(1)
CHILD_LATCH                                     NUMBER




v$sql     ----------   存储的是具体的SQL 和执行计划相关信息，实际上，v$sqlarea 可以看做 v$sql 根据 sqltext 等 做了 group by 之后的信息


SQL> desc v$sql
Name                                      Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT                                        VARCHAR2(1000)
SHARABLE_MEM                                     NUMBER
PERSISTENT_MEM                                  NUMBER
RUNTIME_MEM                                     NUMBER
SORTS                                           NUMBER
LOADED_VERSIONS                                  NUMBER
OPEN_VERSIONS                                      NUMBER
USERS_OPENING                                      NUMBER
FETCHES                                            NUMBER
EXECUTIONS                                         NUMBER
USERS_EXECUTING                                  NUMBER
LOADS                                           NUMBER
FIRST_LOAD_TIME                                  VARCHAR2(38)
INVALIDATIONS                                      NUMBER
PARSE_CALLS                                     NUMBER
DISK_READS                                         NUMBER
BUFFER_GETS                                     NUMBER
ROWS_PROCESSED                                  NUMBER
COMMAND_TYPE                                     NUMBER
OPTIMIZER_MODE                                  VARCHAR2(10)
OPTIMIZER_COST                                  NUMBER
PARSING_USER_ID                                  NUMBER
PARSING_SCHEMA_ID                               NUMBER
KEPT_VERSIONS                                      NUMBER
ADDRESS                                            RAW(4)
TYPE_CHK_HEAP                                      RAW(4)
HASH_VALUE                                         NUMBER
PLAN_HASH_VALUE                                  NUMBER
CHILD_NUMBER                                     NUMBER ----------   注意这个 
MODULE                                           VARCHAR2(64)
MODULE_HASH                                     NUMBER
ACTION                                           VARCHAR2(64)
ACTION_HASH                                     NUMBER
SERIALIZABLE_ABORTS                                NUMBER
OUTLINE_CATEGORY                                   VARCHAR2(64)
CPU_TIME                                        NUMBER
ELAPSED_TIME                                     NUMBER
OUTLINE_SID                                     NUMBER --------------   注意这里跟 outline 有关
CHILD_ADDRESS                                      RAW(4)
SQLTYPE                                            NUMBER
REMOTE                                           VARCHAR2(1)
OBJECT_STATUS                                      VARCHAR2(19)
LITERAL_HASH_VALUE                               NUMBER
LAST_LOAD_TIME                                  VARCHAR2(38)
IS_OBSOLETE                                     VARCHAR2(1)
CHILD_LATCH                                     NUMBER

补充：
1、查一下这些视图的定义你就能理解，它们的源都是一个。
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$SQL_AREA';

2、实际上最模糊的是v$sql与v$sqlarea,区别与联系还有：

a、v$sql_area相当于是按INST_ID, KGLNAOBJ, KGLHDPAR, KGLNAHSH, KGLNATIM, GLOBTS0,GLOBT19, KGLOBTS1, KGLOBT20,DECODE(KGLOBT33, 1, 'Y', 'N'),KGLHDCLT这些列的自v$sql的group by，也就是说v$sql的每一行表示的是每一个sql语句的一个versiion，而v$sqlarea存放的是相同语句不同version一个汇总。

b、 v$sql与v$sqlarea的源都是一个：X$KGLCURSOR

c、实际调优中建议使用v$sql,相对来说比v$sqlarea快，而且还不会产生share pool latch的争用。

3、因v$sql及v$sqlarea存放着统计信息在调优时使用居多，但其sql是不全的，如果想获得完整的sql就要用v$sqltext了。


另外注意这个
QL> desc v$sql_plan
Name                                      Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS                                            RAW(4)
HASH_VALUE                                         NUMBER
CHILD_NUMBER                                     NUMBER ------------ 注意这个和 v$sql 里面的相同字段
OPERATION                                        VARCHAR2(60)
OPTIONS                                            VARCHAR2(60)
OBJECT_NODE                                     VARCHAR2(20)
OBJECT#                                            NUMBER
OBJECT_OWNER                                     VARCHAR2(30)
OBJECT_NAME                                     VARCHAR2(64)
OPTIMIZER                                        VARCHAR2(40)
ID                                              NUMBER
PARENT_ID                                        NUMBER
DEPTH                                           NUMBER
POSITION                                        NUMBER
SEARCH_COLUMNS                                  NUMBER
COST                                               NUMBER
CARDINALITY                                     NUMBER
BYTES                                           NUMBER
OTHER_TAG                                        VARCHAR2(70)
PARTITION_START                                  VARCHAR2(10)
PARTITION_STOP                                  VARCHAR2(10)
PARTITION_ID                                     NUMBER
OTHER                                           VARCHAR2(4000)
DISTRIBUTION                                     VARCHAR2(40)
CPU_COST                                        NUMBER
IO_COST                                            NUMBER
TEMP_SPACE                                         NUMBER
ACCESS_PREDICATES                               VARCHAR2(4000)
FILTER_PREDICATES                               VARCHAR2(4000)


实际上，看起来同样的一句SQL ，往往具有不同的执行计划
如果是不同的数据库用户，那么相应的涉及的 对象可能都不一样，注意v$sql 中
OBJECT#                                            NUMBER
OBJECT_OWNER                                     VARCHAR2(30)
OBJECT_NAME                                     VARCHAR2(64)
OPTIMIZER                                        VARCHAR2(40)

即使是相同的数据库用户，若 session 的优化模式、session 级的参数等不一样，执行计划也能不同。所以即使相同的sql，也可能具有不同的执行计划！

v$sql join   to   v$sql_plan   就代表了具体的sql的执行计划,通过下面3个字段做连接

ADDRESS                                            RAW(4)
HASH_VALUE                                         NUMBER
CHILD_NUMBER                                     NUMBER


而v$SQLAREA 忽略了   执行计划等差异，只是在形式上sql文本看起来一样！相当于做了个聚合，是多个不同执行计划的sql的聚合和累计信息






Oracle服务器为当前在共享池library cache中的sql语句提供了三种查询执行统计信息的动态视图。他们是v$sql，v$sqlarea和v$sqlxs。

V$sql针对不同version的sql语句提供一条记录。这就意味着每个child cursor拥有自己的执行统计数据。

V$sqlarea针对不同sql语句提供一条记录。比如：不同的parent cursor。这就意味这个统计信息是所有child cursor之和。他不是依赖于v$sql。

V$sqlxs是v$sqlarea的简单版本。主要用来statspack产生sql报告部分。他是对v$sql的group by查询。在oracle_home/rdbms/admin/catsnmp.sql中定义。

V$sqlarea或者v$sqlxs可以用来确定按特定分类的几个top sql语句。一旦被确定，v$sql可以用来深入查看该语句不同version的统计信息。

V$sql需要更少资源比起v$sqlarea，因为他避免了group by操作，并且会产生更少的library cache latch竞争。

SELECT * FROM

  (SELECT hash_value,address,substr(sql_text,1,40) sql,

          [list of columns], [list of derived values]

     FROM [V$SQL or V$SQLXS or V$SQLAREA]

    WHERE [list of threshold conditions for columns]

    ORDER BY [list of ordering columns] DESC)

WHERE rownum <= [number of top SQL statements];

这是一个通用的模板。

下面来看一个实际的例子：

SELECT * FROM

  (SELECT hash_value,address,substr(sql_text,1,40) sql,

          buffer_gets, executions, buffer_gets/executions "Gets/Exec"

     FROM V$SQLAREA

    WHERE buffer_gets > 100000 AND executions > 10

   ORDER BY buffer_gets DESC)

WHERE rownum <= 10;



其中：

[list of columns] = buffer_gets, executions

[list of derived values] = buffer_gets/executions

[list of threshold conditions for columns] = buffer_gets > 100000, executions > 10

[list of ordering columns] = buffer_gets

[number of top SQL statements] = 10

评论

1 楼 guji528 2012-06-20  

可以这样监控某Oracle用户已执行的sql：
--TEST用户通过JDBC方式操作Oracle数据库
select * from  v$sql t where t.MODULE like '%JDBC%' and t.PARSING_SCHEMA_NAME = 'TEST' order by t.LAST_LOAD_TIME desc;

--TEST用户对Oracle数据库进行更新或插入操作
select * from  v$sql t where (upper(t.SQL_TEXT) like '%UPDATE%' or upper(t.SQL_TEXT) like '%INSERT%') and t.PARSING_SCHEMA_NAME = 'TEST' order by t.LAST_LOAD_TIME desc;