DBA SQL Scripts

select pid,
       datname as database,
       usename as user,
       client_addr,
       to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS') as session_start,
       state,
       to_char(query_start, 'YYYY-MM-DD HH24:MI:SS') as start,
       now()-query_start as duration,
       backend_type,
       application_name as application,
       E''||replace(query, chr(10), ' ') as current_query
  from pg_stat_activity
 order by state, query_start, pid

select s.sid||','||s.serial# sid_serial,
       s.username,
       q.executions exec,
       q.parse_calls parse,
       q.disk_reads read,
       q.buffer_gets get  ,   
       replace(replace(q.sql_text,'<','<<'),'>','>>') current_query
  from gv$session s, gv$sql q
 where s.sql_address=q.address
   and   s.inst_id = q.inst_id
 order by s.sid

SELECT id, user, host, db, command, time,
       state, substr(replace(info, '\n', ' ') ,1,64) as current_query
from performance_schema.processlist
order by id;

where state = 'active'

and   s.status = 'ACTIVE'

where command = 'Query'

SELECT 'Blocking' as locks, blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS blocking_process_statement
  FROM pg_catalog.pg_locks blocked_locks
       JOIN pg_catalog.pg_stat_activity blocked_activity
         ON blocked_activity.pid = blocked_locks.pid
       JOIN pg_catalog.pg_locks blocking_locks
         ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
       JOIN pg_catalog.pg_stat_activity blocking_activity
         ON blocking_activity.pid = blocking_locks.pid
 WHERE NOT blocked_locks.GRANTED

select l.sid, l.type as lock_type, decode(l.lmode, 0, 'WAITING', 1,'Null', 2, 'Row Share', 
  3, 'Row Exclusive', 4, 'Share',
  5, 'Share Row Exclusive', 6,'Exclusive', l.lmode) lock_mode, 
           decode(l.request, 0,'HOLD', 1,'Null', 2, 'Row Share',
  3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive',
  6,'Exclusive', l.request) request, 
  count(*) lock_id
from gv$lock l
group by l.sid, l.type, l.lmode, l.request
order by l.sid, l.type, l.lmode, l.request

SELECT REQUESTING_ENGINE_TRANSACTION_ID, REQUESTING_ENGINE_LOCK_ID,
        BLOCKING_ENGINE_TRANSACTION_ID,  BLOCKING_ENGINE_LOCK_ID
  from performance_schema.data_lock_waits

select pg_size_pretty(sum(pg_database_size(datname)))
  from pg_database

select to_char(sum(bytes),'999,999,999,999,999')
  from sys.dba_data_files

select format(sum(data_length+index_length))
  from information_schema.tables

select relname as biggest_objects,
 case WHEN relkind='r' THEN 'Table' 
    WHEN relkind='i' THEN 'Index'
    WHEN relkind='t' THEN 'TOAST Table'
    ELSE relkind::text||'' end as object_type,
 rolname as owner,  n.nspname as schema,
 to_char(reltuples,'999G999G999G999G999G999') as rows,
 to_char(relpages::INT8*8*1024,'999G999G999G999G999G999') as bytes
  from pg_class, pg_roles, pg_catalog.pg_namespace n
 where relowner=pg_roles.oid
   and n.oid=pg_class.relnamespace
 order by relpages desc, reltuples desc
 limit 5

select segment_name,
       segment_type,
       owner,
       tablespace_name,
       to_char(bytes,'999,999,999,999,999') as bytes
  from (select segment_name, segment_type,
        tablespace_name, owner, sum(bytes) bytes
        from sys.dba_extents
        group by segment_name, segment_type, tablespace_name, owner order by bytes desc)
 where rownum <= 5
 order by bytes desc

select table_schema,
       table_name,
	'T',engine,
	format(data_length+index_length,0),
	format(table_rows,0)
  from  information_schema.tables
 order by data_length+index_length desc
 limit 5

SELECT pg_get_userbyid(userid) as user, calls,
  round((total_exec_time::numeric / nullif(calls::numeric, 0))/1000,3) as avg_sec,
  round((max_exec_time::numeric)/1000,3) as max_sec,
  round(total_exec_time) as total_time,
  rows,
  round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0)),2)  AS hit_percent,
  round((wal_bytes::numeric)/(1024*1024),0) as wals,
    CASE WHEN toplevel THEN 'T' ELSE 'F' END as top,
  E' '||replace(query, chr(10), ' ') as query_top
  FROM pg_stat_statements 
 ORDER BY total_exec_time DESC LIMIT 5

