9.2 Types of views

---

There are basically two types of views provided by Oracle, called static views and dynamic views. The static views normally contain the administrative details, e.g., metadata information useful for day-to-day administration of the database. The dynamic views contain data that is dynamic in nature, i.e., the data in these views change either after the database is restarted, or in certain cases when a session leaves the database.

Static views can be of various kinds based on the functionality and permissions available for viewing these views. They are ALL_, DBA_, and USER_. The USER_views only contain information pertaining to the user. To determine the various views pertaining to the specific category, the view called DICTIONARY provides a list under each category.

For example, the query below provides a list of all static dictionary views that are classified under ALL_prefix:

SQL> SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'ALL_%'; TABLE_NAME ------------------------------ ALL_COL_PRIVS ALL_COL_PRIVS_MADE ALL_COL_PRIVS_RECD ALL_CONSTRAINTS ALL_CONS_COLUMNS ALL_CONS_OBJ_COLUMNS ALL_CONTEXT ALL_DB_LINKS ALL_DEF_AUDIT_OPTS ALL_DEPENDENCIES ALL_DIMENSIONS . . . . . . ALL_HISTOGRAMS ALL_JOBS ALL_OUTLINES ALL_OUTLINE_HINTS ALL_SNAPSHOT_REFRESH_TIMES 

The above is a listing of static views that begin with ALL_.

The dynamic views are dynamic in nature for two reasons. The first reason is that one or more views have derived columns from one or more underlying tables or views. The second reason is that many of the values in these views are volatile; they are only retained for the duration that the instance or session is active, and cleared once the instance is bounced or the session completes.

On a stand-alone configuration all dynamic views start with V$; for example, V$INSTANCE provides details about the instance. In this case, V$ views only provide visibility to information pertaining to a specific instance. In a RAC environment, two or more instances are configured to provide access to a common physical database. All dynamic views have a corresponding GV$ (global V$) dynamic view, and querying a GV$ view retrieves the V$ view information from all qualified instances. Instance level information in the GV$ views is identified by the additional column INST_ID, which displays the instance number from which the associated V$ view information was obtained.

GV$ views are visible only if the PARALLEL_MAX_SERVERS param eter is set to a value greater than zero on all instances mounting the database.

The following query will list all the dynamic views available in a specific release of Oracle:

SQL> SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'GV$%'; TABLE_NAME ------------------------------ GV$OPEN_CURSOR GV$SUBCACHE GV$DB_OBJECT_CACHE GV$DB_PIPES GV$VERSION GV$CONTROLFILE GV$DATABASE GV$THREAD GV$LOG GV$STANDBY_LOG GV$DATA FILE . . . . . . GV$MAP_ELEMENT GV$MAP_EXT_ELEMENT GV$MAP_COMP_LIST GV$MAP_SUBELEMENT GV$MAP_FILE_IO_STACK GV$MAP_LIBRARY 

Note

The static views are self-explanatory in the sense that the name describes its contents. For example, DBA_TABLES will list all the tables in the database with the corresponding ownership details that a user with DBA role assigned could view. ALL_TABLES will provide a list of all tables that are viewable by the user and that does not require DBA role privileges; similarly, the USER_TABLES will provide a list of tables that belong to the current user's schema.

While there is sufficient documentation available for static tables, the information pertaining to the dynamic views is comparatively scarce. Hence this chapter will only discuss the dynamic views that are required for the day-to-day administration and tuning of the RAC instances.

Dynamic views

These views are called dynamic views because they record real-time values that show and provide visibility to the current state of the database. While the static views provide the status and composition of the data dictionary, the dynamic V$ and GV$ views provide details on the health of the database. For example, what volume of data is contained in each table, does the table contain chained rows, how much data is being read and written to various tables, how much data is being pinned into the database buffer cache and how much is being reloaded? All such information provides a view of the current health of the database and helps the database administrator to take correct action to improve the performance by fixing the anomalies.

Depending on the type of information contained in these views, data in these view columns is obtained by one of the following methods:

1. Cardinality information pertaining to the various tables is gathered by using the ANALYZE or DBMS_STATS package. While ANALYZE has been the procedure used for a while, it is currently a deprecated method and is only available for backward compatibility. This procedure is being replaced with a new package DBMS_STATS. For example, the following command will gather schema level statistics for schema owner 'MVALLATH':

   EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME => 'MVALLATH',  granularity =>'ALL, cascade =>TRUE);

2. Performance-related information, for example, number of selects, number of hits and misses on the data dictionary cache, or the segment level wait information, is only provided if the TIMED_STATISTICS parameter is set to TRUE. The TIMED_STATISTICS parameter can be set in one of two ways, dynamically using an ALTER command as below:

   ALTER SYSTEM SET TIMED_STATISTICS =TRUE;

   This will reset the value when the instance is restarted. Another way of setting this parameter is by permanently setting the value in the init<SID>.ora file. For example, the init<SID>.ora file will contain among other parameters the following:

   *.JOB_QUEUE_PROCESSES  = 2 *.OPEN_CURSORS  = 1024 *.COMPATIBLE  = 9.2.0.2 *.AUDIT_TRIAL  = FALSE *.TIMED_STATISTICS  = TRUE

   There is a general myth regarding this parameter, namely that TIMED_STATISTICS would consume CPU resources and hence should not be set to TRUE. The overhead by enabling this parameter is not significant. A considerable amount of write activity to the data dictionary occurs irrespective of this parameter being set. However, unless this parameter is set, no real-time data regarding the database characteristics is gathered for analysis and tuning. Also the benefits obtained by enabling this parameter outweighs the disadvantages of using this parameter.

In the sections below we will discuss the various dynamic views that would be helpful in day-to-day administration and maintenance of RAC. This chapter will provide a great detail of information with respect to the views themselves and the underlying tables, with minor examples. A detailed discussion on its usability with respect to performance tuning of RAC configurations will be available in Chapters 13, 14, and 15 later in this book.

GV$ACTIVE_INSTANCES

This view helps map instance names to instance numbers for all instances that have the database currently mounted, i.e., the instance should be active and functioning. Instances that are offline or dismounted are not visible in this view.

Based on X$ Table: X$KSIMSI

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
INST_NUMBER	NUMBER	The instance number.
INST_NAME	VARCHAR2(180)	The name assigned to the instance, for example, RAC1, RAC2, etc., and is represented in the following format: node_name.domain_name:instance_name

SQL> COL INST_ID FORMAT 99 SQL> COL INST_NUMBER FORMAT 99 SQL> COL INST_NAME FORMAT A60 SQL> SELECT INST_ID, INST_NUMBER, INST_NAME FROM GV$ACTIVE_INSTANCES; ---------------------------------------------- 21ora-db1.summerskyus.com:RAC1 22ora-db2.summerskyus.com:RAC2 11ora-db1.summerskyus.com:RAC1 12ora-db2.summerskyus.com:RAC2

GV$ARCHIVE

Archive logs are copies of redo log files, when a log file switch is completed; a copy of the redo log file is made to the archive log file destination specified in the parameter file. This view contains information on redo log files that are ready for archiving.

Note

The information contained in this dynamic view is also present in the GV$LOG file and is a better view for archive-log-related information.

Based on X$ Tables: X$KCCLE and X$KCCDI

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
GROUP#	NUMBER	Log file group number.
THREAD#	NUMBER	Log file thread number.
SEQUENCE#	NUMBER	Log file sequence number.
ISCURRENT	VARCHAR2(3)	Indicates if this is the current online redo log. Note: This is a new column intro- duced in Oracle 9i.
CURRENT	VARCHAR2(3)	Contains the same value as ISCURRENT. Note: This column is obsolete in Oracle 9i and is retained for back- ward compatibility.
FIRST_CHANGE#	NUMBER	First SCN stored in the current log.

