Virtual Table

A virtual table is a table that shows various types of information maintained by Machbase starting with v$.

This table maintains various information in real time according to the operation status of Machbase.

Table of Virtual tables

V$COLUMNS V$SESMEM
V$CACHE V$SESSION
V$CACHE_OBJECTS V$SESSTAT
V$STORAGE V$SESTIME
V$STORAGE_MOUNT_DATABASES V$STMT
V$STORAGE_DC_TABLE_COLUMNS V$SYSMEM
V$STORAGE_DC_TABLE_COLUMN_PARTS V$SYSSTAT
V$STORAGE_DC_TABLE_INDEXES V$SYSTIME
V$STORAGE_DC_TABLES V$STORAGE_DC_TABLESPACES
V$STORAGE_DC_LSMINDEX_LEVEL_PARTS V$TABLES
V$STORAGE_DC_LSMINDEX_LEVELS V$STORAGE_DC_TABLESPACE_DISKS
V$PROPERTY V$VERSION

Users can capture and utilize process, which happen inside Machbase.
In addition, this virtual table can generate and confirm various information through joining between log tables, volatile tables or virtual tables. However it is a read-only table that can not be arbitrarily created by the user and can not be inserted, deleted, or changed.

v$tables is the top-level virtual table that is maintained for all virtual tables existing inside Machbase.

Example of v$tables:

Mach> SELECT * FROM  v$tables;

NAME                                      TYPE        ID                   USER_ID     COLCOUNT
----------------------------------------------------------------------------------------------------
V$STORAGE_DC_TABLESPACES                  1           1000013              0           7
V$STORAGE_DC_TABLESPACE_DISKS             1           1000014              0           9
V$SYSTIME                                 1           1000001              0           8
V$SYSSTAT                                 1           1000000              0           5
V$STORAGE                                 1           1000011              0           3
V$STORAGE_DC_TABLES                       1           1000018              0           15
V$STMT                                    1           1000006              0           7
V$PROPERTY                                1           1000003              0           8
V$SESSTAT                                 1           1000007              0           5
V$CACHE_OBJECTS                           1           1000017              0           5
V$TABLES                                  1           1000024              0           7
V$COLUMNS                                 1           1000026              0           12
V$SESTIME                                 1           1000008              0           6
V$SESMEM                                  1           1000009              0           5
V$STORAGE_DC_TABLE_COLUMNS                1           1000019              0           15
V$VERSION                                 1           1000033              0           15
V$CACHE                                   1           1000016              0           3
V$STORAGE_DC_TABLE_INDEXES                1           1000021              0           17
V$SYSMEM                                  1           1000002              0           6
V$STORAGE_DC_TABLE_COLUMN_PARTS           1           1000020              0           19
V$SESSION                                 1           1000005              0           10
[21] row(s) selected.

To check logs that are generated by a user or the list of volatile tables, execute "m$sys_tables".

V$COLUMNS

It shows the column information of virtual tables.

Table 1. Description of columns

Name of columns Description
NAME Name of a column
TYPE Data type of a column
ID Serial ID of a column
LENGTH Size of a column
TABLE_ID Table ID
FLAG Property of an interior column
PART_PAGE_COUNT Number of pages in a partition
PAGE_VALUE_COUNT Number of column values in a page
MINMAX_CACHE_SIZE Buffer size for MINMAX cache
MAX_CACHE_PART_COUNT Number of partition cache

V$CACHE

It shows the information about the cache.

Table 2. Description of columns

Name of columns Description
OBJ_COUNT The number of objects in the cache.

V$CACHE_OBJECTS

It describes the objects in the cache.

Table 3. Description of columns

Name of columns Description
OID Object ID
REF_COUNT Object's reference count
FLAG Object property flag

V$STORAGE

It represents the internal information of the STORAGE.

Table 4. Description of columns

Name of columns Description
DC_TABLE_FILE_SIZE The total size of file a that has data of all disk columnar tables.
DC_TABLESPACE_DWFILE_SIZE The total size of "double-write file" that has data of all disk columnar tablespaces.

V$STORAGE_MOUNT_DATABASES

It shows the information of the mounted database.

Table 5. Description of columns

Name of columns Description
NAME Name of the mounted database
PATH Location of the mounted database
BACKUP_TBSID Backup tablespace ID
BACKUP_SCN Unique ID of backup
DB_BEGIN_TIME Starting time of the duration of the backup database
DB_END_TIME Ending time of the duration of the backup database
BACKUP_BEGIN_TIME Starting time of backup
BACKUP_END_TIME Ending time of backup
FLAG Property flag

V$STORAGE_DC_TABLE_COLUMNS

It shows the internal column information of the log table.

Table 6. Description of columns

