Appendix A. Dynamic Performance Views

In this book, we have been presented with many dynamic performance views, used to access a wide spread of details about an Oracle database, regarding different aspects from the sessions to the SQL statement executed.

In this appendix, we present a summary of these views, in alphabetical order, which can be used as a reference. For each view, there is a brief description and a list of the most useful fields of the view.

ALL_OBJECTS

This view lists all the database objects the current user can access. As for many ALL_ views, there are similar DBA_OBJECTS and USER_OBJECTS views; they have the same fields but DBA_OBJECTS shows all the objects in the database, while USER_OBJECTS shows information only on the objects of the current user.

This view is often joined to other dynamic performance views, for example, V$LOCKED_OBJECT, by OBJECT_ID to obtain the object name and owner.

Fields

The most relevant view fields are as follows:

• OWNER: The owner of the object

• OBJECT_NAME: The name of the object

• OBJECT_ID: The unique ID associated to the object in the data dictionary

• OBJECT_TYPE: The object type, such as TABLE, INDEX, and so on

• CREATED: The timestamp for the creation of the object

• STATUS: The status of the object, such as VALID, INVALID, or N/A

DBA_BLOCKERS

This view returns the session IDs of the sessions holding locks.

Fields

There is only one field in this view, namely, HOLDING_SESSION.

See also

• The DBA_WAITERS and V$SESSION sections in this appendix

DBA_DATA_FILES

This view returns all datafiles. It's often joined with other views by the FILE_ID field, sometimes named as FILE# in other views.

Fields

The most relevant view fields are as follows:

• FILE_ID: A unique ID associated with the file

• FILE_NAME: The name of the database file

• TABLESPACE_NAME: The name of the tablespace to which the file belongs

• BYTES: The size in bytes

• BLOCKS: The size in database blocks

• STATUS: The status of the file, related to its availability; can be AVAILABLE or INVALID (for example, a file in a dropped tablespace)

• ONLINE_STATUS: The online status of the file, can be SYSOFF, SYSTEM, OFFLINE, ONLINE, or RECOVER

See also

• The DBA_EXTENTS, FILE$, V$DATAFILE, V$LOGFILE, DBA_TEMP_FILES, and V$CONTROLFILE sections in this appendix

DBA_EXTENTS

This view returns the extents which build the database files. No extent information is showed for offline datafiles in locally managed tablespaces.

Fields

The most relevant view fields are as follows:

• SEGMENT_TYPE: The type of the segment, such as INDEX (PARTITION), or TABLE (PARTITION)

• OWNER: The owner of the associated segment

• SEGMENT_NAME: The name of the associated segment

• TABLESPACE_NAME: The name of the tablespace containing the extent

• BYTES: The size in bytes

• BLOCKS: The size in database blocks

• FILE_ID: The file identifier of the file containing the extent

• EXTENT_ID: The extent number in the segment

See also

• The DBA_DATA_FILES section in this appendix

DBA_INDEXES

In this view are listed all the indexes in the database. There are the corresponding ALL_INDEXES and USER_INDEXES, showing, respectively, the indexes which the current user can access and the indexes the current user owns.

Fields

The most relevant view fields are as follows:

• OWNER: The owner of the index

• INDEX_NAME: The name of the index

• TABLE_OWNER: The owner of the table on which the index is built

• TABLE_NAME: The name of the table on which the index is built

• TABLESPACE_NAME: The name of the tablespace in which the index is stored

• STATUS: This can be VALID or UNUSABLE

• INDEX_TYPE: The type of the index; can be NORMAL, BITMAP, FUNCTION-BASED NORMAL, FUNCTION-BASED BITMAP, or DOMAIN

• TABLE_TYPE: The type of the indexed object (TABLE or CLUSTER)

• UNIQUENESS: Whether the index is UNIQUE or NONUNIQUE

• COMPRESSION: If the index is compressed then the value is ENABLED, else DISABLED

• PREFIX_LENGTH: The number of columns in the compressed prefix of the key

• LEAF_BLOCKS: The number of leaf blocks in the index

DBA_SQL_PLAN_BASELINES

Displays information about the SQL Plan baselines created for a specific SQL statement.

Fields

The most relevant view fields are as follows:

• SQL_HANDLE: A unique identifier

• SQL_TEXT: The un-normalized SQL text

• PLAN_NAME: A unique plan identifier

