The Data Guy

Tables with high number of alerts

RStoolKit Results: Tables with high number of alerts

RedShift will log the table names into STL_ALERT_EVENT_LOG tables when query optimizer identifies conditions that might indicate performance issues. This table is only visible to the super user. Use can see the following alerts.

RStoolKit Results: Most frequent Alert (> 500 times)

This particular health check part will be notifying you that what are all the most frequent alerts.

Find more frequent alert:

select
	Trim(Split_part(event, ':', 1)) alert,
	count(*) as count
from
	stl_alert_event_log
group by
	alert
order by
	count desc;

List all tables with their alerts:

with cte as (
SELECT
	a."schema",
	Trim(s.perm_table_name) AS table,
	Trim(Split_part(l.event, ':', 1)) AS event
FROM
	stl_alert_event_log AS l
left join stl_scan AS s ON
	s.query = l.query
	AND s.slice = l.slice
	AND s.segment = l.segment
join pg_catalog.svv_table_info a on
	s.tbl = a.table_id
WHERE
	l.userid > 1
	AND l.event_time >= Dateadd(day,
	-7,
	Getdate())
	AND s.perm_table_name NOT LIKE 'volt_tt%'
	AND s.perm_table_name NOT LIKE 'Internal Worktable'
GROUP BY
	1,
	2,
	3)
select
	"schema",
	"table",
	count(event)as total_alert,
	Listagg(event,
	' ,')
from
	cte
group by
	"schema",
	"table"
order by
	total_alert desc

How to fix this problem:

There is no solution to fix these alert until you tune the query, but at least the able queries will help you to understand what tables are getting more alerts.

  1. STL_ALERT_EVENT_LOG - documentation
· aws, redshift, rstoolkit

Advertisement


Loading Comments