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

郑全

我们在进行sql优化中,时常都去提取运行中的sql语句,但我们到底该去从哪一张表中提取比较好呢?
或许通过以下资料可以了解:


v$sql , v$sqlarea，v$sqltext


1、V$SQL%知多少？

一、select table_name from dict where table_name like 'V$SQL%';
V$SQL
V$SQLAREA
V$SQLAREA_PLAN_HASH
V$SQLSTATS
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES
V$SQL_BIND_CAPTURE
V$SQL_BIND_DATA
V$SQL_BIND_METADATA
V$SQL_CURSOR
V$SQL_JOIN_FILTER
V$SQL_OPTIMIZER_ENV
V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL
V$SQL_REDIRECTION
V$SQL_SHARED_CURSOR
V$SQL_SHARED_MEMORY
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
V$SQL_WORKAREA_HISTOGRAM
v$sql , v$sqlarea，v$sqltext 区别

通常有人喜欢问v$sql,v$sqlarea,v$sqltext这三个视图的区别，一般还真很少注意这个(这个很重要么？)，看看就晓得了：

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$SQLAREA';

SELECT view_definition FROM v$fixed_view_definition       WHERE view_name='GV$SQLTEXT';

这里可以看到每个视图的定义，可以发现者三个视图分别基于下面三个表：

x$kglcursor_child

x$kglcursor_child_sqlid

x$kglna

--------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$SQLAREA 列出了共享SQL区（Shared SQL Area）中的SQL统计信息，这些SQL按照SQL文本的不同，每条会记录一行统计数据。注意这里所说的是“按照SQL文本”来进行区分，也就是说这个视图的信息可以看作是根据SQL_TEXT进行的一次汇总统计。 Column Datatype Description SQL_TEXT VARCHAR2(1000) 当前指针的前1000个字符 VERSION_COUNT NUMBER Cache中这个父指针下存在的子指针的数量 EXECUTIONS NUMBER 总的执行次数，包含所有子指针执行次数的汇总 DISK_READS NUMBER 所有子指针的Disk Reads总和 BUFFER_GETS NUMBER 所有子指针的Buffer Gets总和 OPTIMIZER_MODE VARCHAR2(10) SQL执行的优化器模 HASH_VALUE NUMBER 父指针的Hash Value   ------v$sql    ---  存储的是具体的SQL 和执行计划相关信息，实际上，v$sqlarea 可以看做 v$sql 根据 sqltext 等 做了 group by 之后的信息 Column Datatype Descrption SQL_TEXT VARCHAR2(1000) 当前SQL指针的前1000个字符（也就是说这里记录的SQL是不完整的） EXECUTIONS NUMBER 执行次数 DISK_READS NUMBER 这个子指针Disk Read的次数 BUFFER_GETS NUMBER 这个子指针的Buffer Gets数量 OPTIMIZER_MODE VARCHAR2(10) SQL执行的优化器模式 OPTIMIZER_COST NUMBER SQL执行成本 HASH_VALUE NUMBER 在Library Cache中父指针的Hash Value值 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 v$sql里面找不到完整的sql的时候，则需要从v$sqlarea中找或者拼v$sqltext的sql片 另外注意这个 QL> desc v$sql_plan Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- ADDRESS                                            RAW(4) HASH_VALUE                                         NUMBER CHILD_NUMBER                                       NUMBER   ------------   注意这个和 v$sql 里面的相同字段 　　　一个SQL语句可能有多个子游标（Child Cursor);如果不指定值，会罗列出所有子游标的执行计划。 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的聚合和累计信息 总结：

1、V$SQL,V$SQLAREA 源都是一个。 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_text是不全的，如果想获得完整的sql就要用v$sqltext了，可以。 示例： 1.查看消耗资源最多的SQL： SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA WHERE buffer_gets > 10000000 OR disk_reads > 1000000 ORDER BY buffer_gets + 100 * disk_reads DESC; 2.查看某条SQL语句的资源消耗： SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls FROM V$SQLAREA WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0'); 示例：已知hash_value:3111103299，查询sql语句： select * from v$sqltext where hash_value='3111103299' order by piece

郑全

SQL_TEXT           //当前正在执行的游标的sql文本的前1000个字符

SQL_FULLTEXT       //CLOB类型 整个sql文本，不用借助于V$SQL_TEXT视图来查看整个文本

SQL_ID           //库缓存中的SQL父游标的标志

SHARABLE_MEM           //子游标使用的共享内存的大小,bytes

PERSISTENT_MEM           //子游标生存时间中使用的固定内存的总量,bytes

RUNTIME_MEM           //在子游标执行过程中需要的固定内存大小，bytes

SORTS           //子游标发生的排序数量

LOADED_VERSIONS          // 显示上下文堆是否载入，1是，0否

USERS_OPENING          // 执行这个sql的用户数

FETCHES          // sql取数据的次数

EXECUTIONS           //自从被载入共享池后，sql执行的次数  

FIRST_LOAD_TIME          // 父游标产生的时间戳

PARSE_CALLS           //解析调用的次数  

DISK_CALLS             //读磁盘的次数

DIRECT_WRITES           //直接写的次数

BUFFER_GETS           //直接从buffer中得到数据的次数

APPLICATION_WAIT_TIME          // 应用等待时间，毫秒

CONCURRENCY_WAIT_TIME           //并发等待时间，毫秒

USER_IO_WAIT_TIME           //用户IO等待时间

ROWS_PROCESSED SQL           //解析sql返回的总行数

OPTIMIZER_MODE           //优化器模式

OPTIMIZER_COST           //优化器对于sql给出的成本

PARSING_USER_ID           //第一个创建这个子游标的用户id

HASH_VALUES           //解析产生的哈希值

CHILD_NUMBER           //该子游标的数量

SERVICE           //服务名

CPU_TIME           //该子游标解析，执行和获取数据使用的CPU时间，毫秒

ELAPSED_TIME           //sql的执行时间，毫秒

INVALIDATIONS           //该子游标的无效次数

MODULE       //第一次解析该语句时,通过DBMS_APPLICAITON_INFO.SET_ACTION设置的模块名

ACTION      //第一次解析该语句时,通过DBMS_APPLICAITON_INFO.SET_ACTION设置的动作名  

IS_OBSOLETE           //标记该子游标过期与否，当子游标过大时会发生这种情况

is_bind_sensitive           //不仅指出是否使用绑定变量窥测来生成执行计划，而且指出这个执行计划是否依赖于窥测到的值。如果是，这个字段会被设置为Y，否则会被设置为N。

is_bind_aware           //表明游标是否使用了扩展的游标共享。如果是，这个字段会被设置为Y，如果不是，这个字段会被设置为N。如果是设置为N，这个游标将被废弃，不再可用。

is_shareable           //表明游标能否被共享。如果可以，这个字段会被设置为Y，否则，会被设置为N。如果被设置为N，这个游标将被废弃，不再可用。