Oracle中v$sqlarea与v$sql_plan全面分析

·         v$sql 、 v$sqlarea 、 v$sqltext

这三个视图都可以用于查询共享池中已经解析过的SQL 语句及其相关信息。

V$SQL 中列出了共享SQL 区中所有语句的信息，它不包含GROUP BY 字句，并且为每一条SQL 语句中单独存放一条记录；

V$SQLAREA 中一条记录显示了一条共享SQL 区中的统计信息。它提供了有在内存中、解析过的和准备运行的SQL 语句的统计信息；

V$SQLTEXT 包含了库缓存中所有共享游标对应的SQL 语句。它将SQL 语句分片显示。

下面介绍一下我常用的V$SQLAREA 的结构：

字段	数据类型	说明
SQL_TEXT	VARCHAR2(1000)	游标中SQL 语句的前1000 个字符。
SHARABLE_MEM	NUMBER	被游标占用的共享内存大小。如果存在多个子游标，则包含所有子游标占用的共享内存大小。
PERSISTENT_MEM	NUMBER	用于一个打开这条语句的游标的生命过程中的固定内存大小。如果存在多个子游标，则包含所有子游标生命过程中的固定内存大小。
RUNTIME_MEM	NUMBER	一个打开这条语句的游标的执行过程中的固定内存大小。如果存在多个子游标，则包含所有子游标执行过程中的固定内存大小。
SORTS	NUMBER	所有子游标执行语句所导致的排序次数。
VERSION_COUNT	NUMBER	缓存中关联这条语句的子游标数。
LOADED_VERSIONS	NUMBER	缓存中载入了这条语句上下文堆（KGL heap 6 ）的子游标数。
OPEN_VERSIONS	NUMBER	打开语句的子游标数。
USERS_OPENING	NUMBER	打开这些子游标的用户数。
FETCHES	NUMBER	SQL 语句的fetch 数。
EXECUTIONS	NUMBER	所有子游标的执行这条语句次数。
USERS_EXECUTING	NUMBER	通过子游标执行这条语句的用户数。
LOADS	NUMBER	语句被载入和重载入的次数
FIRST_LOAD_TIME	VARCHAR2(19)	语句被第一次载入的时间戳。
INVALIDATIONS	NUMBER	所以子游标的非法次数。
PARSE_CALLS	NUMBER	所有子游标对这条语句的解析调用次数。
DISK_READS	NUMBER	所有子游标运行这条语句导致的读磁盘次数。
BUFFER_GETS	NUMBER	所有子游标运行这条语句导致的读内存次数。
ROWS_PROCESSED	NUMBER	这条语句处理的总记录行数。
COMMAND_TYPE	NUMBER	Oracle 命令类型代号。
OPTIMIZER_MODE	VARCHAR2(10)	执行这条的优化器模型。
PARSING_USER_ID	NUMBER	第一次解析这条语句的用户的ID 。
PARSING_SCHEMA_ID	NUMBER	第一次解析这条语句所用的schema 的ID 。
KEPT_VERSIONS	NUMBER	所有被DBMS_SHARED_POOL 包标识为保持（Keep ）状态的子游标数。
ADDRESS	RAW(4 | 8)	指向语句的地址
HASH_VALUE	NUMBER	这条语句在library cache 中hash 值。
MODULE	VARCHAR2(64)	在第一次解析这条语句是通过调用DBMS_APPLICATION_INFO.SET_MODULE 设置的模块名称。
MODULE_HASH	NUMBER	模块的Hash 值
ACTION	VARCHAR2(64)	在第一次解析这条语句是通过调用DBMS_APPLICATION_INFO.SET_ACTION 设置的动作名称。
ACTION_HASH	NUMBER	动作的Hash 值
SERIALIZABLE_ABORTS	NUMBER	所有子游标的事务无法序列化的次数，这会导致ORA-08177 错误。
IS_OBSOLETE	VARCHAR2(1)	游标是否被废除（Y 或N ）。当子游标数太多了时可能会发生。
CHILD_LATCH	NUMBER	为了包含此游标的子latch 数。

查看当前会话所执行的语句以及会话相关信息：

SQL> select a.sid||'.'||a.SERIAL#, a.username, a.TERMINAL, a.program, s.sql_text

    2    from v$session a, v$sqlarea s

    3    where a.sql_address = s.address(+)

    4    and a.sql_hash_value = s.hash_value(+)

    5    order by a.username, a.sid;

... ...

SQL>

·         v$sql_plan

视图V$SQL_PLAN 包含了library cache 中所有游标的执行计划。通过结合v$sqlarea 可以查出library cache 中所有语句的查询计划。先从v$sqlarea 中得到语句的地址，然后在由v$sql_plan 查出它的查询计划：

SQL> select lpad(' ', 2*(level-1))||operation "Operation",

    2           options "Options",

    3           decode(to_char(id), '0', 'Cost='||nvl(to_char(position), 'n/a'), object_name) "Object Name",

    4           substr(optimizer, 1, 6) "Optimizer"

    5     from v$sql_plan a

    6    start with address = 'C0000000FCCDEDA0'

    7    and id = 0

    8    connect by prior id = a.parent_id

    9    and prior a.address = a.address

 10    and prior a.hash_value = a.hash_value;

Operation             Options                Object Name            Optimizer

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

SELECT STATEMENT                           Cost=0                 CHOOSE

    NESTED LOOPS 

      INDEX             RANGE SCAN             CSS_BL_CNTR_IDX1       ANALYZ

      INDEX             RANGE SCAN             CSS_BKG_BL_ASSN_UQ1    ANALYZ

SQL>