The query corresponding to each monitoring element to check tablespace information is as follows.
Memory Tablespace Usage
VOLATILE tablespace usage can now be viewed. As of Altibase 5.5.1, V$VOL_TABLSPACES, which stores information in volatile memory tablespaces, has been added.
| Column | Description |
|---|---|
| TBS_ID | Unique tablespace number |
| TBS_TYPE | Memory tablespace type 0: As a system memory tablespace, this is a tablespace for storing meta data required for the operation of the database system. 1: This is a system memory tablespace. This is used to store the data created by default when creating a database. 2: User-created memory tablespace 8: User-created volatile memory tablespace |
| MAX(M) | This is the maximum memory size that used in a memory tablespace. When MAXSIZE is not specified when creating a tablespace, "UNDEFINED" is output. When the tablespace attribute is AUTOEXTEND OFF, TOTAL(M) is output. |
| TOTAL(M) | Memory tablespace is the total number of pages allocated so far. That is, it is equal to the size of the data file (memory checkpoint image file) as the same of the 'pages in use' and 'blank pages' allocated so far. When the Altibase server is run, only 'pages in use' are loaded into memory. Therefore, it is difficult to judge that physical memory is used as much as this value. |
| ALLOC(M) | Memory tablespace is the total number of 'pages in use' excluding 'empty pages' among pages allocated so far. For example, if DROP or TRUNCATE is executed on a memory table of size 100M, the total number of pages remains unchanged, but this value is close to 0 because 'page in use' becomes 'empty page' through page return. |
| USED(M) | This is the sum of the 'page actually loaded with data' among the 'pages in use' of the memory tablespace. For example, if the user executes DELETE on a memory table with an ALLOC size of 100M, ALLOC will not change to 100M, but USED will be close to zero (0). |
| USAGE(%) | This is the percentage of 'pages in use' compared to 'pages allocated so far' for memory tablespace (USED / TOTAL) |
| STATE | The state of the tablespace 1: Offline 2: Online 3: Offline tablespace being backed up 4: Online tablespace being backed up 128: Dropped tablespace 1024: Discarded tablespace 1028; Discarded tablespace being backed up |
| AUTOEXTEND | Whether to automatically expand the memory tablespace. ON (1), OFF (2) |
| Column | Description |
|---|---|
| MAX(M) | The total number of pages for which the entire memory tablespace can be allocated. This is defined by the Altibase server property MEM_MAX_DB_SIZE. |
| TOTAL(M) | The total number of pages allocated so far |
| ALLOC(M) | The sum of only 'in use pages' excluding 'empty pages' among the pages allocated so far |
| USED(M) | The sum of the 'pages actually loaded with data' among the 'pages in use' of the entire memory tablespace |
| USAGE(%) | The percentage of 'page in use' compared to 'pages that are fully allocated' for the entire memory tablespace (ALLOC / MAX) |
| Column | Description |
|---|---|
| MAX(M) | The total number of pages that can be allocated. Pages are divided into 'active pages' and 'empty pages'. |
| TOTAL(M) | The total number of pages that the disk tablespace has been allocated so far. That is, this corresponds to the data file size as the sum of the 'pages in use' and 'empty pages' allocated so far. |
| ALLOC(M) | The total number of 'pages in use' excluding 'empty pages' among the pages allocated so far. For example, when DROP or TRUNCATE is executed on a 100M disk table, the total number of pages remains the same, but this value is close to 0 because 'pages in use' become 'empty pages' through page return. |
| USED(M) | The sum of the 'pages actually loaded with data' among the 'pages in use' of the disk tablespace |
| USAGE(%) | The percentage of 'pages in use' compared to 'maximum allocable pages' in disk tablespace (USED / MAX) |
| Column | Description |
|---|---|
| MAX(M) | The maximum size that can be used in undo tablespace |
| TOTAL(M) | The total size allocated to undo tablespace |
| ALLOC(M) | The total number of pages in use, excluding 'empty pages' among the pages allocated so far |
| USED(M) | The usage of undo tablespace. EXTENT size in use or not reusable by change transaction |
| USAGE(%) | Usage rate (Used compared to MAX) |
| STATE | Tablespace state |
| Column | Description |
|---|---|
| SESSION_ID | The ID of the session or Replication object that executed the transaction |
| TX_ID | Transaction ID |
| TX_STATUS | Transaction status |
| SQL_STATUS | SQL statement status |
| CLIENT_IP | Client IP and process ID |
| AUTOCOMMIT | AUTOCOMMIT mode of the session |
| UTRANS_TIMEOUT | UTRANS_TIMEOUT setting value of the session |
| LAST_QUERY_START_TIME | SQL statement start time |
| UNDO_USAGE | Transaction undo usage (in MB) |
| QUERY | The last query performed by a transaction using undo tablesapce or accessing undo tablespace. |
| Column | Description |
|---|---|
| MAX(M) | The maximum size that can be used in undo tablespace |
| TOTAL(M) | The size of the tablespace allocated so far. Same as memory checkpoint image file and disk data size |
| ALLOC(M) | The size of the 'page in use' excluding 'empty pages' among the pages allocated so far in the tablespace |
| USED(M) | The size of the actual data loaded on the page that the tablespace is using |
| USAGE(%) | Total memory tablespace : TOTAL utilization rate compared to MAX (TOTAL / MAX) Memory tablespace : USED utilization rate compared to TOTAL (USED / TOTAL) Disk tablespace : USED utilization rate compared to MAX (USED / MAX) |
| Column | Description |
|---|---|
| FILE# | The number of the data file. A disk tablespace can have multiple data files. |
| Column | Description |
|---|---|
| PHY_READ | The number of physical read I/O occurrences |
| PHY_WRITE | The number of physical write I/O occurrences |
| PHY_TOTAL | The number of physical I/O occurrences |
| READ(%) | The percentage of occurrence compared to total physical read I/O |
| WRITE(%) | The percentage of occurrence compared to total physical write I/O |
| TOTAL(%) | The ratio of occurrence to total physical I/O |
| AVG_IO_TIME | The average physical I/O time, measured in milliseconds |
| Column | Description |
|---|---|
| READ_CNT_PER_PAGE | Read count for a single page |
| READ_TIME_PER_PAGE | Read time for a single page, in milliseconds. |
| AVERAGE_TIME | Average read time for a single page, in milliseconds. |
| Description | |
|---|---|
| NORMAL_AREA_SIZE |