• ENABLED: This indicates whether the plan baseline is enabled (YES/NO)

• ACCEPTED: This indicates if the plan baseline is accepted (YES/NO)

• FIXED: This indicates if the plan baseline is fixed (YES/NO)

• EXECUTIONS: The number of executions at the time the plan baseline was created

DBA_TABLES

This view lists all the tables in the database. There are the corresponding ALL_TABLES and USER_TABLES, showing, respectively, the tables which the current user can access and the tables the current user owns.

Fields

The most relevant view fields are as follows:

• OWNER: The owner of the table

• TABLE_NAME: The name of the table

• TABLESPACE_NAME: The name of the tablespace in which the table is stored

• STATUS: This can be VALID or UNUSABLE

• LOGGING: This indicates whether changes to the table are logged or not (YES/NO)

• NUM_ROWS: The number of rows in the table

• BLOCKS: The number of used database blocks in the table

• EMPTY_BLOCKS: The number of empty database blocks in the table

• AVG_SPACE: The average free space in bytes for allocated data blocks

• CHAIN_CNT: The number of chained rows in the table

• AVG_ROW_LEN: The average length of a row in the table

• AVG_SPACE_FREELIST_BLOCKS: The average free space of all blocks on a freelist

• NUM_FREELIST_BLOCKS: The number of blocks in a freelist

• SAMPLE_SIZE: The sample size used when analyzing the table

• GLOBAL_STATS: For partitioned tables, this indicates whether the statistics were made on all the partitions (YES) or estimated (NO)

• USER_STATS: This indicates whether the statistics were entered by the user (YES/NO)

• LAST_ANALYZED: The date on which the table was last analyzed

DBA_TEMP_FILES

This view shows all temporary files in the database.

Fields

The most relevant view fields are as follows:

• FILE_ID: A unique ID associated to the file

• FILE_NAME: The name of the temporary file

• TABLESPACE_NAME: The name of the tablespace to which the file belongs

• BYTES: The size in bytes

• BLOCKS: The size in database blocks

See also

• The DBA_EXTENTS, FILE$, V$DATAFILE, V$LOGFILE, DBA_DATA_FILES, and V$CONTROLFILE sections in this appendix

DBA_VIEWS

This view shows all the views in the database. There are the corresponding ALL_VIEWS and USER_VIEWS, that show, respectively, the views which the current user can access and the views the current user owns.

Fields

The most relevant view fields are as follows:

• OWNER: The owner of the view

• VIEW_NAME: The name of the view

• TEXT: The text of the view

DBA_WAITERS

This view shows all the sessions waiting for a lock. It correlates a blocked transaction to the blocking one.

Fields

The most relevant view fields are as follows:

• WAITING_SESSION: The ID of the waiting session

• HOLDING_SESSION: The ID of the session holding the lock

• LOCK_TYPE: The type of lock

• MODE_HELD: The locking mode held

• MODE_REQUESTED: The locking mode requested

See also

• The DBA_BLOCKERS, V$LOCK, and V$LOCKED_OBJECT sections in this appendix

INDEX_STATS

This view collects information from index statistics (after an ANALYZE INDEX).

Fields

The most relevant view fields are as follows:

• NAME: The name of the index

• HEIGHT: The height of the B-tree index

• BLOCKS: The number of allocated database blocks

• BTREE_SPACE: The total space allocated

• USED_SPACE: The total space used

• DISTINCT_KEYS: The number of distinct keys in the index

• OPT_CMPR_COUNT: The optimal key compression length

• OPT_CMPR_PCTSAVE: Space saving corresponding to the adoption of the optimal key compression length

See also

• The DBA_INDEXESsection in this appendix

DBA_SEQUENCES

This lists all the user sequences. It's a synonym for USER_SEQUENCES.

Fields

The most relevant view fields are as follows:

• SEQUENCE_OWNER: The owner of the sequence

• SEQUENCE_NAME: The name of the sequence

• MIN_VALUE: The minimum value of the sequence

• MAX_VALUE: The maximum value of the sequence

• INCREMENT_BY: The value by which the sequence is incremented

• CYCLE_FLAG: This indicates whether the sequence numbering restarts after it has reached the upper limit

• ORDER_FLAG: This indicates whether the sequence is ordered (numbers generated in order) or not; this is useful in an RAC environment

• CACHE_SIZE: The number of sequence numbers to cache

• LAST_NUMBER: The last number written to disk; if caching is enabled, it is the last number placed in the sequence cache