GV$ARCHIVE_DEST

This view provides a list of all archive log destinations defined in the parameter file, the details such as the current value, the archive log mode, and current status.

Based on X$ Table: X$KCRRDEST

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
DEST_ID	NUMBER	Identifies the log archive destina- tion parameter. The number of destinations could be between 1 to 10.
DEST_NAME	VARCHAR2(256)	Specifies the parameter used to configure this archive log file. For example LOG_ARCHIVE_DEST_1
STATUS	VARCHAR2(9)	Identifies the current status of the destination. The potential values for this column include: VALID: Initialized and available INACTIVE: No destination information DEFERRED: Manually disabled by the user ERROR: Error during open or copy DISABLED: Disabled after error BADPARAM: Parameter has errors ALTERNATE: Destination is an alternate state FULL: Exceeded quota size for the destination
BINDING	VARCHAR2(9)	Specifies how failure will affect the archival operation. The valid values for the column include: MANDATORY: Successful archival is required OPTIONAL: Successful archival is not required and is based on the parameter LOG_ARCHIVE_MIN_SUCCEED_DEST
NAME_SPACE	VARCHAR2(7)	Identifies the scope of the para- meter setting. The valid values for this column include: SYSTEM: System definition SESSION: Session definition
TARGET	VARCHAR2(7)	Specifies whether the archive des- tination is local or remote to the primary database. The valid values for this column include: PRIMARY: This value indicates that the destination is local STANDBY: This value indicates that the destination is at a remote location, which could be system configured for STANDBY/DATA GUARD purposes
ARCHIVER	VARCHAR2(10)	Identifies the archiver process rela- tive to the database where the query is issued. The valid values for this column include: ARCn FOREGROUND LGWR RFS
SCHEDULE	VARCHAR2(8)	Indicates whether the archival of this destination is INACTIVE, PENDING, ACTIVE or LATENT.
DESTINATION	VARCHAR2(256)	Specifies the physical location for the archived logs. This is the value for the parameter defined in the DEST_NAME column.
LOG_SEQUENCE	NUMBER	Identifies the sequence number of the last archived redo log to be archived.
REOPEN_SECS	NUMBER	Identifies the retry time (in sec- onds) after error.
DELAY_MINS	NUMBER	Identifies the delay interval (in minutes) before the archived redo log is automatically applied to a standby database.
NET_TIMEOUT	NUMBER	Number of seconds the log writer process will wait for status from the network server of a network operation issued by the log writer process.
PROCESS	VARCHAR2(10)	Identifies the archiver process rela- tive to the primary database, even if the query is issued on the standby database: ARCn FOREGROUND LGWR
REGISTER	VARCHAR2(3)	Indicates whether the archived redo log is registered in the remote destination control file. If the archived redo log is registered, it is available to the managed recovery operation. The valid values are: YES: The archived redo log file has been registered in the remote destination control file NO: The archived redo log file has not been registered
FAIL_DATE	DATE	Date and time when the last error was encountered.
FAIL_SEQUENCE	NUMBER	Sequence number of the archived redo log being archived when the last error occurred.
FAIL_BLOCK	NUMBER	Block number of the archived redo log being archived when the last error occurred.
FAILURE_COUNT	NUMBER	Current number of contiguous archival operation failures that have occurred for the destination.
MAX_FAILURE	NUMBER	Contains the number of times log transport services should attempt to re-establish communication and resume archival operations with a failed destination.
ERROR	VARCHAR2(256)	Displays the error text message of the last error.
ALTERNATE	VARCHAR2(256)	Specifies an alternative destination if any defined in the parameter file.
DEPENDENCY	VARCHAR2(256)	Indicates the dependent archive destination.
REMOTE_TEMPLATE	VARCHAR2(256)	Indicates the details of the tem- plate that is to be used to derive the location to be recorded.
QUOTA_SIZE	NUMBER	Quotas allocated for the archive log file at the remote destination and is expressed in bytes.
QUOTA_USED	NUMBER	Indicates the size of all the archived redo logs currently residing on the specified destination.
MOUNTID	NUMBER	Instance mount identifier.
TRASMIT_MODE	VARCHAR2(12)	Indicates the current network transmission mode defined for the transfer of archive redo log file to a remote destination: ASYNC= PARALLEL SYNC= NOPARALLEL
ASYNC_BLOCKS	NUMBER	Number of blocks specified for the ASYNC attribute.
AFFIRM	VARCHAR2(3)	Specifies disk I/O mode.
TYPE	VARCHAR2(7)	Indicates whether the archived log destination definition is PUBLIC or PRIVATE. Only PUBLIC destina- tions can be modified at runtime using the ALTER SYSTEM SET or ALTER SESSION SET state- ments. PUBLIC is the default type.
SP_NAME (Introduced in Oracle 10g)	VAARCHAR2(30)	Service provider name

GV$ARCHIVED_LOG

This view displays archived log information from the control file, including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared. If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE# and FIRST_CHANGE#, however with a different name.

An archive log record is also inserted when an archive log is restored from a backup set or a copy and whenever a copy of a log is made with the RMAN copy command.

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
RECID	NUMBER	Archived log record ID.
STAMP	NUMBER	Archived log record stamp.
NAME	VARCHAR2(513)	Archived log file name. If the column contains a NULL, then it indicates that the log file was cleared before it was archived.
DEST_ID	NUMBER	The original destination from which the archive log was generated.
THREAD#	NUMBER	Redo thread number.
SEQUENCE#	NUMBER	Redo log sequence number.
RESETLOGS_CHANGE#	NUMBER	Resetlogs change# of the database when this log was written.
RESETLOGS_TIME	DATE	Resetlogs time of the database when the log was written.
FIRST_CHANGE#	NUMBER	First change# in the archived logs.
FIRST_TIME	DATE	Timestamp of the first change.
NEXT_CHANGE#	NUMBER	First change in the next log.
NEXT_TIME	DATE	Timestamp of the next change.
BLOCKS	NUMBER	Size of the archived log in blocks.
BLOCK_SIZE	NUMBER	Redo log block size. This is the logical block size of the archived log, which is the same as the logi- cal block size of the online log from which this archived log was copied. The online log logical block size is a platform-specific value that is not adjustable by the user.
CREATOR	VARCHAR2(7)	Identifies the creator of the archive log.
REGISTRAR	VARCHAR2(7)	Identifies the registrar of the archive log.
STANDBY_DEST	VARCHAR2(3)	Indicates if the entry is an archive log destination.
ARCHIVED	VARCHAR2(3)	Indicates that the online redo was archived or that RMAN only inspected the log and created a record for future application of redo logs during recovery.
APPLIED (Introduced in Oracle 9i)	VARCHAR2(3)	Indicateswhetheror notthearchive log has been applied to its corresponding standby database. YES: Indicates that it has been applied NO:Indicatesithasnotbeenapplied
DELETED	VARCHAR2(3)	Specifies whether an RMAN delete command has physically deleted the archived log file from disk, as well as logically removing it from the control of the target database and from the recovery catalog.
STATUS (Introduced in Oracle 9i)	VARCHAR2(1)	The status of this archived log. Possible values are: A: Available D: Deleted U: Unavailable X: Expired
COMPLETION_TIME	DATE	Time when the archiving was completed.
DICTIONARY_BEGIN (Introduced in Oracle 9i)	VARCHAR2(3)	Indicates whether or not this log contains the start of a LogMiner dictionary. The valid values are: YES: This log contains the start of a LogMiner dictionary NO: This log does not contain the start of a LogMiner dictionary
DICTIONARY_END (Introduced in Oracle 9i)	VARCHAR2(3)	Indicates whether or not this log contains the end of a LogMiner dictionary. The valid values are: YES: This log contains the end of a LogMiner dictionary NO: This log does not contain the end of a LogMiner dictionary
END_OF_REDO (Introduced in Oracle 9i)	VARCHAR2(3)	Indicates whether or not this archived redo log contains the end of all redo information from the primary database. The valid values are: YES: This log contains the end of all redo information NO: This log does not contain the end of all redo information
BACKUP_COUNT (Introduced in Oracle 9i)	NUMBER	Indicates the number of times that has been backed up. Values range from 0 to 15. If the file has been backed up more than 15 times the value remains.
ARCHIVAL_THREAD# (Introduced in Oracle 9i)	NUMBER	Indicates the redo thread number of the instance that performed the archival operation. This column differs from the THREAD# column only when a closed thread is archived by another instance.
ACTIVIATION# (Introduced in Oracle 9i)	NUMBER	This is used if Oracle Data Guard has been configured and indicates processes to manage any logfile transfer gaps using the FAL_CLIENT and FAL_SERVER parameters on the primary and Data Guard database. It indicates the number assigned to the data- base instantiation.
IS_RECOVERY_DEST_FILE (Introduced in Oracle 10g)	VARCHAR2 (3)	Indicates whether the file was created in the recovery area destination (YES) or not (NO)
COMPRESSED (Introduced in Oracle 10g)	VARCHAR2 (3)	Indicates whether the archived log is compressed (YES) or not (NO)

