—-Database objects: we need to run the Below query for every database using Super user credentials
SELECT n.nspname AS schema_name
, CASE WHEN c.relkind = ‘v’ THEN ‘view’ when c.relkind = ‘i’ THEN ‘index’ ELSE ‘table’ END
AS table_type
, count(c.relname)
FROM pg_class As c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_description As d
ON (d.objoid = c.oid AND d.objsubid = 0)
WHERE
n.nspname not in (‘information_schema’, ‘pg_catalog’)
group BY n.nspname, CASE WHEN c.relkind = ‘v’ THEN ‘view’ when c.relkind = ‘i’ THEN ‘index’ ELSE ‘table’ END
UNION
SELECT
n.nspname,
‘Stored_procedure’ as table_type
,count(p.prosrc)
FROM
pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON
pronamespace = n.oid
join pg_catalog.pg_user b on
b.usesysid = p.proowner
where
nspname not in (‘information_schema’,
‘pg_catalog’)
group by n.nspname, table_type
—Databases:
select ‘Databases’,count(*) from pg_database
UNION
select ‘Schemas’,count(*) from pg_namespace where nspname not in (‘information_schema’,
‘pg_catalog’)
—-High Data Volume Tables
WITH tbl_ids AS
(
SELECT DISTINCT oid
FROM pg_class c
WHERE –relowner > 1
relkind = ‘r’
),
pcon AS
(
SELECT conrelid,
CASE
WHEN SUM(
CASE
WHEN contype = ‘p’ THEN 1
ELSE 0
END
) > 0 THEN ‘Y’
ELSE ‘N’
END pk,
CASE
WHEN SUM(
CASE
WHEN contype = ‘f’ THEN 1
ELSE 0
END
) > 0 THEN ‘Y’
ELSE ‘N’
END fk,
conname
FROM pg_constraint
WHERE conrelid > 0
AND conrelid IN (SELECT oid FROM tbl_ids)
GROUP BY conrelid,conname
)
SELECT
database
,SCHEMA as schemaname
,”table” AS tablename
,tbl_rows as num_rows
,size AS size_mb
,pcon.pk
,pcon.conname
FROM
svv_table_info ti
LEFT JOIN pcon ON pcon.conrelid = ti.table_id
WHERE ti.SCHEMA !~ ‘^information_schema|catalog_history|pg_’ and size_mb = 10000
—Data for Partitioning / Bucketing
SELECT
database
,SCHEMA as schemaname
,”table” AS table_name
,size AS size_mb
,tbl_rows as num_rows
,pg.attname column_name
,” as num_unique_values
FROM
svv_table_info ti
inner JOIN pg_attribute pg ON pg.attrelid = ti.table_id
WHERE ti.SCHEMA !~ ‘^information_schema|catalog_history|pg_’
—Count of stored procedures and Functions
select database_name,schema_name,function_type,count(*) from SVV_REDSHIFT_FUNCTIONS WHERE schema_name !~ ‘^information_schema|catalog_history|pg_’
group by database_name,schema_name,function_type
—List of stored procedure
select
database_name
,schema_name
,function_type
,function_name
from
SVV_REDSHIFT_FUNCTIONS
where
schema_name not in (‘information_schema’,
‘pg_catalog’)
—Count of external Tables/Views in Redshift
select
redshift_database_name
,schemaname
,tabletype
,count(tablename)
from
SVV_EXTERNAL_TABLES
group by
redshift_database_name
,schemaname
,tabletype
—List of External Tables
select
redshift_database_name
,schemaname
,tabletype
tablename
from
SVV_EXTERNAL_TABLES
—Total I/O Usage by Days
SELECT trunc(start_time) as RUNDATE,sum(local_read_IO+remote_read_IO)as TOTALIOREADS
FROM SYS_QUERY_DETAIL
where trunc(start_time) between ‘2024-03-28’ and ‘2024-04-03’
group by trunc(start_time)
Note: ‘2024-03-28’ and ‘2024-03-31’ Please change the Date range To the period of High usage and of minimum 30 Days.
—-Database Volume:
We need to create the above view in any schema and then execute the Below query to pull the details.
SELECT
dbase_name
,schemaname
,SUM(megabytes) as total_mb
FROM
public.v_space_used_per_tbl
GROUP BY
dbase_name
,schemaname
–Distinct application name
select distinct application_name
from pg_catalog.stl_connection_log where (recordtime between ‘2024-05-01’ and ‘2024-05-30’ )
and application_name is not null
–Distinct client ID
select distinct client_id from pg_catalog.stl_network_throttle where (log_time between ‘2024-05-01’ and ‘2024-05-30‘ )
— The date range is subject to change – as needed for query log assessment.— (15day/1month/6months whatever is possible for extraction)
Leaplogic