DBA_TABLESPACES

This is a dynamic performance view equivalent to the SYS.TS$ table containing information on all tablespaces; the only difference is that DBA_TABLESPACES doesn't list dropped tablespaces.

Fields

The most relevant view fields are as follows:

• TABLESPACE_NAME: The name of the tablespace

• BLOCK_SIZE: The database block size used in the tablespace

• STATUS: This can be ONLINE, OFFLINE, or READ ONLY

• CONTENTS: This can be UNDO, PERMANENT, or TEMPORARY

• LOGGING: This can be LOGGING or NOLOGGING

• EXTENT_MANAGEMENT: This can be DICTIONARY for dictionary managed or LOCAL for locally managed tablespaces

• ALLOCATION_TYPE: This can be SYSTEM, USER, or UNIFORM

DBA_TAB_HISTOGRAMS

This view describes histograms on all the tables and views generated when collecting statistics using the ANALYZE statement or DBMS_STATS package. There are the equivalent views ALL_TAB_HISTOGRAMS and USER_TAB_HISTOGRAMS; the first contains information on all the objects accessible by the current user, the last on the current user objects in the database.

Fields

The most relevant view fields are as follows:

• OWNER: The owner of the table

• TABLE_NAME: The name of the table

• COLUMN_NAME: The name of the column

• ENDPOINT_NUMBER: The histogram bucket number

• ENDPOINT_VALUE: The normalized endpoint value for the bucket

• ENDPOINT_ACTUAL_VALUE: The actual endpoint value (not normalized) for the bucket

V$ADVISOR_PROGRESS

This view inspects the execution progress of a SQL Tuning Advisor set.

Fields

The most relevant view fields are as follows:

• SID: The session ID

• SERIAL#: The session serial number

• USERNAME: The Oracle username

• OPNAME: The operation name

• ADVISOR_NAME: The advisor name

• TASK_ID: The task ID

• SOFAR: The amount of work done so far

• TOTALWORK: The total work to be done

• TIME_REMAINING: The estimated remaining time in seconds

V$BUFFER_POOL_STATISTICS

This displays statistics about buffer pools in the database instance.

Fields

The most relevant view fields are as follows:

• ID: The buffer pool identifier

• NAME: The name of the buffer pool

• FREE_BUFFER_WAIT: The free buffer wait statistic

• WRITE_COMPLETE_WAIT: The write complete wait statistic

• BUFFER_BUSY_WAIT: The buffer busy wait statistic

• DB_BLOCK_GETS: The number of database blocks gotten statistic

• CONSISTENT_GETS: The number of consistent gets statistic

• PHYSICAL_READS: The number of physical reads statistic

• PHYSICAL_WRITES: The number of physical writes statistic

See also

• The V$DB_CACHE_ADVICE section to get advice in sizing the buffer pools

V$CONTROLFILE

This lists the names of the control files.

Fields

The most relevant view fields are as follows:

• STATUS: This is null if the name can be determined, INVALID otherwise

• NAME: The control filename

• IS_RECOVERY_DEST_FILE: This indicates whether the control file was created in the fast recovery area (YES) or not (NO)

• BLOCK_SIZE: The control file block size

• FILE_SIZE_BLKS: The control file size in blocks

See also

• The V$DATAFILE, DBA_TEMP_FILES, V$FILESTAT, V$LOGFILE, and V$TEMPFILE sections in this appendix

V$DATAFILE

This lists datafile information taken from the control file.

Fields

The most relevant view fields are as follows:

• FILE#: The file identification number

• NAME: The filename

• CREATION_TIME: The timestamp of file creation

• TS#: The tablespace identification number

• STATUS: The datafile status; can be OFFLINE, ONLINE, SYSTEM, RECOVER, or SYSOFF

• ENABLED: This describes if the file is accessible; the possible values are DISABLED, READ ONLY, READ WRITE, or UNKNOWN

• BYTES: The datafile size in bytes

• BLOCKS: The datafile size in database blocks

See also

• The V$FILESTAT, V$LOGFILE, and V$TEMPFILE sections in this appendix

V$DB_CACHE_ADVICE

This view contains predictions on the number of physical reads obtained when varying the cache size.

Fields

The most relevant view fields are as follows:

• ID: The buffer pool identifier

• NAME: The buffer pool name

• BLOCK_SIZE: The size of the database block for this pool