GV$ARCHIVE_PROCESSES

This view provides information about the current state of the various archive processes on the cluster. This view would be helpful for debugging or analyzing delays in the archive process.

Based on X$ Table: X$KCRRARCH

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
PROCESS	NUMBER	The identifier for the ARCH process for the instance, numbered 0–9.
STATUS	VARCHAR2(10)	The current status of the ARCH process. This column indicates if the archive process corresponding to the process number has been configured or defined in the parameter file. The possible values for this column are: STOPPED: Never scheduled or configured SCHEDULED: Currently in the scheduled state but not started STARTING: Currently in the process of starting; this is a state after scheduled ACTIVE: Indicates that the specific ARCH process has been configured STOPPING: The archive run is complete and is currently stopping before going back to a scheduled status TERMINATED: ARCH process was termi- nated abnormally either by process crash or by a system failure including instance crash
LOG_SEQUENCE	NUMBER	Indicates the current log sequence number being archived. There are two possible values: The actual log sequence number Zero (indicates that the ARCH process is idle)
STATE	VARCHAR2(4)	This is the current state of the ARCH process. Possible values are: IDLE: ARCH process is pausing before the next schedule BUSY: ARCH process is currently in the middle of archiving

GV$BGPROCESS

This view provides a description of all the background processes used by Oracle on each instance.

Based on X$ Table: X$KSBDP and X$KSBDD

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
PADDR	RAW(4)	Address of the process state object. A raw value in this column indicates that the background process has been configured.
NAME	VARCHAR2(5)	Name of the background process.
DESCRIPTION	VARCHAR2(64)	Description of the background process.
ERROR	NUMBER	Error encountered.

GV$BH

This view specifically contains information pertaining to RAC and provides the status and number of local forced writes and forced reads for every buffer in the buffer cache. It is a very important view and provides critical information used for performance monitoring of the RAC instances.

Based on X$ Tables: X$BH and X$LE

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
FILE#	NUMBER	Data file identifier. The value in this column could be used to join against the DBA_DATA_FILES or V$DBFILES.
BLOCK#	NUMBER	Block number.
CLASS#	NUMBER	Class number.
STATUS	VARCHAR2(1)	Status of the buffer: FREE: Not currently in use XCUR: Exclusive SCUR: Shared current CR: Consistent read READ: Being read from disk MREC: In media recovery mode IREC: In instance recovery mode PI: Past image
XNC	NUMBER	Obsolete in Oracle 9i. Retained for backward compatibility. In 9i this column has no value.
LOCK_ELEMENT_ADDR	RAW(4)	The address of the lock element that is locking this buffer. If two buffers have the same LOCK_ELEMENT_ADDR value lock_element_addr then they are being protected by the same lock.
LOCK_ELEMENT_NAME	NUMBER	The address of the lock element that is locking this buffer.
LOCK_ELEMENT_CLASS	NUMBER	The address of the lock element that is locking this buffer.
FORCED_READS	NUMBER	Number of times the block had to be reread from disk because another instance had forced it out of this lock on this block in lock mode.
FORCED_WRITES	NUMBER	Number of times DBWn had to write this block to disk because this instance had dirtied the block and another instance had requested the lock on the block in conflicting mode.
DIRTY	VARCHAR2(1)	Y indicates that the block was dirtied or modified.
TEMP	VARCHAR2(1)	Y indicates that the block is a temporary block.
PING	VARCHAR2(1)	Y indicates that the block was pinged.
STALE	VARCHAR2(1)	Y indicates that the block is now stale in the current instance.
DIRECT	VARCHAR2(1)	Y indicates that the direct block.
NEW	VARCHAR2(1)	This column has no value in Oracle 9i. Its obsolete.
OBJD	NUMBER	Database object number of the block that the buffer represents.
TS#	NUMBER	Tablespace number that the block belongs to. The value in this column could be joined with the V$TABLESPACE view to determine the actual tablespace the block belongs to.

Columns FORCED_READS and FORCED_WRITES together represent the number of disk I/Os an instance has to perform on each block in the cache due to conflicting lock requests by other instances. These I/Os are wasteful, since they occur only due to lock activity and thus they need to be ignored.

GV$CR_BLOCK_SERVER

This view displays statistics on the block server background process (BSPn) used in cache fusion with RAC.

Based on X$ Table: X$KCLCRST

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
CR_REQUESTS	NUMBER	Number of requests received for a version of a block at a specific SCN.
CURRENT_REQUESTS	NUMBER	Number of requests for the most recent version of a block.
DATA_REQUESTS	NUMBER	Number of current or CR requests for data blocks.
UNDO_REQUESTS	NUMBER	Number of CR requests for undo blocks.
TX_REQUESTS	NUMBER	Number of CR requests for undo segment header blocks.
CURRENT_RESULTS	NUMBER	Number of requests for which no changes were rolled out of the block returned to the requesting instance.
PRIVATE_RESULTS	NUMBER	Number of requests for which changes were rolled out of the block returned to the requesting instance. Only zero-XID transactions can use the block.
ZERO_RESULTS	NUMBER	Number of requests for which changes were rolled out of the block returned to the requesting instance. Only zero-XID transactions can use the block.
DISK_READ_RESULTS	NUMBER	Number of requests for which the requesting instance had to read the requested block from disk.
FAIL_RESULTS	NUMBER	Number of requests that failed; the requesting transaction must reissue the request.
FAIRNESS_DOWN_CONVERTS	NUMBER	Number of times an instance receiving a request has down-converted an X lock on a block because it was not modifying the block.
FAIRNESS_CLEARS	NUMBER	Number of times the ''fairness counter tracks the number of times a block was modified after it was served.
FREE_GC_ELEMENTS	NUMBER	Number of times a request was received from another instance and the X lock had no buffers.
FLUSHES	NUMBER	Number of times the log has been flushed by a BSPn process.
LIGHT_WORKS	NUMBER	Number of times the light-work rule was evoked. This rule prevents the BSP background process from going to disk while responding to CR requests for data, undo, or undo segment header blocks. This rule can prevent the BSPn process from completing its response to the CR request.

GV$CACHE

