Oracle Real Application Clusters
|
| < Day Day Up > |
|
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:
-
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);
-
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 requiredOPTIONAL: 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: ARCnFOREGROUND 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 fileNO: 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: AvailableD: DeletedU: UnavailableX: 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. |
| 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. |
|
| < Day Day Up > |
|