Table of Contents
A dynamic view, which is also called Dynamic Performance View (DPV), belongs to the SYS user schema. Dynamic views are used by the DBA to monitor the internal operating status of Tibero and thus users are required to have DBA authority when viewing most of the dynamic views.
Unlike static views providing structural information of the database, dynamic views provide status information of the database at a specific point in time. As the information of dynamic views is provided by information stored in memory and control files, it provides accurate information of the current status of the database.
As the information written to memory and the control file reflects changes over time, dynamic views are recommended for database monitoring and tuning. The dynamic views can be seen using the tbSQL tool, but are not updatable or deletable as they are managed by system.
This section provides the list of dynamic views of Tibero in alphabetical order and describes the columns which corresponds to each dynamic view in the table.
The following is the list of summarized dynamic views:
Dynamic View | Description |
---|---|
V$ACCESS | Displays information for the objects that the session is accessing. |
V$ACTIVE_SESSION_HISTORY | Displays information on the active session history. To enable ACTIVE_SESSION_HISTORY, TOTAL_SHM_SIZE needs to be greater than 2GB. |
V$ALL_FILESTAT | Displays statistics of data file and temp file I/O. |
V$ARCHIVED_LOG | Displays archive log information. |
V$ARCHIVE_DEST_FILES | Displays archive log information for archive log files in LOG_ARCHIVE_DEST. |
V$AS_ALIAS | All Aliases in all disk spaces mounted by the AS instance. |
V$AS_DISK | All disks discovered by the AS instance. |
V$AS_DISKSPACE | All disk spaces discovered by the AS instance. |
V$AS_EXTENT_MAP | Extent map of each file in each disk space |
V$AS_FILE | All files in all disk spaces mounted by the AS instance. |
V$AS_OPERATION | Ongoing operations in each disk space |
V$AS_USER | All users in all disk spaces mounted by the AS instance. |
V$AS_USERGROUP | All usergroups in all disk spaces mounted by the AS instance. |
V$AS_USERGROUP_MEMBER | Each group members in all disk spaces mounted by the AS instance. |
V$AUTO_COALESCE | Displays statistics of an index auto coalesce operations |
V$BACKUP | Displays the status of online backup. |
V$BACKUP_ARCHIVED_LOG | Displays information of backup archive logs. |
V$BACKUP_SET | Displays information of backup sets. |
V$BACKUP_SET_TABLESPACE | Displays information of backup tablespace |
V$BG_SESSION | Displays information on each background session. |
V$BG_SESSTAT | Displays statistics of each background session. |
V$BH | Displays information for all buffers. |
V$BLOCKER_SESSION | Displays blocker sessions which are not blocked by other session. |
V$BPARAMETERS | Views the value of the parameter set in BTIP. |
V$BUFFER_CACHE_ADVICE | Display the estimate hit ratios |
V$BUFFER_POOL | Displays information for buffer pools. |
V$BUFFER_POOL_STATISTICS | Displays statistics of buffer pools. |
V$CHECKPOINT | Displays information for checkpoints. |
V$CLUSTER_INTERCONNECTS | Displays interconnects available for use in cluster communication. |
V$CONTEXT | Displays context attributes in the current session. |
V$CONTROLFILE | Displays information for control files. |
V$CORRUPT_XID | Displays transactions whose recovery is suspended. |
V$DATABASE | Displays database status information. |
V$DATAFILE | Displays information for data files in the database. |
V$DATAFILE_HEADER | Displays information of datafiles by extracting it from file headers. |
V$DBFILE | Displays datafile names in the database. |
V$DBLINK | Displays information for the open database links in the current session. |
V$DBLINK_STATUS | Displays gateway and remote session information for the open database links in each session. |
V$DB_PIPES | Displays DBMS_PIPE information. |
V$ENCRYPTED_TABLESPACES | Displays information on encrypted tablespaces. |
V$ENCRYPTION_WALLET | Displays information of the status of the wallet. |
V$ENQUEUE_STAT | Displays statistics of locks. |
V$EST_CHECKPOINT_TIME | Displays information for estimated checkpoint time. |
V$EVENT_NAME | Displays event names. |
V$FILESTAT | Displays file read/write statistics. |
V$GLOBAL_TRANSACTION | Displays information of the currently running global transactions. |
V$INSTANCE | Displays instance statuses. |
V$INSTANCEMETRIC | Shows the load status of the current node in TAC. |
V$INSTANCEMETRIC_HISTORY | Shows the load status of the current node in TAC. |
V$INTERCONNECT_LATENCY | Displays interconnect latency in cluster |
V$INTERCONNECTION | Displays information of interconnection(s) in cluster |
V$IPARAM_DESC | Displays initialization parameters. |
V$LATCH | Displays spinlock information. |
V$LIBRARYCACHE | Displays information of PP cache and DD cache. |
V$LICENSE | Displays license information. |
V$LOCK | Displays locks that are currently in use. |
V$LOG | Displays information of log groups. |
V$LOGFILE | Displays information of log member files. |
V$LOG_HISTORY | Displays log history information. |
V$MEDIA_RECOVERY_PROGRESS | Displays the progress of media recovery. |
V$METRIC | Displays information on the metric history. |
V$METRICNAME | Displays specifications of the metrics. |
V$MYSTAT | Displays statistics of the current session. |
V$OBJECT_USAGE | Displays statistics about index usage gathered from the database for the indexes owned by the current user. |
V$OPEN_CURSOR | Displays information for all open cursors for each session |
V$OSSTAT | Displays various OS statistics. |
V$OSSTAT2 | Displays various OS statistics. |
V$PARAMETERS | Displays values of initialization parameters. |
V$PATCH | Displays list of patch files. |
V$PE_PESPLAN | Displays elapsed time information of PES in the format of SQL execution plan. |
V$PE_PESSTAT | Displays statistics of each PES for parallel processing. |
V$PE_PROCESS | Displays information of each parallel execution process currently allocated by QC session. |
V$PE_SESSION | Displays sessions in parallel processing. |
V$PE_SYSSTAT | Displays statistics of parallel execution. |
V$PE_TQSTAT | Displays statistics of table queues for parallel processing. |
V$PGASTAT | Displays statistics of PGA. |
V$PROCESS | Displays processes and threads used in RDBMS. |
V$PROXY_SQLTEXT | Displays texts of SQL statements that are remotely executed through the database link. |
V$PROXY_SQL_PARAM | Displays bind parameters of SQL statements that are remotely executed through the database link. |
V$RECOVERY_FILE_STATUS | Displays the status of a file that is being recovered. |
V$RECOVERY_PROGRESS | Displays recovery progress. |
V$RECOVERY_STATUS | Displays the status of media recovery progress. |
V$RECOVER_FILE | Displays files which require media recovery. |
V$RESERVED_WORDS | Displays a list of all SQL keywords. |
V$RESULT_CACHE_DEPENDENCY | Displays the depends-on relationship between cached result and dependencies. |
V$RESULT_CACHE_OBJECTS | Displays Result Cache Objects and their attributes. |
V$RESULT_CACHE_OBJECT_STATISTICS | Displays Result Cache Object statistics. |
V$RESULT_CACHE_STATISTICS | Displays various Result Cache settings and usage statistics. |
V$ROLLSTAT | Displays statistics of Undo usage. |
V$ROWCACHE | Displays details of status information on DD cache. |
V$RSBSTAT | Displays Stats of RSBs |
V$RSRC_CONSUMER_GROUP | Displays data related to currently active resource consumer groups |
V$RSRC_PLAN | Displays the names of all currently active resource plans |
V$RSRC_SESSION_INFO | Displays resource manager statistics per session |
V$RUNNING_JOBS | Displays information on running jobs. |
V$RUNNING_JOBS_WITH_NAME | Displays information on running jobs with name. |
V$SEGMENT_STATISTICS | !!! No documentation yet !!! |
V$SESSION | Displays information on each session. |
V$SESSION_EVENT | Displays events that are performed in each session. |
V$SESSION_IO | Displays input/output which occurred in each session. |
V$SESSION_LONGOPS | Displays status of sessions taking longer time than specified in LONGOPS_THRESHOLD_SEC seconds. |
V$SESSION_WAIT | Displays the waiting status of each session. |
V$SESSTAT | Displays statistics of each session. |
V$SESS_TIME_MODEL | Displays the session-level time statistics. |
V$SGA | Displays information on shared memory used in the database. |
V$SGASTAT | Displays information on the shared pool for a database, such as its purpose and how much of the shared pool is used. |
V$SHP_ADVICE | Displays advice information on the shared pool for a database, such as a recommended shared pool size. |
V$SPINUSE_STAT | Displays statistics of Spinlock. |
V$SQL | Displays details of a SQL child cursor execution. |
V$SQLAREA | Displays details of SQL statement execution. |
V$SQLSTATS | Displays information on SQL statement execution. |
V$SQLTEXT | Displays texts of SQL statements belonging to the cursor. |
V$SQLTEXT2 | Displays texts of SQL statements belonging to the cursor. |
V$SQLTEXT_WITH_NEWLINES | Displays texts of SQL statements belonging to the cursor, without removing newline characters or blank characters. |
V$SQLTEXT_WITH_NEWLINES2 | Displays texts of SQL statements belonging to the cursor, without removing newline characters or blank characters. |
V$SQL_BIND_CAPTURE | Displays the values of the captured bind variables from the current session |
V$SQL_BIND_CAPTURE_ALL | Displays the values of the captured bind variables from all sessions |
V$SQL_EXPC_INFO | Displays information about expression execution of a physical plan |
V$SQL_MONITOR | Shows the details of SQL statements that are currently being monitored or those that have been monitored through the real time SQL monitoring function. |
V$SQL_PLAN | Displays information on physical plans to execute SQL statements. |
V$SQL_PLAN_MONITOR | Displays plan level information about each SQL execution registered in V$SQL_MONITOR. Each row corresponds to a single plan operation of one SQL execution. |
V$SQL_PLAN_STATISTICS | Displays statistics of results of each physical plan job. |
V$SQL_WORKAREA | Display information about work areas used by SQL cursors. |
V$SQL_WORKAREA_HISTOGRAM | Display the cumulative work area execution statistics |
V$SSVR_CLIENT | Displays information of valid connection to the storage server instance. |
V$SSVR_FLASHCACHE | Displays information of flashcaches used by the storage server instance. |
V$SSVR_GRID_DISK | Displays information of grid disks. |
V$SSVR_IOLOAD | IO load of storage server |
V$SSVR_IORM_CATEGORY_PLAN | Category Plan List used by the storage server instance |
V$SSVR_IORM_DB_PLAN | DB Plan List used by the storage server instance |
V$SSVR_IOSTAT | IO stat per sec of storage server |
V$SSVR_MEMSTAT | Memory stat of storage server |
V$SSVR_SLAB_STAT | used buffer slab information of storage server |
V$SSVR_STORAGE_DISK | Displays information of storage disks used by the storage server instance. |
V$STANDBY | Displays information on the database duplication in the RDBMS Standby Cluster. |
V$STANDBY_DEST | Displays information on the standby DB in the RDBMS Standby Cluster. |
V$STANDBY_STATUS | Displays information on log duplication of the primary DB in the RDBMS Standby Cluster. |
V$STATNAME | Displays the detailed information on the statistics event. |
V$SYSSTAT | Displays statistics of a system. |
V$SYSSTAT_VALUE | Displays values for getting statistics of a system. |
V$SYSTEM_EVENT | Displays events performing in the system. |
V$SYSTEM_PARAMETERS | Displays system parameter values. |
V$SYS_TIME_MODEL | Displays the system-level time statistics. |
V$TABLESPACE | Displays information on tablespaces. |
V$TACSTAT | Displays statistics of a TAC. |
V$TEMPFILE | Displays temporary files in the database. |
V$TEMPSEG_OP_USAGE | Displays temporary segments usage in the server. |
V$TEMPSEG_USAGE | Displays temporary segments in the server. |
V$TEMPSTAT | Displays statistics of file I/O. |
V$THR_ACT_INFO | Displays information on activities of the threads. |
V$THR_EVENT | Displays events that are performed in each thread. |
V$TIMER | Displays information on the system timer. |
V$TIMEZONE_NAMES | Displays timezone names. |
V$TRANSACTION | Displays ongoing transactions. |
V$TSN_TIME | Displays the mapping table of TSN and time. |
V$UNDOSTAT | Displays statistics of Undo storage. |
V$UNDO_FREE_SPACE | Displays free space in block count for each UNDO tablespace. |
V$USGMT_TXENTRY | Displays undo segment transaction entry information. |
V$VERSION | Displays RDBMS version information. |
V$WAITER_SESSION | Displays information on the sessions that wait for a lock acquisition |
V$WAITSTAT | Displays information on block wait events for each block type and class |
V$WAITUSE_STAT | Displays information on block wait events |
V$WAITUSE_THR_STAT | Displays information on block wait events for each block type, class, and pin usage |
Displays information for the objects that the session is accessing.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID |
OWNER | VARCHAR(128) | Owner of the object |
OBJECT | VARCHAR(128) | Name of the object |
TYPE | VARCHAR(23) | Type of the object |
Displays information on the active session history. To enable ACTIVE_SESSION_HISTORY, TOTAL_SHM_SIZE needs to be greater than 2GB.
Columns
Column Name | Data Type | Description |
---|---|---|
SAMPLE_ID | NUMBER | Sample ID |
THREAD# | NUMBER | Thread# |
SAMPLE_TIME | DATE | Sample Time |
SID | NUMBER | Session ID |
SESS_SERIAL_NO | NUMBER | A serial number of a session |
USER_NO | NUMBER | The user's ID |
WAIT_EVENT | NUMBER | Type of the wait event which the session waits |
ID1 | NUMBER | First Parameter |
ID2 | NUMBER | Second Parameter |
WE_SEQ | NUMBER | Sequence# of the Wait Event |
TIME_WAITED | NUMBER | The amount of time waited for this event (msec) |
WAIT_OBJ_ID | NUMBER | ID of the object which the session waits |
WAIT_FILE_NO | NUMBER | Number of the file which the session waits |
WAIT_BLOCK_NO | NUMBER | Number of the block which the session waits |
WAIT_ROW_NO | NUMBER | Number of the row which the session waits |
USGMT_ID | NUMBER | USGMT number at the time of sampling |
SLOTNO | NUMBER | Slot number at the time of sampling |
WRAPNO | NUMBER | Wrap number at the time of sampling |
SQL_ID | VARCHAR(13) | SQL identifier |
SQL_CHILD_NUMBER | NUMBER | The number of this child physical plan |
CURR_HASHVAL | NUMBER | SQL hash value |
MODULE_NAME | VARCHAR(64) | Name of a module specified by dbms_application_info.set_module |
ACTION_NAME | VARCHAR(64) | Name of an action specified by dbms_application_info.set_module/action |
CLIENT_INFO_NAME | VARCHAR(64) | Name of client_info specified by dbms_application_info.set_client_info |
PROG_NAME | VARCHAR(30) | Client program name |
SQL_EXEC_START | DATE | The time when the execution started. |
SQL_EXEC_ID | NUMBER | Execution identifier. It is necessary to identify multiple simultaneous SQL executions. To identify a single SQL execution, three columns; SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID must be defined. The three column values are used to match with corresponding rows in the V$SQL_MONITOR. |
SQL_PLAN_LINE_ID | NUMBER | Identifier of the plan operation |
PORT | NUMBER | Client port number |
DELTA_TIME | NUMBER | Time interval (in seconds) since the last time this session was sampled or created. |
DELTA_PHY_READ_BLKS | NUMBER | The number of blocks read from disk over the last DELTA_TIME seconds |
DELTA_PHY_WRITE_BLKS | NUMBER | The number of blocks written to disk over the last DELTA_TIME seconds |
DELTA_LOG_READ_BLKS | NUMBER | The number of blocks read over the last DELTA_TIME seconds |
PGA_SIZE | NUMBER | The amount of PGA memory consumed by this session |
Displays statistics of data file and temp file I/O.
Columns
Column Name | Data Type | Description |
---|---|---|
FILE# | NUMBER | File number |
TEMPFILE | CHAR(1) | Whether this is tempfile |
PHYRDS | NUMBER | The number of reading times |
PHYWRTS | NUMBER | The number of writing times |
PHYBLKRD | NUMBER | The number of read blocks |
PHYBLKWRT | NUMBER | The number of written blocks |
SINGLEBLKRDS | NUMBER | The number of times of reading a single block |
READTIM | NUMBER | Reading time (msec) |
WRITETIM | NUMBER | Writing time (msec) |
SINGLEBLKRDTIM | NUMBER | Time for which a single block was read (msec) |
AVGIOTIM | NUMBER | Average I/O time (msec) |
LSTIOTIM | NUMBER | Last I/O time (msec) |
MINIOTIM | NUMBER | Shortest I/O time (msec) |
MAXIORTM | NUMBER | Longest reading time (msec) |
MAXIOWTM | NUMBER | Longest writing time (msec) |
Displays archive log information.
Columns
Column Name | Data Type | Description |
---|---|---|
RECID | NUMBER | Archive log record ID |
THREAD# | NUMBER | Log group number recorded by instances of a server |
NAME | VARCHAR(256) | Archive log file path and name |
SEQUENCE# | NUMBER | Sequence number of the log group |
FIRST_CHANGE# | NUMBER | TSN associated with the first archive log that belongs to the log group |
FIRST_TIME | DATE | Time when the first archive log that belongs to the log group was recorded |
NEXT_CHANGE# | NUMBER | TSN associated with the next archive log that belongs to the log group |
RESETLOGS_CHANGE# | NUMBER | Resetlogs TSN of the database when the log was written |
RESETLOGS_TIME | DATE | Resetlogs time of the database when the log was written |
FILESIZE | NUMBER | Size of logfile in bytes |
BLOCKS | NUMBER | Number of blocks in logfile |
BLOCK_SIZE | NUMBER | Size of log block |
Displays archive log information for archive log files in LOG_ARCHIVE_DEST.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(256) | Archive log file path and name |
THREAD# | NUMBER | Log group number recorded by instances of a server |
SEQUENCE# | NUMBER | Sequence number of the log group |
FIRST_CHANGE# | NUMBER | TSN associated with the first archive log that belongs to the log group |
FIRST_TIME | DATE | Time when the first archive log that belongs to the log group was recorded |
NEXT_CHANGE# | NUMBER | TSN associated with the next archive log that belongs to the log group |
RESETLOGS_CHANGE# | NUMBER | Resetlogs TSN of the database when the log was written |
RESETLOGS_TIME | DATE | Resetlogs time of the database when the log was written |
All Aliases in all disk spaces mounted by the AS instance.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(48) | File Alias(File Name String) |
DISKSPACE_NUMBER | NUMBER | Disk space number that the alias belongs to |
FILE_NUMBER | NUMBER | File number for the alias |
FILE_INCARNATION | NUMBER | File incarnation number for the alias |
ALIAS_INCARNATION | NUMBER | Alias incarnation number |
All disks discovered by the AS instance.
Columns
Column Name | Data Type | Description |
---|---|---|
DISKSPACE_NUMBER | NUMBER | Disk space number that the disk belongs to |
DISK_NUMBER | NUMBER | Disk number in the disk space |
STATE | VARCHAR(16) | Disk state in the disk space - ONLINE - PREPARE_ADD - PREPARE_DROP - ADDING - DROPPING - SYNC - FAIL |
OS_MB | NUMBER | Disk size recognized by the OS |
TOTAL_MB | NUMBER | Disk size |
FREE_MB | NUMBER | Unused disk space |
NAME | VARCHAR(48) | Disk name |
FAILGROUP | VARCHAR(48) | Name of the failure group that the disk belongs to |
PATH | VARCHAR(256) | Absolute path of the disk |
CREATE_DATE | DATE | Date when the disk was added to the disk space |
All disk spaces discovered by the AS instance.
Columns
Column Name | Data Type | Description |
---|---|---|
DISKSPACE_NUMBER | NUMBER | Number assigned to the disk space |
NAME | VARCHAR(48) | Name of the disk space |
SECTOR_SIZE | NUMBER | Physical block size (in bytes) |
BLOCK_SIZE | NUMBER | AS metadata block size (in bytes) |
ALLOCATION_UNIT_SIZE | NUMBER | Size of the allocation unit (in bytes) |
STATE | VARCHAR(16) | State of the disk space on the AS instance - ASSIGNED - MOUNTING - CREATING - MOUNT |
TYPE | VARCHAR(8) | Redundancy level of the disk space - EXTERN - NORMAL - HIGH |
TOTAL_MB | NUMBER | Total capacity of the disk space |
FREE_MB | NUMBER | Unused capacity of the disk space |
REQUIRED_MIRROR_FREE_MB | NUMBER | Required free capacity of the disk space for disk failure |
USABLE_FILE_MB | NUMBER | Usable capacity of the disk space |
Extent map of each file in each disk space
Columns
Column Name | Data Type | Description |
---|---|---|
DISKSPACE_NUMBER | NUMBER | Disk space number that the file belongs to |
FILE_NUMBER | NUMBER | File number in the disk space |
EXTENT_NUMBER | NUMBER | Extent number in the file |
REDUN_NUMBER | NUMBER | Redundancy number of the extent - 0 primary - 1, 2 redun copy |
EXTENT_SIZE | NUMBER | # of AUs in the extent |
DISK_NUMBER | NUMBER | Disk number in the disk space |
AU_NUMBER | NUMBER | AU number in the disk |
All files in all disk spaces mounted by the AS instance.
Columns
Column Name | Data Type | Description |
---|---|---|
DISKSPACE_NUMBER | NUMBER | Disk space number that the file belongs to |
FILE_NUMBER | NUMBER | File number in the disk space |
INCARNATION | NUMBER | File incarnation number |
BLOCK_SIZE | NUMBER | Block size of the file (in bytes) |
BYTES | NUMBER | Number of bytes of the file |
TYPE | VARCHAR(64) | File type - DATA - CTRL - CM |
REDUNDANCY | VARCHAR(8) | Redundancy level of the file - EXTERN - NORMAL - HIGH |
STRIPED | VARCHAR(8) | Striping type - COARSE - FINE |
Ongoing operations in each disk space
Columns
Column Name | Data Type | Description |
---|---|---|
DISKSPACE_NUMBER | NUMBER | Disk space number that the operation belongs to |
OPERATION | VARCHAR(16) | Type of the operation |
STATE | VARCHAR(8) | State of the operation |
POWER | NUMBER | Power requested for the operation |
JOB_DONE | NUMBER | # of extents that have been processed so far |
JOB_REMAIN | NUMBER | # of remained extents that have to be processed |
JOB_TOTAL | NUMBER | # of extents that have to be processed by the operation |
EST_SPEED | NUMBER | extents processed divided by elapsed time |
EST_REMAIN_TIME | VARCHAR(32) | job_total divided by ext_speed |
All users in all disk spaces mounted by the AS instance.
Columns
Column Name | Data Type | Description |
---|---|---|
DISKSPACE_NUMBER | NUMBER | Disk space number that the file belongs to |
USER_NUMBER | NUMBER | User number in the disk space |
INCARNATION | NUMBER | User incarnation number |
USER_NAME | VARCHAR(128) | User name |
All usergroups in all disk spaces mounted by the AS instance.
Columns
Column Name | Data Type | Description |
---|---|---|
DISKSPACE_NUMBER | NUMBER | Disk space number that the file belongs to |
USERGROUP_NUMBER | NUMBER | Usergroup number in the disk space |
INCARNATION | NUMBER | Usergroup incarnation number |
OWNER_NUMBER | NUMBER | Owner number of the usergroup |
OWNER_INCARNATION | NUMBER | Owner incarnation number |
USERGROUP_NAME | VARCHAR(128) | Usergroup name |
Each group members in all disk spaces mounted by the AS instance.
Columns
Column Name | Data Type | Description |
---|---|---|
DISKSPACE_NUMBER | NUMBER | Disk space number that the file belongs to |
MEMBER_NUMBER | NUMBER | Member number in the usergroup |
MEMBER_INCARNATION | NUMBER | Member incarnation number |
USERGROUP_NUMBER | NUMBER | Usergroup number |
USERGROUP_INCARNATION | NUMBER | Usergroup incarnation number |
Displays statistics of an index auto coalesce operations
Columns
Column Name | Data Type | Description |
---|---|---|
BUCKET# | NUMBER | Hash bucket number of the coalescing request list |
SGMT_ID | NUMBER | Segment ID of the index to be automatically coalesced |
COAL_REQ_CNT | NUMBER | Total number of the coalescing requests |
COAL_CNT | NUMBER | Total number of the coalescing operations |
Displays the status of online backup.
Columns
Column Name | Data Type | Description |
---|---|---|
FILE# | NUMBER | File number |
STATUS | VARCHAR(10) | Status information - Active: online backup is being performed - Not Active : online backup is not being performed |
CHANGE# | NUMBER | TSN for which backup is started if online backup is being performed |
TIME | DATE | Time when the backup was started if online backup is being performed |
Displays information of backup archive logs.
Columns
Column Name | Data Type | Description |
---|---|---|
SET_ID | NUMBER | Id of backup archivelogs |
THREAD# | NUMBER | Log group number recorded by instances of a server |
MIN_LOG_TIME | DATE | Minimum begin time of backup archivelogs |
MAX_LOG_TIME | DATE | Maximum end time of backup archivelogs |
MIN_LOG_TSN | NUMBER | Minimum begin TSN of backup archivelogs |
MAX_LOG_TSN | NUMBER | Maximum end TSN of backup archivelogs |
MIN_LOG_SEQ | NUMBER | Minimum sequence of backup archivelogs |
MAX_LOG_SEQ | NUMBER | Maximum sequence of backup archivelogs |
RESETLOG_TIME | DATE | Resetlog time of backup archivelogs |
RESETLOG_TSN | NUMBER | Resetlog TSN of backup archivelogs |
Displays information of backup sets.
Columns
Column Name | Data Type | Description |
---|---|---|
SET_ID | NUMBER | Id of backup set |
START_TIME | DATE | Begin time of backup |
FINISH_TIME | DATE | End time of backup |
START_TSN | NUMBER | Start TSN of backup |
FINISH_TSN | NUMBER | Finish TSN of backup |
RESETLOGS_TSN | NUMBER | Resetlogs TSN of backup |
BASE_SET | NUMBER | Base set of backup |
SIZE | NUMBER | Size of backup set |
IS_PARTIAL | VARCHAR(10) | Type of backup |
IS_INCREMENTAL | VARCHAR(10) | Type of backup |
WITH_ARCHIVELOG | VARCHAR(10) | Backed up with archivelogs required for point in time recovery |
Displays information of backup tablespace
Columns
Column Name | Data Type | Description |
---|---|---|
SET_ID | NUMBER | Id of backup set |
TSID | NUMBER | Tablespace ID |
Displays information on each background session.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID |
STATUS | VARCHAR(32) | Status of session - READY: The session is connected - RUNNING: The session is running - TX_RECOVERING: The transaction is being recovered - SESS_CLEANUP: The session resources are being cleaned up - ASSIGNED: Connected to database, but the session is closed |
TYPE | VARCHAR(15) | Session type - WTHR: Working thread - CTHR: Control thread - LGWR: Log writing process - CKPT: Checkpoint process - LARC: Log archive - AGENT: Sequence process - MTHR: Monitoring process - DBWR: Datablock writing process - LNW: Log network writing process |
STATE | VARCHAR(25) | State of working thread - INVALID: Not initialized - NEW: Being created - IDLE: Ready to run - RUNNING: Running - WAITING: Waiting for internal message - RECV_WAITING: Waiting for client message - STOP_BY_MTHR: Stopped by MTHR - DEAD: Dead |
WLOCK_WAIT | VARCHAR(18) | Type of wlock for which the session waits |
WAIT_EVENT | NUMBER | Type of wait_event for which the session waits |
WAIT_TIME | NUMBER | Waiting time of wait_event for which the session waits |
PGA_USED_MEM | NUMBER | The amount of PGA memory occupied by the session |
PID | NUMBER | Index of a process to which a session belongs |
WTHR_ID | NUMBER | Index of a working thread process to which a session belongs |
OSUSER | VARCHAR(128) | OS account name of the session connected |
Displays statistics of each background session.
Columns
Column Name | Data Type | Description |
---|---|---|
TID | NUMBER | Session ID |
STAT# | NUMBER | Number of event to obtain statistics |
NAME | VARCHAR(60) | Name of event to obtain statistics |
CLASS | NUMBER | Class of the event |
VALUE | NUMBER | Value of the event (Corresponding to the event name) |
Displays information for all buffers.
Columns
Column Name | Data Type | Description |
---|---|---|
FILE# | NUMBER | Absolute number of the file |
BLOCK# | NUMBER | Block number |
BLKTYPE | NUMBER | Type of the block |
STATUS | VARCHAR(7) | Status of the buffer - READING - CURRENT - CR |
PIN_MODE | VARCHAR(3) | Pin mode |
TEMP | VARCHAR(1) | Whether it is a buffer of the temporary file or not |
DIRTY | VARCHAR(1) | Whether it is a modified buffer or not |
TS# | NUMBER | ID of the tablespace to which the block corresponding to the buffer belongs |
OBJD | NUMBER | ID of the segment to which the block corresponding to the buffer belongs |
Displays blocker sessions which are not blocked by other session.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID of the blocker session |
TYPE | VARCHAR(25) | Type of the lock |
ID1 | NUMBER | First ID of the lock |
ID2 | NUMBER | Second ID of the lock |
MODE_HELD | NUMBER | Mode in which the lock is held |
Views the value of the parameter set in BTIP.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(64) | Name of the parameter. |
VALUE | VARCHAR(1024) | Value of the parameter. |
Display the estimate hit ratios
Columns
Column Name | Data Type | Description |
---|---|---|
BUFFER_CACHE_SIZE | NUMBER | The buffer cache size (in Mbytes) |
HIT_RATIO | NUMBER | The estimate hit ratio at given buffer cache size |
Displays information for buffer pools.
Columns
Column Name | Data Type | Description |
---|---|---|
ID | NUMBER | Buffer pool ID |
NAME | VARCHAR(7) | Name of the buffer pool |
BLOCK_SIZE | NUMBER | Size of the block |
BUFFERS | NUMBER | The number of the buffers |
WSCNT | NUMBER | The number of the working sets of the buffers pool |
Displays statistics of buffer pools.
Columns
Column Name | Data Type | Description |
---|---|---|
WS# | NUMBER | Working set number of the buffer pool |
TOTAL_CNT | NUMBER | The total number of blocks in the working set |
MAIN_CNT | NUMBER | The number of main lists |
MAIN2_CNT | NUMBER | The number of the second main lists |
AUX_CNT | NUMBER | The number of the auxiliary lists |
HOT_CNT | NUMBER | The number of the most frequently used buffers |
CR_CNT | NUMBER | The number of the buffers read in the Consistent-Read mode |
EXL_CNT | NUMBER | The number of buffers being modified |
SHR_CNT | NUMBER | The number of buffers being scanned |
DIRTY_CNT | NUMBER | The number of the modified buffers |
SUM_GOT | NUMBER | The total number of buffers gotten by the working set |
SUM_WRITE | NUMBER | The total number of the buffers modified by the working set |
SUM_READ | NUMBER | The total number of the buffers scanned by the working set |
FREE_WAIT | NUMBER | The number of times it waited for a free buffer |
WRITE_WAIT | NUMBER | The number of times it waited to write data to disk |
BUSY_WAIT | NUMBER | The number of times it waited for the busy buffer |
FREE_SCAN | NUMBER | The number of times a free buffer is scanned |
MAIN2_SCAN | NUMBER | The number of times the second main list is scanned |
Displays information for checkpoints.
Columns
Column Name | Data Type | Description |
---|---|---|
ONGOING | NUMBER | Whether a checkpoint is activated or not - 0: Checkpoint is not activated - 1: Checkpoint is activated |
REQ_TYPE | VARCHAR(100) | The reason the checkpoint is activated |
WAITERS | NUMBER | The number of waiters waiting for checkpoint to complete |
MIN_WAIT_TSN | NUMBER | The smallest TSN among the target TSNs waiting for checkpoint to complete |
TARGET_TSN | NUMBER | The target TSN of the checkpoint that is ongoing or has recently completed (A checkpoint gets completed when the target TSN is bigger than LOW_CACHE_TSN) |
LOW_CACHE_TSN | NUMBER | TSN for which the current checkpoint is valid |
MAX_TIME | NUMBER | The longest amount of time needed to complete checkpoint |
MAX_BLOCKS | NUMBER | The maximum number of blocks used by checkpoint |
CHECKPOINT_TSN | NUMBER | TSN of the current checkpoint |
Displays interconnects available for use in cluster communication.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(15) | Name of the interconnect |
IP_ADDRESS | VARCHAR(16) | IP address of the interconnect |
IS_PUBLIC | VARCHAR(4) | Type of interface: public or private. Currently, it supports only private so is always set to NO. |
SOURCE | VARCHAR(31) | Indicates where this interface was picked up from. Currently, it is always set to 'the local_cluster_addr parameter' |
Displays context attributes in the current session.
Columns
Column Name | Data Type | Description |
---|---|---|
NAMESPACE | VARCHAR(128) | The namespace of the context attribute |
ATTRIBUTE | VARCHAR(128) | The name of the attribute |
VALUE | VARCHAR(65532) | The value of the attribute |
Displays information for control files.
Columns
Column Name | Data Type | Description |
---|---|---|
STATUS | NUMBER | Status information - 0: Normal - Negative: Error |
NAME | VARCHAR(256) | Path name for the control file |
BLKSIZE | NUMBER | The size of the control file block |
BLOCKS | NUMBER | The number of blocks in the control file |
Displays transactions whose recovery is suspended.
Columns
Column Name | Data Type | Description |
---|---|---|
XID | VARCHAR(65532) | ID of the suspended transaction |
Displays database status information.
Columns
Column Name | Data Type | Description |
---|---|---|
DBID | NUMBER | Database identifier (Unique value specified when creating a database) |
NAME | VARCHAR(40) | Name of the database (Name specified when creating a database) |
CREATE_DATE | DATE | Creation date of the database |
CURRENT_TSN | NUMBER | The current TSN of the database |
OPEN_MODE | VARCHAR(10) | Open mode of the database - MOUNTED - RECOVERY - READ ONLY - READ WRITE - NOMOUNT |
RESETLOG_TSN | NUMBER | TSN associated with the last resetlogs |
RESETLOG_DATE | DATE | The last resetlogs time |
PREV_RESETLOG_TSN | NUMBER | TSN associated with the previous resetlogs |
PREV_RESETLOG_DATE | DATE | The previous resetlogs time |
LOG_MODE | VARCHAR(12) | Archive log mode - ARCHIVELOG - NOARCHIVELOG |
CKPT_TSN | NUMBER | TSN associated with the last checkpoint |
CKPT_DATE | DATE | The last checkpoint time |
CPU_NAME | VARCHAR(32) | The CPU type name |
PLATFORM_NAME | VARCHAR(32) | The OS platform name |
CPU_MODEL | VARCHAR(256) | The CPU model name |
OS_UPTIME | VARCHAR(256) | The OS uptime info |
Displays information for data files in the database.
Columns
Column Name | Data Type | Description |
---|---|---|
FILE# | NUMBER | Datafile number |
CREATE_TSN | NUMBER | TSN that created the datafile |
CREATE_DATE | DATE | Datafile created time |
TS# | NUMBER | Tablespace number to which the datafile belongs |
RFILE# | NUMBER | Relative datafile number in the tablespace |
STATUS | VARCHAR(7) | Status information of the datafile - OFFLINE: Datafile needs to be deleted - ONLINE: Datafile is in the normal state - RECOVER: Datafile needs to be recovered |
ENABLED | CHAR(10) | File accessibility - DISABLED: Unable to read/write - READ ONLY: Readable only - READ WRITE: Readable and writable |
CKPT_TSN | NUMBER | TSN associated with the last checkpoint |
CKPT_DATE | DATE | The last checkpoint time |
CREATE_BYTES | NUMBER | Size of the datafile at the time when it is created |
NAME | VARCHAR(256) | Path name of the datafile |
Displays information of datafiles by extracting it from file headers.
Columns
Column Name | Data Type | Description |
---|---|---|
FILE# | NUMBER | Datafile number |
STATUS | VARCHAR(7) | Status of the datafile - OFFLINE - ONLINE |
DBID | NUMBER | ID of the database to which the datafile belongs |
DBNAME | VARCHAR(40) | Name of the database to which the datafile belongs |
BLKSIZE | NUMBER | Size of the block |
RECOVER | VARCHAR(3) | Media recovery necessity |
FUZZY | VARCHAR(3) | Whether or not the datafile has been normally closed |
CREATE_TSN | NUMBER | TSN that created the datafile |
CREATE_DATE | DATE | Datafile created time |
TABLESPACE_NAME | VARCHAR(128) | Name of the tablespace to which the datafile belongs |
TS# | NUMBER | Tablespace number to which the datafile belongs |
RFILE# | NUMBER | Tablespace relative datafile number |
RESETLOGS_TSN | NUMBER | TSN associated with the last reset logs |
RESETLOGS_DATE | DATE | The last resetlogs time |
CKPT_TSN | NUMBER | TSN associated with the last checkpoint |
CKPT_DATE | DATE | The last checkpoint time |
CKPT_COUNT | NUMBER | Checkpoint count |
FILESIZE | NUMBER | Size of the datafile |
NAME | VARCHAR(256) | Path name of the datafile |
Displays datafile names in the database.
Columns
Column Name | Data Type | Description |
---|---|---|
FILE# | NUMBER | Datafile number |
NAME | VARCHAR(256) | Path name of the datafile |
Displays information for the open database links in the current session.
Columns
Column Name | Data Type | Description |
---|---|---|
DB_LINK | VARCHAR(128) | Name of the database link |
OWNER_ID | NUMBER | ID of the owner of the database link |
OPEN_CURSORS | NUMBER | The number of cursors opened by the current database link |
IN_TRANSACTION | VARCHAR(3) | Whether it is a database link that is accessed from the currently running transaction or other transactions |
HETEROGENEOUS | VARCHAR(3) | Whether or not the remote database is RDBMS |
COMMIT_POINT_STRENGTH | NUMBER | Commit point strength of the remote database |
Displays gateway and remote session information for the open database links in each session.
Columns
Column Name | Data Type | Description |
---|---|---|
SESS_ID | NUMBER | Session ID |
SERIAL# | NUMBER | A serial number of a session (Like SID, used to recognize sessions) |
DB_LINK | VARCHAR(128) | Name of the database link |
OWNER_ID | NUMBER | ID of the owner of the database link |
GATEWAY_PID | NUMBER | PID of the gateway (in heterogeneous link) |
GATEWAY_NAME | VARCHAR(128) | Process name of the gateway (in heterogeneous link) |
GATEWAY_TID | NUMBER | TID (thread id) of the gateway (in heterogeneous link) |
REMOTE_SESS_INFO | VARCHAR(32) | Remote session info |
Displays DBMS_PIPE information.
Columns
Column Name | Data Type | Description |
---|---|---|
OWNERID | NUMBER | Owner id of a pipe. |
NAME | VARCHAR(1000) | Pipe name. |
TYPE | VARCHAR(7) | Pipe type(private or public). |
PIPE_SIZE | NUMBER | Maximum size of pipe. |
Displays information on encrypted tablespaces.
Columns
Column Name | Data Type | Description |
---|---|---|
TS# | NUMBER | A serial number of tablespace |
ENCRYPTIONALG | VARCHAR(7) | An encryption algorithm |
ENCRYPTEDTS | VARCHAR(3) | Whether or not the tablespace was encrypted (YES or NO) |
Displays information of the status of the wallet.
Columns
Column Name | Data Type | Description |
---|---|---|
WRL_TYPE | VARCHAR(20) | Type of the wallet resource locator |
WRL_PARAMETER | VARCHAR(4000) | Parameter of the wallet resource locator (absolute filename) |
STATUS | VARCHAR(10) | Status of the wallet |
Displays statistics of locks.
Columns
Column Name | Data Type | Description |
---|---|---|
IDX | NUMBER | Lock number |
TYPE | VARCHAR(25) | Lock type |
TOTAL_REQ# | NUMBER | Lock requested count |
TOTAL_WAIT# | NUMBER | Lock wait count |
SUCC_REQ# | NUMBER | Lock success count |
FAILED_REQ# | NUMBER | Lock failure count |
CUM_WAIT_TIME | NUMBER | Total Lock wait time (msec) |
Displays information for estimated checkpoint time.
Columns
Column Name | Data Type | Description |
---|---|---|
DIRTY_BLOCKS | NUMBER | The number of dirty blocks in buffer cache |
DISK_WRITE_TIME | NUMBER | The disk write time per block(ms) |
ESTIMATED_CHECKPOINT_TIME | NUMBER | the estimated checkpoint time(s) |
Displays event names.
Columns
Column Name | Data Type | Description |
---|---|---|
EVENT# | NUMBER | Event number |
NAME | VARCHAR(40) | Event name |
CLASS | VARCHAR(40) | Class of the event |
DESC | VARCHAR(60) | Description of the waitevent |
ID1_DESC | VARCHAR(60) | Description of the first event parameter |
ID2_DESC | VARCHAR(60) | Description of the second event parameter |
Displays file read/write statistics.
Columns
Column Name | Data Type | Description |
---|---|---|
FILE# | NUMBER | File number |
PHYRDS | NUMBER | The number of read operations performed |
PHYWRTS | NUMBER | The number of write operations performed |
PHYBLKRD | NUMBER | The number of blocks read |
PHYBLKWRT | NUMBER | The number of blocks written |
SINGLEBLKRDS | NUMBER | Single-block read count |
READTIM | NUMBER | Time spent on read operations (msec) |
WRITETIM | NUMBER | Time spent on write operations (msec) |
SINGLEBLKRDTIM | NUMBER | Time spent to read a single-block (msec) |
AVGIOTIM | NUMBER | Average input/output time (msec) |
LSTIOTIM | NUMBER | Time spent on the last input/output operation (msec) |
MINIOTIM | NUMBER | The minimum time spent on an input/output operation (msec) |
MAXIORTM | NUMBER | The maximum time spent on a read operation (msec) |
MAXIOWTM | NUMBER | The maximum time spent on a write operation (msec) |
Displays information of the currently running global transactions.
Columns
Column Name | Data Type | Description |
---|---|---|
GLOBAL_ID | VARCHAR(256) | ID of the global transaction |
SESS_ID | NUMBER | ID of the current session |
LOCAL_ID | VARCHAR(65532) | ID of the local transaction |
Displays instance statuses.
Columns
Column Name | Data Type | Description |
---|---|---|
INSTANCE_NUMBER | NUMBER | Instance number (Used by CM) |
INSTANCE_NAME | VARCHAR(40) | Name of the instance |
DB_NAME | VARCHAR(40) | Name of the database |
HOST_NAME | VARCHAR(63) | Name of the host |
PARALLEL | VARCHAR(3) | Whether clustering is activated or not |
THREAD# | NUMBER | Log group number recorded by instances of a server |
VERSION | VARCHAR(8) | Version of RDBMS |
STARTUP_TIME | DATE | Instance startup time |
STATUS | VARCHAR(16) | Current status of the server - NOMOUNT - MOUNT - NORMAL - RAISING_SVRMODE |
SHUTDOWN_PENDING | VARCHAR(4) | Whether server shutdown was requested or not |
TIP_FILE | VARCHAR(256) | Pathname of the TIP file |
Shows the load status of the current node in TAC.
Columns
Column Name | Data Type | Description |
---|---|---|
BEGIN_TIME | DATE | Begin time for load calculations |
END_TIME | DATE | End time for load calculations |
INTSIZE_CSEC | NUMBER | Interval length for load calculations (Unit: 0.01 seconds) |
ELAPSEDPERCALL | NUMBER | Average processing time per user request within the interval |
CPUPERCALL | NUMBER | Average CPU processing time per user request within the interval (currently not supported) |
DBTIMEPERCALL | NUMBER | Average DB processing time per user request within the interval (currently not supported) |
CALLSPERSEC | NUMBER | Number of user requests per second within the interval |
DBTIMEPERSEC | NUMBER | The time consumed in DB per second within the interval (currently not supported) |
GOODNESS | NUMBER | Load level of the interval (number of sessions or average processing time used) |
DELTA | NUMBER | Load fluctuation predicated for additional sessions |
Shows the load status of the current node in TAC.
Columns
Column Name | Data Type | Description |
---|---|---|
BEGIN_TIME | DATE | Begin time for load calculations |
END_TIME | DATE | End time for load calculations |
INTSIZE_CSEC | NUMBER | Interval size for load calculations (Unit: 0.01 Seconds) |
ELAPSEDPERCALL | NUMBER | Average processing time per user request within the interval |
CPUPERCALL | NUMBER | Average CPU processing time per user request within the interval (currently not supported) |
DBTIMEPERCALL | NUMBER | Average DB processing time per user request within the interval (currently not supported) |
CALLSPERSEC | NUMBER | Number of user requests per second within the interval |
DBTIMEPERSEC | NUMBER | The time consumed in DB per second within the interval (currently not supported) |
GOODNESS | NUMBER | Load level of the interval (number of sessions or average processing time used) |
DELTA | NUMBER | Load fluctuation predicated for additional sessions |
Displays interconnect latency in cluster
Columns
Column Name | Data Type | Description |
---|---|---|
REMOTE_INST_ID | NUMBER | Remote instance id |
MIN_TIME | NUMBER | min time latency(us) |
MAX_TIME | NUMBER | max time latency(us) |
AVG_TIME | NUMBER | average time latency(us) |
CUR_TIME | NUMBER | current time latency(us) |
Displays information of interconnection(s) in cluster
Columns
Column Name | Data Type | Description |
---|---|---|
CONNECTION_ID | NUMBER | Connection ID |
CONNECTION_NID | NUMBER | Remote instance node ID |
MSG_TO_SEND | NUMBER | Number of messages in send queue |
Displays initialization parameters.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(60) | Name of the initialization parameter |
DESCRIPTION | VARCHAR(65532) | Description of the initialization parameter |
Displays spinlock information.
Columns
Column Name | Data Type | Description |
---|---|---|
LATCH# | NUMBER | Spinlock number |
LEVEL# | NUMBER | Level of the spinlock |
NAME | VARCHAR(30) | Name of the spinlock |
GETS | NUMBER | The number of times a spinlock was acquired |
MISSES | NUMBER | The number of times the requester had to wait for a spinlock |
SPIN_GETS | NUMBER | The number of times the requester failed to acquire a spinlock but succeeded in gaining a spin |
SLEEPS | NUMBER | The number of times the requester was put to sleep while waiting for a spinlock |
IMMEDIATE_GETS | NUMBER | The number of times a trylock was acquired |
IMMEDIATE_MISSES | NUMBER | The number of times the requester failed to acquire a trylock |
WAIT_TIME | NUMBER | The total sleep time of spinlock |
SLEEPS_CNT | NUMBER | The number of actual sleep count while waiting for a spinlock |
Displays information of PP cache and DD cache.
Columns
Column Name | Data Type | Description |
---|---|---|
NAMESPACE | VARCHAR(16) | Name of the table - PP cache: set to ''SQL AREA'' - DD cache: set to the name of each DD table |
GETS | NUMBER | Cache total access count |
GETHITS | NUMBER | Cache hit count |
GETHITRATIO | NUMBER | Cache hit ratio (%) |
PINS | NUMBER | Number of times a PIN was requested for objects |
PINHITS | NUMBER | Number of times objects was pinned |
PINHITRATIO | NUMBER | Ratio of PINHITS to PINS |
Displays license information.
Columns
Column Name | Data Type | Description |
---|---|---|
LICENSE_VERSION | VARCHAR(50) | Version of the license |
LICENSEE | VARCHAR(100) | User of the license |
LICENSE_TYPE | VARCHAR(30) | Type of the license - DEMO - REAL |
PRODUCT_NAME | VARCHAR(100) | Name of the product |
PRODUCT_VERSION | VARCHAR(50) | Version of the product |
EDITION | VARCHAR(30) | Type of product - BASE - STANDARD - ENTERPRISE |
ISSUE_DATE | VARCHAR(10) | Issue date of the license |
EXPIRE_DATE | VARCHAR(10) | Expiration date of the license (Only for the DEMO type of license) |
LIMIT_USER | VARCHAR(10) | Limit for the number of users |
LIMIT_CPU | VARCHAR(10) | Limit for the number of CPUs |
HOST | VARCHAR(100) | Host (IP or host name) |
Displays locks that are currently in use.
Columns
Column Name | Data Type | Description |
---|---|---|
TYPE | VARCHAR(25) | Type of the Lock |
ID1 | NUMBER | The first ID of the Lock |
ID2 | NUMBER | The second ID of the Lock |
LMODE | NUMBER | Mode that the Lock owner owns. - 0: None - 1: Row-Shared(RS) - 2: Row-Exclusive(RX) - 3: Shared(S) - 4: Shared-Row-Exclusive(SRX) - 5: Exclusive(X) - 6: Pin (Special mode for TX-wait) |
REQUESTED | NUMBER | Mode that the Lock waiter requests |
SESS_ID | NUMBER | ID of the thread that owns the Lock(the same as SID) |
CTIME | NUMBER | The amount of elapsed time (in seconds) in current mode |
Displays information of log groups.
Columns
Column Name | Data Type | Description |
---|---|---|
THREAD# | NUMBER | Log group number recorded by instances of a server |
GROUP# | NUMBER | Log group number |
SEQUENCE# | NUMBER | Sequence number of the log group |
BYTES | NUMBER | Size of the log group |
MEMBERS | NUMBER | The number of log member files that belong to the log group |
ARCHIVED | VARCHAR(3) | Whether it was archived or not |
STATUS | VARCHAR(8) | Status information of the log group - ACTIVE: File archive needs to be done - UNUSED: The log file has not been used - CURRENT: The log file is currently in use - INACTIVE: File archive has been completed |
FIRST_CHANGE# | NUMBER | TSN associated with the first log that belongs to the log group |
FIRST_TIME | DATE | Time when the first log that belongs to the log group was recorded |
Displays information of log member files.
Columns
Column Name | Data Type | Description |
---|---|---|
GROUP# | NUMBER | Log group number to which the log member file belongs. |
STATUS | VARCHAR(7) | Status information of the log member file (Whether or not the log member file was deleted. Currently, it is always set to null.) |
TYPE | CHAR(6) | Always ONLINE |
MEMBER | VARCHAR(256) | Path name of the log member file |
Displays log history information.
Columns
Column Name | Data Type | Description |
---|---|---|
THREAD# | NUMBER | Log group number recorded by instances of a server |
SEQUENCE# | NUMBER | Sequence number of the log group |
FIRST_CHANGE# | NUMBER | TSN associated with the first log that belongs to the log group |
FIRST_TIME | DATE | Time when the first log that belongs to the log group was recorded |
NEXT_CHANGE# | NUMBER | TSN associated with the next log that belongs to the log group |
NEXT_TIME | DATE | Time when the next log that belongs to the log group was recorded |
RESETLOGS_CHANGE# | NUMBER | Resetlogs TSN of the database when the log was written |
RESETLOGS_TIME | DATE | Resetlogs time of the database when the log was written |
FILESIZE | NUMBER | Size of the logfile at the time of log switch |
Displays the progress of media recovery.
Columns
Column Name | Data Type | Description |
---|---|---|
THREAD# | NUMBER | Log group number recorded by instances of a server |
START_TIME | VARCHAR(19) | The time when media recovery began |
CURRENT_TIME | VARCHAR(19) | Current time |
ESTIMATE_TIME | VARCHAR(19) | Estimated finish time of the media recovery |
BEGIN_SEQUENCE# | NUMBER | Sequence number of the log group where media recovery started |
RECOVERY_SEQUENCE# | NUMBER | Sequence number of the log group that is currently being replayed |
END_SEQUENCE# | NUMBER | Sequence number of the log group where media recovery would end for complete recovery |
Displays information on the metric history.
Columns
Column Name | Data Type | Description |
---|---|---|
SAMPLE_ID | NUMBER | Sample ID |
THREAD# | NUMBER | Thread# |
BEGIN_TIME | DATE | Begin Time of the Sample |
END_TIME | DATE | End Time of the Sample |
METRIC_ID | NUMBER | Metric ID |
METRIC_NAME | VARCHAR(64) | Metric Name |
VALUE | NUMBER | Value of the Metric |
GROUP_ID | NUMBER | Group ID |
GROUP_NAME | VARCHAR(64) | Group Name |
INTERVAL | NUMBER | Interval of the Collection(Sec) |
Displays specifications of the metrics.
Columns
Column Name | Data Type | Description |
---|---|---|
METRIC_ID | NUMBER | Metric ID |
METRIC_NAME | VARCHAR(64) | Metric Name |
METRIC_UNIT | VARCHAR(64) | Descriptions of the Metric Unit |
GROUP_ID | NUMBER | Group ID |
GROUP_NAME | VARCHAR(64) | Group Name |
INTERVAL | NUMBER | Interval of Metric Collection(Sec) |
Displays statistics of the current session.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | ID of the session |
STAT# | NUMBER | Event number used to obtain statistics |
NAME | VARCHAR(60) | Name of the event used to obtain statistics |
CLASS | NUMBER | Class of the event |
VALUE | NUMBER | The number of times that the event was used |
Displays statistics about index usage gathered from the database for the indexes owned by the current user.
Columns
Column Name | Data Type | Description |
---|---|---|
OWNER_NAME | VARCHAR(128) | Name of user who has the object |
INDEX_NAME | VARCHAR(128) | The name of the index that is monitored |
TABLE_NAME | VARCHAR(128) | The name of the base table |
MONITORING | VARCHAR(1) | Y or N |
USED | VARCHAR(1) | Y or N |
START_MONITORING | DATE | Start monitoring time in sys._object_usage.start_monitoring |
END_MONITORING | DATE | End monitoring time in sys._object_usage.end_monitoring |
Displays information for all open cursors for each session
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID |
CURSOR_ID | NUMBER | cursor ID |
USER_NAME | VARCHAR(128) | Name of the current user |
HASH_VALUE | NUMBER | Hash value of the SQL statement |
PLAN_HASH_VALUE | NUMBER | Hash value of the physical plan |
SQL_ID | VARCHAR(13) | SQL identifier |
SQL_TEXT | VARCHAR(64) | SQL text of the open cursor |
EXEC_TIME | NUMBER | Total working time performed on the cursor |
Displays various OS statistics.
Columns
Column Name | Data Type | Description |
---|---|---|
PID | NUMBER | Pid of process |
NAME | VARCHAR(64) | Name of process |
CPUTIME | NUMBER | CPU user time |
CPUUSAGE | NUMBER | CPU usage in percentage |
Displays various OS statistics.
Columns
Column Name | Data Type | Description |
---|---|---|
ID | NUMBER | Id of OS statistic |
STAT_NAME | VARCHAR(64) | Name of OS statistic |
COMMENTS | VARCHAR(512) | OS statistic comments |
VALUE | NUMBER | OS statistic value |
CUMULATIVE | VARCHAR(3) | Whether the statistic is cumulative('YES' or 'NO') |
Displays values of initialization parameters.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(64) | Name of an initialization parameter |
VALUE | VARCHAR(1024) | Value of an initialization parameter |
DFLT_VALUE | VARCHAR(1024) | Default Value of an initialization parameter |
Displays list of patch files.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(32) | Name of patch file |
Displays elapsed time information of PES in the format of SQL execution plan.
Columns
Column Name | Data Type | Description |
---|---|---|
Execution_Plan | VARCHAR(65532) | Execution plan and embedded PES statistics |
Displays statistics of each PES for parallel processing.
Columns
Column Name | Data Type | Description |
---|---|---|
SQL_ID | VARCHAR(13) | SQL identifier |
PEM_ID | NUMBER | PEM node ID |
PROD_PPN_ID | NUMBER | PPN ID associated with the producer PES |
PROD_PES_ID | NUMBER | Producer PES ID |
CONS_PPN_ID | NUMBER | PPN ID associated with the consumer PES |
CONS_PES_ID | NUMBER | Consumer PES ID |
ELAPSED_TIME | NUMBER | Total elapsed time |
SAMPLE_TIME | NUMBER | Time taken for sampling with the range mode |
Displays statistics of each PES for parallel processing.
Columns
Column Name | Data Type | Description |
---|---|---|
PEP_ID | NUMBER | PE process ID |
OS_PID | NUMBER | PE process ID generated by OS |
STATUS | VARCHAR(16) | The status of PE process |
CTHR_TID | NUMBER | The control thread ID of PE process |
QCSID | NUMBER | ID of a session working as a parallel coordinator |
QCSERIAL# | NUMBER | A serial number of a session working as a parallel coordinator (Like QCSID, used to recognize the session) |
SQL_CNT | NUMBER | The count of processed SQLs plus the current sql after this process has been assigned |
PENDING_MSG | VARCHAR(5) | A flag which indicates whether this process has a pending message from QC |
Displays sessions in parallel processing.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID |
SERIAL# | NUMBER | A serial number of a session (Like SID, used to recognize the session) |
PEP_ID | NUMBER | ID of parallel execution process which this session belongs to |
QCSID | NUMBER | ID of a session working as a parallel coordinator |
QCSERIAL# | NUMBER | A serial number of a session working as a parallel coordinator (Like QCSID, used to recognize the session) |
DEGREE | NUMBER | Parallel processing degree specified by server |
REQ_DEGREE | NUMBER | Parallel processing degree requested by user |
Displays statistics of parallel execution.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(64) | Name of a PE statistic - PEP allocation failure count (current): The number of failure to allocate PEP for current parallel coordinators. This value is incremented at most once for each session. - PEP allocation failure count (accumulated): The number of failure to allocate PEP for all parallel coordinators including nonexistent ones. This value is incremented at most once for each session. |
VALUE | NUMBER | Value of the PE statistic |
Displays statistics of table queues for parallel processing.
Columns
Column Name | Data Type | Description |
---|---|---|
PEM_ID | NUMBER | PEM node ID |
TQ_ID | NUMBER | Table queue ID |
SERVER_TYPE | VARCHAR(10) | The role of server thread - PRODUCER: Transfers a result row to another thread - CONSUMER: Receives a result row from another thread - RANGER: Determines the range in which a result row is transferred |
NUM_ROWS | NUMBER | The number of processed rows |
BYTES | NUMBER | The byte number of processed data |
WAITS | NUMBER | The number of times of waiting |
THREAD | VARCHAR(30) | Thread name |
Displays statistics of PGA.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(64) | Name of a PGA statistic - Ex memory soft limit: The maximum amount of PGA memory that can be allocated for the entire working thread running for queries. The memory tuner within the RDBMS monitors the PGA memory usage of the entire thread and adjusts the value accordingly to ensure the value does not exceed the maximum limit. - Ex memory operation limit: The maximum amount of memory that can be allocated for a single working thread to use memory (Sort, Hash-Join, and so on) during query executions. The memory tuner within the RDBMS adjusts this value properly when distributing the available memory among a number of working threads simultaneously request memory. The sort and hash-join operation references this value to check whether the operation is possible with its allocated memory. And if not possible, it uses a temporary table space and executes the file I/O operation instead. - Total ex memory allocated: The total amount of PGA memory currently occupied by the entire working thread of RDBMS. - Total ex memory allocated (max): The highest total PGA memory usage it has ever reached after RDBMS boots. In turn, the system keeps the highest value of the 'Total ex memory allocated' item. - FIXED pga memory: The fixed amount of memory that the malloc function directly allocates from the system when booted. - ALLOCATED pga memory: The amount of memory allocated using the malloc function and managed by the allocator with the RDBMS. This may increase during the operation. - USED pga memory (from ALLOCATED): The amount of 'ALLOCATED pga memory' currently occupied by server processes. This value must be either less than or equal to the value set in 'ALLOCATED pga memory'. And this value is equal to the sum of each 'pga_used_mem' item of the processes listed via v$process. - Total workarea memory used: The total amount of memory allocated by the server for query processing. - Total workarea temporary segment used: The size of the temporary segments used for query processing. |
VALUE | NUMBER | Value of the PGA statistic |
Displays processes and threads used in RDBMS.
Columns
Column Name | Data Type | Description |
---|---|---|
PID | NUMBER | Thread ID |
SPID | NUMBER | ID of process to which the thread belongs |
NAME | VARCHAR(8) | Thread name |
LATCHWAIT | VARCHAR(18) | Spinlock waiting time of thread (msec) |
Displays texts of SQL statements that are remotely executed through the database link.
Columns
Column Name | Data Type | Description |
---|---|---|
HASH_VALUE | NUMBER | Hash value of the SQL statement |
PLAN_HASH_VALUE | NUMBER | Hash value of the physical plan |
SQL_ID | VARCHAR(13) | SQL identifier |
ID | NUMBER | Number given to each job of a physical plan |
LINK_NAME | VARCHAR(128) | Database link name |
PIECE | NUMBER | Number given to each text of a split SQL statement |
SQL_TEXT | VARCHAR(64) | Text of an SQL statement split into 64-byte texts |
Displays bind parameters of SQL statements that are remotely executed through the database link.
Columns
Column Name | Data Type | Description |
---|---|---|
HASH_VALUE | NUMBER | Hash value of the SQL statement |
PLAN_HASH_VALUE | NUMBER | Hash value of the physical plan |
SQL_ID | VARCHAR(13) | SQL identifier |
ID | NUMBER | Number given to each job of a physical plan |
BIND_NO | NUMBER | The order of binding in a proxy node |
CLIENT_BIND_NO | NUMBER | The order of binding to all plans |
Displays the status of a file that is being recovered.
Columns
Column Name | Data Type | Description |
---|---|---|
FILE# | NUMBER | A serial number of the file |
NAME | VARCHAR(256) | The pathname of the file |
STATUS | CHAR(11) | The status of the file (''IN RECOVERY'' signifies a fixed status) |
Displays recovery progress.
Columns
Column Name | Data Type | Description |
---|---|---|
START_TIME | TIMESTAMP(6) | Start time of the recovery |
END_TIME | TIMESTAMP(6) | End time of the recovery |
TYPE | CHAR(20) | Recovery type |
ITEM | VARCHAR(32) | Name of the recovery information - UNDO BLOCK COUNT - RECOVERY TIME REMAIN |
UNITS | VARCHAR(32) | Unit of the value specified in ITEM column |
AMOUNT | NUMBER | The amount currently recovered |
TOTAL | NUMBER | Total amount to be recovered |
Displays the status of media recovery progress.
Columns
Column Name | Data Type | Description |
---|---|---|
RECOVERY_FILES | NUMBER | Number of datafiles in media recovery |
NEXT_START_TSN | NUMBER | TSN of the next redo log |
MIN_CANCEL_TSN | NUMBER | The minimum number of TSNs to be the end point of incomplete media recovery |
RECO_TSN | NUMBER | TSN of the current redo log |
REDO_THREADS | NUMBER | Number of redo threads needed in media recovery |
INCOMPLETE | VARCHAR(1) | Whether or not the current media recovery mode is incomplete - True: Incomplete media recovery - False: Complete media recovery |
CANCEL_TSN | NUMBER | TSN specified to be the end-point of incomplete media recovery |
CANCEL_DATE | DATE | End-point time of the incomplete media recovery |
Displays files which require media recovery.
Columns
Column Name | Data Type | Description |
---|---|---|
FILE# | NUMBER | A serial number of a file |
ONLINE | VARCHAR(7) | Online status of a file |
ERROR | VARCHAR(200) | Error details of a file to be recovered |
CHANGE# | NUMBER | TSN for which to start recovery |
TIME | DATE | Time that the recovery should be started |
Displays a list of all SQL keywords.
Columns
Column Name | Data Type | Description |
---|---|---|
KEYWORD | VARCHAR(30) | Name of the keyword |
LENGTH | NUMBER | Length of the keyword |
RESERVED | VARCHAR(1) | Whether or not the keyword cannot be used as an identifier |
Displays the depends-on relationship between cached result and dependencies.
Columns
Column Name | Data Type | Description |
---|---|---|
RESULT_ID | NUMBER | ID of the cached result |
DEPEND_ID | NUMBER | ID of the dependency object |
OBJECT_NO | NUMBER | Dictionary object number of the dependency object |
Displays Result Cache Objects and their attributes.
Columns
Column Name | Data Type | Description |
---|---|---|
ID | NUMBER | ID of the cached object |
TYPE | VARCHAR(10) | Type of the cached object - Result - Dependency |
STATUS | VARCHAR(9) | status of the object - New: Result is still under construction - Published: Result is available for use - Invalid: Result is no longer available for use |
BUCKET_NO | NUMBER | Internal hash bucket of the object |
HASH | NUMBER | Hash value of the object |
NAME | VARCHAR(512) | SQL prefix |
NAMESPACE | VARCHAR(3) | Namespace (SQL or PSM) |
CREATION_TIMESTAMP | DATE | Time when the object was created |
CREATOR_UID | NUMBER | ID of the schema that created the object |
DEPEND_COUNT | NUMBER | Number of dependencies or dependents |
TSN | NUMBER | Build TSN or invalidation TSN |
COLUMN_COUNT | NUMBER | Number of columns in the cached result |
ROW_COUNT | NUMBER | Total number of rows in the cached result |
PIN_COUNT | NUMBER | Number of active scans on this result |
BUILD_TIME | NUMBER | Amount of time (in millisecond) it took to build the cached result |
OBJECT_NO | NUMBER | Dictionary object number of the dependency object |
INVALIDATIONS | NUMBER | Number of times the object has invalidated its dependents |
SPACE_USED | NUMBER | Used space (in bytes) for the result |
CACHE_ID | VARCHAR(512) | Cache ID for the result |
Displays Result Cache Object statistics.
Columns
Column Name | Data Type | Description |
---|---|---|
ID | NUMBER | ID of the cached object |
NAME | VARCHAR(512) | SQL prefix |
NAMESPACE | VARCHAR(3) | Namespace (SQL or UDF) |
SEARCH_COUNT | NUMBER | Total number of searches in the cached result |
HIT_COUNT | NUMBER | Total number of hits in the cached result |
HIT_RATIO | NUMBER | Ratio of hits in the cached result |
Displays various Result Cache settings and usage statistics.
Columns
Column Name | Data Type | Description |
---|---|---|
ID | NUMBER | Statistic number |
NAME | VARCHAR(40) | Name of the statistic |
VALUE | NUMBER | Value of the statistic |
Displays statistics of Undo usage.
Columns
Column Name | Data Type | Description |
---|---|---|
USN | NUMBER | Undo segment number |
EXTENTS | NUMBER | The number of extents |
RSSIZE | NUMBER | The size of Undo segment |
SEQNO | NUMBER | Sequence number of undo segment |
INCNO | NUMBER | The number of undo segment incarnation |
XACTS | NUMBER | The number of transactions which are performing the assigned Undo segment |
HWMSIZE | NUMBER | The number of Undo blocks which were already used |
SHRINKS | NUMBER | The number of times of shrinking Undo space |
WRAPS | NUMBER | The number of times of reusing Undo space |
EXTENDS | NUMBER | The number of times of extending Undo space |
SHRINK_SIZE | NUMBER | The number of Undo blocks shrinking Undo space |
ACTIVE_SIZE | NUMBER | The number of Undo blocks currently in use |
STATUS | NUMBER | Status information ("ONLINE" signifies a fixed status) |
CUREXT | NUMBER | Extent that is currently in use |
CURSIZE | NUMBER | The size of extent currently in use |
CURBLK | NUMBER | Block number in use by the current extent |
MAX_ACTIVE_SIZE | NUMBER | The maximum number of blocks used by Undo segment |
Displays details of status information on DD cache.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(16) | DD table name |
SEARCH_KEY | NUMBER | Search key value in DD cache |
BUCKETSET_ID | NUMBER | Bucketset ID in the search key |
ENTRY_CNT | NUMBER | The number of entries cached in the bucketset |
HIT_CNT | NUMBER | The number of times that DD cache succeeded |
MISS_CNT | NUMBER | The number of times that DD cache failed |
CACHE_SIZE | NUMBER | The size of the bucketset (in bytes) |
PINNED_CNT | NUMBER | The number of entries pinned in the bucketset |
INVALID_CNT | NUMBER | The number of invalid entries in the bucketset |
NE_CNT | NUMBER | The number of entries for objects which do not exist in the bucketset |
Displays Stats of RSBs
Columns
Column Name | Data Type | Description |
---|---|---|
CTX | CHAR(3) | Context (CCC, CWS) |
MST_RSB | NUMBER | Count of Master RSBs |
SHD_RSB | NUMBER | Count of Shadow RSBs |
RECLAIMED | NUMBER | Accumulated Count of reclaimed RSBs |
FAILED_BH | NUMBER | Accumulated Count of RSB reclamation failures (cached bh) |
FAILED_USING | NUMBER | Accumulated Count of RSB reclamation failures (in use) |
FAILED_GLOBAL | NUMBER | Accumulated Count of RSB reclamation failures (global rsb) |
FAILED_PIN | NUMBER | Accumulated Count of RSB reclamation failures (pinned) |
FAILED_INVAL | NUMBER | Accumulated Count of RSB reclamation failures (on inval) |
Displays data related to currently active resource consumer groups
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(32) | Name of the consumer group |
ACTIVE_SESSIONS | NUMBER | Number of currently active sessions in the consumer group |
CONSUMED_CPU_TIME | NUMBER | Cumulative amount of CPU time consumed by all sessions in the consumer group |
YIELDS | NUMBER | Cumulative number of times that sessions in the consumer group had to yield CPU to other sessions because of quantum expiration |
SMALL_READ_MEGABYTES | NUMBER | Number of single block megabytes read |
LARGE_READ_MEGABYTES | NUMBER | Number of multiblock megabytes read |
SMALL_READ_REQUESTS | NUMBER | Number of single block read requests |
LARGE_READ_REQUESTS | NUMBER | Number of multiblock read requests |
Displays the names of all currently active resource plans
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(32) | Name of the resource plan |
IS_TOP_PLAN | VARCHAR(5) | Indicates whether the resource plan is the current top plan |
Displays resource manager statistics per session
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session identifier |
CURRENT_CONSUMER_GROUP | VARCHAR(32) | The name of the consumer group in which the session currently belongs |
STATE | VARCHAR(32) | Current state of the session |
CONSUMED_CPU_TIME | NUMBER | Cumulative amount of CPU time consumed by the session |
YIELDS | NUMBER | Cumulative number of times the session had to yield CPU to other sessions due to quantum expiration |
SMALL_READ_MEGABYTES | NUMBER | Total number of single block megabytes read by the session |
LARGE_READ_MEGABYTES | NUMBER | Total number of multiblock megabytes read by the session |
SMALL_READ_REQUESTS | NUMBER | Total number of single block read requests by the session |
LARGE_READ_REQUESTS | NUMBER | Total number of multiblock read requests by the session |
Displays information on running jobs.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID of the currently running job |
JOB_ID | NUMBER | JOB ID of the currently running job |
START_TIME | DATE | Start time of the job |
FAILURE | NUMBER | Job failure count |
INSTANCE | NUMBER | Instance name that started the currently running job |
Displays information on running jobs with name.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID of the currently running job |
JOB_ID | NUMBER | JOB ID of the currently running job |
JOB_NAME | VARCHAR(100) | JOB name of the currently running job |
START_TIME | DATE | Start time of the job |
FAILURE | NUMBER | Job failure count |
INSTANCE | NUMBER | Instance name that started the currently running job |
Columns
Column Name | Data Type | Description |
---|---|---|
ID | NUMBER | Object ID |
NAME | VARCHAR(128) | Object name |
TYPE | VARCHAR(20) | Object type |
STATISTICS# | NUMBER | Segment Statistics Type |
STATISTICS_NAME | VARCHAR(64) | Name of the Segment Statistics |
VALUE | NUMBER | Value of the Segment Statistics |
Displays information on each session.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID |
SERIAL# | NUMBER | A serial number of a session (Like SID, used to recognize sessions) |
AUDSID | NUMBER | A secondary serial number of a session |
USER# | NUMBER | The current user's ID |
USERNAME | VARCHAR(128) | The current user's name |
IPADDR | VARCHAR(21) | IP to which the user connects |
COMMAND | NUMBER | Currently performing SQL type - 1: SELECT - 2: INSERT - 3: UPDATE - 4: DELETE - 5: CALL |
STATUS | VARCHAR(32) | Status of session - READY: The session is ready - RUNNING: The session is running - TX_RECOVERING: The transaction is being recovered - SESS_CLEANUP: The session resources are being cleaned up - ASSIGNED: The session has an assigned thread, but is not ready yet - CLOSING: The session is being closed - ROLLING_BACK: The statement-level transaction of PE slave is rolling back. |
SCHEMA# | NUMBER | The current schema ID |
SCHEMANAME | VARCHAR(128) | The current schema name |
TYPE | VARCHAR(15) | Session type - WTHR: Working thread - CTHR: Control thread - LGWR: Log writing process - CKPT: Checkpoint process - LARC: Log archive - AGENT: Sequence process - MTHR: Monitoring process - DBWR: Datablock writing process - LNW: Log network writing process |
SQL_ID | VARCHAR(13) | ID of the currently running SQL |
SQL_CHILD_NUMBER | NUMBER | Child number of the currently running SQL |
PREV_SQL_ID | VARCHAR(13) | ID of the last executed SQL |
PREV_CHILD_NUMBER | NUMBER | Child number of the last executed SQL |
SQL_ET | NUMBER | Elapsed time (ms) of SQL that is being performed |
LOGON_TIME | DATE | Logon time |
STATE | VARCHAR(25) | State of working thread - INVALID: Not initialized. - NEW: Being created - IDLE: Ready to run - RUNNING: Running - WAITING: Waiting for internal message - RECV_WAITING: Waiting for client message - STOP_BY_MTHR: Stopped by MTHR - DEAD: Dead |
WLOCK_WAIT | VARCHAR(18) | Type of wlock for which the session waits |
WAIT_EVENT | NUMBER | Type of wait_event for which the session waits |
WAIT_TIME | NUMBER | Waiting time of wait_event for which the session waits |
PGA_USED_MEM | NUMBER | The amount of PGA memory occupied by the session |
SQL_TRACE | VARCHAR(8) | Whether a session uses SQL trace |
PROG_NAME | VARCHAR(30) | Client program name |
CLIENT_PID | NUMBER | Client PID |
PID | NUMBER | Identifier of a process to which a session belongs |
WTHR_ID | NUMBER | Index of a worker thread to which a session belongs |
OS_THR_ID | NUMBER | Identifier of thread generated from OS to which a session belongs |
OSUSER | VARCHAR(128) | OS account name of the session connected |
MACHINE | VARCHAR(64) | Host name of the session connected |
TERMINAL | VARCHAR(16) | Terminal(tty) information of the session connected |
MODULE | VARCHAR(64) | Name of a module specified by dbms_application_info.set_module |
ACTION | VARCHAR(64) | Name of an action specified by dbms_application_info.set_module/action |
CLIENT_INFO | VARCHAR(64) | Name of client_info specified by dbms_application_info.set_client_info |
CLIENT_IDENTIFIER | VARCHAR(64) | Name of client ID specified by dbms_session.set_identifier |
PDML_ENABLED | VARCHAR(3) | Parallel dml enabled, YES or NO |
PDML_STATUS | VARCHAR(8) | Parallel dml status, FORCE, ENABLED or DISABLED |
PDDL_STATUS | VARCHAR(8) | Parallel ddl status, FORCE, ENABLED or DISABLED |
PQ_STATUS | VARCHAR(8) | Parallel query status, FORCE, ENABLED or DISABLED |
ROW_WAIT_OBJ_ID | NUMBER | Object ID of the row that causes a lock when waiting for a row lock. |
ROW_WAIT_FILE_NO | NUMBER | File number of the row that causes a lock when waiting for a row lock. |
ROW_WAIT_BLOCK_NO | NUMBER | Block number of the row that causes a lock when waiting for a row lock. |
ROW_WAIT_ROW_NO | NUMBER | Number of row that causes a lock when waiting for a row lock. |
CONSUMER_GROUP | VARCHAR(32) | The name of the consumer group in which the session currently belongs |
CONSUMED_CPU_TIME | NUMBER | Cumulative amount of CPU time consumed by the session |
Displays events that are performed in each session.
Columns
Column Name | Data Type | Description |
---|---|---|
TID | NUMBER | Session ID |
IDX | NUMBER | Event number |
NAME | VARCHAR(40) | Name of the event |
CLASS | VARCHAR(40) | Class of the event |
DESC | VARCHAR(60) | Description of the event |
TOTAL_WAITS | NUMBER | Total number of event calls |
TOTAL_TIMEOUTS | NUMBER | The number of times that a time-out occurred in an event |
TIME_WAITED | NUMBER | The amount of time waited for this event (msec) |
AVERAGE_WAIT | NUMBER | The average amount of time waited for this event (msec) |
MAX_WAIT | NUMBER | The maximum amount of time waited for this event (msec) |
Displays input/output which occurred in each session.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID |
BLOCK_GETS | NUMBER | The number of times of reading blocks in Current mode |
CONSISTENT_GETS | NUMBER | The number of times of reading blocks in Consistent Read (CR) mode |
PHYSICAL_READS | NUMBER | The number of blocks read in the disk |
BLOCK_CHANGES | NUMBER | The number of times of changing a buffer read in Current mode |
CONSISTENT_CHANGES | NUMBER | The number of times of changing a buffer read in Consistent Read mode |
Displays status of sessions taking longer time than specified in LONGOPS_THRESHOLD_SEC seconds.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID |
SERIAL# | NUMBER | A serial number of a session (Like SID, used to recognize sessions) |
USER_NAME | VARCHAR(128) | Name of the current user |
WTHR_ID | NUMBER | Index of a working thread process to which a session belongs |
SQL_ID | VARCHAR(13) | SQL identifier |
SQL_PLAN_ID | NUMBER | SQL physical plan identifier |
OPNAME | VARCHAR(128) | Name of the operation |
TARGET | VARCHAR(128) | Name of the target object |
SOFAR | NUMBER | The work throughput done so far |
TOTALWORK | NUMBER | The overall work throughput |
UNITS | VARCHAR(128) | Unit of the work throughput |
START_TIME | DATE | Start time of the session |
ELAPSED_TIME | NUMBER | Total time for which a particular job has been performed (in milliseconds) |
END_TIME | DATE | End time of the session |
Displays the waiting status of each session.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID |
NAME | VARCHAR(24) | Name of waiting event |
ID1 | NUMBER | ID1 of waiting target |
ID2 | NUMBER | ID2 of waiting target |
SEQ# | NUMBER | Sequence of wait event |
TIME_WAITED | NUMBER | Time for which an event was waiting (msec) |
TIMEOUT | NUMBER | Time-out value (msec) |
Displays statistics of each session.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID |
STAT# | NUMBER | Number of event to obtain statistics |
NAME | VARCHAR(60) | Name of event to obtain statistics |
CLASS | NUMBER | Class of the event |
VALUE | NUMBER | Value of the event (Corresponding to the event name) |
Displays the session-level time statistics.
Columns
Column Name | Data Type | Description |
---|---|---|
SID | NUMBER | Session ID |
STAT_ID | NUMBER | Identifier of a time statistics |
STAT_NAME | VARCHAR(64) | Name of a time statistics |
VALUE | NUMBER | Amount of time (in microseconds) |
Displays information on shared memory used in the database.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(40) | Name of shared memory space - SHARED MEMORY: Total shared memory size allocated during instance startup according to the value of TOTAL_SHM_SIZE. - FIXED MEMORY: Total fixed memory area size fixed for database buffers, redo buffers, etc. - SHARED POOL MEMORY: Total/used shared pool size for various purposes. Memory is allocated on demand using a shared pool allocator specific to the purpose. The size of the allocators can be extended as long as the pool is available. - SHARED POOL ALLOCATORS (LC): Total/used memory size of the allocators for library cache memory. - SHARED POOL ALLOCATORS (DD): Total/used memory size of the allocators for data dictionary cache memory. - SHARED POOL ALLOCATORS (SLAB): Total/used memory size of the allocators for slab memory. - SHARED POOL ALLOCATORS (MISC): Total/used memory size of the allocators for miscellaneous memory. - SHARED POOL ALLOCATORS (Total): Total/used memory size of the allocators for all purposes. This size equals the sum of all allocator sizes. - Database Buffers: Total database buffer size allocated since instance startup according to the value of DB_CACHE_SIZE. - Redo Buffers: Total redo buffer size allocated during instance startup according to the value of LOG_BUFFER. |
TOTAL | NUMBER | Size of shared memory space |
USED | NUMBER | Usage of shared memory space |
Displays information on the shared pool for a database, such as its purpose and how much of the shared pool is used.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(40) | Purpose for which shared pool is used |
SIZE | NUMBER | Usage of shared pool |
Displays advice information on the shared pool for a database, such as a recommended shared pool size.
Columns
Column Name | Data Type | Description |
---|
Displays statistics of Spinlock.
Columns
Column Name | Data Type | Description |
---|---|---|
LOCATION | VARCHAR(60) | Location of Spinlock |
SLEEPS_CNT | NUMBER | The number of cases that a LOCK is possessed in LOCATION while sleeping to obtain Spinlock |
Displays details of a SQL child cursor execution.
Columns
Column Name | Data Type | Description |
---|---|---|
SQL_ID | VARCHAR(13) | SQL identifier |
CHILD_NUMBER | NUMBER | The number of this child physical plan |
HASH_VALUE | NUMBER | Hash value of the SQL statement |
PLAN_HASH_VALUE | NUMBER | Hash value of the physical plan |
FIRST_LOAD_TIME | DATE | Time that an SQL statement was first loaded |
LAST_ACTIVE_TIME | DATE | Last time that statistics were collected |
PARSING_USER_ID | NUMBER | Identifier of user that parsed an SQL statement for the first time |
PARSING_SCHEMA_ID | NUMBER | Identifier of schema that parsed an SQL statement for the first time |
MODULE | VARCHAR(64) | Name of module that had been set when an SQL statement was parsed for the first time |
ACTION | VARCHAR(64) | Name of action that had been set when an SQL statement was first parsed |
SQL_TEXT | VARCHAR(30000) | SQL text of the current cursor |
IS_SHAREABLE | VARCHAR(1) | Indicates whether this child cursor can be shared |
CPU_TIME | NUMBER | CPU time (microseconds) |
ELAPSED_TIME | NUMBER | Total execution time |
PARSE_CALLS | NUMBER | The number of parsing requests |
ROWS_PROCESSED | NUMBER | Total number of processed rows |
FETCHES | NUMBER | The number of fetching times |
EXECUTIONS | NUMBER | The number of times executing an SQL statement |
END_OF_FETCH_COUNT | NUMBER | The number of times this SQL statement was fully executed |
PX_SERVERS_EXECUTIONS | NUMBER | The number of times executing parallel execution slaves |
APPLICATION_WAIT_TIME | NUMBER | Total amount of application wait time |
CONCURRENCY_WAIT_TIME | NUMBER | Total amount of concurrency wait time |
CLUSTER_WAIT_TIME | NUMBER | Total amount of cluster wait time |
USER_IO_WAIT_TIME | NUMBER | Total amount of I/O wait time (by user) |
DISK_READS | NUMBER | Disk read count |
DIRECT_WRITES | NUMBER | Direct write count |
BUFFER_GETS | NUMBER | Total number of buffers read in Consistent-Read mode |
PHYSICAL_READ_REQUESTS | NUMBER | Disk read request count |
PHYSICAL_READ_BYTES | NUMBER | Total number of bytes read from disks (in bytes) |
PHYSICAL_WRITE_REQUESTS | NUMBER | Disk write request count |
PHYSICAL_WRITE_BYTES | NUMBER | Total number of bytes write to disks (in bytes) |
SORTS | NUMBER | The number of sorts performed |
OBJECT_STATUS | VARCHAR(14) | Status of the cursor |
Displays details of SQL statement execution.
Columns
Column Name | Data Type | Description |
---|---|---|
SQL_ID | VARCHAR(13) | SQL identifier |
HASH_VALUE | NUMBER | Hash value of the SQL statement |
PLAN_HASH_VALUE | NUMBER | Hash value of the physical plan |
LAST_ACTIVE_TIME | DATE | Last time that statistics were collected |
FIRST_LOAD_TIME | DATE | Time that an SQL statement was first loaded |
PARSING_USER_ID | NUMBER | Identifier of user that parsed an SQL statement for the first time |
PARSING_SCHEMA_ID | NUMBER | Identifier of schema that parsed an SQL statement for the first time |
MODULE | VARCHAR(64) | Name of module that had been set when an SQL statement was parsed for the first time |
ACTION | VARCHAR(64) | Name of action that had been set when an SQL statement was first parsed |
SQL_TEXT | VARCHAR(30000) | SQL text of the current cursor |
CPU_TIME | NUMBER | CPU time (microseconds) |
ELAPSED_TIME | NUMBER | Total execution time |
PARSE_CALLS | NUMBER | The number of parsing requests |
ROWS_PROCESSED | NUMBER | Total number of processed rows |
FETCHES | NUMBER | The number of fetching times |
EXECUTIONS | NUMBER | The number of times executing an SQL statement |
END_OF_FETCH_COUNT | NUMBER | The number of times this SQL statement was fully executed |
PX_SERVERS_EXECUTIONS | NUMBER | The number of times executing parallel execution slaves |
APPLICATION_WAIT_TIME | NUMBER | Total amount of application wait time |
CONCURRENCY_WAIT_TIME | NUMBER | Total amount of concurrency wait time |
CLUSTER_WAIT_TIME | NUMBER | Total amount of cluster wait time |
USER_IO_WAIT_TIME | NUMBER | Total amount of I/O wait time (by user) |
DISK_READS | NUMBER | Disk read count |
DIRECT_WRITES | NUMBER | Direct write count |
BUFFER_GETS | NUMBER | Total number of buffers read in Consistent-Read mode |
PHYSICAL_READ_REQUESTS | NUMBER | Disk read request count |
PHYSICAL_READ_BYTES | NUMBER | Total number of bytes read from disks (in bytes) |
PHYSICAL_WRITE_REQUESTS | NUMBER | Disk write request count |
PHYSICAL_WRITE_BYTES | NUMBER | Total number of bytes write to disks (in bytes) |
SORTS | NUMBER | The number of sorts performed |
Displays information on SQL statement execution.
Columns
Column Name | Data Type | Description |
---|---|---|
SQL_TEXT | VARCHAR(1000) | First thousand characters of the SQL text |
SQL_ID | VARCHAR(13) | SQL identifier |
HASH_VALUE | NUMBER | Hash value of the SQL statement |
LAST_ACTIVE_TIME | DATE | Last time that statistics were collected |
LAST_ACTIVE_CHILD_ADDRESS | RAW(8) | Address of the child physical plan when the statistics were collected |
PLAN_HASH_VALUE | NUMBER | Hash value of the physical plan |
SHAREABLE_MEM | NUMBER | Total shared memory (in bytes) occupied by all child cursors |
CPU_TIME | NUMBER | CPU time (microseconds) |
ELAPSED_TIME | NUMBER | Total execution time (in microseconds) |
PARSE_CALLS | NUMBER | The number of parsing requests |
ROWS_PROCESSED | NUMBER | Total number of processed rows |
FETCHES | NUMBER | The number of fetching times |
EXECUTIONS | NUMBER | The number of times executing an SQL statement |
END_OF_FETCH_COUNT | NUMBER | The number of times this SQL statement was fully executed |
PX_SERVERS_EXECUTIONS | NUMBER | The number of times executing parallel execution slaves |
APPLICATION_WAIT_TIME | NUMBER | Total amount of application wait time |
CONCURRENCY_WAIT_TIME | NUMBER | Total amount of concurrency wait time |
CLUSTER_WAIT_TIME | NUMBER | Total amount of cluster wait time |
USER_IO_WAIT_TIME | NUMBER | Total amount of I/O wait time (by user) |
DISK_READS | NUMBER | Disk read count |
DIRECT_WRITES | NUMBER | Direct write count |
BUFFER_GETS | NUMBER | Total number of buffers read in Consistent-Read mode |
PHYSICAL_READ_REQUESTS | NUMBER | Disk read request count |
PHYSICAL_READ_BYTES | NUMBER | Total number of bytes read from disks (in bytes) |
PHYSICAL_WRITE_REQUESTS | NUMBER | Disk write request count |
PHYSICAL_WRITE_BYTES | NUMBER | Total number of bytes write to disks (in bytes) |
SORTS | NUMBER | The number of sorts performed |
DISK_READ_TIME | NUMBER | Total disk read time |
TEMP_SGMT_READ_TIME | NUMBER | Total temp segment read time |
TEMP_SGMT_WRITE_TIME | NUMBER | Total temp segment write time |
Displays texts of SQL statements belonging to the cursor.
Columns
Column Name | Data Type | Description |
---|---|---|
HASH_VALUE | NUMBER | Hash value of SQL statement |
PLAN_HASH_VALUE | NUMBER | Hash value of the physical plan |
SQL_ID | VARCHAR(13) | SQL identifier |
CHILD_NUMBER | NUMBER | The number of this child physical plan |
COMMAND_TYPE | NUMBER | Number code of SQL statement type - 0: COMMAND_TYPE_NONE - 1: COMMAND_TYPE_SELECT - 2 : COMMAND_TYPE_INSERT - 3: COMMAND_TYPE_UPDATE - 4: COMMAND_TYPE_DELETE - 5: COMMAND_TYPE_MERGE - 6: COMMAND_TYPE_CALL - 7: COMMAND_TYPE_MAX |
PIECE | NUMBER | Number given to each text of a split SQL statement |
SQL_TEXT | VARCHAR(64) | Text of an SQL statement split into 64-byte texts |
See also
Displays texts of SQL statements belonging to the cursor.
Columns
Column Name | Data Type | Description |
---|---|---|
HASH_VALUE | NUMBER | Hash value of SQL statement |
PLAN_HASH_VALUE | NUMBER | Hash value of the physical plan |
SQL_ID | VARCHAR(13) | SQL identifier |
CHILD_NUMBER | NUMBER | The number of this child physical plan |
COMMAND_TYPE | NUMBER | Number code of SQL statement type - 0: COMMAND_TYPE_NONE - 1: COMMAND_TYPE_SELECT - 2: COMMAND_TYPE_INSERT - 3: COMMAND_TYPE_UPDATE - 4: COMMAND_TYPE_DELETE - - 5: COMMAND_TYPE_MERGE - 6: COMMAND_TYPE_CALL - 7: COMMAND_TYPE_MAX |
PIECE | NUMBER | Number given to each text of a split SQL statement |
SQL_TEXT | VARCHAR(30000) | Text of an SQL statement split into 65532-byte texts |
See also
Displays texts of SQL statements belonging to the cursor, without removing newline characters or blank characters.
Columns
Column Name | Data Type | Description |
---|---|---|
HASH_VALUE | NUMBER | Hash value of SQL statement |
PLAN_HASH_VALUE | NUMBER | Hash value of the physical plan |
SQL_ID | VARCHAR(13) | SQL identifier |
CHILD_NUMBER | NUMBER | The number of this child physical plan |
COMMAND_TYPE | NUMBER | Number code of SQL statement type - 0: COMMAND_TYPE_NONE - 1: COMMAND_TYPE_SELECT - 2: COMMAND_TYPE_INSERT - 3: COMMAND_TYPE_UPDATE - 4: COMMAND_TYPE_DELETE - 5: COMMAND_TYPE_MERGE - 6: COMMAND_TYPE_CALL - 7: COMMAND_TYPE_MAX |
PIECE | NUMBER | Number given to each text of a split SQL statement. |
SQL_TEXT | VARCHAR(64) | Text of an SQL statement split into 64-byte texts |
See also
Displays texts of SQL statements belonging to the cursor, without removing newline characters or blank characters.
Columns
Column Name | Data Type | Description |
---|---|---|
HASH_VALUE | NUMBER | Hash value of SQL statement |
PLAN_HASH_VALUE | NUMBER | Hash value of the physical plan |
SQL_ID | VARCHAR(13) | SQL identifier |
CHILD_NUMBER | NUMBER | The number of this child physical plan |
COMMAND_TYPE | NUMBER | Number code of SQL statement type - 0: COMMAND_TYPE_NONE - 1: COMMAND_TYPE_SELECT - 2: COMMAND_TYPE_INSERT - 3: COMMAND_TYPE_UPDATE - 4: COMMAND_TYPE_DELETE - 5: COMMAND_TYPE_MERGE - 6: COMMAND_TYPE_CALL - 7: COMMAND_TYPE_MAX |
PIECE | NUMBER | Number given to each text of a split SQL statement. |
SQL_TEXT | VARCHAR(65532) | Text of an SQL statement split into 65532-byte texts |
See also
Displays the values of the captured bind variables from the current session
Columns
Column Name | Data Type | Description |
---|---|---|
SQL_HASH_VALUE | NUMBER | The Hash value of the sql |
PLAN_HASH_VALUE | NUMBER | The Hash value of the physical plan |
SQL_ID | VARCHAR(13) | The SQL identifier |
CHILD_NUMBER | NUMBER | The number of this child physical plan |
USER_ID | NUMBER | The user id that performed the SQL |
NAME | VARCHAR(128) | The name of the bind variable |
POSITION | NUMBER | The position of the bind variable in the SQL |
TYPE | NUMBER | The type number of the bind variable |
TYPE_NAME | VARCHAR(128) | The type of the bind variable |
PRECISION | NUMBER | The precision of the bind variable |
SCALE | NUMBER | The scale of the bind variable |
LENGTH | NUMBER | The length of the bind variable |
TIME_CAPTURED | DATE | The time when the bind variable was captured |
VALUE | VARCHAR(65532) | The value of the bind variable |
Displays the values of the captured bind variables from all sessions
Columns
Column Name | Data Type | Description |
---|---|---|
SQL_HASH_VALUE | NUMBER | The Hash value of the sql |
PLAN_HASH_VALUE | NUMBER | The Hash value of the physical plan |
SQL_ID | VARCHAR(13) | The SQL identifier |
CHILD_NUMBER | NUMBER | The number of this child physical plan |
USER_ID | NUMBER | The user id that performed the SQL |
NAME | VARCHAR(128) | The name of the bind variable |
POSITION | NUMBER | The position of the bind variable in the SQL |
TYPE | NUMBER | The type number of the bind variable |
TYPE_NAME | VARCHAR(128) | The type of the bind variable |
PRECISION | NUMBER | The precision of the bind variable |
SCALE | NUMBER | The scale of the bind variable |
LENGTH | NUMBER | The length of the bind variable |
TIME_CAPTURED | DATE | The time when the bind variable was captured |
VALUE | VARCHAR(65532) | The value of the bind variable |
Displays information about expression execution of a physical plan
Columns
Column Name | Data Type | Description |
---|---|---|
SQL_ID | VARCHAR(13) | SQL identifier |
BIND_PARAM_CNT | NUMBER | Number of bind parameters |
BIND_TYPES | VARCHAR(65532) | Type of bind parameters |
ALLOC_SIZE | NUMBER | Size of memory usage |
Shows the details of SQL statements that are currently being monitored or those that have been monitored through the real time SQL monitoring function.
Columns
Column Name | Data Type | Description |
---|---|---|
STATUS | VARCHAR(19) | Shows the current execution state of the SQL statement. - EXECUTING: The SQL statement is currently being executed. - DONE (ERROR): The SQL statement generated a runtime error and was closed. - DONE (FIRST N ROWS): Execution completed before all rows were fetched. - DONE (ALL ROWS): Execution completed after all rows were fetched. - DONE: Completed execution of parallel slaves. |
SQL_ID | VARCHAR(13) | SQL identifier |
SQL_EXEC_START | DATE | The time when the execution started. |
SQL_EXEC_ID | NUMBER | Execution identifier. Identifies multiple, simultaneous SQL executions. To specify a single SQL execution, three columns; SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID must be defined. |
SID | NUMBER | Session identifier |
SESSION_SERIAL# | NUMBER | Session serial number |
SQL_PLAN_HASH_VALUE | NUMBER | Hash value of the execution plan |
PX_QCSID | NUMBER | For parallel execution slaves, this indicates the identifier of the query coordinator session. |
PX_MAXDOP | NUMBER | For parallel execution coordinators, this indicates the maximum allocated DDP value. |
PX_SERVERS_REQUESTED | NUMBER | For parallel execution coordinators, this indicates the number of requested parallel slaves. |
PX_SERVERS_ALLOCATED | NUMBER | For parallel execution coordinators, this indicates the number of allocated parallel slaves. |
PX_SERVER# | NUMBER | For parallel execution slaves, this indicates a logical number of slaves within a single parallel execution set. |
PX_SERVER_SET | NUMBER | Whether the parallel execution slave belongs to the producer set or the consumer set. (either 1 or 2) |
PX_SERVER_GROUP | NUMBER | For parallel execution slaves, this indicates the logical group number when queries are performed by multiple query coordinators |
MODULE | VARCHAR(48) | Name of a module specified by dbms_application_info.set_module |
ACTION | VARCHAR(32) | Name of an action specified by dbms_application_info.set_module/action |
CLIENT_INFO | VARCHAR(64) | Name of client_info specified by dbms_application_info.set_client_info |
CLIENT_IDENTIFIER | VARCHAR(64) | Name of client identifier specified by dbms_session.set_identifier |
PROGRAM | VARCHAR(48) | Client program name |
WTHR_ID | NUMBER | Working thread identifier |
USER# | NUMBER | The current user's identifier |
USERNAME | VARCHAR(30) | The current user's name |
SQL_TEXT | VARCHAR(2000) | Executed SQL statements (A maximum of 2000 characters) |
IS_FULL_SQLTEXT | VARCHAR(1) | Whether the value saved in the SQL_TEXT column is a full or partial SQL statement. |
ERROR_NUMBER | NUMBER | Error number when a runtime error occurs. |
ERROR_MESSAGE | VARCHAR(256) | Error messages when a runtime error occurs. |
PSM_ENTRY_OBJECT_ID | NUMBER | Object ID of the top-most PSM procedure or function on the stack. |
PSM_ENTRY_SUBPROGRAM_ID | NUMBER | The subprogram identifier of the procedure or function that was called upon entry into the PSM execution. |
PSM_OBJECT_ID | NUMBER | The object identifier of the current executed PSM |
PSM_SUBPROGRAM_ID | NUMBER | The subprogram identifier of the current executed PSM |
CPU_TIME | NUMBER | CPU time (microseconds) |
ELAPSED_TIME | NUMBER | SQL execution time (microseconds) |
FETCHES | NUMBER | Number of fetches |
APPLICATION_WAIT_TIME | NUMBER | Total amount of application wait time |
CONCURRENCY_WAIT_TIME | NUMBER | Total amount of concurrency wait time |
CLUSTER_WAIT_TIME | NUMBER | Total amount of cluster wait time |
USER_IO_WAIT_TIME | NUMBER | Total amount of I/O wait time (by user) |
DISK_READS | NUMBER | Disk read count |
DIRECT_WRITES | NUMBER | Direct disk write cnt without through buffer cache |
BUFFER_GETS | NUMBER | Total number of buffers read in Consistent-Read mode |
PHYSICAL_READ_REQUESTS | NUMBER | Disk read request count |
PHYSICAL_READ_BYTES | NUMBER | Total number of bytes read from disks (in bytes) |
PHYSICAL_WRITE_REQUESTS | NUMBER | Disk write request count |
PHYSICAL_WRITE_BYTES | NUMBER | Total number of bytes write to disks (in bytes) |
Displays information on physical plans to execute SQL statements.
Columns
Column Name | Data Type | Description |
---|---|---|
HASH_VALUE | NUMBER | Hash value of the SQL statement |
PLAN_HASH_VALUE | NUMBER | Hash value of the physical plan |
SQL_ID | VARCHAR(13) | SQL identifier |
CHILD_NUMBER | NUMBER | The number of this child physical plan |
OPERATION | VARCHAR(128) | Name of operation job |
OBJECT# | NUMBER | Identifier of object accessed by the job |
OBJECT_OWNER | VARCHAR(128) | Name of user who has the object |
OBJECT_NAME | VARCHAR(128) | Object name |
OBJECT_TYPE | VARCHAR(20) | Object type |
ID | NUMBER | Number given to each job of the physical plan |
PARENT_ID | NUMBER | ID of the next execution step that operates on the output of the current step in the physical plan |
DEPTH | NUMBER | Tree level of the physical plan |
POSITION | NUMBER | Positions between all the jobs with the same PARENT_ID |
SEARCH_COLUMNS | NUMBER | The number of keys used in index search |
COST | NUMBER | Job cost estimated by query optimizer |
CPU_COST | NUMBER | CPU cost of the operation as estimated by optimizer |
IO_COST | NUMBER | I/O cost of the operation as estimated by optimizer |
CARDINALITY | NUMBER | The number of output results estimated by query optimizer |
PSTART | VARCHAR(38) | In a partitioned table, a start partition for access |
PEND | VARCHAR(38) | In a partitioned table, an end partition for access |
OTHERS | VARCHAR(4000) | Other information specific to the execution step that users may find useful |
ACCESS_PREDICATES | VARCHAR(4000) | Predicate information to access indexes or to handle joins |
FILTER_PREDICATES | VARCHAR(4000) | Predicate information to handle filters |
ADVISED_COLNO | VARCHAR(4000) | Column number(s) recommended as indexes by the advisor for the plan's TSCAN FULL node. |
Displays plan level information about each SQL execution registered in V$SQL_MONITOR. Each row corresponds to a single plan operation of one SQL execution.
Columns
Column Name | Data Type | Description |
---|---|---|
STATUS | VARCHAR(19) | Shows the current execution state of the SQL statement. - EXECUTING: The SQL statement is currently being executed. - DONE (ERROR): The SQL statement generated a runtime error and was closed. - DONE (FIRST N ROWS): Execution completed before all rows were fetched. - DONE (ALL ROWS): Execution completed after all rows were fetched. - DONE: Completed execution of parallel slaves. |
SQL_ID | VARCHAR(13) | SQL identifier |
SQL_EXEC_START | DATE | The time when the execution started. |
SQL_EXEC_ID | NUMBER | Execution identifier. It is necessary to identify multiple simultaneous SQL executions. To identify a single SQL execution, three columns; SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID must be defined. The three column values are used to match with corresponding rows in the V$SQL_MONITOR. |
SID | NUMBER | Session identifier |
SQL_PLAN_HASH_VALUE | NUMBER | Hash value of the execution plan |
PLAN_PARENT_ID | NUMBER | Identifier of the parent plan operation of this operation |
PLAN_LINE_ID | NUMBER | Identifier of the plan operation |
PLAN_OPERATION | VARCHAR(30) | Name of the plan operation |
PLAN_DEPTH | NUMBER | Level of the plan operation within the execution plan tree |
PLAN_POSITION | NUMBER | Logical number among siblings within the plan execution tree |
PLAN_COST | NUMBER | Cost of the plan operation estimated by the optimizer |
PLAN_CPU_COST | NUMBER | CPU cost of the plan operation estimated by the optimizer |
PLAN_IO_COST | NUMBER | I/O cost of the plan operation estimated by the optimizer |
PLAN_CARDINALITY | NUMBER | The number of rows displayed in a plan operation estimated by the optimizer |
PLAN_OBJECT_OWNER | VARCHAR(30) | Owner of the table or index used by this operation |
PLAN_OBJECT_NAME | VARCHAR(30) | Name of the table or index used by this operation |
PLAN_OBJECT_TYPE | VARCHAR(30) | Type of object used by this operation |
PLAN_PARTITION_START | VARCHAR(64) | Start partition when accessing part of a partitioned object |
PLAN_PARTITION_STOP | VARCHAR(64) | End partition when accessing part of a partitioned object |
FIRST_CHANGE_TIME | DATE | First time the operation was changed to an upper level operation |
LAST_CHANGE_TIME | DATE | Last time the operation was changed to an upper level operation |
STARTS | NUMBER | Number of times the operation was executed. (Repeated index accesses, etc.) |
OUTPUT_ROWS | NUMBER | Number of rows produced by this operation |
PHYSICAL_READ_REQUESTS | NUMBER | Disk read count requested by this operation |
PHYSICAL_READ_BYTES | NUMBER | Total number of bytes read from disks by this operation (in bytes) |
PHYSICAL_WRITE_REQUESTS | NUMBER | Disk write count requested by this operation |
PHYSICAL_WRITE_BYTES | NUMBER | Total number of bytes write to disks by this operation (in bytes) |
WORKAREA_MEM | NUMBER | Size of private memory space occupied by this operation (in bytes) |
WORKAREA_MAX_MEM | NUMBER | Maximum value for WORKAREA_MEM |
WORKAREA_TEMPSEG | NUMBER | Sum of temporary segment size currently occupied by this operation (in bytes) |
WORKAREA_MAX_TEMPSEG | NUMBER | Maximum value for WORKAREA_TEMPSEG |
Displays statistics of results of each physical plan job.
Columns
Column Name | Data Type | Description |
---|---|---|
HASH_VALUE | NUMBER | Hash value of the SQL statement |
PLAN_HASH_VALUE | NUMBER | Hash value of the physical plan |
SQL_ID | VARCHAR(13) | SQL identifier |
CHILD_NUMBER | NUMBER | The number of this child physical plan |
ID | NUMBER | Number given to each job of the physical plan |
EXECUTIONS | NUMBER | The number of times that a particular job has been performed |
LAST_STARTS | NUMBER | The number of times that a particular job was started during the last execution |
STARTS | NUMBER | Total number of times that a particular job has been started |
LAST_OUTPUT_ROWS | NUMBER | The number of rows created in a particular job during the last execution |
OUTPUT_ROWS | NUMBER | Total number of rows created in a particular job |
LAST_LEFT_INPUT_ROWS | NUMBER | The number of rows which a particular job accepted from a left-side lower job during the last execution |
LEFT_INPUT_ROWS | NUMBER | Total number of rows which a particular job has accepted from a left-side lower job |
LAST_RIGHT_INPUT_ROWS | NUMBER | The number of rows which a particular job accepted from a right-side lower job during the last execution |
RIGHT_INPUT_ROWS | NUMBER | Total number of rows which a particular job has accepted from a right-side lower job |
LAST_CR_BUFFER_GETS | NUMBER | The number of buffers which a particular job read in Consistent-Read mode during the last execution |
CR_BUFFER_GETS | NUMBER | Total number of buffers which a particular job has read in Consistent-Read mode |
LAST_ELAPSED_TIME | NUMBER | Time for which a particular job was performed in the last execution (in milliseconds) |
ELAPSED_TIME | NUMBER | Total time for which a particular job has been performed (in milliseconds) |
LAST_MEM_USAGE | NUMBER | Memory usage for a particular operation during the last execution. |
MEM_USAGE | NUMBER | Total memory usage for which a particular job has been performed |
LAST_TEMP_SEGMENT_READ_CNT | NUMBER | The number of temporary segment read which a particular job was performed in the last execution |
TEMP_SEGMENT_READ_CNT | NUMBER | Total number of temporary segment read which a particular job was performed in the last execution |
LAST_TEMP_SEGMENT_WRITE_CNT | NUMBER | The number of temporary segment write which a particular job was performed in the last execution |
TEMP_SEGMENT_WRITE_CNT | NUMBER | Total number of temporary segment write which a particular job was performed in the last execution |
LAST_CU_BUFFER_GETS | NUMBER | The number of buffers which a particular job read in Current mode during the last execution |
CU_BUFFER_GETS | NUMBER | Total number of buffers which a particular job has read in Current mode |
Display information about work areas used by SQL cursors.
Columns
Column Name | Data Type | Description |
---|---|---|
ADDRESS | RAW(8) | Address of the parent statement |
HASH_VALUE | NUMBER | Hash value of the parent statement in the library cache |
SQL_ID | VARCHAR(13) | SQL identifier of the parent statement in the library cache |
WORKAREA_ADDRESS | RAW(8) | Address of the work area |
OPERATION_TYPE | VARCHAR(20) | Operation type that uses the work area |
OPERATION_ID | NUMBER | Identifier used to distinguish this operation from the execution plan |
POLICY | VARCHAR(10) | Policy that determines the work area size (MANUAL or AUTO) |
ESTIMATED_OPTIMAL_SIZE | NUMBER | The minimum size (in bytes) for the work area to be executed in memory only |
ESTIMATED_ONEPASS_SIZE | NUMBER | The minimum size (in bytes) of the work area, to be executed by one-time disk use |
LAST_MEMORY_USED | NUMBER | The memory size (in bytes) that was used by the work area during the last execution |
LAST_EXECUTION | VARCHAR(10) | The Process that the work area used during the last execution - OPTIMAL: optimal mode execution - N PASS: multi pass mode execution |
LAST_DEGREE | NUMBER | Parallelization degree of the work area, during the last execution |
TOTAL_EXECUTIONS | NUMBER | Count of the number of times the work area was executed |
OPTIMAL_EXECUTIONS | NUMBER | Count of the number of times that the work area was executed in optimal mode |
ONEPASS_EXECUTIONS | NUMBER | Count of the number of times that the work area was executed in one-pass mode |
MULTIPASSES_EXECUTIONS | NUMBER | Count of the number of times that the work area was executed in multi-pass mode |
ACTIVE_TIME | NUMBER | Average time this work area is active |
MAX_TEMPSEG_SIZE | NUMBER | The maximum size (in bytes) of a temporary segment, that the work area uses |
LAST_TEMPSEG_SIZE | NUMBER | The temporary segment size (in bytes), that was used by the work area in the last execution |
Display the cumulative work area execution statistics
Columns
Column Name | Data Type | Description |
---|---|---|
LOW_OPTIMAL_SIZE | NUMBER | The lower bounding value (in bytes) for the optimal size |
HIGH_OPTIMAL_SIZE | NUMBER | The higher bounding value (in bytes) for the optimal size |
OPTIMAL_EXECUTIONS | NUMBER | Number of work areas with an optimal memory requirement between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which have been executed in optimal mode. |
ONEPASS_EXECUTIONS | NUMBER | The number of work areas that were executed in one-pass mode, within each LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE bound |
MULTIPASSES_EXECUTIONS | NUMBER | The number of work areas that were executed in multi-pass mode, within each LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE bound |
TOTAL_EXECUTIONS | NUMBER | The total number of work areas that were executed within each LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE bound |
Displays information of valid connection to the storage server instance.
Columns
Column Name | Data Type | Description |
---|---|---|
ADDRESS | VARCHAR(20) | ITHR address number |
PORT | NUMBER | ITHR port number |
NAME | VARCHAR(128) | DB name |
THREAD_NUMBER | NUMBER | Connection Thread number |
Displays information of flashcaches used by the storage server instance.
Columns
Column Name | Data Type | Description |
---|---|---|
FLASHCACHE_NUMBER | NUMBER | Flashcache number |
NAME | VARCHAR(32) | Flashcache name |
PATH | VARCHAR(256) | Flashcache path (full) |
OS_BYTES | NUMBER | Flashcache size |
Displays information of grid disks.
Columns
Column Name | Data Type | Description |
---|---|---|
GRID_DISK_NUMBER | NUMBER | Grid disk number |
NAME | VARCHAR(128) | Grid disk name |
STORAGE_DISK_NUMBER | NUMBER | Storage disk number |
STORAGE_DISK_OFFSET | NUMBER | Storage disk offset |
TOTAL_BYTES | NUMBER | Grid disk size |
IO load of storage server
Columns
Column Name | Data Type | Description |
---|---|---|
TYPE | VARCHAR(32) | !!! No documentation yet !!! |
NAME | VARCHAR(32) | !!! No documentation yet !!! |
READ_CNT | NUMBER | !!! No documentation yet !!! |
READ_CNT_INTERNAL | NUMBER | !!! No documentation yet !!! |
READ_MB | NUMBER | !!! No documentation yet !!! |
READ_INTERNAL_MB | NUMBER | !!! No documentation yet !!! |
WRITE_CNT | NUMBER | !!! No documentation yet !!! |
WRITE_CNT_INTERNAL | NUMBER | !!! No documentation yet !!! |
WRITE_MB | NUMBER | !!! No documentation yet !!! |
WRITE_INTERNAL_MB | NUMBER | !!! No documentation yet !!! |
Category Plan List used by the storage server instance
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(128) | !!! No documentation yet !!! |
SHARE_NUMBER | NUMBER | !!! No documentation yet !!! |
LIMIT | NUMBER | !!! No documentation yet !!! |
STATUS | VARCHAR(8) | !!! No documentation yet !!! |
DB Plan List used by the storage server instance
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(128) | !!! No documentation yet !!! |
SHARE_NUMBER | NUMBER | !!! No documentation yet !!! |
LIMIT | NUMBER | !!! No documentation yet !!! |
STATUS | VARCHAR(8) | !!! No documentation yet !!! |
IO stat per sec of storage server
Columns
Column Name | Data Type | Description |
---|---|---|
TYPE | VARCHAR(32) | !!! No documentation yet !!! |
NAME | VARCHAR(32) | !!! No documentation yet !!! |
TPS_READ | NUMBER | !!! No documentation yet !!! |
TPS_READ_INTERNAL | NUMBER | !!! No documentation yet !!! |
READ_MB_sec | NUMBER | !!! No documentation yet !!! |
READ_INTERNAL_MB_sec | NUMBER | !!! No documentation yet !!! |
TPS_WRITE | NUMBER | !!! No documentation yet !!! |
TPS_WRITE_INTERNAL | NUMBER | !!! No documentation yet !!! |
WRITE_MB_sec | NUMBER | !!! No documentation yet !!! |
WRITE_INTERNAL_MB_sec | NUMBER | !!! No documentation yet !!! |
Memory stat of storage server
Columns
Column Name | Data Type | Description |
---|---|---|
TOTAL_PGA_MEMORY | NUMBER | !!! No documentation yet !!! |
FIXED_PGA_MEMORY | NUMBER | !!! No documentation yet !!! |
USED_PGA_MEMORY | NUMBER | !!! No documentation yet !!! |
TOTAL_SHARED_MEMORY | NUMBER | !!! No documentation yet !!! |
FIXED_SHARED_MEMORY | NUMBER | !!! No documentation yet !!! |
USED_SHARED_MEMORY | NUMBER | !!! No documentation yet !!! |
used buffer slab information of storage server
Columns
Column Name | Data Type | Description |
---|---|---|
SLAB_SIZE_L1 | NUMBER | buf_L1_pool slab size |
SLAB_GET_CNT_L1 | NUMBER | buf_L1_pool slab get count |
TOTAL_CHUNK_CNT_L1 | NUMBER | buf_L1_pool chunk count |
MAX_CHUNK_CNT_L1 | NUMBER | buf_L1_pool max chunk count |
SLAB_SIZE_L2 | NUMBER | buf_L2_pool slab size |
SLAB_GET_CNT_L2 | NUMBER | buf_L2_pool slab get count |
TOTAL_CHUNK_CNT_L2 | NUMBER | buf_L2_pool chunk count |
MAX_CHUNK_CNT_L2 | NUMBER | buf_L2_pool max chunk count |
SLAB_SIZE_L3 | NUMBER | buf_L3_pool slab size |
SLAB_GET_CNT_L3 | NUMBER | buf_L3_pool slab get count |
TOTAL_CHUNK_CNT_L3 | NUMBER | buf_L3_pool chunk count |
MAX_CHUNK_CNT_L3 | NUMBER | buf_L3_pool max chunk count |
Displays information of storage disks used by the storage server instance.
Columns
Column Name | Data Type | Description |
---|---|---|
STORAGE_DISK_NUMBER | NUMBER | Storage disk number |
NAME | VARCHAR(128) | Storage disk name |
PATH | VARCHAR(256) | Storage disk path (full) |
OS_BYTES | NUMBER | Storage disk size |
Displays information on the database duplication in the RDBMS Standby Cluster.
Columns
Column Name | Data Type | Description |
---|---|---|
PRIMARY_ADDR | VARCHAR(80) | Host name and port number of the connection from the primary DB |
STATUS | VARCHAR(40) | Status of a thread related to standby |
PRIMARY_TSN | NUMBER | TSN updated from primary DB |
RECVED_SEQ | NUMBER | Log sequence number received from primary DB |
RECVED_BLKNO | NUMBER | Log block number received from primary DB |
RECVED_TSN | NUMBER | TSN of last received log block |
ACKED_SEQ | NUMBER | Sequence number of the log which the standby DB reported was successfully flushed |
ACKED_BLKNO | NUMBER | Block number of the log which the standby DB reported was successfully flushed |
RECOVERED_SEQ | NUMBER | Log sequence number recovered by SMR of the standby DB |
RECOVERED_BLKNO | NUMBER | Log block number recovered by SMR of the standby DB |
RECOVERED_TSN | NUMBER | TSN after recovered by SMR of the standby DB |
OPEN_SEQ | NUMBER | Log sequence number that cannot be used to access the DB as standby |
OPEN_BLKNO | NUMBER | Log block number that cannot be used to access the DB as standby |
Displays information on the standby DB in the RDBMS Standby Cluster.
Columns
Column Name | Data Type | Description |
---|---|---|
STANDBY_ADDR | VARCHAR(80) | Host name of standby DB and port number |
TYPE | VARCHAR(16) | Standby type - LGWR SYNC - LGWR ASYNC - ARCH ASYNC - LGWR PROXY |
THREAD# | NUMBER | Redo thread# |
FLAGS | VARCHAR(32) | Other information of standby - CONNECTED: connected - NOT CONNECTED: not connected |
SENT_SEQ | NUMBER | Sequence number of transmitted log |
SENT_BLKNO | NUMBER | Block number of transmitted log |
ACKED_SEQ | NUMBER | Log sequence number reporting that the standby DB was successfully flushed |
ACKED_BLKNO | NUMBER | Log block number reporting that the standby DB was successfully flushed |
Displays information on log duplication of the primary DB in the RDBMS Standby Cluster.
Columns
Column Name | Data Type | Description |
---|---|---|
STANDBY_MODE | VARCHAR(16) | Data protection level |
FLUSHED_SEQ | NUMBER | Sequence number of log flushed by the standby DB working in LGWR SYNC mode |
FLUSHED_BLKNO | NUMBER | Block number of the log flushed by the standby DB working in LGWR SYNC mode |
SYNC | VARCHAR(8) | Whether or not LGWR participates in the actions of standby |
WAITING | VARCHAR(8) | Event for which LGWR is waiting |
DETAILS | VARCHAR(40) | Details of the event for which LGWR is waiting |
Displays the detailed information on the statistics event.
Columns
Column Name | Data Type | Description |
---|---|---|
STAT# | NUMBER | Event number to obtain statistics |
NAME | VARCHAR(60) | Event name to obtain statistics |
CLASS | NUMBER | Class number of the event |
CLASS_NAME | VARCHAR(40) | Class name of the event |
Displays statistics of a system.
Columns
Column Name | Data Type | Description |
---|---|---|
STAT# | NUMBER | Event number to obtain statistics |
NAME | VARCHAR(60) | Event name to obtain statistics |
CLASS | NUMBER | Class number of the event |
VALUE | NUMBER | Value of the event (Corresponding to the event name) |
Displays values for getting statistics of a system.
Columns
Column Name | Data Type | Description |
---|---|---|
STAT# | NUMBER | Event number to obtain statistics |
NAME | VARCHAR(60) | Event name to obtain statistics |
VALUE | NUMBER | Value of the event |
Displays events performing in the system.
Columns
Column Name | Data Type | Description |
---|---|---|
IDX | NUMBER | Event number |
NAME | VARCHAR(40) | Name of the event |
CLASS | VARCHAR(40) | Class of the event |
DESC | VARCHAR(60) | Description of the event |
TOTAL_WAITS | NUMBER | Total number of event calls |
TOTAL_TIMEOUTS | NUMBER | The number of times that a time-out occurred in an event |
TIME_WAITED | NUMBER | The amount of time waited for this event (msec) |
AVERAGE_WAIT | NUMBER | The average amount of time waited for this event (msec) |
MAX_WAIT | NUMBER | The maximum amount of time waited for this event (msec) |
Displays system parameter values.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(64) | System parameter name |
VALUE | VARCHAR(1024) | System parameter value |
Displays the system-level time statistics.
Columns
Column Name | Data Type | Description |
---|---|---|
STAT_ID | NUMBER | Identifier of a time statistics |
STAT_NAME | VARCHAR(64) | Name of a time statistics |
VALUE | NUMBER | Amount of time (in microseconds) |
Displays information on tablespaces.
Columns
Column Name | Data Type | Description |
---|---|---|
TS# | NUMBER | A serial number of tablespace |
NAME | VARCHAR(128) | Tablespace name |
TYPE | VARCHAR(4) | Tablespace type - DATA - UNDO - TEMP |
BIGFILE | CHAR(2) | NO in all cases |
FLASHBACK_ON | CHAR(2) | NO in all cases |
Displays statistics of a TAC.
Columns
Column Name | Data Type | Description |
---|---|---|
STAT# | NUMBER | Event number to obtain statistics |
NAME | VARCHAR(60) | Event name to obtain statistics |
VALUE | NUMBER | Value of the event (Corresponding to the event name) |
Displays temporary files in the database.
Columns
Column Name | Data Type | Description |
---|---|---|
FILE# | NUMBER | Temporary file number |
CREATE_TSN | NUMBER | TSN that created the temporary file |
CREATE_DATE | DATE | Time that the temporary file was created |
TS# | NUMBER | Number of a tablespace including the temporary file |
RFILE# | NUMBER | Relative number of the temporary file in a tablespace |
ENABLED | CHAR(3) | Availability (YES in all cases) |
BLOCKS | NUMBER | Size of temporary file (the number of blocks) |
CREATE_SIZE | NUMBER | Temporary file size when the file was created |
NAME | VARCHAR(256) | Path name of temporary file |
Displays temporary segments usage in the server.
Columns
Column Name | Data Type | Description |
---|---|---|
SEGTYPE | VARCHAR(32) | Temporary segment type - SORT - HASH - ANALYTIC - INDEX - DATA - LOBDATA |
CURRENT_EXTENTS | NUMBER | The number of extents currently allocated |
PEEK_EXTENTS | NUMBER | The peak number of extents allocated after instance boot |
MAX_ONE_OP_EXTENTS | NUMBER | The maximum number of extents allocated in single operation |
Displays temporary segments in the server.
Columns
Column Name | Data Type | Description |
---|---|---|
USERNAME | VARCHAR(32) | Name of user using the temporary segment |
SESSION_NUM | NUMBER | Number of sessions using the temporary segment |
SQL_ID | NUMBER | ID of PP cache used by the temporary segment |
TABLESPACE | VARCHAR(128) | Name of tablespace containing the temporary segment |
SEGTYPE | VARCHAR(32) | Temporary segment type - SORT - HASH - ANALYTIC - INDEX - DATA - LOBDATA |
SEGFILE# | NUMBER | Absolute number of a file containing the header block of the temporary segment |
SEGBLK# | NUMBER | Block number of the header block of the temporary segment |
EXTENTS | NUMBER | The number of extents in the temporary segment |
BLOCKS | NUMBER | The number of blocks in the temporary segment |
SEGRFNO# | NUMBER | Relative number of a file containing the header block of the temporary segment |
Displays statistics of file I/O.
Columns
Column Name | Data Type | Description |
---|---|---|
FILE# | NUMBER | File number |
PHYRDS | NUMBER | The number of reading times |
PHYWRTS | NUMBER | The number of writing times |
PHYBLKRD | NUMBER | The number of read blocks |
PHYBLKWRT | NUMBER | The number of written blocks |
SINGLEBLKRDS | NUMBER | The number of times of reading a single block |
READTIM | NUMBER | Reading time (msec) |
WRITETIM | NUMBER | Writing time (msec) |
SINGLEBLKRDTIM | NUMBER | Time for which a single block was read (msec) |
AVGIOTIM | NUMBER | Average I/O time (msec) |
LSTIOTIM | NUMBER | Last I/O time (msec) |
MINIOTIM | NUMBER | Shortest I/O time (msec) |
MAXIORTM | NUMBER | Longest reading time (msec) |
MAXIOWTM | NUMBER | Longest writing time (msec) |
Displays information on activities of the threads.
Columns
Column Name | Data Type | Description |
---|---|---|
THR_ID | NUMBER | Thread index |
THR_TYPE | VARCHAR(15) | Thread type |
THR_NAME | VARCHAR(8) | Thread name |
THR_STATUS | VARCHAR(25) | Thread status |
PID | NUMBER | Process id |
PROC_IDX | NUMBER | Process index |
PHY_TID | NUMBER | Thread index within the process |
PTHREAD_ID | NUMBER | Pthread id (for pthread_kill etc.) |
CUR_ACT_CNT | NUMBER | Current activity counter |
NO_ACT_TIME | NUMBER | Elapsed time without explicit thread activity |
NO_ACT_TIME_LIMIT | NUMBER | Limit of the elapsed time without explicit thread activity |
ACT_REPORT | VARCHAR(3) | Whether or not to report thread activity (on/off) |
Displays events that are performed in each thread.
Columns
Column Name | Data Type | Description |
---|---|---|
TID | NUMBER | Thread ID |
IDX | NUMBER | Index of the event |
NAME | VARCHAR(40) | Name of the event |
CLASS | VARCHAR(40) | Class of the event |
DESC | VARCHAR(60) | Description of the event |
TOTAL_WAITS | NUMBER | Total number of event calls |
TOTAL_TIMEOUTS | NUMBER | The number of times that a time-out occurred in an event |
TIME_WAITED | NUMBER | The amount of time waited for this event (msec) |
AVERAGE_WAIT | NUMBER | The average amount of time waited for this event (msec) |
MAX_WAIT | NUMBER | The maximum amount of time waited for this event (msec) |
Displays information on the system timer.
Columns
Column Name | Data Type | Description |
---|---|---|
TICK | NUMBER | Elapsed time since the system started |
Displays timezone names.
Columns
Column Name | Data Type | Description |
---|---|---|
TZNAME | VARCHAR(40) | Name of the timezone |
TZABBREV | VARCHAR(8) | Abbreviation of the timezone |
Displays ongoing transactions.
Columns
Column Name | Data Type | Description |
---|---|---|
SESS_ID | NUMBER | ID of session operation transactions |
USN | NUMBER | Number of Undo segment assigned in the transaction |
SLOT | NUMBER | Slot number of transaction in use |
WRAP | NUMBER | The number of times of reusing a transaction slot |
UEA_FILE | NUMBER | Number of a file containing Undo which a transaction used most recently |
UEA_BLK | NUMBER | Number of a block containing Undo which a transaction used most recently |
UEA_SEQ | NUMBER | A serial number of Undo which a transaction used most recently |
UEA_ROW | NUMBER | A row number of Undo which a transaction used most recently |
STATE | NUMBER | Status of transaction |
START_TIME | DATE | Transaction start time |
START_BASE | NUMBER | Child value of transaction start TSN |
START_WRAP | NUMBER | Parent value of transaction start TSN |
START_UEXT | NUMBER | Extent number of Undo space when the transaction starts |
START_FILE | NUMBER | File number of Undo space when the transaction starts |
START_BLK | NUMBER | Block number of Undo space when the transaction starts |
START_SEQ | NUMBER | A serial number of Undo space when the transaction starts |
START_ROW | NUMBER | Row number of Undo space when the transaction starts |
START_INCNO | NUMBER | Incarnation number of Undo space when the transaction starts |
SPACE | NUMBER | Whether or not the transaction is a space management transaction |
NAME | VARCHAR(256) | Transaction name |
PTX_USN | NUMBER | Number of Undo segment assigned to the parent transaction |
PTX_SLOT | NUMBER | Number of transaction slot in use by the parent transaction |
PTX_WRAP | NUMBER | The number of times of reusing a transaction slot in use by the parent transaction |
LOG_IO | NUMBER | The number of logical io by transactions |
PHY_IO | NUMBER | The number of physical io used by transactions |
CR_GET | NUMBER | The number of Consistent block gets by transactions |
RECOVERY | VARCHAR(8) | Recovery status - RECOVERY - SUSPEND |
USED_BLK | NUMBER | The number of Undo blocks used by transactions |
USED_REC | NUMBER | The number of Undo records used by transactions |
EXP_RB_TIME | NUMBER | Expectation value of rollback time |
TX_TYPE | VARCHAR(20) | Transaction type - LOCAL - XA - XA_RECOVERY - DBLINK |
Displays the mapping table of TSN and time.
Columns
Column Name | Data Type | Description |
---|---|---|
TSN | NUMBER | TSN |
TIME | TIMESTAMP(6) | TIMESTAMP |
Displays statistics of Undo storage.
Columns
Column Name | Data Type | Description |
---|---|---|
BEGIN_TIME | DATE | Start time of statistics |
END_TIME | DATE | End time of statistics |
UNDO_TSNO | NUMBER | Tablespace number |
UNDOBLKS | NUMBER | The number of used blocks |
TXCOUNT | NUMBER | Total number of executed transactions |
MAXCONCURRENCY | NUMBER | The largest number of concurrently executed transactions |
SNAP_TOO_OLD_CNT | NUMBER | The number of errors caused by failing to read old data |
NOSPACEERRCNT | NUMBER | The number of errors caused by lack of Undo storage space |
ACTIVEBLKS | NUMBER | The number of blocks in use |
UNEXPIREDBLKS | NUMBER | The number of non reusable blocks |
EXPIREDBLKS | NUMBER | The number of reusable blocks |
Displays free space in block count for each UNDO tablespace.
Columns
Column Name | Data Type | Description |
---|---|---|
TS_ID | NUMBER | ID of the UNDO tablespace |
TS_NAME | VARCHAR(128) | Name of the UNDO tablespace |
TOTAL_BLOCKS | NUMBER | The total number of blocks in the UNDO tablespace |
FREE_BLOCKS | NUMBER | The number of free blocks in the UNDO tablespace |
TOTAL_SIZE | NUMBER | The total size of the UNDO tablespace in bytes |
FREE_SIZE | NUMBER | The free size of the UNDO tablespace in bytes |
Displays undo segment transaction entry information.
Columns
Column Name | Data Type | Description |
---|---|---|
USN | NUMBER | Undo segment number |
SLOT | NUMBER | Slot number of transaction entry |
WRAP | NUMBER | The number of times of reusing a transaction slot |
STATUS | VARCHAR(32) | Status information |
FLAGS | VARCHAR(32) | Other information of transaction entry |
UEA_FILE | NUMBER | Number of a file containing undo which a transaction used most recently |
UEA_BLOCK | NUMBER | Number of a block containing undo which a transaction used most recently |
UEA_DBA | NUMBER | Starting DBA of a transaction |
UBLKCNT | NUMBER | Used undo block count |
Displays RDBMS version information.
Columns
Column Name | Data Type | Description |
---|---|---|
NAME | VARCHAR(32) | Name of the version information |
VALUE | VARCHAR(32) | Value of the version information |
VALUE | VARHCAR(256) | Banner of the version information |
Displays information on the sessions that wait for a lock acquisition
Columns
Column Name | Data Type | Description |
---|---|---|
WAIT_SID | NUMBER | Session ID that waits for a lock acquisition |
TYPE | VARCHAR(25) | Type of the lock |
ID1 | NUMBER | First ID of the lock |
ID2 | NUMBER | Second ID of the lock |
MODE_REQ | NUMBER | Lock mode by which is requested |
HOLD_SID | NUMBER | Session ID that owns the requested lock |
MODE_HELD | NUMBER | Lock mode by which is owned |
Displays information on block wait events for each block type and class
Columns
Column Name | Data Type | Description |
---|---|---|
CLASS | VARCHAR(20) | Block class (Block type) |
COUNT | NUMBER | The number of times waiting for the block class |
TIME | NUMBER | Total time spent waiting for the block class |
Displays information on block wait events
Columns
Column Name | Data Type | Description |
---|---|---|
CLASS | VARCHAR(20) | Block class (Block type) |
PIN_USE | VARCHAR(15) | The pin usage of the block pin owners |
WAIT_USE | VARCHAR(15) | The pin usage of the block pin waiters |
COUNT | NUMBER | The number of times waiting for the block class and pin usage |
TIME | NUMBER | Total time spent waiting for the block class and pin usage |
Displays information on block wait events for each block type, class, and pin usage
Columns
Column Name | Data Type | Description |
---|---|---|
CLASS | VARCHAR(20) | Block class (Block type) |
PIN_USE | VARCHAR(15) | The pin usage of the buffer pin owners |
WAIT_USE | VARCHAR(15) | The pin usage of the buffer pin waiters |
COUNT | NUMBER | The number of times waiting for the block class and pin usage |
TIME | NUMBER | Total time spent waiting for the block class and pin usage |