Details

    • Type: Explanation
    • Status: Published
    • Affects Version/s: EXASolution 4.2.0, EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
      None
    • Solution:
      Hide

      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.

      Show
      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

      Attachments

        Issue Links

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: