This page has not been translated into your language. Request translation

Reviewing query alerts by table

PDFRSS

The following query identifies tables that have had alert events logged for them, and also identifies what type of alerts are most frequently raised.

If the minutes value for a row with an identified table is high, check that table to see if it needs routine maintenance, such as having ANALYZE or VACUUM run against it.

If the count value is high for a row but the table value is null, run a query against STL_ALERT_EVENT_LOG for the associated event value to investigate why that alert is getting raised so often.

select trim(s.perm_table_name) as table, 
(sum(abs(datediff(seconds, s.starttime, s.endtime)))/60)::numeric(24,0) as minutes, trim(split_part(l.event,':',1)) as event,  trim(l.solution) as solution, 
max(l.query) as sample_query, count(*) 
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 and s.step = l.step
where l.event_time >=  dateadd(day, -7, current_Date) 
group by 1,3,4 
order by 2 desc,6 desc;