| Oracle7 Server Reference Manual |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Server Reference Manual | Library |
Product |
Contents |
Index |
The following topics are included in this chapter:
Although these tables appear to be regular database tables, they are not. Like ROWIDs and ROWNUMs, these tables may be selected from, but never updated or altered.
The file CATALOG.SQL contains definitions of the views and public synonyms for the dynamic performance tables. You must run CATALOG.SQL to create these views and synonyms.
The dynamic performance tables are used by Server Manager, which is the primary interface for accessing information about system performance.
Suggestion: Once the instance is started, the V$ views are accessible. The database does not have to be mounted or open. One important consequence of this fact is that the V$LOG view can be used to identify log files needed for recovery.
Warning: Information about the dynamic performance tables is presented for completeness only; this information does not imply a commitment to support these tables in the future.
For information on running UTLMONTR.SQL on your system, see your operating system specific Oracle documentation.
For more information, see Oracle7 Server Utilities.
| This Column | Datatype | Represents This |
| FILE# | NUMBER | File identifier |
| MAXEXTEND | NUMBER | Value from the MAXSIZE parameter |
| INC | NUMBER | Value from the NEXT parameter |
| This Column | Datatype | Represents This |
| SID | NUMBER | Session number that is accessing an object |
| OWNER | VARCHAR2 | Owner of the object |
| OBJECT | VARCHAR2 | Name of the object |
| OB_TYP | NUMBER | Type identifier for the object |
| This Column | Datatype | Represents This |
| INST_NUMBER | NUMBER | The instance number |
| INST_NAME | VARCHAR2(60) | The instance name |
| This Column | Datatype | Represents This |
| GROUP# | NUMBER | Log file group number |
| THREAD# | NUMBER | Log file thread number |
| SEQUENCE# | NUMBER | Log file sequence number |
| CURRENT | VARCHAR2 | Archive log currently in use |
| FIRST_CHANGE# | NUMBER | First SCN stored in the current log |
| This Column | Datatype | Represents This |
| FILE# | NUMBER | File identifier |
| STATUS | VARCHAR2 | File status: NOT ACTIVE, ACTIVE (backup in progress), OFFLINE NORMAL, or description of an error |
| CHANGE# | NUMBER | System change number when backup started |
| TIME | VARCHAR2 | Time the backup started |
| This Column | Datatype | Represents This |
| PADDR | RAW(4) | Address of the process state object |
| NAME | VARCHAR2 | Name of this background process |
| DESCRIPTION | VARCHAR2 | Description of the background process |
| ERROR | NUMBER | Error encountered |
This view gives the status and number of pings for every buffer in the SGA.
| This Column | Datatype | Represents This |
| FILE# | NUMBER | Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES) |
| BLOCK | NUMBER | Block number |
| STATUS | VARCHAR2(1) | 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 conversions due to contention with another instance |
| LOCK_ELEMENT_ ADDR | RAW(4) | The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| DIRTY | VARCHAR2(1) | Y = block modified. |
| TEMP | VARCHAR2(1) | Y = temporary block |
| PING | VARCHAR2(1) | Y = block pinged |
| STALE | VARCHAR2(1) | Y = block is stale |
| DIRECT | VARCHAR2(1) | Y = direct block |
| NEW | VARCHAR2(1) | Y = new block |
This view contains information from the block header of each block in the SGA of the current instance as related to particular database objects.
| This Column | Datatype | Represents This |
| FILE# | NUMBER | Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES) |
| 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 conversions due to contention with another instance |
| NAME | VARCHAR2(30) | Name of the database object containing the block |
| KIND | VARCHAR2(12) | Type of database object: TABLE CLUSTER INDEX UNDO = rollback segment |
| 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 more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
For more information, see Oracle7 Parallel Server Concepts & Administration.
| This Column | Datatype | Represents This |
| CIRCUIT | RAW(4) | Circuit address |
| DISPATCHER | RAW(4) | Current dispatcher process address |
| SERVER | RAW(4) | Current server process address |
| WAITER | RAW(4) | Address of server process that is waiting for the (currently busy) circuit to become available |
| SADDR | RAW(4) | Address of session bound to the circuit |
| STATUS | VARCHAR2 | Status of the circuit: BREAK (currently interrupted), EOF (about to be removed), OUTBOUND (an outward link to aremote database), NORMAL (normal circuit into the local database) |
| QUEUE | VARCHAR2 | Queue the circuit is currently on: COMMON (on the common queue, waiting to be picked up by a server process), DISPATCHER (waiting for the dispatcher), SERVER (currently being serviced), OUTBOUND (waiting to establish an outbound connection), NONE (idle circuit) |
| MESSAGE0 | NUMBER | Size in bytes of the messages in the first message buffer |
| MESSAGE1 | NUMBER | Size in bytes of the messages in the second message buffer. |
| MESSAGES | NUMBER | Total number of messages that have gone through this circuit |
| BYTES | NUMBER | Total number of bytes that have gone through this circuit |
| BREAKS | NUMBER | Total number of breaks (interruptions) for this circuit |
| This Column | Datatype | Represents This |
| TYPE_ID | VARCHAR2(8 | Internal feature identifier |
| RELEASE | VARCHAR2(60) | Release in which that feature appeared |
| DESCRIPTION | VARCHAR2(64) | Description of the feature |
| This Column | Datatype | Represents This |
| TYPE_ID | VARCHAR2(8) | Internal feature identifier |
| RELEASE | VARCHAR2(60) | Release in which that feature appeared. The software must be able to interpret data formats added in that release |
| UPDATED | VARCHAR2(60) | Release that first used the feature |
| This Column | Datatype | Represents This |
| STATUS | VARCHAR2(7) | INVALID if the name cannot be determined, which should not occur. NULL if the name can be determined. |
| NAME | VARCHAR2(257) | The name of the control file. |
| This Column | Datatype | Represents This |
| NAME | VARCHAR2 | Name of the database |
| CREATED | VARCHAR2 | Creation date |
| LOG_MODE | VARCHAR2 | Archive log mode: NOARCHIVELOG or ARCHIVELOG |
| CHECKPOINT_ CHANGE# | NUMBER | Last SCN checkpointed |
| ARCHIVE_CHANGE# | NUMBER | Last SCN archived |
| This Column | Datatype | Represents This |
| FILE# | NUMBER | File identification number |
| STATUS | VARCHAR2 | Type of file (system or user) and its status. Values: OFFLINE, ONLINE, SYSTEM, RECOVER, SYSOFF (an offline file from the SYSTEM tablespace). |
| ENABLED | VARCHAR2(10) | Describes how accessible the file is from SQL. It is one of the values in the following table. |
| CHECKPOINT_ CHANGE# | NUMBER | SCN at last checkpoint |
| BYTES | NUMBER | Size in bytes |
| CREATE_BYTES | NUMBER | Size when created, in bytes |
| NAME | VARCHAR2 | Name of the file |
| ENABLED Column Value | Description |
| DISABLED | No SQL access allowed |
| READ ONLY | No SQL updates allowed |
| READ WRITE | Full access allowed |
| UNKNOWN | Should not occur unless the control file is corrupted |
| This Column | Datatype | Represents This |
| FILE# | NUMBER | File identifier |
| NAME | VARCHAR2 | Name of file |
| This Column | Datatype | Represents This |
| DB_LINK | VARCHAR2(128) | Name of the database link |
| OWNER_ID | NUMBER | Owner of the database link UID |
| LOGGED_ON | VARCHAR2(3) | Whether the database link is currently logged on |
| HETEROGENEOUS | VARCHAR2(3) | Whether the database link is heterogeneous |
| PROTOCOL | VARCHAR2(6) | Communication protocol for the database link |
| OPEN_CURSORS | NUMBER | Whether there are open cursors for the database link |
| IN_TRANSACTION | VARCHAR2(3) | Whether the database link is currently in a transaction |
| UPDATE_SENT | VARCHAR2(3) | Whether there has been an update on the database link |
| COMMIT_POINT_ STRENGTH | NUMBER | Commit point strength of the transactions on the database link |
| This Column | Datatype | Represents This |
| OWNER | VARCHAR2 | Owner of the object |
| NAME | VARCHAR2 | Name of the object |
| DB_LINK | VARCHAR2 | Database link name, if any |
| NAMESPACE | VARCHAR2 | Library cache namespace of the object: TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT |
| TYPE | VARCHAR2 | 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 | Not used. To see actual execution counts, see V$SQL_AREA ![[*]](jump.gif) .
|
| LOCKS | NUMBER | Number of users currently locking this object |
| PINS | NUMBER | Number of users currently pinning this object |
| KEPT | VARCHAR2(3) | YES or NO, depending on whether this object has been "kept" (permanently pinned in memory) with the PL/SQL procedure DBMS_SHARED_POOL.KEEP |
| This Column | Datatype | Represents This |
| OWNERID | NUMBER | The owner of the pipe if this is a private pipe; null otherwise. |
| NAME | VARCHAR2 | The name of the pipe; for example, scott.pipe |
| TYPE | VARCHAR2 | PUBLIC or PRIVATE |
| SIZE | NUMBER | The amount of memory the pipe uses |
| This Column | Datatype | Represents This |
| NAME | VARCHAR2 | Name of the dispatcher process |
| NETWORK | VARCHAR2 | Network protocol supported by this dispatcher. For example, TCP or DECNET. |
| PADDR | RAW(4) | Process address |
| STATUS | VARCHAR2 | Dispatcher status: WAIT (idle), SEND (sending a message connection), RECEIVE (receiving a message), CONNECT (establishing a connection), DISCONNECT (handling a disconnect request), BREAK (handling a break), OUTBOUND (establishing an outbound connection) |
| ACCEPT | VARCHAR2 | Whether this dispatcher is accepting new connections: YES, NO |
| MESSAGES | NUMBER | Number of messages processed by this dispatcher |
| BYTES | NUMBER | Size in bytes of messages processed by this dispatcher |
| BREAKS | NUMBER | Number of breaks occurring in this connection |
| OWNED | NUMBER | Number of circuits owned by this dispatcher |
| CREATED | NUMBER | Number of circuits created by this dispatcher |
| IDLE | NUMBER | Total idle time for this dispatcher in hundredths of a second |
| BUSY | NUMBER | Total busy time for this dispatcher in hundredths of a second |
| LISTENER | NUMBER | The most recent Oracle error number the dispatcher received from the listener |
| This Column | Datatype | Represents This |
| PRIV_NUMBER | NUMBER | Numeric identifier of enabled privileges |
| This Column | Datatype | Represents This |
| EVENT# | NUMBER | The number of the wait event |
| NAME | VARCHAR2(64) | The name of the wait event |
| PARAMETER1 | VARCHAR2(64) | The description of the first parameter for the wait event |
| PARAMETER2 | VARCHAR2(64) | The description of the second parameter for the wait event |
| PARAMETER3 | VARCHAR2(64) | The description of the third parameter for the wait event |
This view shows buffers that may be getting false pings. That is, buffers pinged more than 100 times that are protected by the same lock as another buffer that pinged more than 100 times. Buffers identified as getting false pings can be remapped in GC_FILES_TO_LOCKS to reduce lock collisions.
| This Column | Datatype | Represents This |
| FILE# | NUMBER | Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES) |
| 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 conversions due to contention with another instance |
| NAME | VARCHAR2(30) | Name of the database object containing the block |
| KIND | VARCHAR2(12) | Type of database object: TABLE CLUSTER INDEX UNDO = rollback segment |
| 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 more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| This Column | Datatype | Represents This |
| FILE# | NUMBER | Number of the file |
| PHYRDS | NUMBER | Number of physical reads done |
| PHYWRTS | NUMBER | Number of physical writes done |
| PHYBLKRD | NUMBER | Number of physical blocks read |
| PHYBLKWRT | NUMBER | Number of physical blocks written |
| READTIM | NUMBER | Time (in milliseconds) spent doing reads if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE |
| WRITETIM | NUMBER | Time (in milliseconds) spent doing writes if the TIMED_STATISTICS parameter is TRUE; 0 if FALSE |
| This Column | Datatype | Represents This |
| NAME | VARCHAR2 | Name of the object |
| OBJECT_ID | NUMBER | Identifier of the fixed object |
| TYPE | VARCHAR2 | Object type: TABLE, VIEW |
| TABLE_NUM | NUMBER | Number that identifies the dynamic performance table if it is of type TABLE |
| This Column | Datatype | Represents This |
| VIEW_NAME | VARCHAR2(30) | The name of the fixed view |
| VIEW_DEFINITION | VARCHAR2(2000) | The definition of the fixed view |
| This Column | Datatype | Represents This |
| TABLE_NAME | VARCHAR2(30) | The name of the dynamic performance table that is indexed |
| INDEX_NUMBER | NUMBER | Number that distinguishes to which index a column belongs |
| COLUMN_NAME | VARCHAR2(30) | Name of the column that is being indexed |
| COLUMN_POSITION | NUMBER | Position of the column in the index key (this is mostly relevant for multicolumn indexes) |
| This Column | Datatype | Represents This |
| KEY | VARCHAR2 | Name of state variable, from Table 3 - 2 |
| VALUE | NUMBER | Value of state variable |
| This Column | Datatype | Represents This |
| ADDR | RAW(4) | Address of latch object |
| LATCH# | NUMBER | Latch number |
| LEVEL# | NUMBER | Latch level |
| NAME | VARCHAR2(64) | Latch name |
| GETS | NUMBER | Number of times gotten wait |
| MISSES | NUMBER | Number of times gotten wait but failed first try |
| SLEEPS | NUMBER | Number of times slept when wanted wait |
| IMMEDIATE_GETS | NUMBER | Number of times gotten without wait |
| IMMEDIATE_MISSES | NUMBER | Number of time failed to get without wait |
| WAITERS_WOKEN | NUMBER | How many times a waiter was woken |
| WAITS_HOLDING_ LATCH | NUMBER | Number of waits while holding a different latch |
| SPIN_GETS | NUMBER | Gets that missed first try but suceeded on spin |
| SLEEP1 | NUMBER | Waits that slept 1 time |
| SLEEP2 | NUMBER | Waits that slept 2 times |
| SLEEP3 | NUMBER | Waits that slept 3 times |
| SLEEP4 | NUMBER | Waits that slept 4 times |
| SLEEP5 | NUMBER | Waits that slept 5 times |
| SLEEP6 | NUMBER | Waits that slept 6 times |
| SLEEP7 | NUMBER | Waits that slept 7 times |
| SLEEP8 | NUMBER | Waits that slept 8 times |
| SLEEP9 | NUMBER | Waits that slept 9 times |
| SLEEP10 | NUMBER | Waits that slept 10 times |
| SLEEP11 | NUMBER | Waits that slept 11 times |
| This Column | Datatype | Represents This |
| PID | NUMBER | Identifier of process holding the latch |
| SID | NUMBER | Identifier of the session that owns the latch |
| LADDR | RAW(4) | Latch address |
| NAME | VARCHAR2 | Name of latch being held |
| This Column | Datatype | Represents This |
| LATCH# | NUMBER | Latch number |
| NAME | VARCHAR2(64) | Latch name |
| This Column | Datatype | Represents This |
| ADDR | RAW(4) | Address of latch object |
| LATCH# | NUMBER | Latch number for a parent latch |
| CHILD# | NUMBER | Child number of a parent latch shown in LATCH# |
| LEVEL# | NUMBER | Latch level |
| NAME | VARCHAR2(64) | Latch name |
| GETS | NUMBER | Number of times gotten wait |
| MISSES | NUMBER | Number of times gotten wait but failed first try |
| SLEEPS | NUMBER | Number of times slept when wanted wait |
| IMMEDIATE_GETS | NUMBER | Number of times gotten without wait |
| IMMEDIATE_MISSES | NUMBER | Number of time failed to get without wait |
| WAITERS_WOKEN | NUMBER | How many times a waiter was woken |
| WAITS_HOLDING_ LATCH | NUMBER | Number of waits while holding a different latch |
| SPIN_GETS | NUMBER | Gets that missed first try but suceeded on spin |
| SLEEPn | NUMBER | Waits that slept n times |
| This Column | Datatype | Represents This |
| PARENT_NAME | VARCHAR2 | Latch name of a parent latch |
| WHERE | VARCHAR2 | Location that attempted to acquire the latch |
| NWFAIL_COUNT | NUMBER | Number of times that no-wait acquisition of the latch failed |
| SLEEP_COUNT | NUMBER | Number of times that acquisition attempts caused sleeps |
| This Column | Datatype | Represents This |
| NAMESPACE | VARCHAR2(15) | The library cache namespace |
| GETS | NUMBER | The number of times a lock was requested for objects of this namespace |
| GETHITS | NUMBER | The number of times an object's handle was found in memory |
| GETHITRATIO | NUMBER | The ratio of GETHITS to GETS |
| PINS | NUMBER | The number of times a pin was requested for objects of this namespace |
| PINHITS | NUMBER | The number of times all of the meta data pieces of the library object were found in memory |
| PINHITRATIO | NUMBER | The ratio of PINHITS to PINS |
| RELOADS | NUMBER | Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk |
| INVALIDATIONS | NUMBER | The total number of times objects in this namespace were marked invalid because a dependent object was modified |
| DLM_LOCK_REQUESTS | NUMBER | The number of GET requests lock instance locks |
| DLM_PIN_REQUESTS | NUMBER | The number of PIN requests lock instance locks |
| DLM_PIN_RELEASES | NUMBER | The number of release requests pin instance locks |
| DLM_INVALIDATION_ REQUESTS | NUMBER | The number of GET requests for invalidation instance locks |
| DLM_INVALIDATIONS | NUMBER | The number of invalidation pings received from other instances |
| This Column | Datatype | Represents This |
| SESSIONS_MAX | NUMBER | Maximum number of concurrent user sessions allowed for the instance |
| SESSIONS_WARNING | NUMBER | Warning limit for concurrent user sessions for the instance |
| SESSIONS_CURRENT | NUMBER | Current number of concurrent user sessions |
| SESSIONS_ HIGHWATER | NUMBER | Highest number of concurrent user sessions since the instance started |
| USERS_MAX | NUMBER | Maximum number of named users allowed for the database |
| This Column | Datatype | Represents This |
| READ | NUMBER | Number of records read |
| REJECTED | NUMBER | Number of records rejected |
| TDISCARD | NUMBER | Total number of discards during the load |
| NDISCARD | NUMBER | Number of discards from the current file |
| SAVEDATA | NUMBER | Whether save data points are used |
| This Column | Datatype | Represents This |
| LOADED | NUMBER | Number of records loaded |
| REJECTED | NUMBER | Number of records rejected |
| FAILWHEN | NUMBER | Number of records that failed to meet any WHEN clause |
| ALLNULL | NUMBER | Number of records that were completely null and were therefore not loaded |
| LEFT2SKIP | NUMBER | Number of records yet to skip during a continued load |
| User Lock Type | Description |
| TM | DML enqueue lock |
| TX | Transaction enqueue lock |
| UL | User supplied lock |
| This Column | Datatype | Represents This |
| ADDR | RAW(4) | Address of lock state object |
| KADDR | RAW(4) | Address of lock |
| SID | NUMBER | Identifier for session holding or acquiring the lock |
| TYPE | VARCHAR2(2) | Type of lock: MR, Media Recovery RT, Redo Thread UN, User Name TX, Transaction TM, DML UL, PL/SQL User Lock DX, Distributed Xaction CF, Control File IS, Instance State FS, File Set IR, Instance Recovery ST, Disk Space Transaction TS, Temp Segment IV, Library Cache Invalidation LS, Log Start or Switch RW, Row Wait SQ, Sequence Number TE, Extend Table TT, Temp Table |
| ID1 | NUMBER | Lock identifier #1 (depends on type) |
| ID2 | NUMBER | Lock identifier #2 (depends on type) |
| LMODE | NUMBER | Mode the lock is currently held in by the session: 0, None 1, Null 2, Row-S (SS) 3, Row-X (SX) 4, Share 5, S/Row-X (SSX) 6, Exclusive |
| REQUEST | NUMBER | Mode the lock is being requested in by the process: 0, None 1, Null 2, Row-S (SS) 3, Row-X (SX) 4, Share 5, S/Row-X (SSX) 6, Exclusive |
| CTIME | NUMBER | Time since current mode was granted |
| BLOCK | NUMBER | The lock is blocking another lock |
V$LOCK_ACTIVITY shows the DLM lock operation activity of the current instance. Each row corresponds to a type of lock operation.
| This Column | Datatype | Represents This |
| FROM_VAL | VARCHAR2(4) | PCM lock initial state: NULL S X SSX |
| TO_VAL | VARCHAR2(4) | PCM lock initial state: NULL S X SSX |
| ACTION_VAL | VARCHAR2(51) | Description of lock conversions Lock buffers for read Lock buffers for write Make buffers CR (no write) Upgrade read lock to write Make buffers CR (write dirty buffers) Downgrade write lock to read (write dirty buffers) Write transaction table/undo blocks Transaction table/undo blocks (write dirty buffers) Make transaction table/undo blocks available share Rearm transaction table write mechanism |
| COUNTER | NUMBER | |
| This Column | Datatype | Represents This |
| LOCK_ELEMENT_ ADDR | RAW(4) | The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has 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 |
| MODE_HELD | NUMBER | Platform dependent value for lock mode held; often: 3 = share 5 = exclusive |
| BLOCK_COUNT | NUMBER | Number of blocks covered by PCM lock |
| RELEASING | NUMBER | Non-zero if PCM lock is being downgraded |
| ACQUIRING | NUMBER | Non-zero if PCM lock is being upgraded |
| INVALID | NUMBER | Non-zero if PCK lock is invalid. (A lock may become invalid after a system failure.) |
| This Column | Datatype | Represents This |
| XIDUSN | NUMBER | Undo segment number |
| XIDSLOT | NUMBER | Slot number |
| XIDSQN | NUMBER | Sequence number |
| OBJECT_ID | NUMBER | Object ID being locked |
| SESSION_ID | NUMBER | Session ID |
| ORACLE_USERNAME | VARCHAR2(30) | Oracle user name |
| OS_USER_NAME | VARCHAR2(15) | OS user name |
| PROCESS | VARCHAR2(9) | OS process ID |
| LOCKED_MODE | NUMBER | Lock mode |
| This Column | Datatype | Represents This |
| LOCK_ELEMENT_ ADDR | RAW(4) | The address of the lock element that contains the PCM lock covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| This Column | Datatype | Represents This |
| GROUP# | NUMBER | Log group number |
| THREAD# | NUMBER | Log thread number |
| SEQUENCE# | NUMBER | Log sequence number |
| BYTES | NUMBER | Size of the log in bytes |
| MEMBERS | NUMBER | Number of members in the log group |
| ARCHIVED | VARCHAR2 | Archive status: TRUE, FALSE |
| STATUS | VARCHAR2(16) | Log status. The STATUS column can have the values in the following table. |
| FIRST_CHANGE# | NUMBER | Lowest SCN in the log |
| FIRST_TIME | VARCHAR2 | Time of first SCN in the log |
The following table defines values in the log STATUS column.
| This Column | Datatype | Represents This |
| GROUP# | NUMBER | Redo log group identifier number |
| STATUS | VARCHAR2 | Status of this log member: INVALID (file is inaccessible), STALE (file's contents are incomplete), DELETED (file is no longer used), or blank (file is in use) |
| MEMBER | VARCHAR2 | Redo log member name |
| This Column | Datatype | Represents This |
| THREAD# | NUMBER | Log thread number |
| SEQUENCE# | NUMBER | Log sequence number |
| FIRST_CHANGE# | NUMBER | Lowest SCN in the log |
| FIRST_TIME | VARCHAR2 | Time of first SCN in the log |
| SWITCH_CHANGE# | NUMBER | SCN at which the log switch occurred; one more than highest SCN in the log |
| This Column | Datatype | Represents This |
| THREAD# | NUMBER | Thread number of the archived log |
| SEQUENCE# | NUMBER | Sequence number of the archived log |
| TIME | VARCHAR2 | Time of first entry (lowest SCN) in the log |
| LOW_CHANGE# | NUMBER | Lowest SCN in the log |
| HIGH_CHANGE# | NUMBER | Highest SCN in the log |
| ARCHIVE_NAME | VARCHAR2 | Name of archive file, using the naming convention specified by the current values of the LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_DEST initialization parameters. Note that the value of this column indicates the path based on the current values of these initialization parameters, therefore archive logs that were created with other settings of these initialization parameters may indicate the wrong path. |
For more information, see the Trusted Oracle7 Server Administrator's Guide.
| This Column | Datatype | Represents This |
| MAXIMUM_ CONNECTIONS | NUMBER | The maximum number of connections each dispatcher can support. This value is determined at startup time using SQL*Net constants and other port-specific information. |
| SERVERS_STARTED | NUMBER | The total number of multi-threaded servers started since the instance started (but not including those started during startup) |
| SERVERS_ TERMINATED | NUMBER | The total number of multi-threaded servers stopped by Oracle since the instance started |
| SERVERS_ HIGHWATER | NUMBER | The highest number of servers running at one time since the instance started. If this value reaches the value set for the MTS_MAX_SERVERS initialization parameter, consider raising the value of MTS_MAX_ SERVERS. |
| This Column | Datatype | Represents This |
| SID | NUMBER | The id of the current session |
| STATISTIC# | NUMBER | The number of the statistic |
| VALUE | NUMBER | The value of the statistic |
| This Column | Datatype | Represents This |
| PARAMETER | VARCHAR2 | Parameter name: NLS_LANGUAGE NLS_SORT NLS_TERRITORY NLS_CHARACTERSET NLS_CURRENCY NLS_ISO_CURRENCY NLS_NUMERIC_CHARACTERS NLS_DATE_FORMAT NLS_DATE_LANGUAGE |
| VALUE | VARCHAR2 | NLS parameter value |
| This Column | Datatype | Represents This |
| PARAMETER | VARCHAR2(64) | NLS_* parameter: LANGUAGE SORT TERRITORY CHARACTERSET |
| VALUE | VARCHAR2(64) | NLS_* parameter value |
| This Column | Datatype | Represents This |
| FROM_ADDRESS | RAW(4) | The address of a procedure, package, or cursor that is currently loaded in the shared pool |
| FROM_HASH | NUMBER | The hash value of a procedure, package, or cursor that is currently loaded in the shared pool |
| TO_OWNER | VARCHAR2(64) | The owner of the object that is depended on |
| TO_NAME | VARCHAR2 (1000) | The name of the object that is depended on |
| TO_ADDRESS | RAW(4) | The address of the object that is depended on. These can be used to look up more information on the object in V$DB_OBJECT_CACHE. |
| TO_HASH | NUMBER | The hash value of the object that is depended on. These can be used to look up more information on the object in V$DB_OBJECT_CACHE. |
| TO_TYPE | NUMBER | The type of the object that is depended on |
| This Column | Datatype | Represents This |
| SADDR | RAW | Session address |
| SID | NUMBER | Session identifier |
| USER_NAME | VARCHAR2 | User that is logged in to the session |
| ADDRESS | RAW | Used with HASH_VALUE to identify uniquely the SQL statement being executed in the session |
| HASH_VALUE | NUMBER | Used with ADDRESS to identify uniquely the SQL statement being executed in the session |
| SQL_TEXT | VARCHAR2 | First 60 characters of the SQL statement that is parsed into the open cursor |
| This Column | Datatype | Represents This |
| PARAMETER | VARCHAR2(64) | The name of the option |
| VALUE | VARCHAR2(64) | TRUE if the option is installed |
| This Column | Datatype | Represents This |
| NUM | NUMBER | Parameter number |
| NAME | VARCHAR2(64) | Parameter name |
| TYPE | NUMBER | Parameter type |
| VALUE | VARCHAR2(512) | Parameter value |
| ISDEFAULT | VARCHAR2(9) | Whether parameter is default |
The V$PING view is identical to the V$CACHE view but only shows blocks that have been pinged at least once. This view contains information from the block header of each block in the SGA of the current instance as related to particular database objects.
| This Column | Datatype | Represents This |
| FILE# | NUMBER | Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES) |
| 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 conversions due to contention with another instance |
| LOCK_ELEMENT_ ADDR | RAW(4) | The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| NAME | VARCHAR2(30) | Name of the database object containing the block |
| KIND | VARCHAR2(12) | Type of database object: TABLE CLUSTER INDEX UNDO = rollback segment |
| OWNER# | NUMBER | Owner number |
| This Column | Datatype | Represents This |
| STATISTIC | VARCHAR2(30) | Name of the statistic |
| LAST_QUERY | NUMBER | The value of the statistic for the last query |
| SESSION_TOTAL | NUMBER | The value of the statistic for the entire session to this point in time |
| This Column | Datatype | Represents This |
| SLAVE_NAME | VARCHAR2(4) | Name of the parallel query server |
| STATUS | VARCHAR2(4) | The current status of the parallel query server (BUSY or IDLE) |
| SESSIONS | NUMBER | The number of sessions that have used this parallel query server |
| IDLE_TIME_CUR | NUMBER | The amount of time spent idle while processing statements in the current session |
| BUSY_TIME_CUR | NUMBER | The amount of time spent busy while processing statements in the current session |
| CPU_SECS_CUR | NUMBER | The amount of CPU time spent on the current session |
| MSGS_SENT_CUR | NUMBER | The number of messages sent while processing statements for the current session |
| MSGS_RCVD_CUR | NUMBER | The number of messages received while processing statements for the current session |
| IDLE_TIME_TOTAL | NUMBER | The total amount of time this query server has been idle |
| BUSY_TIME_TOTAL | NUMBER | The total amount of time this query server has been active |
| CPU_SECS_TOTAL | NUMBER | The total amount of CPU time this query server has used to process statements |
| MSGS_SENT_TOTAL | NUMBER | The total number of messages this query server has sent |
| MSGS_RCVD_TOTAL | NUMBER | The total number of messages this query server has received |
| This Column | Datatype | Represents This |
| STATISTIC | VARCHAR2(30) | Name of the statistic |
| VALUE | NUMBER | The value of the statistic |
| This Column | Datatype | Represents This |
| DFO_NUMBER | NUMBER | The data flow operator (DFO) tree number to differentiate queries |
| TQ_ID | NUMBER | The table queue ID within the query, which represents the connection between two DFO nodes in the query execution tree |
| SERVER_TYPE | VARCHAR2(10) | The role in table queue - producer/consumer/ranger |
| NUM_ROWS | NUMBER | The number of rows produced/consumed |
| BYTES | NUMBER | The number of bytes produced/consumed |
| OPEN_TIME | NUMBER | Time (secs) the table queue remained open |
| AVG_LATENCY | NUMBER | Time (ms) for a message to be dequeued after it enters the queue |
| WAITS | NUMBER | The number of waits encountered when dequeueing |
| TIMEOUTS | NUMBER | The number of timeouts when waiting for a message |
| PROCESS | VARCHAR2(10) | Process ID |
| INSTANCE | NUMBER | Instance ID |
While the LATCHWAIT column indicates what latch a process is waiting for, the LATCHSPIN column indicates what latch a process is spinning on. On multi-processor machines, Oracle processes will spin on a latch before waiting on it.
| This Column | Datatype | Represents This |
| ADDR | RAW(4) | Address of process state object |
| PID | NUMBER | Oracle process identifier |
| SPID | VARCHAR2 | Operating system process identifier |
| USERNAME | VARCHAR2 | Operating system process username. Any Two-Task user coming across the network has "-T" appended to the username. |
| SERIAL# | NUMBER | Process serial number |
| TERMINAL | VARCHAR2 | Operating system terminal identifier |
| PROGRAM | VARCHAR2 | Program in progress |
| BACKGROUND | VARCHAR2 | 1 for a background process; null for a normal process |
| LATCHWAIT | VARCHAR2 | Address of latch the process is waiting for; null if none |
| LATCHSPIN | VARCHAR2 | Address of latch the process is being spun on; null if none |
| This Column | Datatype | Represents This |
| USERNAME | VARCHAR2(30) | The name of the user that is contained in the password file. |
| SYSDBA | VARCHAR2(5) | If the value of this column is TRUE, the user can connect with SYSDBA privileges. |
| SYSOPER | VARCHAR2(5) | If the value of this column is TRUE, the user can connect with SYSOPER privileges |
| This Column | Datatype | Represents This |
| PADDR | RAW(4) | Address of the process that owns the queue |
| TYPE | VARCHAR2 | Type of queue: COMMON (processed by servers), OUTBOUND (used by remote servers), DISPATCHER. |
| QUEUED | NUMBER | Number of items in the queue |
| WAIT | NUMBER | Total time that all items in this queue have waited. Divide by TOTALQ for average wait per item. |
| TOTALQ | NUMBER | Total number of items that have ever been in the queue |
| This Column | Datatype | Represents This |
| THREAD# | NUMBER | Thread number of the archived log |
| SEQUENCE# | NUMBER | Sequence number of the archived log |
| TIME | VARCHAR2 | Time of first entry (lowest SCN) in the log |
| ARCHIVE_NAME | VARCHAR2 | Name of file when archived, using the naming convention specified by the LOG_ARCHIVE_FORMAT initialization parameter |
| This Column | Datatype | Represents This |
| FILE# | NUMBER | File identifier number |
| ONLINE | VARCHAR2 | Online status: ONLINE, OFFLINE |
| ERROR | VARCHAR2 | Why the file needs to be recovered: NULL if reason unknown, or OFFLINE NORMAL if recovery not needed |
| CHANGE# | NUMBER | SCN where recovery must start |
| TIME | VARCHAR2 | Time of SCN where recovery must start |
| This Column | Datatype | Represents This |
| FILENUM | NUMBER | The number of the file being recovered |
| FILENAME | VARCHAR2(257) | The filename of the datafile being recovered |
| STATUS | VARCHAR2(13) | The status of the recovery. Contains one of the following values: · IN RECOVERY · CURRENT · NOT RECOVERED |
| This Column | Datatype | Represents This |
| RECOVERY_CHECKPOINT | VARCHAR2(20) | The point in time to which the recovery has occurred. If no logs have been applied, this is the point in time the recovery starts. |
| THREAD | NUMBER | The number of the redo thread currently being processed. |
| SEQUENCE_NEEDED | NUMBER | Log sequence number of the log needed by the recovery process. The value is 0 if no log is needed. |
| SCN_NEEDED | VARCHAR2(16) | The low SCN of the log needed by recovery. The value is 0 if unknown or no log is needed. |
| TIME_NEEDED | VARCHAR2(20) | Time when the log was created. The value is midnight on 1/1/88 if the time is unknown or if no log is needed. |
| PREVIOUS_LOG_NAME | VARCHAR2(257) | The filename of the log. |
| PREVIOUS_LOG_STATUS | VARCHAR2(13) | The status of the previous log. Contains one of the following values: w RELEASE · WRONG NAME · MISSING NAME · UNNEEDED NAME · NONE |
| REASON | VARCHAR2(13) | The reason recovery is returning control to the user. Contains one of the following values: w NEED LOG · LOG REUSED · THREAD DISABLED |
| This Column | Datatype | Represents This |
| BUCKET | NUMBER | Bucket number: 0..11; the maximum time for each bucket is (4 * 2^N)/100 seconds |
| COUNT | NUMBER | Count of requests whose total time to complete (excluding wait time) falls in this range |
| This Column | Datatype | Represents This |
| ADDR | RAW(4) | Address of resource object |
| TYPE | VARCHAR2 | Resource type |
| ID1 | NUMBER | Resource identifier #1 |
| ID2 | NUMBER | Resource identifier #2 |
| This Column | Datatype | Represents This |
| USN | NUMBER | Rollback (undo) segment number |
| NAME | VARCHAR2 | Rollback segment name |
| This Column | Datatype | Represents This |
| USN | NUMBER | Rollback segment number |
| EXTENTS | NUMBER | Number of rollback extents |
| RSSIZE | NUMBER | Size in bytes of rollback segment |
| WRITES | NUMBER | Number of bytes written to rollback segment |
| XACTS | NUMBER | Number of active transactions |
| GETS | NUMBER | Number of header gets |
| WAITS | NUMBER | Number of header waits |
| OPTSIZE | NUMBER | Optimal size of rollback segment |
| HWMSIZE | NUMBER | High water mark of rollback segment size |
| SHRINKS | NUMBER | Number of times the size of a rollback segment decreases |
| WRAPS | NUMBER | Number of times rollback segment is wrapped |
| EXTENDS | NUMBER | Number of times rollback segment size is extended |
| AVESHRINK | NUMBER | Average shrink size |
| AVEACTIVE | NUMBER | Current size of active extents, averaged over time. |
| STATUS | VARCHAR2(15) | Rollback segment status |
| CUREXT | NUMBER | Current extent |
| CURBLK | NUMBER | Current block |
| This Column | Datatype | Represents This |
| CACHE# | NUMBER | Row cache ID number |
| TYPE | VARCHAR2 | Parent or subordinate row cache type |
| SUBORDINATE# | NUMBER | Subordinate set number |
| PARAMETER | VARCHAR2 | Name of the initialization parameter that determines the number of entries in the data dictionary cache |
| COUNT | NUMBER | Total number of entries in the cache |
| USAGE | NUMBER | Number of cache entries that contain valid data |
| FIXED | NUMBER | Number of fixed entries in the cache |
| GETS | NUMBER | Total number of requests for information on the data object |
| GETMISSES | NUMBER | Number of data requests resulting in cache misses |
| SCANS | NUMBER | Number of scan requests |
| SCANMISSES | NUMBER | Number of times a scan failed to find the data in the cache |
| SCANCOMPLETES | NUMBER | For a list of subordinate entries, the number of times the list was scanned completely |
| MODIFICATIONS | NUMBER | Number of inserts, updates, and deletions |
| FLUSHES | NUMBER | Number of times flushed to disk |
For more information, see the Trusted Oracle7 Server Administrator's Guide.
| This Column | Datatype | Represents This |
| SADDR | RAW(4) | Session address |
| SID | NUMBER | Session identifier |
| SERIAL# | NUMBER | Session serial number. Used to identify uniquely a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID. |
| AUDSID | NUMBER | Auditing session ID |
| PADDR | RAW(4) | Address of the process that owns this session |
| USER# | NUMBER | Oracle user identifier |
| USERNAME | VARCHAR2 | Oracle username |
| COMMAND | NUMBER | Command in progress (last statement parsed); for a list of values, see Table 3 - 6 |
| TADDR | VARCHAR2 | Address of transaction state object |
| LOCKWAIT | VARCHAR2 | Address of lock waiting for; null if none |
| STATUS | VARCHAR2 | Status of the session: ACTIVE (currently executing SQL), INACTIVE, KILLED (marked to be killed), CACHED (temporarily cached for use by Oracle*XA) |
| SERVER | VARCHAR2 | Server type: DEDICATED, SHARED, PSEUDO, NONE |
| SCHEMA# | NUMBER | Schema user identifier |
| SCHEMANAME | VARCHAR2 | Schema user name |
| OSUSER | VARCHAR2 | Operating system client user name |
| PROCESS | VARCHAR2 | Operating system client process ID |
| MACHINE | VARCHAR2 | Operating system machine name |
| TERMINAL | VARCHAR2 | Operating system terminal name |
| PROGRAM | VARCHAR2 | Operating system program name |
| TYPE | VARCHAR2 | Session type |
| SQL_ADDRESS | RAW(4) | Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed |
| SQL_HASH_VALUE | NUMBER | Used with SQL_ADDRESS to identify the SQL statement that is currently being executed |
| MODULE | VARCHAR2(48) | Contains the name of the currently executing module as set by calling the DBMS_APPLICATION_ INFO.SET_MODULE procedure. |
| MODULE_HASH | NUMBER | The hash value of the above module_name |
| ACTION | VARCHAR2(32) | Contains the name of the currently executing action as set by calling the dbms_application_info.set_action procedure. |
| ACTION_HASH | NUMBER | The hash value of the above action name |
| CLIENT_INFO | VARCHAR2(64) | Information set by the DBMS_ APPLICATION_INFO.SET_ CLIENT_INFO procedure. |
| FIXED_TABLE_ SEQUENCE | NUMBER | This contains a number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table. This column can be used by performance monitors that wish to monitor statistics in the database. Each time the performance monitor looks at the database, it only needs to look at sessions that are currently active or have a higher value in this column then the highest value that the performance monitor saw the last time. All the other sessions have been idle since the last time the performance monitor looked at the database. |
| ROW_WAIT_OBJ# | NUMBER | Object id for the table containing the rowid specified in ROW_WAIT_ROW# |
| ROW_WAIT_FILE# | NUMBER | Identifier for the datafile containing the rowid specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is non-zero. |
| ROW_WAIT_BLOCK# | NUMBER | Identifier for the block containing the rowid specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is non-zero. |
| ROW_WAIT_ROW# | NUMBER | The current rowid being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is non-zero. |
| This Column | Datatype | Represents This |
| SID | NUMBER | Session identifier (can be used to join this view with V$SESSION) |
| AUTHENTICATION_ TYPE | VARCHAR2(15) | How the user was authenticated: OS, PROTOCOL, or NETWORK. |
| OSUSER | VARCHAR2(30) | The external username for this database user |
| NETWORK_ SERVICE_BANNER | VARCHAR2(2000) | Product banners for each SQL*Net service used for this connection (one row per banner) |
| This Column | Datatype | Represents This |
| MAXIMUM | NUMBER | Maximum number of cursors to cache. Once you hit this number, some cursors will need to be closed to open more |
| COUNT | NUMBER | Current number of cursors (in use or not) |
| OPENED_ONCE | NUMBER | Number of cursors opened at least once |
| OPEN | NUMBER | Current number of open cursors |
| OPENS | NUMBER | Cumulative total of cursor opens |
| HITS | NUMBER | Cumulative total of cursor open hits |
| HIT_RATIO | NUMBER | Ratio of the number of times we found an open cursor divided by the number of times we looked for a cursor |
Note that the TIME_WAITED and AVERAGE_WAIT columns will contain a value of zero on those platforms that do not support a fast timing mechanism. If you are running on one of these platforms and you wish this column to reflect true wait times, you must set TIMED_STATISTICS to TRUE in the parameter file. Please remember that doing this will have a small negative effect on system performance.
| This Column | Datatype | Represents This |
| SID | NUMBER | The id of the session |
| EVENT | VARCHAR2(64) | The name of the wait event |
| TOTAL_WAITS | NUMBER | The total number of waits for this event by this session |
| TOTAL_TIMEOUTS | NUMBER | The total number of timeouts for this event by this session |
| TIME_WAITED | NUMBER | The total amount of time waited for this event by this session, in hundredths of a second |
| AVERAGE_WAIT | NUMBER | The average amount of time waited for this event by this session, in hundredths of a second |
The following are tuning considerations:
| This Column | Datatype | Represents This |
| SID | NUMBER | Session identifier |
| SEQ# | NUMBER | Sequence number that uniquely identifies this wait. Incremented for each wait. |
| EVENT | VARCHAR2 | 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 |
| P2TEXT | VARCHAR2 | Description of second parameter |
| P2 | NUMBER | Second additional parameter |
| P2RAW | RAW(4) | Second additional parameter |
| P3TEXT | VARCHAR2 | Description of third parameter |
| P3 | NUMBER | Third additional parameter |
| P3RAW | RAW(4) | Third additional parameter |
| WAIT_TIME | NUMBER | A non-zero value is the session's last wait time. A zero value means the session is currently waiting. |
| STATE | VARCHAR2 | Wait state (see following table) |
The following table defines values in the V$SESSION_WAIT STATUS column.
| This Column | Datatype | Represents This |
| SID | NUMBER | Session identifier |
| STATISTIC# | NUMBER | Statistic number (identifier) |
| VALUE | NUMBER | Statistic value |
| This Column | Datatype | Represents This |
| SID | NUMBER | Session identifier |
| BLOCK_GETS | NUMBER | Block gets for this session |
| CONSISTENT_GETS | NUMBER | Consistent gets for this session |
| PHYSICAL_READS | NUMBER | Physical reads for this session |
| BLOCK_CHANGES | NUMBER | Block changes for this session |
| CONSISTENT_ CHANGES | NUMBER | Consistent changes for this session |
| This Column | Datatype | Represents This |
| NAME | VARCHAR2 | SGA component group |
| VALUE | NUMBER | Memory size in bytes |
| This Column | Datatype | Represents This |
| NAME | VARCHAR2 | SGA component name |
| BYTES | NUMBER | Memory size in bytes |
The following columns of V$SHARED_POOL_RESERVED are only valid if the initialization parameter SHARED_POOL_RESERVED_SIZE is set to a valid value.
| This Column | Datatype | Represents This |
| FREE_SPACE | NUMBER | Total amount of free space on the reserved list |
| AVG_FREE_SIZE | NUMBER | Average size of the free memory on the reserved list |
| FREE_COUNT | NUMBER | Number of free pieces of memory on the reserved list |
| MAX_FREE_SIZE | NUMBER | Size of the largest free piece of memory on the reserved list |
| USED_SPACE | NUMBER | Total amount of used memory on the reserved list |
| AVG_USED_SIZE | NUMBER | Average size of the used memory on the reserved list |
| USED_COUNT | NUMBER | Number of used pieces of memory on the reserved list |
| MAX_USED_SIZE | NUMBER | Size of the largest used piece of memory on the reserved list |
| REQUESTS | NUMBER | Number of times that the reserved list was searched for a free piece of memory |
| REQUEST_MISSES | NUMBER | Number of times the reserved list did not have a free piece of memory to satisfy the request, and started flushing objects from the LRU list |
| LAST_MISS_SIZE | NUMBER | Request size of the last request miss, when the reserved list did not have a free piece of memory to satisfy the request and started flushing objects from the LRU list |
| MAX_MISS_SIZE | NUMBER | Request size of the largest request miss, when the reserved list did not have a free piece of memory to satisfy the request and started flushing objects from the LRU list |
| This Column | Datatype | Represents This |
| REQUEST_FAILURES | NUMBER | Number of times that no memory was found to satisfy a request (that is, the number of times the error ORA-4031 occurred) |
| LAST_FAILURE_SIZE | NUMBER | Request size of the last failed request (that is, the request size for the last ORA-4031 error) |
| ABORTED_REQUEST _THRESHOLD | NUMBER | Minimum size of a request which signals an ORA-4031 error without flushing objects |
| ABORTED_REQUESTS | NUMBER | Number of requests that signalled an ORA-4031 error without flushing objects |
| LAST_ABORTED_SIZE | NUMBER | Last size of the request that returned an ORA-4031 error without flushing objects from the LRU list |
| This Column | Datatype | Represents This |
| NAME | VARCHAR2 | Name of the server |
| PADDR | RAW(4) | Server's process address |
| STATUS | VARCHAR2 | Server status: EXEC (executing SQL), WAIT (ENQ) (waiting for a lock), WAIT (SEND) (waiting to send data to user), WAIT (COMMON) (idle; waiting for a user request), WAIT (RESET) (waiting for a circuit to reset after a break), QUIT (terminating) |
| MESSAGES | NUMBER | Number of messages processed |
| BYTES | NUMBER | Total number of bytes in all messages |
| BREAKS | NUMBER | Number of breaks |
| CIRCUIT | RAW(4) | Address of circuit currently being serviced |
| IDLE | NUMBER | Total idle time in hundredths of a second |
| BUSY | NUMBER | Total busy time in hundredths of a second |
| REQUESTS | NUMBER | Total number of requests taken from the common queue in this server's lifetime |
| This Column | Datatype | Represents This |
| TABLESPACE_NAME | VARCHAR2(31) | Name of tablespace |
| SEGMENT_FILE | NUMBER | File number of the first extent |
| SEGMENT_BLOCK | NUMBER | Block number of the first extent |
| EXTENT_SIZE | NUMBER | Extent size |
| CURRENT_USERS | NUMBER | Number of active users of the segment |
| TOTAL_EXTENTS | NUMBER | Total number of extents in the segment |
| TOTAL_BLOCKS | NUMBER | Total number of blocks in the segment |
| USED_EXTENTS | NUMBER | Extents allocated to active sorts |
| USED_BLOCKS | NUMBER | Blocks allocated to active sorts |
| FREE_EXTENTS | NUMBER | Extents not allocated to any sort |
| FREE_BLOCKS | NUMBER | Blocks not allocated to any sort |
| ADDED_EXTENTS | NUMBER | Number of extent allocations |
| EXTENT_HITS | NUMBER | Number of times an unused extent was found in the pool |
| FREED_EXTENTS | NUMBER | Number of deallocated extents |
| FREE_REQUESTS | NUMBER | Number of requests to deallocate |
| MAX_SIZE | NUMBER | Maximum number of extents ever used |
| MAX_BLOCKS | NUMBER | Maximum number of blocks ever used |
| MAX_USED_SIZE | NUMBER | Maximum number of extents used by all sorts |
| MAX_USED_BLOCKS | NUMBER | Maximum number of blocks used by all sorts |
| MAX_SORT_SIZE | NUMBER | Maximum number of extents used by an individual sort |
| MAX_SORT_BLOCKS | NUMBER | Maximum number of blocks used by an individual sort |
| This Column | Datatype | Represents This |
| SQL_TEXT | VARCHAR2(1000) | The first eighty characters of the SQL text for the current cursor |
| SHARABLE_MEM | NUMBER | The sum of all sharable memory, in bytes, of all the child cursors under this parent |
| PERSISTENT_MEM | NUMBER | The sum of all persistent memory, in bytes, of all the child cursors under this parent |
| RUNTIME_MEM | NUMBER | The sum of all the ephemeral frame sizes of all the children |
| SORTS | NUMBER | The sum of the number of sorts that was done for all the children |
| LOADED_VERSIONS | NUMBER | The number of children that are present in the cache AND have their context heap (KGL heap 6) loaded |
| OPEN_VERSIONS | NUMBER | The number of child cursors that are currently open under this current this parent |
| USERS_OPENING | NUMBER | The number of users that have any of the child cursors open |
| EXECUTIONS | NUMBER | The number of executions that took place on this object since it was brought into the library cache |
| USERS_EXECUTING | NUMBER | The sum of all users executiong the statement over all children |
| LOADS | NUMBER | The number of times the object was loaded or reloaded |
| FIRST_LOAD_TIME | VARCHAR2(19) | The time stamp of the parent creation time |
| INVALIDATIONS | NUMBER | The sum of invalidations over all the children |
| PARSE_CALLS | NUMBER | The sum of all parse calls to all the child cursors under this parent |
| DISK_READS | NUMBER | The sum of the number of disk reads over all child cursors |
| BUFFER_GETS | NUMBER | The sum of buffer gets over all child cursors |
| ROWS_PROCESSED | NUMBER | The total number of rows the parsed SQL statement returns |
| COMMAND_TYPE | NUMBER | The Oracle command type definition |
| OPTIMIZER_MODE | VARCHAR2(10) | Mode under which the SQL statement is executed |
| PARSING_USER_ID | NUMBER | The user ID of the user that has parsed the very first cursor under this parent |
| PARSING_SCHEMA_ID | NUMBER | The schema ID that was used to parse this child cursor |
| KEPT_VERSIONS | NUMBER | The number of child cursors that have been marked to be kept using the DBMS_SHARED_POOL package |
| ADDRESS | RAW(4) | The address of the handle to the parent for this cursor |
| HASH_VALUE | NUMBER | The hash value of the parent statement in the library cache |
| CHILD_NUMBER | NUMBER | The number of the child of the original SQL text, beginning from 0 |
| MODULE | VARCHAR2(64) | Contains the name of the module that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_MODULE |
| MODULE_HASH | NUMBER | The hash value of the module that is named in the MODULE column |
| ACTION | VARCHAR2(64) | Contains the name of the action that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_ACTION |
| ACTION_HASH | NUMBER | The hash value of the action that is named in the ACTION column |
| SERIALIZABLE_ ABORTS | NUMBER | The number of times the transaction fails to serialize, producing ORA-8177 errors, per cursor |
| This Column | Datatype | Represents This |
| SQL_TEXT | VARCHAR2(1000) | The first eighty characters of the SQL text for the current cursor |
| SHARABLE_MEM | NUMBER | The sum of all sharable memory, in bytes, of all the child cursors under this parent |
| PERSISTENT_MEM | NUMBER | The sum of all persistent memory, in bytes, of all the child cursors under this parent |
| RUNTIME_MEM | NUMBER | The sum of all the ephemeral frame sizes of all the children |
| SORTS | NUMBER | The sum of the number of sorts that was done for all the children |
| VERSION_COUNT | NUMBER | The number of children that are present in the cache under this parent |
| LOADED_VERSIONS | NUMBER | The number of children that are present in the cache AND have their context heap (KGL heap 6) loaded |
| OPEN_VERSIONS | NUMBER | The number of child cursors that are currently open under this current this parent |
| USERS_OPENING | NUMBER | The number of users that have any of the child cursors open |
| EXECUTIONS | NUMBER | The number of executions that took place on this object since it was brought into the library cache |
| USERS_EXECUTING | NUMBER | The sum of all users executiong the statement over all children |
| LOADS | NUMBER | The number of times the object was loaded or reloaded |
| FIRST_LOAD_TIME | VARCHAR2(19) | The time stamp of the parent creation time |
| INVALIDATIONS | NUMBER | The sum of invalidations over all the children |
| PARSE_CALLS | NUMBER | The sum of all parse calls to all the child cursors under this parent |
| DISK_READS | NUMBER | The sum of the number of disk reads over all child cursors |
| BUFFER_GETS | NUMBER | The sum of buffer gets over all child cursors |
| ROWS_PROCESSED | NUMBER | The total number of rows the parsed SQL statement returns |
| COMMAND_TYPE | NUMBER | The Oracle command type definition |
| OPTIMIZER_MODE | VARCHAR2(10) | Mode under which the SQL statement is executed |
| PARSING_USER_ID | NUMBER | The user ID of the user that has parsed the very first cursor under this parent |
| PARSING_SCHEMA_ID | NUMBER | The schema ID that was used to parse this child cursor |
| KEPT_VERSIONS | NUMBER | The number of child cursors that have been marked to be kept using the dbms_shared_pool package |
| ADDRESS | RAW(4) | The address of the handle to the parent for this cursor |
| HASH_VALUE | NUMBER | The hash value of the parent statement in the library cache |
| MODULE | VARCHAR2(64) | Contains the name of the module that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_MODULE |
| MODULE_HASH | NUMBER | The hash value of the module that is named in the MODULE column |
| ACTION | VARCHAR2(64) | Contains the name of the action that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_ACTION |
| ACTION_HASH | NUMBER | The hash value of the action that is named in the ACTION column |
| SERIALIZABLE_ ABORTS | NUMBER | The number of times the transaction fails to serialize, producing ORA-8177 errors, per cursor |
| This Column | Datatype | Represents This |
| ADDRESS | RAW | Used with HASH_VALUE to identify uniquely a cached cursor |
| HASH_VALUE | NUMBER | Used with ADDRESS to identify uniquely a cached cursor |
| PIECE | NUMBER | Number used to order the pieces of SQL text |
| SQL_TEXT | VARCHAR2 | A column containing one piece of the SQL text |
| COMMAND_TYPE | NUMBER | Code for the type of SQL statement (SELECT, INSERT, etc.) |
| This Column | Datatype | Represents This |
| ADDRESS | RAW | Used with HASH_VALUE to identify uniquely a cached cursor |
| HASH_VALUE | NUMBER | Used with ADDRESS to identify uniquely a cached cursor |
| PIECE | NUMBER | Number used to order the pieces of SQL text |
| SQL_TEXT | VARCHAR2 | A column containing one piece of the SQL text |
| COMMAND_TYPE | NUMBER | Code for the type of SQL statement (SELECT, INSERT, etc.) |
| This Column | Datatype | Represents This |
| STATISTIC# | NUMBER | Statistic number |
| NAME | VARCHAR2 | Statistic name |
| CLASS | NUMBER | Statistic class: 1 (User), 2 (Redo), 4 (Enqueue), 8 (Cache), 16 (OS), 32 (Parallel Server), 64 (SQL), 128 (Debug) |
For more information, see the Trusted Oracle7 Server Administrator's Guide.
| This Column | Datatype | Represents This |
| STATISTIC# | NUMBER | Statistic number |
| NAME | VARCHAR2(64) | Statistic name |
| CLASS | NUMBER | Statistic class: 1 (User), 2 (Redo), 4 (Enqueue), 8 (Cache), 16 (OS), 32 (Parallel Server), 64 (SQL), 128 (Debug) |
| VALUE | NUMBER | Statistic value |
| This Column | Datatype | Represents This |
| OPENS | NUMBER | Cumulative total of cursor opens |
| HITS | NUMBER | Cumulative total of cursor open hits |
| HIT_RATIO | NUMBER | Ratio of the number of times you found an open cursor divided by the number of times you looked for a cursor |
Note that the TIME_WAITED and AVERAGE_WAIT columns will contain a value of zero on those platforms that do not support a fast timing mechanism. If you are running on one of these platforms and you wish this column to reflect true wait times, you must set TIMED_STATISTICS to TRUE in the parameter file. Please remember that doing this will have a small negative effect on system performance.
| This Column | Datatype | Represents This |
| EVENT | VARCHAR2(64) | The name of the wait event |
| TOTAL_WAITS | NUMBER | The total number of waits for this event |
| 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 |
| This Column | Datatype | Represents This |
| THREAD# | NUMBER | Thread number |
| STATUS | VARCHAR2 | Thread status: OPEN, CLOSED |
| ENABLED | VARCHAR2 | Enabled status: DISABLED, (enabled) PRIVATE, or (enabled) PUBLIC |
| GROUPS | NUMBER | Number of log groups assigned to this thread |
| INSTANCE | VARCHAR2 | Instance name, if available |
| OPEN_TIME | VARCHAR2 | Last time the thread was opened |
| CURRENT_GROUP# | NUMBER | Current log group |
| SEQUENCE# | NUMBER | Sequence number of current log |
| CHECKPOINT_ CHANGE# | NUMBER | SCN at last checkpoint |
| CHECKPOINT_TIME | VARCHAR2 | Time of last checkpoint |
| This Column | Datatype | Represents This |
| HSECS | NUMBER | Elapsed time in hundredths of a second |
| This Column | Datatype | Represents This |
| ADDR | RAW(4) | Address of transaction state object |
| XIDUSN | NUMBER | Undo segment number, invalid if inactive |
| XIDSLOT | NUMBER | Slot number, invalid if inactive |
| XIDSQN | NUMBER | Sequence number, invalid if inactive |
| UBAFIL | NUMBER | Undo block address (UBA) filenum, invalid if inactive |
| UBABLK | NUMBER | UBA block number, invalid if inactive |
| UBASQN | NUMBER | UBA sequence number, invalid if inactive |
| UBAREC | NUMBER | UBA record number, invalid if inactive |
| STATUS | VARCHAR2(16) | Status |
| START_TIME | VARCHAR2(20) | Start time |
| START_SCNB | NUMBER | Start system change number (SCN) base |
| START_SCNW | NUMBER | Start SCN wrap |
| START_UEXT | NUMBER | Start extent number |
| START_UBAFIL | NUMBER | Start UBA file number |
| START_UBABLK | NUMBER | Start UBA block number |
| START_UBASQN | NUMBER | Start UBA sequence number |
| START_UBAREC | NUMBER | Start UBA record number |
| SES_ADDR | RAW(4) | Session object address |
| FLAG | NUMBER | Flag |
| SPACE | VARCHAR2(3) | Is a space transaction |
| RECURSIVE | VARCHAR2(3) | Is a recursive transaction |
| NOUNDO | VARCHAR2(3) | Is a noundo transaction |
| PRV_XIDUSN | NUMBER | Parent transaction ID |
| PRV_XIDSLT | NUMBER | Parent transaction slot number |
| PRV_XIDSQN | NUMBER | Parent transaction sequence number |
| USED_UBLK | NUMBER | Undo blocks used |
| USED_UREC | NUMBER | Undo record used |
| LOG_IO | NUMBER | Logical I/O |
| PHY_IO | NUMBER | Physical I/O |
| CR_GET | NUMBER | Consistent gets |
| CR_CHANGE | NUMBER | Consistent changes |
| This Column | Datatype | Represents This |
| COMPONENT | VARCHAR2 | Component name, such as segment or buffer header |
| TYPE | VARCHAR2 | Component type |
| DESCRIPTION | VARCHAR2 | Description of component |
| SIZE | NUMBER | Size of component |
| This Column | Datatype | Represents This |
| BANNER | VARCHAR2 | Component name and version number |
| This Column | Datatype | Represents This |
| CLASS | VARCHAR2 | Class of block subject to contention |
| COUNT | NUMBER | Number of waits by this OPERATION for this CLASS of block |
| TIME | NUMBER | Sum of all wait times for all the waits by this OPERATION for this CLASS of block |
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |