SVCS_ALERT_EVENT_LOG - Amazon Redshift
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

本文属于机器翻译版本。若本译文内容与英语原文存在差异,则一律以英文原文为准。

SVCS_ALERT_EVENT_LOG

当查询优化程序发现可能指示性能问题的条件时记录警报。该视图派生自 STL_ALERT_EVENT_LOG 系统表,但不显示在并发扩展集群上运行的查询的切片级别。使用 SVCS_ALERT_EVENT_LOG 表标识用于改进查询性能的机会。

一个查询包含多个区段,而且每个区段包含一个或多个步骤。有关更多信息,请参阅 查询处理。)

注意

带有前缀 SVCS 的系统视图提供了有关主集群和并发扩展集群上的查询的详细信息。这些视图与带有前缀 STL 的表类似,但 STL 表仅提供在主集群上运行的查询的信息。

SVCS_ALERT_EVENT_LOG 对所有用户可见。超级用户可以查看所有行;普通用户只能看到自己的数据。有关更多信息,请参阅 Visibility of data in system tables and views

Table columns

列名称 数据类型 Description
userid integer 生成该条目的用户 ID。
query integer 查询 ID。查询列可用于连接其他系统表和视图。
segment integer 标识查询区段的数字。
step integer 已执行的查询步骤。
pid integer 与语句和切片关联的进程 ID。如果同一查询在多个切片上执行,则该查询可能有多个 PID。
xid bigint 与语句关联的事务 ID。
event character(1024) 警报事件的描述。
solution character(1024) 建议的解决方案。
event_time timestamp 开始执行查询的时间 (用 UTC 表示),有 6 位数字精度,可精确到小数秒。例如:2009-06-12 11:29:19.131358

Usage notes

您可以使用 SVCS_ALERT_EVENT_LOG 来标识查询中的潜在问题,然后按照优化查询性能中的做法来优化数据库设计并重新编写查询。SVCS_ALERT_EVENT_LOG 将记录以下警报:

  • Missing statistics

    Statistics are missing. Run ANALYZE following data loads or significant updates and use STATUPDATE with COPY operations. For more information, see Amazon Redshift best practices for designing queries.

  • Nested loop

    A nested loop is usually a Cartesian product. Evaluate your query to ensure that all participating tables are joined efficiently.

  • Very selective filter

    The ratio of rows returned to rows scanned is less than 0.05. Rows scanned is the value of rows_pre_user_filter and rows returned is the value of rows in the STL_SCAN system table. Indicates that the query is scanning an unusually large number of rows to determine the result set. This can be caused by missing or incorrect sort keys. For more information, see 选择排序键.

  • Excessive ghost rows

    A 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. For more information, see 对表执行 vacuum 操作.

  • Large distribution

    More than 1,000,000 rows were redistributed for hash join or aggregation. For more information, see 选择数据分配方式.

  • Large broadcast

    More than 1,000,000 rows were broadcast for hash join. For more information, see 选择数据分配方式.

  • Serial execution

    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. For more information, see 选择数据分配方式.

Sample queries

以下查询显示了四种查询的警报事件。

SELECT query, substring(event,0,25) as event, substring(solution,0,25) as solution, trim(event_time) as event_time from svcs_alert_event_log order by query; query | event | solution | event_time -------+-------------------------------+------------------------------+--------------------- 6567 | Missing query planner statist | Run the ANALYZE command | 2014-01-03 18:20:58 7450 | Scanned a large number of del | Run the VACUUM command to rec| 2014-01-03 21:19:31 8406 | Nested Loop Join in the query | Review the join predicates to| 2014-01-04 00:34:22 29512 | Very selective query filter:r | Review the choice of sort key| 2014-01-06 22:00:00 (4 rows)