v$sqlarea,v$sql,v$sqltext提供的sql语句区别

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

　　

　　另外注意这个

　　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的聚合和累计信息

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

(1) v$sql

　　一条语句可以映射多个cursor,因为对象所指的cursor可以有不同用户(如例1)。如果有多个cursor(子游标)存在，在V$SQLAREA为所有cursor提供集合信息。

例1：

这里介绍以下child cursor

user A: select * from tbl

user B: select * from tbl

大家认为这两条语句是不是一样的啊，可能会有很多人会说是一样的，但我告诉你不一定，那为什么呢？

这个tblA看起来是一样的，但是不一定哦，一个是A用户的, 一个是B用户的，这时他们的执行计划分析代码差别可能就大了哦，改下写法大家就明白了:

select * from A.tbl

select * from B.tbl

　　在个别cursor上，v$sql可被使用。该视图包含cursor级别资料。当试图定位session或用户以分析cursor时被使用。

　　PLAN_HASH_VALUE列存储的是数值表示的cursor执行计划。可被用来对比执行计划。PLAN_HASH_VALUE让你不必一行一行对比即可轻松鉴别两条执行计划是否相同。

V$SQL中的列说明：

l SQL_TEXT：SQL文本的前1000个字符

l SHARABLE_MEM：占用的共享内存大小(单位：byte)

l PERSISTENT_MEM：生命期内的固定内存大小(单位：byte)

l RUNTIME_MEM：执行期内的固定内存大小

l SORTS：完成的排序数

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

l OPEN_VERSIONS：显示子游标是否被锁，1是0否

l USERS_OPENING：执行语句的用户数

l FETCHES：SQL语句的fetch数。

l EXECUTIONS：自它被载入缓存库后的执行次数

l USERS_EXECUTING：执行语句的用户数

l LOADS：对象被载入过的次数

l FIRST_LOAD_TIME：初次载入时间

l INVALIDATIONS：无效的次数

l PARSE_CALLS：解析调用次数

l DISK_READS：读磁盘次数

l BUFFER_GETS：读缓存区次数

l ROWS_PROCESSED：解析SQL语句返回的总列数

l COMMAND_TYPE：命令类型代号

l OPTIMIZER_MODE：SQL语句的优化器模型

l OPTIMIZER_COST：优化器给出的本次查询成本

l PARSING_USER_ID：第一个解析的用户ID

l PARSING_SCHEMA_ID：第一个解析的计划ID

l KEPT_VERSIONS：指出是否当前子游标被使用DBMS_SHARED_POOL包标记为常驻内存

l ADDRESS：当前游标父句柄地址

l TYPE_CHK_HEAP：当前堆类型检查说明

l HASH_VALUE：缓存库中父语句的Hash值

l PLAN_HASH_VALUE：数值表示的执行计划。

l CHILD_NUMBER：子游标数量

l MODULE：在第一次解析这条语句是通过调用DBMS_APPLICATION_INFO.SET_MODULE设置的模块名称。

l ACTION：在第一次解析这条语句是通过调用DBMS_APPLICATION_INFO.SET_ACTION设置的动作名称。

l SERIALIZABLE_ABORTS：事务未能序列化次数

l OUTLINE_CATEGORY：如果outline在解释cursor期间被应用，那么本列将显示出outline各类，否则本列为空

l CPU_TIME：解析/执行/取得等CPU使用时间(单位，毫秒)

l ELAPSED_TIME：解析/执行/取得等消耗时间(单位，毫秒)

l OUTLINE_SID：outline session标识

l CHILD_ADDRESS：子游标地址

l SQLTYPE：指出当前语句使用的SQL语言版本

l REMOTE：指出是否游标是一个远程映象(Y/N)

l OBJECT_STATUS：对象状态(VALID or INVALID)

l IS_OBSOLETE：当子游标的数量太多的时候，指出游标是否被废弃(Y/N)

(2)-V$SQL_PLAN

　　本视图提供了一种方式检查那些执行过的并且仍在缓存中的cursor的执行计划。

　　通常，本视图提供的信息与打印出的EXPLAIN PLAN非常相似，不过，EXPLAIN PLAN显示的是理论上的计划，并不一定在执行的时候就会被使用，但V$SQL_PLAN中包括的是实际被使用的计划。获自EXPLAIN PLAN语句的执行计划跟具体执行的计划可以不同，因为cursor可能被不同的session参数值编译(如，HASH_AREA_SIZE)。

V$SQL_PLAN中数据可以：

l 确认当前的执行计划

l 鉴别创建表索引效果

l 寻找cursor包括的存取路径(例如，全表查询或范围索引查询)

l 鉴别索引的选择是否最优

l 决定是否最优化选择的详细执行计划(如，nested loops join)如开发者所愿。

　　本视图同时也可被用于当成一种关键机制在计划对比中。计划对比通常用于下列各项发生改变时：

l 删除和新建索引

l 在数据库对象上执行分析语句

l 修改初始参数值

l 从rule-based切换至cost-based优化方式

l 升级应用程序或数据库到新版本之后

　　如果之前的计划仍然在(例如，从V$SQL_PLAN选择出记录并保存到oracle表中供参考)，那么就有可能去鉴别一条SQL语句在执行计划改变后性能方面有什么变化。