Name of columns Description
TABLE_ID Table ID
ID Column ID
FLAG Column property
SIZE Column size
PARTITION_VALUE_COUNT Maximum value in a partition
PAGE_VALUE_COUNT Maximum value in a page
CACHE_VALUE_COUNT Maximum number of cache values
MINMAX_CACHE_SIZE Cache size (Byte) of Min/Max values of column partition
CUR_APPEND_PARTITION_ID ID of a partition that currently appending
CUR_CACHE_PARTITION_COUNT Number of currently cached partitions
CUR_MINMAX_CACHE_SIZE Size of values stored in Min/Max cache
END_RID_FOR_DEFAULT_VALUE When the value of RID is smaller than the value set by END_RID_FOR_DEFAULT_VALUE, it has default value.
DISK_FILE_SIZE The sum of partitioned column file size which has stored data of columns.

V$STORAGE_DC_TABLE_COLUMN_PARTS

It represents the partition information that consisted of a log table.

Table 7. Description of columns

Name of columns Description
TABLE_ID Table ID
COLUMN_ID COLUMN_ID
ID ID
FLAG Property information
BEGIN_RID ID of the first row
END_RID The last row ID +1
END_SYNC_RID If RID among the values of column partition meets the following formula, BEGIN_RID <= RID < END_SYNC_RID, the value will be applied to the disk.
MIN_TIME Minimum value of the time of the column partition row
MAX_TIME Maximum value of the time of the column partition row
MAX_VALUE_COUNT_PER_PARTITION Maximum number of rows per partition
MAX_VALUE_COUNT_PER_PAGE Maximum number of rows for a page of column partition
MAX_PAGE_COUNT Maximum number of pages for column partition
PAGE_SIZE Size of page in column partition
PAGE_COUNT Number of pages in the current column partition
COMPRESS_RATIO It represents the ratio of data compression of column partition (when it is recorded in the disk, it is 0 if there is no history on the compressed disk)
DISK_FILENAME Name of the file where column partition is stored
EXTERNAL_PART_SIZE Very large value (larger than 1024 by default) is stored on external partition. And it is the size of the external partition.

V$STORAGE_DC_TABLE_INDEXES

It indicates the index information in the log table.
Table 8. Description of columns

Name of columns Description
TABLE_ID Table ID
COLUMN_ID COLUMN_ID
FLAG Column property
TABLE_BEGIN_RID Begin RID of table where index exists
TABLE_END_RID End RID of table where index exists
BEGIN_RID The first row ID of an index.
END_RID The last row ID of an index
END_SYNC_RID The last row ID +1 which is stored on the disk among rows that the index has.
COLUMN_COUNT Number of columns
BEGIN_PART_ID First partition ID of the index
END_PART_ID Last partition ID of the index
FLUSH_REQUEST_COUNT The number of the build index partition that has not been saved on the disk yet.
MAX_KEY_SIZE Maximum key size
INDEX_TYPE Index type
DISK_FILE_SIZE The sum of the index partition file size that stores the Index data.

V$STORAGE_DC_TABLES

It represents the internal information of the log table.

Table 9. Description of columns

Name of columns Description
ID Table ID
VERSION It is updated whenever a DDL is performed by the table version.
DDL_REF_COUNT It is the number of holding tables of DDL such as CREATE index. It is reduced when the DDL is terminated. If there is no DDL, it's 0.
BEGIN_RID First row ID of a table
END_RID Last row ID + 1 of a table
BEGIN_META_RID Begin ID of table which is reflected on the disk meta
END_META_RID End ID of table which is reflected on the disk meta
END_SYNC_RID The last row ID + 1 reflected on the disk
FLAG Flag property of table
COLUMN_COUNT Column number of the table
INDEX_COUNT Number of index
INDEX_MIN_END_RID Last row ID + 1 moved to the index
TYPE Table type

V$STORAGE_DC_LSMINDEX_LEVEL_PARTS

It displays partition objects about LSM index.

Table 10. Description of columns

Name of columns Description
TABLE ID Table ID where Index was created.
INDEX_ID Index ID
LEVEL A level where partition belongs to.
PARTITION_ID Partition ID
BEGIN_RID The first records ID in the partition.
END_RID The last records ID + 1 in the partition
KEY_VALUE_COUNT The number of key values that the partitions has
KEY_VALUE_TABLE_SIZE Sum of index page size about key value page
KEY_VALUE_TABLE_PAGE_COUNT The number of index page about key value page
BITMAP_TABLE_SIZE Sum of index page size about bitmap page
BITMAP_TABLE_PAGE_COUNT The number of index pages about bitmap page
META_SIZE Sum of mega pages that have meta information
META_PAGE_COUNT The number of meta pages that have meta information

V$STORAGE_DC_LSMINDEX_LEVELS

It describes the status information about each level of LSM index.

Table 11. Description of columns

