Helpful pg_stat_activity queries Show all sessions ordered by state

select pid, usename, application_name, wait_event_type, wait_event, state from pg_stat_activity order by state;

Active sessions and the query they are running

select pid, usename, application_name, wait_event_type, wait_event, state, query from pg_stat_activity where state = 'active'

basic query for pg_stat_statements

Select userid, substring(query,1,80), calls, mean_time, rows/calls from pg_stat_statements

****************************************** keywords: pg_stat ****************************************** rdbms version: postgres 15 ******************************************

Postgres - oh no, I'm not about to post about Postgres

Well my job now envolves a lot of Postgres so.... More to come.

Statspack Analysis

Sample Queries for analysing Statspack base tables over time.

These Queries can be used to graph details via sql developer or excel.

OS Stats over time

SELECT sn.snap_time time , nm.stat_name , os.value FROM stats$osstat os , stats$osstatname nm , stats$snapshot sn WHERE os.osstat_id = nm.osstat_id AND nm.stat_name ='LOAD' --See stats$osstatname.stat_name for other os stats. AND sn.snap_id = os.snap_id AND sn.instance_number = ( SELECT instance_number FROM v$instance ) AND os.instance_number = sn.instance_number AND sn.snap_time between '06-FEB-17' and '11-FEB-17' order by 1;

Database time (DB Time)

SELECT to_char(sn.snap_time,'YYYY-MM-DD HH24:MI:SS') time , nm.stat_name , ( LEAD( tm.value, 1 ) OVER( ORDER BY tm.snap_id ) - tm.value ) /1000000/60 AS DB_TIME FROM stats$sys_time_model tm , stats$time_model_statname nm , stats$snapshot sn WHERE tm.stat_id = nm.stat_id AND nm.stat_name = 'DB time' AND sn.snap_id = tm.snap_id AND sn.instance_number = ( SELECT instance_number FROM v$instance ) AND tm.instance_number = sn.instance_number AND sn.snap_time > sysdate -10 order by sn.snap_time;

System Event Wait(ms) over time
Edit the query to select a particular event (select distinct (event) from stats$system_event order by 1;)

select to_char(snap_time,'Mon-dd hh24:mi:ss') snap_time, event event_name, round((time_waited_ms - prv_time_waited_ms) / decode((total_waits - prv_waits), 0,null, (total_waits - prv_waits)),2) wait_average_ms from ( select ss.snap_time, se.event, se.total_waits, lag(se.total_waits,1) over (order by se.snap_id) prv_waits, se.time_waited_micro/1000 time_waited_ms, lag(se.time_waited_micro/1000,1) over (order by se.snap_id) prv_time_waited_ms from perfstat.stats$snapshot ss, perfstat.stats$system_event se where ss.snap_time > sysdate -7 and se.snap_id = ss.snap_id and se.instance_number = ss.instance_number and se.dbid = ss.dbid -- select the event you want --and se.event = 'db file sequential read' --and se.event = 'db file scattered read' and se.event = 'log file sync' ) order by snap_time

Wait Class

select sh.snap_time , en.wait_class ,sse.total_waits from stats$system_event sse, STATS$SNAPSHOT SH , v$event_name en where sh.snap_id = sse.snap_id and sh.dbid = sse.dbid and sse.event_id = en.event_id --and en.wait_class = 'System I/O' --AND sh.snap_time between '27-FEB-17' and '5-MAR-17' AND trunc(sh.snap_time) > sysdate -7 order by sh.snap_time, en.wait_class

SQL over snaps

select ss.snap_id, ss.snap_time, sql_id, executions, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, disk_reads/decode(nvl(executions,0),0,1,executions) avg_pio, elapsed_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_elap, cpu_time/decode(nvl(executions,0),0,1,executions)/1000000avg_cpu , rows_processed/decode(nvl(executions,0),0,1,executions) rows_per_exec, application_wait_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_app_wait, concurrency_wait_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_con_wait, user_io_wait_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_io_wait from STATS$SQL_SUMMARY sql , stats$snapshot ss where ss.snap_id = sql.snap_id and sql_id = '8dfk0qpzvdcbc' and ss.snap_time > sysdate -2;

******************************************
 keywords: stats pack statspack
******************************************
 rdbms version:
******************************************

Tracing events

Most things that happen in a database is an 'event'. Oracle lets us trace events.

When development / testing teams are receiving an oracle warning and they dont know where the warning is occuring we can trace it.

 Eg: ORA-29261: bad argument

alter system set events '29261 trace name errorstack level 1';

This will produce a trace file every time the event ORA-29261 happens.

----- Error Stack Dump ----- ORA-29261: bad argument ----- Current SQL Statement for this session (sql_id=cz0fcp412ms20) ----- begin DECLARE l_page VARCHAR2(10); BEGIN IF :P203_NEWCLIENT = 'Y' THEN l_page := '207'; ELSE l_page := '205'; END IF; rsv.rsv_user.send_apex_mail_register(p_user_email => :P203_EMAIL_ADDRESS, p_password => :P203_password, p_from_email => :P203_EMAIL_ADDRESS, p_page_nav => l_page, p_request => 'INITIALISE'); END; end;

Turn off the tracing for the event.

alter system set events '29261 trace name context off';

******************************************
keywords: trace tracing events event
******************************************
rdbms version: 12c
******************************************

Solaris process information

Helping to find out what a process on Solaris is doing. Using the prstat command to see.

$ prstat -mL -p 1660 PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG PROCESS/LWP 1660 oracle 99 0.0 0.1 0.0 0.0 0.0 0.0 0.6 3 960 1K 0 oracle/1

USR The percentage of time the process has spent in user mode.
SYS The percentage of time the process has spent in system mode.
TRP The percentage of time the process has spent in processing system traps.
TFL The percentage of time the process has spent processing text page faults.
DFL The percentage of time the process has spent processing data page faults.
LCK The percentage of time the process has spent waiting for user locks.
SLP The percentage of time the process has spent sleeping.
LAT The percentage of time the process has spent waiting for CPU.
VCX The number of voluntary context switches.
ICX The number of involuntary context switches.
SCL The number of system calls.
SIG The number of signals received.

We can run dtrace to see is a process is doing anything

# dtrace -n :::entry'/pid == 1437/{ @syscalls[probefunc] = count(); }' dtrace: description ':::entry' matched 256026 probes

After a few seconds you can control-C the command. If the process if doing nothing, the command will print nothing to the screen. If the process is doing something you will see a count of the providers.

******************************************
keywords: prstat process
******************************************
rdbms version: solaris
******************************************