This is another view that contains performance-related data for RAC. This view contains information from the block header of each block in the SGA of the current instance as related to particular database objects.

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
FILE#	NUMBER	Data file identifier. The value in this column could be used to join against the DBA_DATA_FILES or V$DBFILES.
BLOCK#	NUMBER	Block number.
CLASS#	NUMBER	The class number.
STATUS	VARCHAR2(1)	Status of the buffer: FREE: Not currently in use XCUR: Exclusive SCUR: Shared current CR: Consistent read READ: Being read from disk MREC: In media recovery mode IREC: In instance recovery mode PI: Past image
XNC	NUMBER	Obsolete in Oracle 9i. Retained for backward compatibility. In 9i this column has no value.
FORCED_READS	NUMBER	Number of times the block had to be reread from disk because another instance had forced it out of this requesting the lock on this block in lock mode.
FORCED_WRITES	NUMBER	Number of times DBWR had to write this block to disk because this instance had dirtied the block and another instance had requested the lock on the block in conflicting mode.
NAME	VARCHAR2(30)	Name of the database object containing the block.
PARTITION_NAME	VARCHAR2(30)	The name of the partition; NULL for non-partitioned objects.
KIND	VARCHAR2(12)	Type of database object. The column contains the following potential values: 1: INDEX 2: TABLE 3: CLUSTER 4: VIEW 5: SYNONYM 6: SEQUENCE 7: PROCEDURE 8: FUNCTION 9: PACKAGE 10: NONEXISTENT 11: PACKAGE BODY 12: TRIGGER 13: TYPE 14: TYPE BODY 19: TABLE PARTITION 20: INDEX PARTITION 21: LOB 22: LIBRARY
OWNER#	NUMBER	Owner number.
LOCK_ELEMENT_ADDR	RAW(4)	The address of the lock element that is locking this buffer. If two buffers have the same LOCK_ELEMENT_ADDR value then the same lock is protecting them.
LOCK_ELEMENT_NAME	NUMBER	The address of the lock element that is locking this buffer.

GV$CACHE_LOCK

This is a RAC view and contains information similar to that found in the GV$CACHE view; however, it contains platform-specific lock manager identifiers. This information may be useful if the platform-specific lock manager provides tools for monitoring the PCM lock operations that are occurring. For example, first query to find the lock element address using INDX and CLASS, then query GV$BH to find the buffers that are covered by the lock.

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
FILE#	NUMBER	Block number.
STATUS	VARCHAR2(4)	Status of block: FREE: Not currently in use XCUR: Exclusive SCUR: Shared current CR: Consistent read READ: Being read from disk MREC: In media recovery mode IREC: In instance recovery mode
XNC	NUMBER	Number of parallel cache management PCM lock conver- sions due to contention with another instance.
NAME	VARCHAR2(30)	Name of the database object containing the block.
KIND	VARCHAR2(12)	Type of database object: 1: INDEX 2: TABLE 3: CLUSTER 4: VIEW 5: SYNONYM 6: SEQUENCE 7: PROCEDURE 8: FUNCTION 9: PACKAGE 10: NONEXISTENT 11: PACKAGE BODY 12: TRIGGER 13: TYPE 14: TYPE BODY 19: TABLE PARTITION 20: INDEX PARTITION 21: LOB 22: LIBRARY Null: UNKNOWN
OWNER#	NUMBER	Owner number.
LOCK_ELEMENT_ADDR	RAW(4)	The address of the lock element that contains the PCM lock that is covering the buffer. If two or more buffers have the same address, then these buf- fers are covered by the same PCM lock.
LOCK_ELEMENT_NAME	NUMBER	The address of the lock element that contains the PCM lock that is covering the buffer. If two or more buffers have the same address, then these buffers are covered by the same PCM lock.
FORCED_READS	NUMBER	Number of times the block had to be reread from disk because another instance had forced it out of this cache by requesting the lock on this block in lock mode.
FORCED_WRITES	NUMBER	Number of times DBWR had to write this block to disk because this instance had dirtied the block and another instance had requested the lock on the block in conflicting mode.
INDX	NUMBER	Platform-specific lock manager identifier.
CLASS	NUMBER	Platform-specific lock manager identifier.

GV$CACHE_TRANSFER

This is a RAC-specific view. This view is also identical to the GV$CACHE view; however, it only displays blocks that have been pinged at least once. It contains information from the block header of each block in the SGA of the current instance as related to particular database objects.

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
FILE#	NUMBER	Data file identifier. The value in this column could be used to join against the DBA_DATA_FILES or V$DBFILES.
BLOCK#	NUMBER	Block number.
CLASS#	NUMBER	Class number.
STATUS	VARCHAR2(1)	Status of the buffer: FREE: Not currently in use XCUR: Exclusive SCUR: Shared current CR: Consistent read READ: Being read from disk MREC: In media recovery mode IREC: In instance recovery mode PI: Past image
XNC	NUMBER	Obsolete in Oracle 9i. Retained for backward compatibility. In 9i this column has no value.
FORCED_READS	NUMBER	Number of times the block had to be reread from disk because another instance had forced it out of this requesting the lock on this block in lock mode.
FORCED_WRITES	NUMBER	Number of times DBWn had to write this block to disk because this instance had dirtied the block and another instance had requested the lock on the block in conflicting mode.
NAME	VARCHAR2(30)	Name of the database object containing the block.
PARTITION_NAME	VARCHAR2(30)	The name of the partition; NULL for non-partitioned objects.
KIND	VARCHAR2(12)	Type of database object. The column contains the following potential values: 1: INDEX 2: TABLE 3: CLUSTER 4: VIEW 5: SYNONYM 6: SEQUENCE 7: PROCEDURE 8: FUNCTION 9: PACKAGE 10: NONEXISTENT 11: PACKAGE BODY 12: TRIGGER 13: TYPE 14: TYPE BODY 19: TABLE PARTITION 20: INDEX PARTITION 21: LOB 22: LIBRARY
OWNER#	NUMBER	Owner number.
GC_ELEMENT_ADDR	RAW(4)	The address of the lock element that is locking this buffer. If two buffers have the same LOCK_ELEMENT_ADDR value then the same lock is protecting them.
GC_ELEMENT_NAME	NUMBER	The address of the lock element that is locking this buffer.

GV$DB_CACHE_ADVICE

This view contains rows that predict the number of physical reads for the cache size corresponding to each row. The rows also compute a ''physical read factor,'' which is the ratio of the number of estimated reads to the number of reads actually performed by the real buffer cache during the measurement interval.

Based on X$ Tables: X$KCBSC and X$KCBWBPD

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the para- meter file when the instance was first created.
ID	NUMBER	Buffer pool identifier; values range from 1 to 8.
NAME	VARCHAR2(20)	Buffer pool name.
BLOCK_SIZE	NUMBER	Block size in bytes for buf- fers in this pool. Possible values: the standard block size, the power of 2 non- standard block size, 2048, 4096, 8192, 16,384, 32,768.
ADVICE_STATUS	VARCHAR2(3)	Status of the advisory: ON indicates it is currently running OFF indicates it is disabled
SIZE_FOR_ESTIMATE	NUMBER	Cache size for predication (in megabytes).
BUFFERS_FOR_ESTIMATE	NUMBER	Cache size for prediction (in terms of buffers).
EST_PHYSICAL_READ_FACTOR	NUMBER	Physical read factor for this cache size, which is the ratio of the number of estimated physical reads to the number of reads in the real cache. If there are no physical reads in the real cache, the value of this column is null.
ESTD_PHYSICAL_READS NUMBER		Estimated number of physi- cal reads for this cache size.

New Feature

This view is new with Oracle Version 9.1.0 and is populated only if the DB_CACHE parameter is used instead of the DB_BLOCK_BUFFERS parameter and the DB_CACHE_ADVICE parameter is enabled.

