What is the best way to assess tables that need to be vacuumed or analyzed?
This query returns tables where greater than 20% of rows are unsorted or statistics are 20% stale.
SELECT "database", "schema", "table", unsorted, stats_off
FROM svv_table_info
WHERE unsorted > 20
OR stats_off > 20
How can I troubleshoot loading errors?
Selecting from stl_load_errors provides information about errors during loading, and can be helpful for troubleshooting problematic loads.
SELECT *
FROM stl_load_errors
ORDER BY starttime DESC
LIMIT 100;
How to look specifically for failed logins?
SELECT *
FROM stl_connection_log
WHERE event='authentication failure'
ORDER BY recordtime;
Showing successfully authenticated users with the number of successful authentications:
SELECT username, event, COUNT(*)
FROM stl_connection_log
WHERE event = 'authenticated'
GROUP BY 1, 2
ORDER BY 3 DESC;
Showing successfully authenticated users by hourly buckets:
SELECT DATE_PART(YEAR, recordtime) || '-' ||
LPAD(DATE_PART(MONTH, recordtime),2,'0') || '-' ||
LPAD(DATE_PART(DAY, recordtime),2,'0') || ' ' ||
LPAD(DATE_PART(HOUR, recordtime),2,'0') AS hour_bucket, username, COUNT(*)
FROM stl_connection_log
WHERE event = 'authenticated'
GROUP BY 1, 2
ORDER BY 1, 2 DESC;
Showing a list of the connection drivers used by the redshift users:
SELECT username, application_name, COUNT(*)
FROM stl_connection_log
WHERE application_name != ''
GROUP BY 1,2
ORDER BY 1,2;
Privilege violation logging & monitoring in AWS Redshift
Prior to setting such access controls, you will be able to see queries pulling data from these resources by querying STL_QUERY, as seen below: Retrieving queries access to specific objects in Redshift:
SELECT * FROM STL_QUERY
WHERE userid!=1
AND querytxt LIKE '%customers%'
ORDER BY query DESC
LIMIT 100;
SELECT username,dbname,recordtime
FROM stl_connection_log
WHERE event='authentication failure'
ORDER BY recordtime > '2022-07-08';
Get the disk based queries information for last 2 days
SELECT q.query,
q.endtime - q.starttime AS duration,
SUM(( bytes ) / 1024 / 1024 / 1024) AS GigaBytes,
aborted,
q.querytxt
FROM stl_query q
join svl_query_summary qs
ON qs.query = q.query
WHERE qs.is_diskbased = 't'
AND q.starttime BETWEEN SYSDATE - 2 AND SYSDATE
GROUP BY q.query,
q.querytxt,
duration,
aborted
ORDER BY gigabytes DESC ;
/* Query showing information about sessions with currently running queries */
SELECT s.process AS pid
,date_Trunc ('second',s.starttime) AS S_START
,datediff(minutes,s.starttime,getdate ()) AS conn_mins
,trim(s.user_name) AS USER
,trim(s.db_name) AS DB
,date_trunc ('second',i.starttime) AS Q_START
,i.query
,trim(i.query) AS sql
FROM stv_sessions s
LEFT JOIN stv_recents i
ON s.process = i.pid
AND i.status = 'Running'
WHERE s.user_name <> 'rdsdb'
ORDER BY 1;
/* Query shows EXPLAIN plans which flagged "missing statistics" on the underlying tables */
SELECT substring(trim(plannode),1,100) AS plannode
,COUNT(*)
FROM stl_explain
WHERE plannode LIKE '%missing statistics%'
AND plannode NOT LIKE '%redshift_auto_health_check_%'
GROUP BY plannode
ORDER BY 2 DESC;
/* query showing queries which are waiting on a WLM Query Slot */
SELECT w.query
,substring(q.querytxt,1,100) AS querytxt
,w.queue_start_time
,w.service_class AS class
,w.slot_count AS slots
,w.total_queue_time / 1000000 AS queue_seconds
,w.total_exec_time / 1000000 exec_seconds
,(w.total_queue_time + w.total_Exec_time) / 1000000 AS total_seconds
FROM stl_wlm_query w
LEFT JOIN stl_query q
ON q.query = w.query
AND q.userid = w.userid
WHERE w.queue_start_Time >= dateadd(day,-7,CURRENT_DATE)
AND w.total_queue_Time > 0
-- and q.starttime >= dateadd(day, -7, current_Date)
-- and ( querytxt like 'select%' or querytxt like 'SELECT%' )
ORDER BY w.total_queue_time DESC
,w.queue_start_time DESC limit 35;
/* query showing queries which are waiting on a WLM Query Slot */
SELECT w.query
,substring(q.querytxt,1,100) AS querytxt
,w.queue_start_time
,w.service_class AS class
,w.slot_count AS slots
,w.total_queue_time / 1000000 AS queue_seconds
,w.total_exec_time / 1000000 exec_seconds
,(w.total_queue_time + w.total_Exec_time) / 1000000 AS total_seconds
FROM stl_wlm_query w
LEFT JOIN stl_query q
ON q.query = w.query
AND q.userid = w.userid
WHERE w.queue_start_Time >= dateadd(day,-7,CURRENT_DATE)
AND w.total_queue_Time > 0
-- and q.starttime >= dateadd(day, -7, current_Date)
-- and ( querytxt like 'select%' or querytxt like 'SELECT%' )
ORDER BY w.total_queue_time DESC
,w.queue_start_time DESC limit 35;