• SIZE_FOR_ESTIMATE: The cache size for prediction in megabytes

• BUFFERS_FOR_ESTIMATE: The cache size for prediction in number of buffers

• ESTD_PHYSICAL_READS: The estimated number of physical reads for the cache size evaluated

• ESTD_PHYSICAL_READ_FACTOR: The estimated physical read factor for the cache size evaluated, a ratio to the number of reads in the cache

See also

• The V$BUFFER_POOL_STATISTICS and V$DB_OBJECT_CACHE sections in this appendix

V$DB_OBJECT_CACHE

This view lists objects currently cached in the library cache.

Fields

The most relevant view fields are as follows:

• OWNER: The owner of the object

• NAME: The object name

• TYPE: The object type, for example, INDEX, TABLE, VIEW, CLUSTER, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, TRIGGER, or CLASS

• SHARABLE_MEM: The amount of memory in the shared pool used by the object

• KEPT: If the object was pinned in the library cache using the DBMS_SHARED_POOL.KEEP procedure, the value is YES, otherwise NO

• LOADS: The number of times the object was loaded

• LOCKS: The number of users locking the object

• PINS: The number of users pinning the object

• INVALIDATIONS: The number of times the object was marked invalid because a dependent object was modified

See also

• The ALL_OBJECTS section in this appendix

V$ENQUEUE_LOCK

This view shows the locks owned by enqueue state objects.

Fields

The most relevant view fields are as follows:

• ADDR: The address of the lock state object

• KADDR: The address of the lock

• SID: The identifier for the session holding or acquiring the lock

• TYPE: The type of lock

• ID1: Lock identifier #1 (depends on TYPE)

• ID2: Lock identifier #2 (depends on TYPE)

• LMODE: A lock mode in which the session holds the lock; values can be:

  • NONE

  • NULL (NULL)

  • ROW-S (SS)

  • ROW-X

  • SHARE (S)

  • S/ROW-X (SSX)

  • EXCLUSIVE (X)

• REQUEST: A lock mode in which the process request locks; values are the same as for LMODE fields

• CTIME: The time since the current mode was grant

• BLOCK: Whether this lock is blocking another lock

See also

• The V$LOCK, V$LOCKED_OBJECT, DBA_WAITERS, and DBA_BLOCKERS sections in this appendix

V$FILESTAT

This view displays statistics about I/O operations on files.

Fields

The most relevant view fields are as follows:

• FILE#: The file identification number

• PHYRDS: The number of physical reads done

• PHYWRTS: The number of physical writes done

• READTIM: The time spent doing reads (in hundredths of a second)

• WRITETIM: The time spent doing writes (in hundredths of a second)

See also

• The V$DATAFILE section in this appendix

V$FIXED_TABLE

This view lists all V$ and X$ Dynamic Performance Views.

Fields

The most relevant view fields are as follows:

• NAME: The object name

• OBJECT_ID: The object identification number

• TYPE: The object type; can be TABLE or VIEW

• TABLE_NUM: If the type is TABLE, it identifies the dynamic performance table

V$INSTANCE_RECOVERY

This view monitors the mechanisms available to the user/DBA to limit the I/O needed for recovery.

Fields

The most relevant view fields are as follows:

• RECOVERY_ESTIMATED_IOS: The estimated number of data blocks to process during recovery

• ESTIMATED_MTTR: The estimated time recovery based on the current system load

• TARGET_MTTR: The Mean Time To Recover (MTTR) target value in seconds

• LOG_FILE_SIZE_REDO_BLKS: The number of redo blocks required to be sure that a log switch won't occur before the checkpoint completes

• LOG_CHKPT_INTERVAL_REDO_BLKS: The number of redo blocks that will be processed during recovery to satisfy the LOG_CHECKPOINT_INTERVAL parameter

• LOG_CHKPT_TIMEOUT_REDO_BLKS: The number of redo blocks that will be processed during recovery to satisfy the LOG_CHECKPOINT_TIMEOUT parameter

• ACTUAL_REDO_BLKS: The current number of redo blocks needed to recover

• TARGET_REDO_BLKS: The current target number of redo blocks that must be processed for recovery

V$LATCH

This view displays aggregate latch statistics.

Fields

The most relevant view fields are as follows:

• LATCH#: The latch number

• NAME: The latch name

• GETS: The number of times the latch is requested in the willing-to-wait mode