GV$DB_OBJECT_CACHE

This view displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers.

Based on X$ Table: X$KGLOB

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
OWNER	VARCHAR2(64)	Owner of the object.
NAME	VARCHAR2(1000)	Name of the object.
DB_LINE	VARCHAR2(64)	Database link name.
NAMESPACE	VARCHAR2(28)	Library cache namespace of the object: TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT
TYPE	VARCHAR2(28)	Type of the object: INDEX, TABLE CLUSTER VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK
SHARABLE_MEM	NUMBER	Amount of sharable memory in the shared pool consumed by the object.
LOADS	NUMBER	Number of times the object has been loaded. This count also increases when an object has been invalidated.
EXECUTIONS	NUMBER	Execution counts, column not used.
LOCKS	NUMBER	Number of users currently locking this object.
PINS	NUMBER	Number of users currently pinning this object.
KEPT	VARCHAR2(3)	Indicates if the object has been kept with the package DBMS_SHARED_POOL_KEEP. Valid values are: YES: Has been kept NO: Has not been kept
CHILD_LATCH	NUMBER	Child latch number that is protecting the object.

GV$ENQUEUE_LOCK

This view describes the locks pertaining to enqueue state objects. You will notice that the columns in this view are identical to the columns in GV$LOCK view; however, the contents vary. While GV$LOCK describes locks held by the Oracle server and outstanding requests for a lock or latch, the GV$ENQUEUE_LOCK describes locks pertaining to enqueue state objects.

Based on X$ Tables: X$KSQEQ, X$KSUSE, and X$KSQRS

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
ADDR	RAW(4)	Address of lock state object.
KADDR	RAW(4)	Address of lock.
SID	NUMBER	Identifier for session holding or acquir- ing the lock.
TYPE	VARCHAR2(2)	Type of lock. Lists users and system types that can have locks.
ID1	NUMBER	Lock identifier #1 (depends on type).
ID2	NUMBER	Lock identifier #2 (depends on type).
LMODE	NUMBER	Lock mode in which the session holds the lock: 0: None 1: 1: Null (NULL) 2: Row S (SS) 3: Row X (SX) 4: Share (S) 5: S/Row: X (SSX) 6: Exclusive (X)
REQUEST	NUMBER	Lock mode in which the process requests the lock: 0: None 1: Null (NULL) 2: Row S (SS) 3: Row X (SX) 4: Share (S) 5: S/Row: X (SSX) 6: Exclusive (X)
CTIME	NUMBER	Time since current mode was granted.
BLOCK	NUMBER	The lock is blocking another lock.

GV$ENQUEUE_STAT

This view displays statistics on the number of enqueue (lock) requests for each type lock.

Based on X$ Table: X$KSQST

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
EQ_TYPE	VARCHAR2(2)	Type of enqueue requested.
TOTAL_REQ#	NUMBER	Total number of enqueue requests or enqueue conversions for this type of enqueue.
TOTAL_WAIT#	NUMBER	Total number of times an enqueue request or conversion resulted in a wait.
SUCC_REQ#	NUMBER	Number of times an enqueue request or conversion was granted.
FAILED_REQ#	NUMBER	Number of times an enqueue request or conversion failed.
CUM_WAIT_TIME	NUMBER	Total number of times (in milliseconds) spent waiting for the enqueue or enqueue conversion.

GV$EVENT_NAME

This view provides a listing of all events and parameter definitions.

Based on X$ Table: X$KSLED

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
EVENT#	NUMBER	The number of the wait event.
NAME	VARCHAR2(64)	The name of the event.
PARAMETER1	VARCHAR2(64)	The description of the first parameter for the wait event.
PARAMETER2	VARCHAR2(64)	The description of the second parameter for wait event.
PARAMETER3	VARCHAR2(64)	The description of the third parameter for the wait event.
CLASS# (Introduced in Oracle 10g)	NUMBER	Number of the class of the wait event
CLASS (Introduced in Oracle 10g)	VARCHAR2 (64)	Name of the class of the wait event

Note

Details about the various events names and the respective values for the various parameter columns can be found in the Oracle reference manual.

GV$FALSE_PING

This view is for RAC implementations and displays buffers that may be getting false pings. Buffers identified as getting false pings can be remapped in GC_FILES_TO_LOCKS to reduce lock collisions.

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
FILE#	NUMBER	Data file identifier number. The value in this column could be used to join against the V$DATA_FILE to get the name of the file.
BLOCK#	NUMBER	Block number.
STATUS	VARCHAR2(1)	Status of block: FREE: Not currently in use XCUR: Exclusive SCUR: Shared current CR: Consistent read READ: Being read from disk MREC: In media recovery mode IREC: In instance recovery mode
XNC	NUMBER	Number of PCM lock conversion from Exclusive mode due to contention with another instance. This column is obsolete but is retained for his- torical compatibility.
FORCED_READS	NUMBER	Number of times the block had to reread from disk because another instance had forced it out of this requesting the PCM lock on the block in exclusive mode.
FORCED_WRITES	NUMBER	Number of times DBWR had to write this block to disk because this instance had dirtied the block and another instance had requested the lock on the block in conflicting mode.
NAME	VARCHAR2(30)	Name of the database object containing the block.
PARTITION_NAME	VARCHAR2(30)	The name of the partition; NULL for non-partitioned objects.
KIND	VARCHAR2(12)	Type of database object. The column contains the following potential values: 1: INDEX 2: TABLE 3: CLUSTER 4: VIEW 5: SYNONYM 6: SEQUENCE 7: PROCEDURE 8: FUNCTION 9: PACKAGE 10: NONEXISTENT 11: PACKAGE BODY 12: TRIGGER 13: TYPE 14: TYPE BODY 19: TABLE PARTITION 20: INDEX PARTITION 21: LOB 22: LIBRARY
OWNER#	NUMBER	Owner number.
LOCK_ELEMENT_ADDR	RAW(4)	The address of the lock element that is locking this buffer. If two buffers have the same LOCK_ELEMENT_ADDR value then the same lock is protecting them.
LOCK_ELEMENT_NAME	NUMBER	The address of the lock element that is locking this buffer.

Note

In a RAC environment the parameter GC_FILES_TO_LOCKS should not be used. If this parameter is configured, the cache fusion technology available in RAC is disabled and the OPS behavior is invoked. This means transfer of blocks will happen using the pinging mechanism.

GV$FILE_CACHE_TRANSFER

A very useful view in a RAC environment that displays the number of blocks pinged per data file. This information in turn can be used to determine access patterns to existing data files and decide new mappings from data file blocks to PCM locks.

Based on X$ Tables: X$KCFIO and X$KCCFE

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
FILE_NUMBER	NUMBER	Number of the data file.
X_2_NULL	NUMBER	Number of lock conversions from Exclusive to NULL for all blocks in the file.
X_2_NULL_FORCED_WRITE	NUMBER	Number of forced writes that occur for blocks of the specified file due to Exclusive-to-NULL conversions.
X_2_NULL_FORCED_STATE	NUMBER	Number of times a block in the file was made STALE due to Exclusive-to-NULL conversions.
X_2_S	NUMBER	Number of lock conversions from Exclusive to Shared for all blocks in the file
X_2_S_FORCED_WRITES	NUMBER	Number of forced writes that occur for blocks of the specified file due to Exclusive-to-Shared conversion.
S_2_NULL	NUMBER	Number of lock conversion from Shared to NULL for all blocks in the file.
S_2_NULL_FORCED_STALE	NUMBER	Number of times a block in the file was made STALE due to Shared- to-NULL conversions.
RBR	NUMBER	Number of times the instance received a reuse block range cross-instance call for this file.
RBR_FORCED_WRITE	NUMBER	Number of blocks written due to reuse of block range cross- instance calls for this file.
RBR_FORCED_STALE	NUMBER	Number of times a block in this file was made STALE due to reuse of block range cross- instance calls.
NULL_2_K	NUMBER	Number of lock conversions from NULL to Exclusive for all blocks of the specified file.
S_2_X	NUMBER	Number of lock conversions from Shared to Exclusive for all blocks of the specified file.
NULL_2_S	NUMBER	Number of lock conversions from NULL to Shared for all blocks of the specified file.