SELECT s.username,
       s.schema_name,
       s.calls,
       ROUND(s.exec_elapsed_time/1000000, 3) AS total_sec,
       ROUND(s.exec_elapsed_time/1000000/NULLIF(s.calls, 0), 3) AS avg_sec,
       ROUND(MAX(s.exec_elapsed_time)/1000000, 3) AS max_sec,
       s.rows_processed AS rows,
       ROUND(100.0 * s.buffer_gets / NULLIF(s.buffer_gets + s.disk_reads, 0), 2) AS hit_percent,
       ROUND(s.physical_reads, 0) AS reads,
       CASE WHEN s.parsing_schema_name IS NOT NULL THEN 'T' ELSE 'F' END AS top,
       REPLACE(REPLACE(SUBSTR(s.sql_text, 1, 200), CHR(10), ' '), CHR(13), ' ') AS query_top
  FROM gv$sqlarea s
 WHERE s.sql_text IS NOT NULL
 ORDER BY s.exec_elapsed_time DESC
 FETCH FIRST 5 ROWS ONLY

-- Requires Diagnostic Pack Option (since it uses an AWR view)
SELECT h.username,
       h.parsing_schema_name AS schema_name,
       SUM(h.exec_total) AS calls,
       ROUND(SUM(h.elapsed_time_total)/1000000, 3) AS total_sec,
       ROUND(SUM(h.elapsed_time_total)/1000000/NULLIF(SUM(h.exec_total), 0), 3) AS avg_sec,
       ROUND(MAX(h.elapsed_time_total)/1000000, 3) AS max_sec,
       SUM(h.rows_processed) AS rows,
       ROUND(100.0 * SUM(h.buffer_gets) / NULLIF(SUM(h.buffer_gets) + SUM(h.disk_reads), 0), 2) AS hit_percent,
       ROUND(SUM(h.disk_reads), 0) AS reads,
       'T' AS top,
       REPLACE(REPLACE(SUBSTR(t.sql_text, 1, 200), CHR(10), ' '), CHR(13), ' ') AS query_top
  FROM dba_hist_sqlstat h
  JOIN dba_hist_sqltext t
    ON t.sql_id = h.sql_id
 WHERE h.dbid = (SELECT dbid FROM v$database)
   AND h.instance_number = (SELECT instance_number FROM v$instance)
   AND h.snap_id IN (
        SELECT snap_id
          FROM dba_hist_snapshot
         WHERE begin_interval_time >= SYSDATE - 7
       )
   AND t.sql_text IS NOT NULL
 GROUP BY h.username, h.parsing_schema_name, t.sql_text
 ORDER BY total_sec DESC
 FETCH FIRST 5 ROWS ONLY

SELECT SCHEMA_NAME, COUNT_STAR, 
 SUM_TIMER_WAIT, SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000) hr_time, 
 round(AVG_TIMER_WAIT/1000000000000,3) AVG_TIMER_WAIT, 
 SUM_ROWS_SENT, concat('\n', substring(DIGEST_TEXT, 1, 128)) as query_top
  from performance_schema.events_statements_summary_by_digest
 order by SUM_TIMER_WAIT desc limit 5

select datname as database, 
       round((blks_hit)*100.0/nullif(blks_read+blks_hit, 0),2) as hit_ratio
  from pg_stat_database
 where datname not like 'template%'
 order by datname

select round(1-(sum(decode(name,'physical reads',1,0)*value) 
                /(sum(decode(name,'db block gets',1,0)*value) 
                  +sum(decode(name,'consistent gets',1,0)*value))), 3)*100
 from v$sysstat
 where name in ('db block gets', 'consistent gets', 'physical reads')

select format(100-t1.variable_value*100/t2.variable_value,2)
  from performance_schema.global_status t1, performance_schema.global_status t2
 where t1.variable_name='INNODB_BUFFER_POOL_READS'
   and t2.variable_name='INNODB_BUFFER_POOL_READ_REQUESTS'

select client_addr, state, sync_state, txid_current_snapshot() as txid_current,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS') as backend_start,
       write_lag, flush_lag, replay_lag
  from pg_stat_replication

select round((ARCHIVED_TIME-APPLIED_TIME)*24*60) Est_GAP_IN_MINUTES
  from (SELECT MAX(COMPLETION_TIME) ARCHIVED_TIME
          FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),
       (SELECT MAX(COMPLETION_TIME) APPLIED_TIME
          FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES')

select CHANNEL_NAME, GROUP_NAME, SOURCE_UUID, THREAD_ID,
       SERVICE_STATE, COUNT_RECEIVED_HEARTBEATS,
       LAST_HEARTBEAT_TIMESTAMP, RECEIVED_TRANSACTION_SET, LAST_ERROR_NUMBER,
       LAST_ERROR_MESSAGE, LAST_ERROR_TIMESTAMP 
  from performance_schema.replication_connection_status

select name,
       case when unit='kB'  then pg_size_pretty(setting::bigint*1024)
            when unit='8kB' then pg_size_pretty(setting::bigint*1024*8)
            when unit='B'   then pg_size_pretty(setting::bigint)
            when unit='MB'  then pg_size_pretty(setting::bigint*1024*1024)
            else setting||' '||coalesce(unit,'') end as value,
       min_val, max_val,
       context,
       unit, source,
       setting, substring(category,1,40)  as category --, short_desc
  from pg_settings
 order by name

select name, value
  from v$parameter
 order by name

select variable_name, format(variable_value,0)
  from performance_schema.global_variables