USEFUL QUERIES FOR TROUBLESHOOTING IN AMAZON REDSHIFT

 USEFUL QUERIES FOR TROUBLESHOOTING IN AMAZON REDSHIFT

TO CHECK LIST OF RUNNING QUERIES AND USERNAMES:
select a.userid, cast(u.usename as varchar(100)), a.query, a.label, a.pid, a.starttime, b.duration,
b.duration/1000000 as duration_sec, b.query as querytext
from stv_inflight a, stv_recents b, pg_user u
where a.pid = b.pid and a.userid = u.usesysid

**----To get  all schema table
select trim(pgdb.datname) as Database, trim(pgn.nspname) as Schema,trim(a.name) as Table, b.mbytes, a.rows
from ( select db_id, id, name, sum(rows) as rows from stv_tbl_perm a group by db_id, id, name ) as a join pg_class as pgc on pgc.oid = a.id join pg_namespace as pgn on pgn.oid = pgc.relnamespace
join pg_database as pgdb on pgdb.oid = a.db_id join (select tbl, count(*) as mbytes from stv_blocklist group by tbl) b on a.id=b.tbl --where pgn.nspname='stg'
order by a.db_id, a.name;
---To check Recents Running Queries
select * from stv_recents where status='Running';

--TO Kill the Queries
cancel <PID>
---To Check copy command
select getdate()- starttime as CurrentDuration ,user_name, duration, query from stv_recents where status = 'Running' and query like 'COPY%';

---To check Cluster Total disk space and Free space
select   sum(capacity)/1024 as capacity_gbytes,   sum(used)/1024 as used_gbytes,   (sum(capacity) - sum(used))/1024 as free_gbytes from   stv_partitions where part_begin=0;
-- Table Sizes & Rows
select    trim(pgdb.datname) as Database, trim(pgn.nspname) as Schema,trim(a.name) as Table,b.mbytes,a.rows
from ( select db_id, id, name, sum(rows) as rows from stv_tbl_perm a  group by db_id, id, name) as a
join pg_class as pgc on pgc.oid = a.id join pg_namespace as pgn on pgn.oid = pgc.relnamespace join pg_database as pgdb on pgdb.oid = a.db_id
join ( select tbl, count(*) as mbytes  from stv_blocklist group by tbl ) b on a.id = b.tbl
order by mbytes desc, a.db_id, a.name;
---To Check DB Infro and user
select * from pg_database_info;
select * from pg_user_info;
--TO find databaseSize;
select sum(mbytes)/1024 as db_size_in_gb, database from (select trim(pgdb.datname) as Database,trim(a.name) as Table, b.mbytes from stv_tbl_perm a join pg_database as pgdb on pgdb.oid = a.db_id
join (select tbl, count(*) as mbytes from stv_blocklist group by tbl) b on a.id=b.tbl
where a.slice=0 order by db_id, name) group by database;



TO LOOK FOR ALERTS:
select * from STL_ALERT_EVENT_LOG
where query = 1011
order by event_time desc
limit 100;


TO CHECK TABLE SIZE:
select trim(pgdb.datname) as Database, trim(pgn.nspname) as Schema,
trim(a.name) as Table, b.mbytes, a.rows
from ( select db_id, id, name, sum(rows) as rows from stv_tbl_perm a group by db_id, id, name ) as a
join pg_class as pgc on pgc.oid = a.id
join pg_namespace as pgn on pgn.oid = pgc.relnamespace
join pg_database as pgdb on pgdb.oid = a.db_id
join (select tbl, count(*) as mbytes
from stv_blocklist group by tbl) b on a.id=b.tbl
order by b.mbytes desc, a.db_id, a.name;


TO CHECK FOR TABLE COMPRESSION:
analyze <tablename>;
analyze compression <tablename>;


TO ANALYZE ENCODING:
select "column", type, encoding
from pg_table_def where tablename = 'biglist';


TO CHECK LIST OF FILES COPIED:
select * from stl_load_errors

select * from stl_load_commits


select query, trim(filename) as file, curtime as updated, *
from stl_load_commits
where query = pg_last_copy_id();


TO CHECK LOAD ERRORS
select d.query, substring(d.filename,14,20),
d.line_number as line,
substring(d.value,1,16) as value,
substring(le.err_reason,1,48) as err_reason
from stl_loaderror_detail d, stl_load_errors le
where d.query = le.query
and d.query = pg_last_copy_id();

TO CHECK FOR DISKSPACE USED IN REDSHIFT:
select owner as node, diskno, used, capacity
from stv_partitions
order by 1, 2, 3, 4;
select query, trim(querytxt) as sqlquery
from stl_query
order by query desc limit 5;