Statistical System Tables
The statistical system tables contain historical data about the usage and the status of the DBMS. The statistical system tables are placed in the system schema EXA_STATISTICS.
The system schemas SYS and EXA_STATISTICS are automatically integrated into the namespace. This means that the tables in the system schemas can be accessed without specifying the schema as long as the same table name is not in use in the current schema.
The tables can also be retrieved in the EXA_SYSCAT system table. Timestamps of historical statistics are stored in the current database time zone (DBTIMEZONE).
Statistics are updated periodically, for an explicit update, you can use the command FLUSH STATISTICS.
Most statistical system tables can be accessed by all users as read-only and are subject to the transactional concept explained in Transaction Management. Therefore, you may have to open a new transaction to see the updated data.
System tables with DBA in its name require the SELECT ANY DICTIONARY privilege to access them.
Within a transaction, you should access exclusively either statistical system tables or normal database objects to minimize transactional conflicts for the user and the DBMS.
Click on the table names below for more details about each statistical system table.
Statistical System Table Name | Description |
---|---|
EXA_DB_SIZE_DAILY | This system table contains information about daily aggregated database sizes at cluster level, sorted by the interval start. |
EXA_DB_SIZE_HOURLY | This system table contains information about hourly aggregated database sizes at cluster level, sorted by the interval start. |
EXA_DB_SIZE_LAST_DAY | This system table contains cluster-level database size of the last 24 hours. For each cluster the information is aggregated across all nodes. |
EXA_DB_SIZE_MONTHLY | This system table contains information about monthly aggregated database sizes at cluster level, sorted by the interval start. |
EXA_DBA_AUDIT_IMPERSONATION | This system table contains all impersonations. |
EXA_DBA_AUDIT_SESSIONS | This system table contains all sessions if auditing is enabled. |
EXA_DBA_AUDIT_SQL | This system table contains all executed SQL statements if auditing is enabled. |
EXA_DBA_IMPERSONATION_LAST_DAY | This system table contains all impersonation of last day. |
EXA_DBA_PROFILE_LAST_DAY | This system table contains all profiling information of sessions with activated profiling. |
EXA_DBA_PROFILE_RUNNING | This system table contains all profiling information of running queries. |
EXA_DBA_SESSIONS_LAST_DAY | This system table contains all sessions of the last day. |
EXA_DBA_TRANSACTION_CONFLICTS | This system table contains all transaction conflicts. |
EXA_MONITOR_DAILY | This system table contains the daily aggregated monitoring information (of values from EXA_MONITOR_LAST_DAY) for a cluster sorted by the interval start and cluster name. |
EXA_MONITOR_HOURLY | This system table contains the hourly aggregated monitoring information (of values from EXA_MONITOR_LAST_DAY) for a cluster sorted by the interval start and cluster name. |
EXA_MONITOR_LAST_DAY | This system table contains monitoring information (the maximum values in the cluster). |
EXA_MONITOR_MONTHLY | This system table contains the monthly aggregated monitoring information (of values from EXA_MONITOR_LAST_DAY) for a cluster sorted by the interval start and cluster name. |
EXA_SQL_DAILY | This system table contains the daily aggregated number of executed SQL statements per cluster sorted by the interval start. |
EXA_SQL_HOURLY | This system table contains the hourly aggregated number of executed SQL statements per cluster sorted by the interval start. |
EXA_SQL_LAST_DAY | This system table contains all executed SQL statements without any reference to the executing user or detail SQL texts. |
EXA_SQL_MONTHLY | This system table contains the monthly aggregated number of executed SQL statements per cluster sorted by the interval start. |
EXA_SYSTEM_EVENTS | This system table contains system events like startup or shutdown of the DBMS. |
EXA_USAGE_DAILY | This system table contains the cluster level daily aggregated usage information of the DBMS, sorted by the interval start. |
EXA_USAGE_HOURLY | This system table contains the cluster level hourly aggregated usage information of the DBMS, sorted by the interval start. |
EXA_USAGE_LAST_DAY | This system table contains information about the DBMS usage of the last 24 hours. |
EXA_USAGE_MONTHLY | This system table contains the cluster level monthly aggregated usage information of the DBMS, sorted by the interval start. |
EXA_USER_IMPERSONATION_LAST_DAY | This system table contains all impersonations involving the current user. |
EXA_USER_PROFILE_LAST_DAY | This system table contains all profiling information of own sessions with activated profiling. |
EXA_USER_PROFILE_RUNNING | This system table contains all profiling information of running queries for the current user. |
EXA_USER_SESSIONS_LAST_DAY | This system table contains all own sessions of the last day. All users have access to the table. |
EXA_USER_TRANSACTION_CONFLICTS_LAST_DAY | This system table describes all transaction conflicts linked to the current user's sessions. |