Use Monitoring Tables

QUERY_EVENTS System Table

The QUERY_EVENTS system table provides useful information about queries that have been recently executed in your Data Warehouse instance (see QUERY_EVENTS).

• The EVENT_TIMESTAMP column contains the timestamp that is recorded when the event occurs, which can assist in identifying the code that was being executed at the time of the event.
• The EVENT_TYPE column provides information about the type of the captured events. The following values may indicate a problem that requires attention:
  • PREDICATE OUTSIDE HISTOGRAM: The query optimizer encountered a predicate that was false for the entire histogram created by ANALYZE_STATISTICS or ANALYZE HISTOGRAM.
  • NO HISTOGRAM: The query optimizer encountered a predicate on a column lacking a histogram.
  • MEMORY LIMIT HIT: The optimizer used all allocated memory when creating the query plan. Simplify your query instead of increasing the memory allocation.
• The EVENT_DESCRIPTION column provides information about the captured events. For example:

  • GROUP BY key set did not fit in memory, using external sort grouping: To fix this issue, consider a projection sorted by the GROUP BY key to enable pipelined GROUP BY sorting, which forces only the group that is currently being processed to be retained in memory (see GROUP BY Paths).

  • Many rows were resegmented during plan execution: To fix this issue, consider using identically segmented projections (see Minimize Network Joins).

  • The optimized encountered a predicate on a column for which it does not have a histogram: The database needs to have statistics updated.

Statement Identifiers

In the QUERY_EVENTS table, the combination of TRANSACTION_ID, REQUEST_ID, and STATEMENT_ID fields uniquely identify the SQL statement that is located in the QUERY_REQUESTS table.

QUERY_REQUESTS Table

This table retains information about query plans, optimization, and execution events. For any unique combination of TRANSACTION_ID and STATEMENT_ID, the value of the REQUEST field contains an SQL request.

• The REQUEST_TYPE column contains the type of the statement (QUERY, DDL, or other).
• The REQUEST column contains the text of the SQL statement.
• The SUCCESS column contains a Boolean value indicating whether the query executed properly. Errors are logged in the ERROR_MESSAGES system table.
• The START_TIMESTAMP column contains the beginning timestamp for the logged event.
• The END_TIMESTAMP column contains the ending timestamp for the logged event.