Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 19
Sessions, waits, plans, and workload time
Connect requests to active sessions, wait events, runtime plans, statement aggregates, and historical workload evidence.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
Database response time divides into CPU execution and waits for resources or coordination. A useful signal preserves how many sessions experienced each state and for how long.
02
Active Session History samples sessions running on CPU or waiting in a database call. Aggregating samples approximates database time by SQL, event, module, object, or plan line.
03
V$SESSION and pg_stat_activity show present session state. Repeated sampling can reconstruct a short history, but transaction age and prior statements require explicit capture.
04
A plan explains operators; actual rows, loops, buffers, spills, and waits explain execution. Statement aggregates hide skew unless parameter and plan identity are retained.
05
SQL text alone cannot distinguish checkout from reconciliation or retries from first attempts. Module, action, application_name, trace identifiers, and query comments connect database work to its caller.
06
Start from an incident interval, quantify database demand, split CPU from waits, rank dimensions, and only then inspect representative statements and blockers.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
Current activity becomes useful history when sampled with timestamps, transaction age, wait classification, SQL identity, and application metadata.
SELECT clock_timestamp(), pid, application_name, query_id, state,
wait_event_type, wait_event, xact_start, query_start
FROM pg_stat_activity WHERE backend_type='client backend';pg_stat_statements aggregates normalized queries. Calls and variance context matter: a high total can be harmless throughput while one spill-heavy call breaks latency.
SELECT queryid, calls, total_exec_time, mean_exec_time, rows,
shared_blks_read, temp_blks_written, wal_bytes
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;ASH samples active sessions. Converting samples to approximate active-session count requires dividing by samples per second and preserving plan line or wait class dimensions.
SELECT sql_id, session_state, wait_class, event, COUNT(*) samples
FROM v$active_session_history
WHERE sample_time >= systimestamp - interval '15' minute
GROUP BY sql_id, session_state, wait_class, event
ORDER BY samples DESC FETCH FIRST 20 ROWS ONLY;Set low-cardinality application identity when a pooled connection is borrowed, then clear or replace it before reuse. This turns database evidence into an application workflow.
-- PostgreSQL
SET application_name = 'checkout/payment';
-- Oracle
BEGIN dbms_application_info.set_module('checkout','payment'); END;
/08
Optional deep dives with the complete experiments and product-version context behind this guide.