• MISSES: The number of times the latch is requested in willing-to-wait mode and the requestor had to wait

• SLEEPS: The number of times a willing-to-wait latch request resulted in a session sleeping

• SPIN_GETS: The number of times a willing-to-wait latch request was satisfied only after a spin

• WAIT_TIME: The elapsed time spent waiting for the latch in microseconds

• IMMEDIATE_GETS: The number of times the latch is requested in no-wait mode

• IMMEDIATE_MISSES: The number of times a no-wait latch request was unsatisfied

See also

• The V$LATCH_CHILDREN recipe in this appendix

V$LATCH_CHILDREN

This view displays statistics about child latches.

Fields

The most relevant view fields are:

• CHILD#: The child latch number

• LATCH#: The parent latch number

• NAME: The latch name

• GETS: The number of times the latch is requested in willing-to-wait mode

• MISSES: The number of times the latch is requested in willing-to-wait mode, and the requestor had to wait

• SLEEPS: The number of times a willing-to-wait latch request resulted in a session sleeping

• SPIN_GETS: The number of times a willing-to-wait latch request was satisfied only after a spin

• WAIT_TIME: The elapsed time spent waiting for the latch in microseconds

• IMMEDIATE_GETS: The number of times the latch is requested in no-wait mode

• IMMEDIATE_MISSES: The number of times a no-wait latch request was unsatisfied

See also

• The V$LATCH section in this appendix

V$LIBRARYCACHE

Displays statistics on library cache activity.

Fields

The most relevant view fields are:

• NAMESPACE: The library cache namespace

• GETS: The number of times a lock was requested for objects in the namespace

• GETHITRATIO: The number of times the object's handle was found in memory

• PINS: The number of times a PIN was requested for objects in the namespace

• PINHITRATIO: The number of times the object's metadata were found in memory

• RELOADS: Any PIN of the object, following the first after object creation, which requires loading the object from disk

• INVALIDATIONS: The number of times objects in the namespace were marked invalid due to dependent object modifications

V$LOCK

This view lists the locks held by the database.

Fields

The most relevant view fields are:

• SID: An identifier for the session holding or acquiring the lock

• ID1: Lock identifier #1 (depends on TYPE)

• ID2: Lock identifier #2 (depends on TYPE)

• TYPE: The type of lock; user locks can be TM (DML enqueue), TX (transaction enqueue), or UL (user supplied)

• LMODE: A lock mode in which the session holds the lock; values can be:

  • NONE

  • NULL (NULL)

  • ROW-S (SS)

  • ROW-X

  • SHARE (S)

  • S/ROW-X (SSX)

  • EXCLUSIVE (X)

• REQUEST: A lock mode in which the process request locks; values are the same as for the LMODE field

• CTIME: The time since current mode was granted

• BLOCK: If the lock is blocking another lock the value is 1, otherwise the value is 0

See also

• The DBA_BLOCKERS, DBA_WAITERS, V$ENQUEUE_LOCK, and V$LOCKED_OBJECT sections in this appendix

V$LOCKED_OBJECT

This view shows which sessions are holding DML locks and on what objects.

Fields

The most relevant view fields are:

• XIDUSN: The undo segment number

• XIDSLOT: The slot number

• XIDSQN: The sequence number

• OBJECT_ID: The object ID being locked

• SESSION_ID: The session identifier number

• ORACLE_USERNAME: The Oracle username

• OS_USER: The operating system user

• PROCESS: The operating system process identifier

• LOCKED_MODE: The lock mode

See also

• The DBA_BLOCKERS, DBA_WAITERS, V$ENQUEUE_LOCK, and V$LOCK sections in this appendix

V$LOG

This view displays information on log files from the control file.

Fields

The most relevant view fields are:

• GROUP#: The log group number

• THREAD#: The log thread number

• SEQUENCE#: The log sequence number

• BYTES: The size of the log in bytes

• BLOCKSIZE: The block size of the log file

• MEMBERS: The number of members in the log group

• ARCHIVED: The archive status (YES or NO)

• STATUS: The log status; it can be UNUSED, CURRENT, ACTIVE, CLEARING, CLEARING_CURRENT, or INACTIVE

• FIRST_CHANGE#: The lowest system change number stored in the log

• FIRST_TIME: The time of the first system change number in the log

See also

• The V$LOG_HISTORY and V$LOGFILE sections in this appendix

V$LOG_HISTORY

