Oracle V$ Views Quick Reference - Dynamic Performance Views Guide

Oracle V$ Views Quick Reference

Quick reference guide to Oracle’s dynamic performance views (V$ views) for database monitoring and troubleshooting.

Understanding V$ Views

V$ views are Oracle’s dynamic performance views that provide real-time information about database performance, sessions, memory, and more.

-- List all V$ views
SELECT VIEW_NAME FROM V$FIXED_VIEW_DEFINITION
WHERE VIEW_NAME LIKE 'V$%'
ORDER BY VIEW_NAME;

-- View underlying base tables
SELECT VIEW_NAME, VIEW_DEFINITION
FROM V$FIXED_VIEW_DEFINITION
WHERE VIEW_NAME = 'V$SESSION';

Session & Process Views

V$SESSION

Current session information

-- Active sessions
SELECT SID, SERIAL#, USERNAME, STATUS, SQL_ID, EVENT, WAIT_CLASS
FROM V$SESSION
WHERE USERNAME IS NOT NULL AND STATUS = 'ACTIVE';

-- Key columns: SID, SERIAL#, USERNAME, STATUS, MACHINE, PROGRAM, SQL_ID, EVENT, BLOCKING_SESSION

V$PROCESS

OS process information

-- Session to OS process mapping
SELECT s.SID, s.SERIAL#, s.USERNAME, p.SPID AS os_pid, p.PGA_USED_MEM
FROM V$SESSION s
JOIN V$PROCESS p ON s.PADDR = p.ADDR
WHERE s.USERNAME IS NOT NULL;

V$ACTIVE_SESSION_HISTORY (ASH)

Recent session activity (sampled every second)

-- Top SQL in last hour
SELECT SQL_ID, COUNT(*) AS samples
FROM V$ACTIVE_SESSION_HISTORY
WHERE SAMPLE_TIME > SYSDATE - 1/24
GROUP BY SQL_ID
ORDER BY COUNT(*) DESC
FETCH FIRST 10 ROWS ONLY;

SQL & Cursor Views

V$SQL

SQL statements in shared pool

-- Top SQL by elapsed time
SELECT SQL_ID, EXECUTIONS, ELAPSED_TIME/1000000 AS elapsed_sec,
       BUFFER_GETS, DISK_READS, SQL_TEXT
FROM V$SQL
ORDER BY ELAPSED_TIME DESC
FETCH FIRST 10 ROWS ONLY;

V$SQLAREA

Aggregated SQL statistics

-- SQL with high buffer gets
SELECT SQL_ID, EXECUTIONS, BUFFER_GETS, BUFFER_GETS/EXECUTIONS AS gets_per_exec
FROM V$SQLAREA
WHERE EXECUTIONS > 0
ORDER BY BUFFER_GETS DESC
FETCH FIRST 10 ROWS ONLY;

V$SQLTEXT

Full SQL text (64-char pieces)

-- Reconstruct full SQL
SELECT SQL_TEXT FROM V$SQLTEXT
WHERE SQL_ID = '&sql_id'
ORDER BY PIECE;

V$SQL_PLAN

Execution plans in cache

SELECT PLAN_TABLE_OUTPUT FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST')
);

Memory Views

V$SGA

System Global Area summary

SELECT NAME, VALUE/1024/1024 AS size_mb FROM V$SGA;

V$SGAINFO

Detailed SGA information

SELECT NAME, BYTES/1024/1024 AS mb, RESIZEABLE FROM V$SGAINFO;

V$BUFFER_POOL

Buffer cache pools

SELECT NAME, BLOCK_SIZE, CURRENT_SIZE/1024/1024 AS mb, BUFFERS
FROM V$BUFFER_POOL;

V$PGASTAT

PGA memory statistics

SELECT NAME, VALUE/1024/1024 AS mb FROM V$PGASTAT
WHERE NAME LIKE '%memory%';

V$SHARED_POOL_ADVICE

Shared pool sizing advice

SELECT SHARED_POOL_SIZE_FOR_ESTIMATE AS mb,
       ESTD_LC_TIME_SAVED_FACTOR AS time_saved_factor
FROM V$SHARED_POOL_ADVICE;

I/O Views

V$DATAFILE

Datafile information

SELECT FILE#, NAME, STATUS, BYTES/1024/1024 AS size_mb
FROM V$DATAFILE;

V$FILESTAT

Datafile I/O statistics

SELECT
  d.NAME,
  f.PHYRDS AS reads,
  f.PHYWRTS AS writes,
  f.AVGIOTIM/100 AS avg_io_sec
FROM V$FILESTAT f
JOIN V$DATAFILE d ON f.FILE# = d.FILE#
ORDER BY f.PHYRDS + f.PHYWRTS DESC;

V$TEMPFILE

Temporary file information