GV$GCSHVMASTER_INFO

Provides information regarding cache fusion in a RAC environment. It describes the current and previous master instances and the number of times GCS resources have been remastered, except those belonging to files mapped to a particular master.

Based on X$ Table: X$KJDRPCMHV

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
HV_ID	NUMBER	PCM hash value ID.
CURRENT_MASTER	NUMBER	Master instance of this PCM hash value ID.
PREVIOUS_MASTER	NUMBER	Previous master instance of this PCM hash value ID.
REMASTER_CNT	NUMBER	Number of times this has been remastered.

GV$GCSPFMASTER_INFO

This view is for RAC and describes the current and previous master instances and the number of times GCS resources belonging to files mapped to instances have been remastered.

Based on X$ Table: X$KJDRPCMPF

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
FILE_ID	NUMBER	File number.
CURRENT_MASTER	NUMBER	Master instance of this file.
PREVIOUS_MASTER	NUMBER	Previous master instance of this file.
REMASTER_CNT	NUMBER	Number of times this has been remastered.

GV$GC_ELEMENT

This view is used in RAC. It contains entries for each PCM lock that is used by the buffer cache. The name of the PCM lock that corresponds to a lock element is (''BL,''indx,class).

Based on X$ Table: X$LE

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
GC_ELEMENT_ADDR	RAW(4)	Address of the lock element that contains the PCM lock that is cov- ering the buffer. If two or more buffers have the same address, then these buffers are covered by the same PCM lock.
INDX	NUMBER	Platform-specific lock manager identifier.
CLASS	NUMBER	Platform-specific lock manager identifier.
GC_ELEMENT_NAME	NUMBER	Name of the lock that contains the PCM lock that is covering the buffer.
MODE_HELD	NUMBER	Platform-dependent value for lock mode held, often 3 = share and 5 = exclusive.
BLOCK_COUNT	NUMBER	Number of blocks covered by PCM lock.
RELEASING	NUMBER	Nonzero if PCM lock is being downgraded.
ACQUIRING	NUMBER	Nonzero if PCM lock is being upgraded.
INVALID	NUMBER	Nonzero if PCM lock is invalid.
FLAGS	NUMBER	Process level flags for the lock element.

GV$GC_ELEMENTS_WITH_COLLISIONS

This view is specific to a RAC implementation. This view helps identify the locks that protect multiple buffers, each of which has been either force-written or force-read at least 10 times.

Based on V$ View: V$BH

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
GC_ELEMENT_ADDR	RAW(4)	Address of the lock element that con- tains the PCM lock covering the buffer. If two or more buffers have the same address, then these buffers are covered by the same PCM lock.

GV$GES_BLOCKING_ENQUEUE

This is a RAC-specific view and provides information on all locks currently known to the lock manager that are being blocked or blocking others. The output of this view is a subset of the output from GV$GES_ENQUEUE.

Based on V$ View: V$GES_ENQUEUE

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the para- meter file when the instance was first created.
HANDLE	RAW(4)	Lock pointer.
GRANT_LEVEL	VARCHAR2(9)	Granted level of the lock.
REQUEST_LEVEL	VARCHAR2(9)	Requested level of the lock.
RESOURCE_NAME1	VARCHAR2(30)	Resource name for the lock.
RESOURCE_NAME2	VARCHAR2(30)	Resource name for the lock.
PID	NUMBER	Process identifier which holds the lock.
TRANSACTION_ID0	NUMBER	Lower 4 bytes of the transac- tion identifier to which the lock belongs.
TRANSACTION_ID1	NUMBER	Upper 4 bytes of the transac- tion identifier to which the lock belongs.
GROUP_ID	NUMBER	Group identifier for the lock.
OPEN_OPT_DEADLOCK	NUMBER	1 if deadlock open option is set, otherwise 0.
OPEN_OPT_PERSISTENT	NUMBER	1 if persistent open option is set, otherwise 0.
OPEN_OPT_PROCESS_OWNED NUMBER	NUMBER	1 if process_owned open option is set, otherwise 0.
OPEN_OPT_NO_XID	NUMBER	1 if NO_XID open option is set, otherwise 0.
CONVERT_OPT_GETVALUE	NUMBER	1 if GETVALUE convert option is set, otherwise 0.
CONVERT_OPT_PUTVALUE	NUMBER	1 if PUTVALUE convert option is set, otherwise 0.
CONVERT_OPT_NOVALUE	NUMBER	1 if NOVALUE convert option is set, otherwise 0.
CONVERT_OPT_DUBVALUE	NUMBER	1 if DUBVALUE convert option is set, otherwise 0.
CONVERT_OPT_NOQUEUE	NUMBER	1 if NOQUEUE convert option is set, otherwise 0.
CONVERT_OPT_EXPRESS	NUMBER	1 if EXPRESS convert option is set, otherwise 0.
CONVERT_OPT_NODEADLOCKWAIT	NUMBER	1 if NODEADLOCKWAIT convert option is set, other- wise 0.
CONVERT_OPT_NODEADLOCKBLOCK	NUMBER	1 if NODEADLOCKBLOCK convert option is set, otherwise 0.
WHICH_QUEUE	NUMBER	In which queue the lock is currently located: 0 for NULL queue 1 for GRANTED queue 2 for CONVERT queue
STATE	VARCHAR2(64)	State of lock as owner sees it.
AST_EVENTO	NUMBER	Last AST event.
OWNER_NODE	NUMBER	Node identifier.
BLOCKED	NUMBER	1 if the lock request is blocked by others, other- wise 0.
BLOCKER	NUMBER	1 if this lock is blocking others, otherwise 0.

GV$GES_CONVERT_LOCAL

This view provides information regarding average convert time, count information, and timed statistics for local GES enqueue operations.

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
CONVERT_TYPE	VARCHAR2(64)	Conversion type, for example: 1: CREATE TABLE 2: INSERT 3: SELECT . . . 12: DROP TABLE 13: CREATE SEQUENCE . . . 97: CREATE PACKAGE BODY 98: ALTER PACKAGE BODY 99: DROP PACKAGE BODY
AVERAGE_CONVERT_TIME	NUMBER	Average conversion time for each type of lock operation. The value is displayed in hundredths of a second.
CONVERT_COUNT	NUMBER	The number of operations.

GV$GES_CONVERT_REMOTE

This view displays the average convert time, count information, and timed statistics for remote GES enqueue operations.

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
CONVERT_TYPE	VARCHAR2(64)	Conversion types (for a listing of the conversion types refer to Table 9.1). Average conversion time for each type of lock operation. The value is displayed in hundredths of a second.
AVERAGE_CONVERT_TIME	NUMBER	Average conversion time for each type of lock operation. The value is displayed in hundredths of a second.
CONVERT_COUNT	NUMBER	The number of operations.

Table 9.1: GES Conversion Types