This view shows the log history from the control file.

Fields

The most relevant view fields are:

• RECID: The control file record ID

• STAMP: The control file record stamp

• THREAD#: The thread number of the archived log

• SEQUENCE#: The sequence number of the archived log

• FIRST_CHANGE#: The lowest system change number in the log

• FIRST_TIME: The time of the first entry in the log

• NEXT_CHANGE#: The highest system change number in the log

• RESETLOGS_CHANGE#: The resetlogs change number of the database when the log was written

• RESETLOGS_TIME: The resetlogs time of the database when the log was written

See also

• The V$LOG and V$LOGFILE sections in this appendix

V$LOGFILE

This view contains information about redo log files.

Fields

The most relevant view fields are as follows:

• GROUP#: The redo log group identification number

• STATUS: The status of the log member; it can be INVALID, STALE, DELETED, or NULL when the file is in use

• TYPE: The type of the log file, it can be ONLINE or STANDBY

• MEMBER: The redo log member name

• IS_RECOVERY_DEST_FILE: This indicates whether the file was created in the fast recovery area (YES) or not (NO)

See also

• The V$LOG and V$LOG_HISTORY recipes in this appendix

V$MYSTAT

This view contains statistics on the current session.

Fields

The most relevant view fields are as follows:

• SID: The session identifier for the current session

• STATISTIC#: The number of the STATISTIC

• VALUE: The value of the STATISTIC

See also

• The V$STATNAME, V$SESSTAT, and V$SYSSTAT sections in this appendix

V$PROCESS

This view displays information about the currently active processes.

Fields

The most relevant view fields are as follows:

• PID: An Oracle process identifier

• SPID: The operating system process identifier

• ADDR: The address of the process state object

• SERIAL#: The process serial number

• PNAME: The name of the process

• USERNAME: The operating system process username

• TERMINAL: The operating system terminal identifier

• PROGRAM: The program currently in progress

See also

• The V$SESSION section in this appendix

V$ROLLSTAT

This view contains rollback segments statistics.

Fields

The most relevant view fields are as follows:

• USN: The rollback segment number

• LATCH: The latch for the rollback segment

• EXTENTS: The number of extents in the rollback segment

• RSSIZE: The size of the rollback segment in bytes

• WRITES: The number of bytes written to the rollback segment

• XACTS: The number of active transactions

• GETS The number of header gets

• WAITS: The number of header waits

• STATUS: The rollback segment status, it can be ONLINE, OFFLINE, PENDING OFFLINE, or FULL

V$ROWCACHE

This view displays statistics about the data dictionary activity.

Fields

The most relevant view fields are as follows:

• PARAMETER: The name of the initialization parameter that determines the number of entries in the data dictionary cache

• GETS: The total number of requests for information on the data object

• GETMISSES: The number of data requests resulting in cache misses

• MODIFICATIONS: The number of inserts, updates, and deletions

• FLUSHES: The number of times flushed to disk

V$SESSION

This view displays information on each current session.

Fields

The most relevant view fields are as follows:

• SID: The session identifier

• SADDR: The session address

• SERIAL#: The session serial number

• PROCESS: The operating system client process ID

• PADDR: The address of the process that owns this session

• AUDSID: The auditing session ID

• EVENT: The resource or event for which the session is waiting

• P1, P2, P3: Wait event parameters

• WAIT_TIME: This is set to:

  • -2 if TIMED_STATISTICS is set to false

  • -1 if the last wait duration was less than a hundredth of a second

  • 0 if the session is currently waiting

  • A value greater than zero, which is the duration of the last wait in hundredths of a second

• LAST_CALL_ET: The elapsed time in seconds since the session has become active/inactive

• SQL_ID: The identifier of the currently executed SQL statement

• PREV_SQL_ID: The identifier of the last SQL statement executed

• ROW_WAIT_BLOCK#: The identifier for the block containing the row specified in ROW_WAIT_ROW#

• ROW_WAIT_ROW#: The current row being locked

• ROW_WAIT_FILE#: The file identifier for the datafile containing the row specified in ROW_WAIT_ROW#

• ROW_WAIT_OBJ#: The object ID for the table containing the row specified in ROW_WAIT_ROW#

See also

• The V$SESSION_EVENT and V$SESSTAT sections in this appendix

V$SESSION_EVENT

This view displays information on waits for an event by a session.

Fields

The most relevant view fields are as follows:

