Dynamic Performance Views for Sessions, SQL, Locks, I/O, Memory, and Instance Health

Learning outcomes

ServiceHub users report “the database is slow right now.” Before reaching for historical reports, the DBA needs to answer immediate questions: Which sessions are active? Which SQL are they running? Is one session blocking another? Are reads/writes increasing? Is memory pressure visible? Are process/session limits near their configured ceilings? Oracle's dynamic performance views—the V$/GV$ family—expose live instance state and cumulative counters.

1. Dynamic views answer “what is true now?”

V$SESSION exposes session identity/state, V$SQL exposes cursor-level SQL statistics, V$SYSTEM_EVENT/V$SESSION_EVENT expose cumulative waits, and specialized views expose locks, files, memory and configured resource utilization. Most values are memory-derived and reset/recycle with instance restart, session end, cursor aging/reload or object lifecycle.

SELECT  SYS_CONTEXT('USERENV','CON_NAME') AS con_name,  SYS_CONTEXT('USERENV','CON_ID') AS con_id,  SYS_CONTEXT('USERENV','INSTANCE_NAME') AS instance_nameFROM dual;SELECT instance_name,startup_time,statusFROM v$instance;

2. Active sessions: status, event, SQL and application identity

SELECT  sid,  serial#,  username,  status,  service_name,  module,  action,  client_identifier,  sql_id,  event,  wait_class,  seconds_in_waitFROM v$sessionWHERE type='USER'ORDER BY status DESC,sid;

ACTIVE means the session is executing a database call—not necessarily consuming CPU. An active session can be waiting on I/O, a lock, network or another resource. INACTIVE can be healthy connection-pool idleness.

3. Join the SQL ID to cursor statistics

SELECT  sql_id,  child_number,  plan_hash_value,  executions,  rows_processed,  buffer_gets,  disk_reads,  cpu_time,  elapsed_time,  parse_calls,  substr(sql_text,1,120) AS sql_textFROM v$sqlWHERE sql_id=:sql_idORDER BY child_number;

These are cumulative cursor statistics. A high lifetime BUFFER_GETS can simply mean a very frequently executed healthy SQL statement. Normalize by executions and take interval deltas before calling it the current incident cause.

4. Reproducible blocking lab

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh22_lock_case PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh22_lock_case (  case_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL);INSERT INTO sh22_lock_case VALUES(1,'OPEN',100);INSERT INTO sh22_lock_case VALUES(2,'OPEN',200);COMMIT;

BEGIN  DBMS_APPLICATION_INFO.SET_MODULE('SH22_LOCK_LAB','HOLDER');END;/UPDATE sh22_lock_caseSET amount=amount+10WHERE case_id=1;-- Do NOT commit yet.

BEGIN  DBMS_APPLICATION_INFO.SET_MODULE('SH22_LOCK_LAB','WAITER');END;/UPDATE sh22_lock_caseSET status_code='HOLD'WHERE case_id=1;-- This call waits until Session A commits/rolls back or a timeout/interrupt occurs.

5. Diagnose the blocker from a third/admin session

SELECT  sid,  serial#,  module,  action,  status,  event,  wait_class,  seconds_in_wait,  blocking_instance,  blocking_session,  final_blocking_session,  sql_idFROM v$sessionWHERE module='SH22_LOCK_LAB'ORDER BY sid;

The waiter should show an enqueue event such as enq: TX - row lock contention and identify Session A as the blocker. The blocker may show INACTIVE while holding an open transaction—important proof that inactive does not mean harmless.

SELECT  sid,  type,  id1,  id2,  lmode,  request,  block,  ctimeFROM v$lockWHERE sid IN (  SELECT sid FROM v$session WHERE module='SH22_LOCK_LAB')ORDER BY type,sid;

The TX enqueue rows expose holder/requester state. They do not tell you whether the business transaction should be committed or rolled back; application/owner context decides the safe repair.

6. Safe repair: end the correct transaction

ROLLBACK;-- Session B can now proceed with its UPDATE.

ROLLBACK;BEGIN  DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);END;/

Do not kill a blocker as the first reaction. A forced session kill can trigger rollback of a large transaction and prolong the incident while obscuring the business owner of the work.

7. I/O, memory and process/session headroom

SELECT  d.name AS datafile,  f.phyrds,  f.phyblkrd,  f.phywrts,  f.phyblkwrt,  f.readtim,  f.writetimFROM v$filestat fJOIN v$datafile d  ON d.file#=f.file#ORDER BY f.phyrds+f.phywrts DESC;

SELECT name,bytesFROM v$sgainfoORDER BY bytes DESC NULLS LAST;SELECT name,value,unitFROM v$pgastatWHERE name IN (  'total PGA allocated',  'maximum PGA allocated',  'total PGA inuse',  'over allocation count',  'cache hit percentage')ORDER BY name;

SELECT  resource_name,  current_utilization,  max_utilization,  initial_allocation,  limit_valueFROM v$resource_limitWHERE resource_name IN (  'processes',  'sessions',  'transactions')ORDER BY resource_name;

V$FILESTAT and memory/resource views are cumulative/state evidence. A large number is not automatically a bottleneck. Snapshot them at T1/T2 and correlate deltas with workload volume and OS/storage evidence.

8. Deliberately wrong: sort V$SYSTEM_EVENT by TIME_WAITED and declare the top row the root cause

Lifetime cumulative waits include yesterday's backups, startup work and normal workload. A top wait can be irrelevant to the current five-minute incident. The repair is the core diagnostic discipline of this chapter: capture bounded intervals, classify idle/non-idle waits, correlate with throughput and current sessions, and only then formulate a hypothesis.

9. Cleanup

DROP TABLE sh22_lock_case PURGE;

10. Production judgment

Start incidents with cheap live evidence. Restrict catalog access: application users should not receive broad SELECT ANY DICTIONARY merely for monitoring; use a dedicated diagnostic role with narrowly required V_$... grants or an approved catalog role/tool. Preserve timestamps and query output externally because live state disappears.

No optional pack, restart or COMPATIBLE change is required for the core views used here. In RAC use GV$ plus INST_ID; in a PDB understand which rows/counters are container-filtered and use CON_ID where supplied. Lesson 2 converts cumulative views into bounded wait/time-model deltas.

Authoritative references

• Dynamic Performance Views — V$/GV$ live-state model
• V$SESSION — session/wait/blocker fields
• V$SQL — cursor cumulative statistics
• V$LOCK — enqueue/lock state
• V$RESOURCE_LIMIT — resource utilization/headroom