Type	Description
NULL->SS	NULL mode to subshared mode
NULL->SX	NULL mode to shared exclusive mode
NULL-> S	NULL mode to shared mode
NULL->SSX	NULL mode to subshared exclusive mode
NULL->X	NULL mode to exclusive mode
SS->SX	Subshared mode to shared exclusive mode
SS->X	NULL mode to exclusive mode
SS->SX	Subshared mode to shared exclusive mode
SS->S	Subshared mode to shared mode
SS->SSX	Subshared mode to subshared exclusive mode
SS->.X	Subshared mode to exclusive mode
SX->S	Shared exclusive mode to shared mode
SX->SSX	Shared exclusive mode to subshared exclusive mode
SX->X	Shared exclusive mode to exclusive mode
S->SX	Shared mode to shared exclusive mode
S->SSX	Shared mode to subshared exclusive mode
S->X	Shared mode to exclusive mode
SSX->X	Subshared exclusive mode to exclusive mode

GV$GES_ENQUEUE

This is a RAC view and describes all locks currently known to the lock manager.

Based on X$ Table: X$KJILKFT

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
HANDLE	RAW(4)	Lock pointer.
GRANT_LEVEL	VARCHAR2(9)	Granted level of the lock.
REQUEST_LEVEL	VARCHAR2(9)	Requested level of the lock.
RESOURCE_NAME1	VARCHAR2(30)	Resource name for the lock.
RESOURCE_NAME2	VARCHAR2(30)	Resource name for the lock.
PID	NUMBER	Process identifier which holds the lock.
TRANSACTION_ID0	NUMBER	Lower 4 bytes of the trans- action identifier to which the lock belongs.
TRANSACTION_ID1	NUMBER	Upper 4 bytes of the trans- action identifier to which the lock belongs.
GROUP_ID	NUMBER	Group identifier for the lock.
OPEN_OPT_DEADLOCK	NUMBER	1 if DEADLOCK open option is set, otherwise 0.
OPEN_OPT_PERSISTENT	NUMBER	1 if PERSISTENT open option is set, otherwise 0.
OPEN_OPT_PROCESS_OWNED	NUMBER	1 if PROCESS_OWNED open option is set, other- wise 0.
OPEN_OPT_NO_XID	NUMBER	1 if NO_XID open option is set, otherwise 0.
CONVERT_OPT_GETVALUE	NUMBER	1 if GETVALUE convert option is set, otherwise 0.
CONVERT_OPT_PUTVALUE	NUMBER	1 if PUTVALUE convert option is set otherwise 0.
CONVERT_OPT_NOVALUE	NUMBER	1 if NOVALUE convert option is set otherwise 0.
CONVERT_OPT_DUBVALUE	NUMBER	1 if DUBVALUE convert option is set otherwise 0.
CONVERT_OPT_NOQUEUE	NUMBER	1 if NOQUEUE convert option is set otherwise 0.
CONVERT_OPT_EXPRESS	NUMBER	1 if EXPRESS convert option is set otherwise 0.
CONVERT_OPT_NODEADLOCKWAIT	NUMBER	1 if NODEADLOCKWAIT convert option is set, otherwise 0.
CONVERT_OPT_NODEADLOCKBLOCK	NUMBER	1 if NODEADLOCK-BLOCK convert option is set, otherwise 0.
WHICH_QUEUE	NUMBER	In which queue the lock is currently located: 0 for NULL queue 1 for GRANTED queue 2 for CONVERT queue
STATE	VARCHAR2 (64)	State of lock as owner sees it.
AST_EVENTO	NUMBER	Last AST event.
OWNER_NODE	NUMBER	Node identifier.
BLOCKED	NUMBER	1 if the lock request is blocked by others, other- wise 0.
BLOCKER	NUMBER	1 if this lock is blocking others, otherwise 0.

GV$GES_RESOURCE

This view is present in RAC and displays information of all resources currently known to the lock manager.

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
RESP	RAW(4)	Resource pointer.
RESOURCE_NAME	VARCHAR2(30)	Resource name in hexadecimal for the lock.
ON_COVERT_Q	NUMBER	1 if no convert queue, 0 otherwise.
ON_GRANT_Q	NUMBER	1 if no granted queue, 0 otherwise.
PERSISTENT_RES	NUMBER	1 if it is a persistent resource, 0 other- wise.
RDOMAIN_NAME	VARCHAR2(25)	Recovery domain name.
RDOMAINP	RAW(4)	Recovery domain pointer.
MASTER_NODE	NUMBER	Master node ID.
NEXT_CVT_LEVEL	VARCHAR2(9)	Next lock level to convert on global convert queue.
VALUE_BLK_STATE	VARCHAR2(32)	State of the value block.
VALUE_BLK	VARCHAR2(64)	First 64 bytes of the value block.

GV$GES_STATISTICS

This is present in RAC and displays miscellaneous GES statistics.

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
STATISTIC#	NUMBER	Statistic number.
NAME	VARCHAR2(64)	Name of the statistic.
VALUE	NUMBER	Value associated with the statistic.

GV$HVMASTER_INFO

TThis is a RAC only view and describes the current and previous master instances and the number of times that GES resources have been remastered. This view is used to monitor the remastering of instances during instance failures and when the resources held by the instance are distributed to the other available instances.

Based on X$ Table: X$KJDRHV

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
HV_ID	NUMBER	Hash value ID.
CURRENT_MASTER	NUMBER	Master instance of this hash value ID.
PREVIOUS_MASTER	NUMBER	Previous master of this hash value ID.
REMASTER_CNT	NUMBER	Number of times this has been remastered.

GV$MTTR_TARGET_ADVICE

This view is introduced in Oracle 9i. It contains rows that predict the number of physical I/Os for the MTTR corresponding to each row. The rows also compute a physical I/O factor, which is the ratio of the number of estimated I/Os to the number of I/Os actually performed by the current MTTR setting during the measurement interval.

Based on X$ Table: X$KCBMMAV

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first cre- ated.
MTTR_TARGET_FOR_ESTIMATE	NUMBER	MTTR setting being simulated. Equal to the current MTTR sett- ing if this is the first row of the view.
ADVICE_STATUS	VARCHAR2(5)	Current status of MTTR simula- tion: ON: Simulation is on READY: Ready for simulation OFF: Simulation is off
DIRTY_LIMIT	NUMBER	Dirty buffer limit derived from the MTTR being simulated.
ESTD_CACHE_WRITES	NUMBER	Estimated number of cache phy- sical writes under this MTTR.
ESTD_CACHE_WRITE_FACTOR	NUMBER	Estimated cache physical write ratio under this MTTR. It is the ratio of the estimated number of cache writes to the number of cache writes under the current MTTR setting.
ESTD_TOTAL_WRITES	NUMBER	Estimated total number of physi- cal writes under this MTTR.
ESTD_TOTAL_WRITE_FACTOR	NUMBER	Estimated total physical write ratio under this MTTR. It is the ratio of the estimated total num- ber of physical writes to the total number of physical writes under the current MTTR setting.
ESTD_TOTAL_IOS	NUMBER	Estimated total number of I/Os under this MTTR.
ESTD_TOTAL_IO_FACTOR	NUMBER	Estimated total I/O ratio under this MTTR. It is the ratio of the estimated total number of I/Os to the total number of I/Os under the current MTTR setting.

GV$PGASTAT

This view provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled. This view contains cumulative values since instance startup.

Based on X$ Table: X$QESMMSGA

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
NAME	VARCHAR2(64)	Name of the statistic.
VALUE	NUMBER	Statistic value.
UNITS	VARCHAR2(12)	Unit for the value. Depending on the type of statistics the units could be either in microseconds, bytes, or percent.

GV$PGA_TARGET_ADVICE

This view is introduced in Oracle 9i to support the usage of a new parameter called PGA_AGGREGATE_TARGET. This view predicts how the cache hit percentage and overallocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE TARGET parameter selected around its current value. The advice statistics are generated by stimulating the past workload run by the instance.

