V$SQL

This view lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered.

Column

Datatype

Description

SQL_TEXT

VARCHAR2(1000)

The first thousand characters of the SQL text for the current cursor

SHARABLE_MEM

NUMBER

Amount of shared memory, in bytes, used by this child cursor

PERSISTENT_MEM

NUMBER

Fixed amount of memory, in bytes, used for the lifetime of this child cursor

RUNTIME_MEM

NUMBER

Fixed amount of memory required during the execution of this child cursor

SORTS

NUMBER

The number of sorts that was done for this child cursor

LOADED_VERSIONS

NUMBER

1 if context heap is loaded, 0 otherwise

OPEN_VERSIONS

NUMBER

1 if the child cursor is locked, 0 otherwise

USERS_OPENING

NUMBER

The number of users executing the statement

EXECUTIONS

NUMBER

The number of executions that took place on this object since it was brought into the library cache

USERS_EXECUTING

NUMBER

The number of users executing the statement

LOADS

NUMBER

The number of times the object was loaded or reloaded

FIRST_LOAD_TIME

VARCHAR2(19)

The time stamp of the parent creation time

INVALIDATIONS

NUMBER

The number of times this child cursor has been invalidated

PARSE_CALLS

NUMBER

The number of parse calls for this child cursor

DISK_READS

NUMBER

The number of disk reads for this child cursor

BUFFER_GETS

NUMBER

The number of buffer gets for this child cursor

ROWS_PROCESSED

NUMBER

The total number of rows the parsed SQL statement returns

COMMAND_TYPE

NUMBER

The Oracle command type definition

OPTIMIZER_MODE

VARCHAR2(10)

Mode under which the SQL statement is executed

OPTIMIZER_COST

NUMBER

The cost of this query given by the optimizer

PARSING_USER_ID

NUMBER

The user ID of the user who originally built this child cursor

PARSING_SCHEMA_ID

NUMBER

The schema ID that was used to originally build this child cursor

KEPT_VERSIONS

NUMBER

Indicates whether this child cursor has been marked to be kept pinned in cache using the DBMS_SHARED_POOL package

ADDRESS

RAW(4)

The address of the handle to the parent for this cursor

TYPE_CHK_HEAP

RAW(4)

The descriptor of the type check heap for this child cursor

HASH_VALUE

NUMBER

The hash value of the parent statement in the library cache

PLAN_HASH_VALUE

NUMBER

A 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).

CHILD_NUMBER

NUMBER

The number of this child cursor

MODULE

VARCHAR2(64)

Contains the name of the module that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_MODULE

MODULE_HASH

NUMBER

The hash value of the module that is named in the MODULE column

ACTION

VARCHAR2(64)

Contains the name of the action that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_ACTION

ACTION_HASH

NUMBER

The hash value of the action that is named in the ACTION column

SERIALIZABLE_ABORTS

NUMBER

The number of times the transaction fails to serialize, producing ORA-08177 errors, per cursor

OUTLINE_CATEGORY

VARCHAR2(64)

If an outline was applied during construction of the cursor, this column displays the category of that outline. Otherwise the column is left blank.

CPU_TIME

NUMBER

CPU time (in microseconds) used by this cursor for parsing/executing/fetching

ELAPSED_TIME

NUMBER

Elapsed time (in microseconds) used by this cursor for parsing/executing/fetching

OUTLINE_SID

NUMBER

Outline session identifier

CHILD_ADDRESS

RAW(4)

Address of the child cursor

SQLTYPE

NUMBER

Denotes the version of the SQL language used for this statement

REMOTE

VARCHAR2(1)

(Y/N) Identifies whether the cursor is remote mapped

OBJECT_STATUS

VARCHAR2(19)

Status of the cursor (VALID/INVALID)

LITERAL_HASH_VALUE

NUMBER

The hash value of the literals which are replaced with system generated bind variables and are to be matched, when CURSOR_SHARING is used. This is not the hash value for the SQL statement. If CURSOR_SHARING is not used, the value is 0.