[SOL-222] EXA_STATISTICS schema Created: 16.01.2015  Updated: 02.04.2020

Status: Published
Project: Solution Center
Component/s: EXASolution
Affects Version/s: EXASolution 4.2.0, EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0
Fix Version/s: None

Type: Explanation
Reporter: Captain EXASOL Assignee: Captain EXASOL
Labels: None

Issue Links:
Related
relates SOL-583 Overview of Exasol's data and memory ... Published
is related to SOL-75 How to explain query performance usin... Published
is related to SOL-135 Transaction System Published
is related to SOL-221 LogServer Published
Solution:

Explanation

General Information

EXASolution 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" (see SOL-221), for manually flushing statistical data the command "FLUSH STATISTICS" is available. All tables are subject to the transaction system (see SOL-135). 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
  • 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:

  • EXA_MONITOR_LAST_DAY
  • EXA_DB_SIZE_HOURLY
  • EXA_SQL_DAILY
  • EXA_USAGE_MONTHLY

Further statistical system tables

Auditing data

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 data

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. See SOL-75 or manual section 3.4. for further information on profiling.

Transaction conflicts

The table EXA_DBA_TRANSACTION_CONFLICTS lists all transaction conflicts that occured. 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 occured within sessions created by the current user.
Both tables can be truncated with the "TRUNCATE AUDIT LOGS" statement.

System events

The table EXA_SYSTEM_EVENTS contains system events:

  • STARTUP, SHUTDOWN, RESTART
  • BACKUP_START, BACKUP_END
  • RESTORE_START, RESTORE_END
  • FAILSAFETY, RECOVERY_START, RECOVERY_END

Example

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,
       RAW_OBJECT_SIZE_AVG,
       MEM_OBJECT_SIZE_AVG
FROM EXA_DB_SIZE_MONTHLY;

Additional References

Further details on statistical system tables

Appendix A.2.3. in the user manual lists all statistical system tables including information on all available columns.
The command "DESCRIBE FULL" can be used to achieve further information on the columns of a statistical system table.

Category 1: Database Administration - Performance
Generated at Sun Jul 12 15:39:06 CEST 2020 using Jira 7.13.13#713013-sha1:4c0f5f5e3383570393fbbf3d2fd5de1eb4057c36.