v$sql和v$sqlstats

最新推荐文章于 2024-08-10 14:07:47 发布

coin2016

最新推荐文章于 2024-08-10 14:07:47 发布

阅读量327

点赞数

文章标签： java

V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.

V$SQL列出了再共享sql区域的统计信息的列表，不包括group by子句，包括输入的原始sql文本的一行。V$SQL中的统计信息一般在需求执行的最后进行更新。然而，对于长时间执行的需求，每5秒更新一次。这使得很难容易查看长时间执行的sql当她们还在运行的时候带来的影响。

SQL_TEXT 当前正在执行的游标的sql文本的前1000个字符
SQL_FULLTEXT CLOB类型整个sql文本，不用借助于V$SQL_TEXT视图来查看整个文本。
SQL_ID   库缓存中的SQL父游标的标志
SHARABLE_MEM 子游标使用的共享内存的总量
PERSISTENT_MEM 子游标生存时间中使用的固定内存的总量
RUNTIME_MEM      在子游标执行过程中需要的内存总量
SORTS    子游标发生的排序数量
USERS_OPENING 执行这个sql的用户数
FETCHES   与sql文本联系的取数据的数量
EXECUTIONS   自从游标存在于库缓存中以后在这个对象上发生的执行次数
FIRST_LOAD_TIME 父游标产生的时间戳
PARSE_CALLS    解析调用的次数
DISK_CALLS     子游标磁盘读的数量
DIRECT_WRITES   子游标直接读的数量
BUFFER_GETS     子游标得到的buffer的个数
APPLICATION_WAIT_TIME 应用等待时间
CONCURRENCY_WAIT_TIME 并发等待时间
USER_IO_WAIT_TIME
JAVA_EXEC_TIME
PLSQL_EXEC_TIME
ROWS_PROCESSED SQL解析描述返回的总行数
OPTIMIZER_MODE   优化器模式
OPTIMIZER_COST   优化器对于请求给出的消耗
PARSING_USER_ID   首先创建这个子游标的用户id
HASH_VALUES     解析产生的哈希值
CHILD_NUMBER   子游标的数量
SERVICE          服务名
CPU_TIME   S游标解析，执行和获取数据使用的CPU时间
ELAPSED_TIME   sql解析执行和获取数据一共经历的时间

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
V$SQLSTATS也是返回基本的统计信息，但是信息保留的时间比v$sql和v$sqlarea时间长，其中的数据是它们的子集。
V$SQLSTATS returns basic performance statistics for SQL cursors, with each row representing the data for a unique combination of SQL text and optimizer plan (that is, unique combination of SQL_ID, and PLAN_HASH_VALUE). The column definitions for columns in V$SQLSTATS are identical to those in the V$SQL and V$SQLAREA views. However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a greater data retention (the statistics may still appear in this view, even after the cursor has been aged out of the shared pool). Note that V$SQLSTATS contains a subset of columns that appear in V$SQL and V$SQLAREA.

Column Datatype Description
SQL_TEXT VARCHAR2(1000) First thousand characters of the SQL text for the current cursor
SQL_FULLTEXT CLOB Full text for the SQL statement exposed as a CLOB column. THe full text of a SQL statement can be retrieved using this column instead of joining with the V$SQL_TEXT dynamic performance view.
SQL_ID VARCHAR2(13) SQL identifier of the parent cursor in the library cache
LAST_ACTIVE_TIME DATE The last time that statistics for a cursor with this text and plan were updated
LAST_ACTIVE_CHILD_ADDRESS RAW(4) The address of the cursor that last updated statistics
PLAN_HASH_VALUE NUMBER Numerical representation of the SQL plan for this cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line)
PARSE_CALLS NUMBER Number of parse calls for all cursors with this SQL text and plan
DISK_READS NUMBER Number of disk reads for all cursors with this SQL text and plan
DIRECT_WRITES NUMBER Number of direct writes for all cursors with this SQL text and plan
BUFFER_GETS NUMBER Number of buffer gets for all cursors with this SQL text and plan
ROWS_PROCESSED NUMBER Total number of rows the parsed SQL statement returns
SERIALIZABLE_ABORTS NUMBER Number of time the transaction fails to serialize, producing ORA-08177 errors, per cursor
FETCHES NUMBER Number of fetches associated with the SQL statement
EXECUTIONS NUMBER Number of executions that took place on this object since it was brought into the library cache
END_OF_FETCH_COUNT NUMBER Number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the END_OF_FETCH_COUNT column should be less or equal to the value of the EXECUTIONS column.
LOADS NUMBER Number of times the object was either loaded or reloaded
VERSION_COUNT NUMBER number of cursors present in the cache with this SQL text and plan
INVALIDATIONS NUMBER Number of times this child cursor has been invalidated
PX_SERVERS_EXECUTIONS NUMBER Total number of executions performed by Parallel eXecution Servers. The value is 0 when the statement has never been executed in parallel.
CPU_TIME NUMBER CPU time (in microseconds) used by this cursor for parsing, executing, and fetching
ELAPSED_TIME NUMBER Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching
APPLICATION_WAIT_TIME NUMBER Application wait time (in microseconds)
CONCURRENCY_WAIT_TIME NUMBER Concurrency wait time (in microseconds)
CLUSTER_WAIT_TIME NUMBER Cluster wait time (in microseconds)
USER_IO_WAIT_TIME NUMBER User I/O wait time (in microseconds)
PLSQL_EXEC_TIME NUMBER PL/SQL execution time (in microseconds)
JAVA_EXEC_TIME NUMBER Java execution time (in microseconds)
SORTS NUMBER Number of sorts that were done for the child cursor
SHAREABLE_MEM NUMBER Total shared memory (in bytes) currently occupied by all cursors with this SQL text and plan
TOTAL_SHAREABLE_MEM NUMBER Total shared memory (in bytes) occupied by all cursors with this SQL text and plan if they were to be fully loaded in the shared pool (that is, cursor size)