Reviewing Query Alerts
To use the STL_ALERT_EVENT_LOG system table to identify and correct potential performance issues with your query, follow these steps:
Run the following to determine your query ID:Copy
select query, elapsed, substring from svl_qlog order by query desc limit 5;
Examine the truncated query text in the
substringfield to determine which
queryvalue to select. If you have run the query more than once, use the
queryvalue from the row with the lower
elapsedvalue. That is the row for the compiled version. If you have been running many queries, you can raise the value used by the LIMIT clause used to make sure your query is included.
Select rows from STL_ALERT_EVENT_LOG for your query:Copy
Select * from stl_alert_event_log where query = MyQueryID;
Evaluate the results for your query. Use the following table to locate potential solutions for any issues that you have identified.
Not all queries will have rows in STL_ALERT_EVENT_LOG, only those with identified issues.
Issue Event Value Solution Value Recommended Solution Statistics for the tables in the query are missing or out of date. Missing query planner statistics Run the ANALYZE command See Table Statistics Missing or Out of Date. There is a nested loop join (the least optimal join) in the query plan. Nested Loop Join in the query plan Review the join predicates to avoid Cartesian products See Nested Loop. The scan skipped a relatively large number of rows that are marked as deleted but not vacuumed, or rows that have been inserted but not committed. Scanned a large number of deleted rows Run the VACUUM command to reclaim deleted space See Ghost Rows or Uncommitted Rows. More than 1,000,000 rows were redistributed for a hash join or aggregation. Distributed a large number of rows across the network:RowCount rows were distributed in order to process the aggregation Review the choice of distribution key to collocate the join or aggregation See Suboptimal Data Distribution. More than 1,000,000 rows were broadcast for a hash join. Broadcasted a large number of rows across the network Review the choice of distribution key to collocate the join and consider using distributed tables See Suboptimal Data Distribution. A DS_DIST_ALL_INNER redistribution style was indicated in the query plan, which forces serial execution because the entire inner table was redistributed to a single node. DS_DIST_ALL_INNER for Hash Join in the query plan Review the choice of distribution strategy to distribute the inner, rather than outer, table See Suboptimal Data Distribution.