• SID: The session identifier

• EVENT: The name of the wait event

• TOTAL_WAITS: The total number of waits for the event in the session

• TOTAL_TIMEOUTS: The total number of timeouts for the event in the session

• TIME_WAITED: The total amount of time waited, in hundredths of a second for the event in the session

• AVERAGE_WAIT: The average amount of time waited, in hundredths of a second for the event in the session

• MAX_WAIT: The maximum time waited, in hundredths of a second for the event in the session

• EVENT_ID: The wait event identifier

See also

• The V$SESSION and V$SESSTAT sections in this appendix

V$SESSTAT

This view displays statistics on the sessions.

Fields

The most relevant view fields are as follows:

• SID: The session identifier

• STATISTIC#: The statistic number

• VALUE: The statistic value

See also

• The V$STATNAME section in this appendix

V$SGA

This view displays summary information about the System Global Area (SGA).

Fields

The most relevant view fields are as follows:

• NAME: The SGA component group

• VALUE: The memory size of the SGA component group in bytes

See also

• The V$SGAINFO section in this appendix

V$SGAINFO

This view displays detailed information about the SGA components.

Fields

The most relevant view fields are as follows:

• NAME: The name of the SGA component

• BYTES: The size in bytes

• RESIZEABLE: This indicates whether the component is resizable (YES) or not (NO)

See also

The V$SGA section in this appendix

V$SHARED_POOL_RESERVED

This view displays statistics useful in tuning the shared pool.

Fields

The most relevant view fields are as follows:

• FREE_SPACE: The amount of free space on the reserved list

• USED_SPACE: The amount of used memory on the reserved list

• REQUESTS: The number of times that the reserved list was searched for a free piece of memory

• REQUEST_MISSES: The number of times the reserved list did not have a free piece of memory to satisfy the request, and started flushing objects from the LRU list

• REQUEST_FAILURES: The number of times that no memory was found to satisfy a request

V$SORT_SEGMENT

This view displays information about every sort segment in a given instance.

Fields

The most relevant view fields are as follows:

• TABLESPACE_NAME: The name of the tablespace

• CURRENT_USERS: The active users of the segment

• TOTAL_BLOCKS: The total number of blocks in the segment

• USED_BLOCKS: The number of blocks in the segment allocated to active sorts

• FREE_BLOCKS: The number of blocks in the segment not allocated to any sort

• MAX_BLOCKS: The maximum number of blocks ever used

• MAX_USED_BLOCKS: The maximum number of blocks used by all sorts

• MAX_SORT_BLOCKS: The maximum number of blocks used by an individual sort

• EXTENT_SIZE: The extent size

• TOTAL_EXTENTS: The total number of extents in the segment

• USED_EXTENTS: The extents allocated to active sorts

• FREE_EXTENTS: The extents not allocated to any sort

• EXTENT_HITS: The number of times an unused extent was found in the pool

V$SQL

This view lists statistics on shared SQL areas.

Fields

The most relevant view fields are as follows:

• SQL_ID: The identifier of the parent cursor in the library cache

• SQL_TEXT: The first thousand characters of the SQL text

• SQL_FULLTEXT: The full text of the SQL statement

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

• INVALIDATIONS: The number of times this child cursor has been invalidated

• PARSE_CALLS: The number of parse calls for this child cursor

• IS_BIND_SENSITIVE: This indicates whether the cursor is bind sensitive (Y) or not (N). A query is considered bind-sensitive if the optimizer peeked at one of its bind variable values when computing predicate selectivity and where a change in a bind variable value may cause the optimizer to generate a different plan

• IS_BIND_AWARE: This indicates whether the cursor is bind aware (Y) or not (N); a query is considered bind-aware if it has been marked to use extended cursor sharing

• IS_SHAREABLE: This indicates whether the cursor can be shared (Y) or not (N)

See also

• The V$SESSION, V$SQL_PLAN, and V$SQLAREA sections in this appendix

V$SQL_PLAN

This view contains information on execution plans for each child cursor loaded in the library cache.

Fields

The most relevant view fields are as follows:

• OPERATION: The name of the internal operation performed in the step

• OBJECT_OWNER: The owner of the object (table or index)

• OBJECT_NAME: The name of the object (table or index)

• HASH_VALUE: The hash value of the parent statement in the library cache

• SQL_ID: The SQL identifier of the parent cursor in the library cache