SELECT FILE#, NAME, STATUS, BYTES/1024/1024 AS size_mb FROM V$TEMPFILE;

V$LOG

Redo log groups

SELECT GROUP#, SEQUENCE#, BYTES/1024/1024 AS mb, MEMBERS, STATUS, ARCHIVED
FROM V$LOG;

V$LOGFILE

Redo log file members

SELECT GROUP#, MEMBER, STATUS FROM V$LOGFILE;

Performance Views

V$SYSSTAT

System statistics

SELECT NAME, VALUE FROM V$SYSSTAT
WHERE NAME IN ('db block gets', 'consistent gets', 'physical reads');

V$SYSTEM_EVENT

System-wide wait events

SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO/1000000 AS secs, WAIT_CLASS
FROM V$SYSTEM_EVENT
WHERE WAIT_CLASS != 'Idle'
ORDER BY TIME_WAITED_MICRO DESC
FETCH FIRST 10 ROWS ONLY;

V$SESSION_EVENT

Session wait events

SELECT EVENT, TOTAL_WAITS, TIME_WAITED/100 AS secs
FROM V$SESSION_EVENT
WHERE SID = &sid
ORDER BY TIME_WAITED DESC;

V$LATCH

Latch statistics

SELECT NAME, GETS, MISSES, SLEEPS, SPIN_GETS
FROM V$LATCH
ORDER BY MISSES DESC
FETCH FIRST 10 ROWS ONLY;

V$LIBRARYCACHE

Library cache performance

SELECT NAMESPACE, GETS, GETHITRATIO, PINS, PINHITRATIO, INVALIDATIONS
FROM V$LIBRARYCACHE;

Lock Views

V$LOCK

Current locks

-- Session locks
SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
FROM V$LOCK
WHERE TYPE IN ('TX', 'TM');

V$LOCKED_OBJECT

Locked objects

SELECT
  l.SESSION_ID,
  l.ORACLE_USERNAME,
  o.OWNER,
  o.OBJECT_NAME,
  o.OBJECT_TYPE
FROM V$LOCKED_OBJECT l
JOIN DBA_OBJECTS o ON l.OBJECT_ID = o.OBJECT_ID;

V$TRANSACTION

Active transactions

SELECT
  s.SID,
  s.SERIAL#,
  t.USED_UBLK AS undo_blocks,
  t.USED_UREC AS undo_records
FROM V$TRANSACTION t
JOIN V$SESSION s ON t.ADDR = s.TADDR;

Database Information Views

V$DATABASE

Database information

SELECT DBID, NAME, CREATED, OPEN_MODE, LOG_MODE, FLASHBACK_ON
FROM V$DATABASE;

V$INSTANCE

Instance information

SELECT INSTANCE_NUMBER, INSTANCE_NAME, HOST_NAME, VERSION, STATUS, STARTUP_TIME
FROM V$INSTANCE;

V$VERSION

Version information

SELECT * FROM V$VERSION;

V$PARAMETER

Initialization parameters

SELECT NAME, VALUE, ISDEFAULT, ISMODIFIED FROM V$PARAMETER
WHERE NAME = 'sga_target';

V$SPPARAMETER

SPFILE parameters

SELECT NAME, VALUE, ISSPECIFIED FROM V$SPPARAMETER
WHERE VALUE IS NOT NULL;

Archive & Recovery Views

V$ARCHIVED_LOG

Archived redo logs

SELECT SEQUENCE#, NAME, FIRST_TIME, NEXT_TIME, DELETED, STATUS
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME > SYSDATE - 1
ORDER BY SEQUENCE#;

V$BACKUP

Datafile backup status

SELECT FILE#, STATUS, CHANGE# FROM V$BACKUP;

V$RECOVER_FILE

Files needing recovery

SELECT FILE#, ERROR FROM V$RECOVER_FILE;

V$RECOVERY_AREA_USAGE

Flash recovery area usage

SELECT FILE_TYPE, PERCENT_SPACE_USED, NUMBER_OF_FILES
FROM V$RECOVERY_AREA_USAGE;

RAC Views (GV$)

-- GV$ views show all instances
SELECT INST_ID, SID, SERIAL#, USERNAME, STATUS
FROM GV$SESSION
WHERE USERNAME IS NOT NULL;

-- Key RAC views:
-- GV$INSTANCE, GV$SESSION, GV$SQL, GV$LOCK
-- V$ACTIVE_INSTANCES, V$CLUSTER_INTERCONNECTS

Most Essential V$ Views Summary

View	Purpose
V$SESSION	Current sessions
V$PROCESS	OS processes
V$SQL	Cached SQL
V$SYSSTAT	System statistics
V$SYSTEM_EVENT	Wait events
V$LOCK	Locks
V$DATABASE	Database info
V$INSTANCE	Instance info
V$PARAMETER	Parameters
V$SGA	Memory usage