Name of columns Description
TABLE ID Table ID where Index was created.
INDEX_ID Index ID
LEVEL A level where partition belongs to.
PARTITION_ID Partition ID
BEGIN_RID The first records ID in the partition.
END_RID The last records ID + 1 in the partition
META_BEGIN_RID BEGIN_RID that is reflected into meta.
META_END_RID END_RID that is reflected into meta.
DELETE_END_RID RID + 1 of the deleted record.

V$PROPERTY

It indicates the information of the property that was set on the server.

Table 12. Description of columns

Name of columns Description
NAME Property name
VALUE Value of the property that is set
TYPE Property data types
DEFLT Default value (If not specified, the value is used.)
MIN Minimum value of a range
MAX Maximum value of a range

V$SESMEM

It shows the amount of memory usage that the connected sessions use.

Table 13. Description of columns

Name of columns Description
SID Session ID
ID ID of memory category
USAGE USAGE

V$SESSION

It shows all the sessions connected to Machbase.

Table 14. Description of columns

Name of columns Description
ID Session ID
CLOSED Connected or disconnected (If it is 1, the session is over.)
USER_ID User ID
LOGIN_TIME Connection time
SQL_LOGGING The first records ID in the partition. It represents the logging flag bit of the session (32-bit). 0-th bit: error logging at the preparation (1)/ 1-th bit: all DDL query logging (2)
SHOW_HIDDEN_COLS It represents whether the column is hidden or not in the current session. 0: hidden column/ 1: public column
DATE_FORMAT In the session, it shows the string format that will be used for the default.
HASH_BUCKET_SIZE It represents the size of a hash table used in the current session.

V$SESSTAT

It shows the statistics of the session level.

Table 15. Description of columns

Name of columns Description
SID Session ID
ID ID of status category
VALUE Status value

V$SESTIME

It represents the timing information of the session level.

Table 16. Description of columns

Name of columns Description
SID Session ID
ID Timing ID
ACCUM_TICK Total number of clock cycles
MAX_TICK Maximum clock cycles

V$STMT

It shows the user-defined SQL statements that every session of the system uses.

Table 17. Description of columns

Name of columns Description
ID ID of the statement
SESS_ID Session ID
STATE Condition
RECORD_SIZE Size of the record while conducting SELECT statement
QUERY User query string (Display up to 256 characters)

V$SYSMEM

It shows the memory usage at the system level of Machbase.

Table 18. Description of columns

Name of columns Description
ID ID of system memory stats
NAME Name of system memory & statistics
USAGE Amount of system memory usage
MAX_USAGE Maximum amount of system memory usage

V$SYSSTAT

It shows various statistical information of system level of Machbase.

Table 19. Column dcescriptions

Name of columns Description
ID ID of system statistics
NAME Name of system statistics
VALUE Total amount of system statistics

V$SYSTIME

It shows a variety of system-level timing information of Machbase.

Table 20. Description of columns

Name of columns Description
ID ID of system timing
NAME Name of system timing
ACCUM_TICK Accumulated clock cycles of system timing
AVG_TICK Avergage number of system timing
MIN_TICK Minimum clock cycles of system timing
MAX_TICK Maximum colock cycles of system timing

V$STORAGE_DC_TABLESPACES

**It shows a list of user's tablespaces.

Table 21. Description of columns

Name of columns Description
NAME Tablespace name
ID Tablespace ID
FLAG Tablespace properties flag
REF_COUNT Tablespace reference count
DISK_COUNT The number of disks in the tablespace

V$TABLES

It shows a list of virtual tables that Machbase has.

Table 22. Description of columns

Name of columns Description
NAME Column name
TYPE Column data type
ID Serial ID of columns
USER_ID User ID
OLCOUNT Number of columns

V$STORAGE_DC_TABLESPACE_DISKS

This shows information of all the disks in the Machbase.

Table 23. Description of columns

Name of columns Description
NAME Name of the disk
ID Disk ID
TABLESPACE_ID Disk belongs to Tablespace IDs
PATH Location in the file system of the disk
IO_THREAD_COUNT The number of I/O threads assigned to the disk

V$VERSION

This displays the version information of the Machbase.

Table 24. Description of columns

Name of columns Description
BINARY_DB_MAJOR_VERSION Database Major Version
BINARY_DB_MINOR_VERSION Database Minor Version
BINARY_META_MAJOR_VERSION Database Meta Major Version
BINARY_META_MINOR_VERSION Database Meta Minor Version
BINARY_CM_MAJOR_VERSION Database Communication Module Major Version
BINARY_CM_MINOR_VERSION Database Communication Module Minor Version
FILE_DB_MAJOR_VERSION File Database Major Version
FILE_DB_MINOR_VERSION File Database Minor Version
FILE_META_MAJOR_VERSION File Meta Major Version
FILE_META_MINOR_VERSION File Meta Minor Version
FILE_CM_MAJOR_VERSION File Communication Module Major Version
FILE_CM_MINOR_VERSION File Communication Module Minor Version
FILE_CREATE_TIME File Create Time

results matching ""

    No results matching ""