Based on X$ Table: X$QESMMAHIST

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
PGA_TARGET_FOR_ESTIMATE	NUMBER	Value of PGA_AGGREGATE_TARGET for this predication (in bytes).
PGA_TARGET_FACTOR	NUMBER	PGA_TARGET_FOR_ESTIMATE/ the current value of the PGA_AGGREGATE_TARGET parameter.
ADVICE_STATUS	VARCHAR2(3)	Indicates whether the advice is enabled or disabled depending on the value of the STATISTICS_LEVEL parameter.
BYTES_PROCESSED	NUMBER	Total bytes processed by all the work areas considered by this advice.
ESTD_EXTRA_BYTES_RW	NUMBER	Estimated number of extra bytes that would be read or written if PGA_AGGREGATE_TARGET was not set to the value of the PGA_TARGET_FOR_ESTIMATE column. This number is derived from the estimated number and size of work areas which would run in one-pass for the value of PGA_AGGREGATE_TARGET.
ESTD_PGA_CACHE_HIT_PERCENTAGE	NUMBER	Estimated value of the cache hit percentage statistic when PGA_AGGREGATE_TARGET equals PGA_TARGET_FOR_ESTIMATE. This column is derived from the above two columns and is equal to: BYTES PROCESSED/(BYTES_PROCESSED + ESTD_EXTRA_BYTES_RW)
ESTD_OVERALLOC_COUNT	NUMBER	Estimated number of PGA memory overallocations if the value of PGA_AGGREGATE_TARGET is set to PGA_TARGET_FOR_ESTIMATE column. A nonzero value means that the PGA_TARGET_FOR_ESTIMATE is not large enough to run the work area workload. Hence, the DBA should not set PGA_AGGREGATE_TARGET to PGA_TARGET_FOR_ESTIMATE column, since Oracle will not be able to honor that target.

GV$SEGMENT_STATISTICS

Displays information about segment level statistics. This view is new in Oracle 9i Release 2 and contains good information that will allow easy tuning of the tablespaces based on issues.

Based on X$ Tables: OBJ$, USER$, X$KSOLSFTS, TS$

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
OWNER	VARCHAR2(30)	Owner of the object.
OBJECT_NAME	VARCHAR2(30)	Name of the object.
SUBOBJECT_NAME	VARCHAR2(30)	Name of the subobject.
TABLESPACE_NAME	VARCHAR2(30)	Name of the tablespace to which the object belongs.
TS#	NUMBER	Tablespace number.
OBJ#	NUMBER	Dictionary object number of the object.
DATAOBJ#	NUMBER	Data object number of the object.
OBJECT_TYPE	VARCHAR2(18)	Type of the object.
STATISTIC_NAME	VARCHAR2(64)	Name of the statistic.
STATISTIC#	NUMBER	Statistic number.
VALUE	NUMBER	Statistic value.

GV$SESSION_WAIT

This view lists the resources or events for which active sessions are waiting.

Based on X$ Tables: X$KSUSECST and X$KSLED

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
SID	NUMBER	Session identifier.
SEQ#	NUMBER	Sequence number that uniquely identifies the wait. Incremented for each wait.
EVENT	VARCHAR2(64)	Resource or event for which the session is waiting.
P1TEXT	VARCHAR2	Description of first additional parameter.
P1	NUMBER	First additional parameter.
P1RAW	RAW(4)	First additional parameter. Same as P1, displayed in hexa- decimal.
P2TEXT	VARCHAR2	Description of second parameter.
P2	NUMBER	Second additional parameter.
P2RAW	RAW(4)	Second additional parameter. Same as P2, displayed in hexa- decimal.
P3TEXT	VARCHAR2	Description of third parameter.
P3	NUMBER	Third additional parameter.
P3RAW	RAW(4)	Third additional parameter. Same as P3 displayed in hexi- decimal.
WAIT_CLASS# (Introduced in Oracle 10g)	NUMBER	Wait class number
WAIT_CLASS (Introduced in Oracle 10g)	VARCHAR2(64)	Name of the wait class
WAIT_TIME (Introduced in Oracle 10g)	NUMBER	A nonzero value is the last wait time. A zero value indicates that session is cur- rently active.
WAIT_TIME	NUMBER	A nonzero value is the last wait time. A zero value means the session is currently waiting.
SECONDS_IN_WAITSTATE	NUMBER VARCHAR2	The number of seconds in wait event. Wait state: 0: waiting 1: waited for short time 2: waited for unknown time > 0: waited for a known time

GV$SESSTAT

This view shows cumulative user session-wide statistics since the beginning of each session. The data in this view only remains for the life of the session, that is, when the session completes, at which time the data pertaining to the session is cleared from this view.

Based on X$ Table: X$KSUSESTA

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
SID	NUMBER	Session identifier.
STATISTIC#	NUMBER	Statistic number.
VALUE	NUMBER	Statistic value.

Note

Statistics numbers are not guaranteed to remain constant from one release to another. It is therefore advised that the statistics name is used in place of the statistics number.

GV$SHARED_POOL_ADVICE

This view displays information about estimated parse time savings in the shared pool for different sizes. The sizes range from 50% to 200% of the current shared pool size, in equal intervals. The value of the interval depends on the current size of the shared pool.

Based on X$ Tables: X$KSMSPR and X$KGHLU

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instanceviatheparameterfilewhen the instance was first created.
SHARED_POOL_SIZE_FOR_ESTIMATE	NUMBER	Shared pool size for the estimate. The value is displayed in mega- bytes.
SHARED_POOL_SIZE_FACTOR	NUMBER	Size factor with respect to the current shared pool size.
ESTD_LC_SIZE	NUMBER	Estimated memory in use by the library cache. This value is displayed in megabytes.
ESTD_LC_MEMORY_OBJECTS	NUMBER	Estimated number of library cache memory objects in the shared pool of the specified size.
ESTD_LC_TIME_SAVED	NUMBER	Estimated elapsed parse time saved owing to library cache memory objects being found in a shared pool of the specified size. This is the time that would have been spent in reloading the required objects in the shared pool had they been aged out due to insufficient amount of available free memory.
ESTD_LC_TIME_SAVED_FACTOR	NUMBER	Estimated parse time saved factor with respect to the current shared- pool size.
ESTD_LC_MEMORY_OBJECT_HITS	NUMBER	Estimated number of times a library cache memory object was found in a shared pool of the spe- cified size.

GV$SYSSTAT

This view provides the system statistics information of all instances. The description of the statistic associated with each statistic number could be obtained from V$STATNAME view.

Based on X$ Table: X$KSUSGSTA

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
STATISTIC#	NUMBER	Statistic number.
NAME	VARCHAR2(64)	Statistic name.
CLASS	NUMBER	Represents one or more statistics class. The following classes are additive: 1: User 2: Redo 3: Enqueue 4: Cache 16: O/S 32: Parallel server 64: SQL 128: Debug
VALUE	NUMBER	Statistic value.

GV$SYSTEM_EVENT

This view contains information on total waits for an event since the instance was started. The data is maintained at the system level for the duration that the instances are up.

Based on X$ Tables: X$KSLEI and X$KSLED

Column	Datatype	Description
INST_ID	NUMBER	A unique number assigned to the instance via the parameter file when the instance was first created.
EVENT	VARCHAR2(64)	The name of the wait event.
TOTAL_WAITS	NUMBER	Thetotalnumberofwaitsfortheevent.
TOTAL_TIMEOUTS	NUMBER	The total number of timeouts for this event.
TIME_WAITED	NUMBER	The total amount of time waited for this event, in hundredths of a second.
AVERAGE_WAIT	NUMBER	The average amount of time waited for this event, in hundredths of a second.

---