• PLAN_HASH_VALUE: The hash value representing the SQL plan for the cursor, useful to compare two plans

See also

The V$SQL and V$SQLAREA sections in this appendix

V$SQLAREA

This view displays statistics on shared SQL areas, containing one row per SQL string.

Fields

The most relevant view fields are as follows:

• SQL_ID: The identifier of the parent cursor in the library cache

• SQL_TEXT: The first thousand characters of the SQL text

• SQL_FULLTEXT: The full text of the SQL statement

• USERS_EXECUTING: The total number of users executing the statement

• LOADS: The number of times the object was loaded or reloaded

• HASH_VALUE: The hash value for the parent statement in the library cache

• ADDRESS: The address of the handle for the parent cursor

• COMMAND_TYPE: The Oracle command type definition

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

• INVALIDATIONS: The number of times this child cursor has been invalidated

• PARSE_CALLS: The number of parse calls for this child cursor

• IS_BIND_SENSITIVE: This indicates whether the cursor is bind sensitive (Y) or not (N). A query is considered bind-sensitive if the optimizer peeked at one of its bind variable values when computing predicate selectivity and where a change in a bind variable value may cause the optimizer to generate a different plan

• IS_BIND_AWARE: This indicates whether the cursor is bind aware (Y) or not (N); a query is considered bind-aware if it has been marked to use extended cursor sharing

See also

• The V$SQL and V$SQL_PLAN recipes in this appendix

V$STATNAME

This view is used to decode a statistic identifier to its description.

Fields

The most relevant view fields are as follows:

• STATISTIC#: The statistic number (may change across different database versions)

• NAME: The statistic name

• CLASS: A number representing one or more STATISTIC classes ORed together; this can be:

  • 1: User

  • 2: Redo

  • 4: Enqueue

  • 8: Cache

  • 16: OS

  • 32: Real Application Clusters

  • 64: SQL

  • 128: Debug

• STAT_ID: The identifier of the STATISTIC

See also

• The V$MYSTAT, V$SESSTAT, and V$SYSSTAT sections in this appendix

V$SYSSTAT

This view displays system statistics.

Fields

The most relevant view fields are as follows:

• STATISTIC#: The statistic number

• NAME: The statistic name

• CLASS: A number representing one or more statistic classes ORed together; the values are same as those mentioned in the previous section

• VALUE: The statistic value

• STAT_ID: The identifier of the statistic

See also

• The V$MYSTAT, V$SESSTAT, and V$STATNAME sections in this appendix

V$SYSTEM_EVENT

This view displays information on total waits for an event.

Fields

The most relevant view fields are as follows:

• EVENT: The name of the wait event

• EVENT_ID: The identifier of the wait event

• TOTAL_WAITS: The total number of waits for the event

• TIME_WAITED: The total amount of time waited in hundredths of a second for the event

• AVERAGE_WAIT: The average amount of time waited in hundredths of a second for the event

• TOTAL_TIMEOUTS: The total number of timeouts for the event

V$TEMPFILE

This view displays temporary file information.

Fields

The most relevant view fields are as follows:

• FILE#: The file identifier number

• NAME: The filename

• STATUS: The file status; it can be ONLINE or OFFLINE

• ENABLED: This is enabled for read and/or write

• BYTES: The size of the file in bytes

• BLOCKS: The size of the file in blocks

• BLOCK_SIZE: The block size for the file

V$TEMPSTAT

This view displays statistics about I/O operations on temporary files.

Fields

The most relevant view fields are as follows:

• FILE#: The file identification number

• PHYRDS: The number of physical reads performed

• PHYWRTS: The number of physical writes performed

• READTIM: The time spent doing reads in hundredths of a second

• WRITETIM: The time spent doing writes in hundredths of a second

See also

• The V$TEMPFILE section in this appendix

V$WAITSTAT

This view displays block contention statistics.

Fields

The most relevant view fields are as follows:

• CLASS: The class of the block

• COUNT: The number of waits for this CLASS of block

• TIME: The sum of all wait times for this CLASS of block

See also

• The V$SESSION and V$SESSION_EVENT sections in this appendix

X$BH

This view displays the status and number of pings for every buffer in the SGA.

Fields

The most relevant view fields are as follows:

• OBJ: The object identifier

• HLADDR: The address of the child latch

• TS#: The tablespace identifier number

• FILE#: The file identifier number

• BLOCK#: The database block identifier number