Each Exasol database includes two out-of-the-box schemas: SYS and EXA_STATISTICS. It is not possible to change these schemas manually, and the information visible in some system tables depend on the object and system privileges the user is assigned. It is also possible to grant access to every table in these schemas by granting the SELECT ANY DICTIONARY privilege to a user or role.
Exasol offers numerous statistical system tables containing data about the usage and the status of the DBMS. These system tables are located in the "EXA_STATISTICS" schema but are automatically integrated into the current namespace. This means that if an object with the same name does not exist in the current schema, they can be queried without the schema name, "EXA_STATISTICS". Otherwise, the system tables can be accessed via the respective schema-qualified name, EXA_STATISTICS.<table_name> (e.g. "SELECT * FROM EXA_STATISTICS.EXA_MONITOR_LAST_DAY").
All timestamps of historical statistics are stored in the current database time zone (DBTIMEZONE).
Statistics are updated periodically by a Server Process named "SQL LOGSERVER" (more info here). To manually flush statistical data, the command "FLUSH STATISTICS" is available. All tables are subject to the transaction system. Therefore it might be necessary to open a new transaction to see the up-to-date data. Statistical system tables, except those tables that are critical to security (e.g. auditing data), can be accessed by all users.
Statistical data classes
In general there are four different classes of statistical data:
Monitoring data (EXA_MONITOR_*), e.g. CPU usage, Temp_DB_RAM, HDD_Read, etc, for the entire database
DB size data (EXA_DB_SIZE_*), e.g. compressed database size
Query data (EXA_SQL_*), e.g. average query duration
Usage data (EXA_USAGE_*), e.g. concurrent queries
For each class there are four shapes:
Detailed data for the last 24 hours (*_LAST_DAY)
Aggregated data (*_HOURLY, *_DAILY, *_MONTHLY)
Therefore there are a total of 16 tables. Examples:
Additional statistical system tables
If Auditing is enabled for the database, the tables EXA_DBA_AUDIT_SESSION and EXA_DBA_AUDIT_SQL are used to trace all sessions/queries connected to/sent to the database. Those tables can be accessed by users having the "SELECT ANY DICTIONARY" system privilege. Auditing data can be dropped by the "TRUNCATE AUDIT LOGS" statement.
Profiling can be used to analyze queries in detail. Therefore the tables EXA_DBA_PROFILE_LAST_DAY and EXA_USER_PROFILE_LAST_DAY can be used. Seeherefor further information on profiling. This information is only available for the previous 24 hours.
The table EXA_DBA_TRANSACTION_CONFLICTS lists all transaction conflicts that occurred. This table can be accessed by users having the "SELECT ANY DICTIONARY" system privilege. The table EXA_USER_TRANSACTION_CONFLICTS_LAST_DAY lists all transaction conflicts that occurred within sessions created by the current user for the last day. Both tables can be truncated with the "TRUNCATE AUDIT LOGS" statement.
The table EXA_SYSTEM_EVENTS contains system events, such as:
STARTUP, SHUTDOWN, RESTART
FAILSAFETY, RECOVERY_START, RECOVERY_END
Determining the overall average compression ratio, average raw database size and average compressed database size on monthly basis:
SELECT RAW_OBJECT_SIZE_AVG/NULLIFZERO(MEM_OBJECT_SIZE_AVG) AS COMPRESSION_RATIO,