Skip Headers
Oracle® Database Reference
11g Release 1 (11.1)

Part Number B28320-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

V$SQLSTATS

V$SQLSTATS displays 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$SQLTEXT view.
SQL_ID VARCHAR2(13) SQL identifier of the parent cursor in the library cache
LAST_ACTIVE_TIME DATE Last time that statistics for a cursor with this text and plan were updated
LAST_ACTIVE_CHILD_ADDRESS RAW(4 | 8) Address of the cursor that last updated statistics
PLAN_HASH_VALUE NUMBER Numeric 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 times the transaction failed 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 (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
AVG_HARD_PARSE_TIME NUMBER Average hard parse 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
SHARABLE_MEM NUMBER Total shared memory (in bytes) currently occupied by all cursors with this SQL text and plan
TOTAL_SHARABLE_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)
TYPECHECK_MEM NUMBER ???