注意：

Oracle公司强烈推荐你使用DBMS_STATS包而非ANALYZE收集优化统计。该包可以让你平行地搜集统计项，收集分区对象(partitioned objects)的全集统计，并且通过其它方式更好的调整你的统计收集方式。此处，cost-based优化器将最终使用被DBMS_STATS收集的统计项。浏览Oracle9i Supplied PL/SQL包和类型参考以获得关于此包的更多信息。

不过，你必须使用ANALYZE语句而非DBMS_STATS进行统计收集，不涉及cost-based优化器，就像：

·使用VALIDATE或LIST CHAINED ROWS子句

·在freelist blocks上收集信息。

V$SQL_PLAN中的常用列：

除了一些新加列，本视图几乎包括所有的PLAN_TABLE列，那些同样存在于PLAN_TABLE中的列拥有相同的值：

l ADDRESS：当前cursor父句柄位置

l HASH_VALUE：在library cache中父语句的HASH值。

ADDRESS和HASH_VALUE这两列可以被用于连接v$sqlarea查询 cursor-specific 信息。

l CHILD_NUMBER：使用这个执行计划的子cursor数

列ADDRESS,HASH_VALUE以及CHILD_NUMBER可被用于连接v$sql查询子cursor信息。

l OPERATION: 在各步骤执行内部操作的名称，例如：TABLE ACCESS

l OPTIONS: 描述列OPERATION在操作上的变种，例如：FULL

l OBJECT_NODE: 用于访问对象的数据库链接database link 的名称对于使用并行执行的本地查询该列能够描述操作中输出的次序。

l OBJECT#: 表或索引对象数量

l OBJECT_OWNER: 对于包含有表或索引的架构schema 给出其所有者的名称

l OBJECT_NAME: 表或索引名

l OPTIMIZER: 执行计划中首列的默认优化模式；例如，CHOOSE。比如业务是个存储数据库，它将告知是否对象是最优化的。

l ID: 在执行计划中分派到每一步的序号。

l PARENT_ID: 对ID 步骤的输出进行操作的下一个执行步骤的ID。

l DEPTH: 业务树深度(或级)。

l POSITION: 对于具有相同PARENT_ID 的操作其相应的处理次序。

l COST: cost-based方式优化的操作开销的评估，如果语句使用rule-based方式，本列将为空。

l CARDINALITY: 根据cost-based方式操作所访问的行数的评估。

l BYTES: 根据cost-based方式操作产生的字节的评估，。

l OTHER_TAG: 其它列的内容说明。

l PARTITION_START: 范围存取分区中的开始分区。

l PARTITION_STOP: 范围存取分区中的停止分区。

l PARTITION_ID: 计算PARTITION_START和PARTITION_STOP这对列值的步数

l OTHER: 其它信息即执行步骤细节，供用户参考。

l DISTRIBUTION: 为了并行查询，存储用于从生产服务器到消费服务器分配列的方法

l CPU_COST: 根据cost-based方式CPU操作开销的评估。如果语句使用rule-based方式，本列为空。

l IO_COST: 根据cost-based方式I/O操作开销的评估。如果语句使用rule-based方式，本列为空。

l TEMP_SPACE: cost-based方式操作(sort or hash-join)的临时空间占用评估。如果语句使用rule-based方式，本列为空。

l ACCESS_PREDICATES: 指明以便在存取结构中定位列，例如，在范围索引查询中的开始或者结束位置。

l FILTER_PREDICATES: 在生成数据之前即指明过滤列。

CONNECT BY操作产生DEPTH列替换LEVEL伪列，有时被用于在SQL脚本中帮助indent PLAN_TABLE数据

V$SQL_PLAN中的连接列

　　列ADDRESS,HASH_VALUE和CHILD_NUMBER被用于连接V$SQL或V$SQLAREA来获取cursor-specific信息，例如，BUFFER_GET,或连接V$SQLTEXT获取完整的SQL语句。

Column View Joined Column(s)

ADDRESS, HASH_VALUE V$SQLAREA ADDRESS, HASH_VALUE

ADDRESS,HASH_VALUE,CHILD_NUMBER V$SQL ADDRESS,HASH_VALUE,CHILD_NUMBER

ADDRESS, HASH_VALUE V$SQLTEXT ADDRESS, HASH_VALUE

确认SQL语句的优化计划

　　下列语句显示一条指定SQL语句的执行计划。查看一条SQL语句的执行计划是调整优化SQL语句的第一步。这条被查询到执行计划的SQL语句是通过语句的HASH_VALUE和ADDRESS列识别。分两步执行：

1.SELECT sql_text, address, hash_value FROM v$sql

WHERE sql_text like '%TAG%';

SQL_TEXT ADDRESSHASH_VALUE

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

821577841224822469

2.SELECT operation, options, object_name, cost FROM v$sql_plan

WHERE address = '82157784' AND hash_value = 1224822469;

OPERATION OPTIONS OBJECT_NAME COST

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

SELECT STATEMENT 5

SORT

AGGREGATE

HASH JOIN 5

TABLE ACCESS FULL DEPARTMENTS 2

TABLE ACCESS FULL EMPLOYEES 2