Oracle Performance Views (V$ and GV$): A Practical Guide for 23c

The dynamic performance views (V$ and cluster-wide GV$) expose what the Oracle instance is doing right now. They are not tables on disk; they surface in-memory state maintained by the kernel. Use them to observe wait events, SQL execution, memory, I/O, and session activity—and to validate changes with real data.

How to approach tuning (modern method)

1. Start with waits: Identify top wait classes/events before tweaking memory or parameters.
2. Find the workload: Which SQL, sessions, or files drive those waits?
3. Change and measure: Apply a fix; then verify with the same views/metrics.

Core views by task

• Waits & time model: V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION, V$ACTIVE_SESSION_HISTORY (ASH), V$SYS_TIME_MODEL, V$EVENTMETRIC
• SQL performance: V$SQL, V$SQLSTATS, V$SQLAREA, V$SQL_PLAN
• Memory (SGA/PGA): V$SGASTAT, V$SGAINFO, V$PGASTAT, V$MEMORY_TARGET_ADVICE
• I/O & files: V$IOSTAT_FILE, V$FILEMETRIC, V$FILESTAT (compat), V$TEMPSEG_USAGE
• Library cache & parsing: V$LIBRARYCACHE, V$DB_OBJECT_CACHE
• Sessions, locks, latches: V$SESSION, V$LOCK, V$LATCH, V$MUTEX_SLEEP

Quick-start queries (drop-in)

Top wait events since instance start

SELECT event,
       total_waits,
       time_waited/100 AS seconds_waited
FROM   v$system_event
WHERE  wait_class <> 'Idle'
ORDER  BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;

Active sessions right now (what they wait on)

SELECT s.sid, s.serial#, s.username, s.module,
       s.state, s.wait_class, s.event, s.seconds_in_wait
FROM   v$session s
WHERE  s.type = 'USER'
ORDER  BY s.state DESC, s.seconds_in_wait DESC;

Top SQL by elapsed time

SELECT sql_id, plan_hash_value, executions,
       elapsed_time/1e6 AS seconds, rows_processed
FROM   v$sql
WHERE  executions > 0
ORDER  BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

Library cache health (hard parse pressure)

SELECT namespace,
       pins, pinhits, (pinhits/pins) AS pin_hit_ratio,
       reloads, invalidations
FROM   v$librarycache
ORDER  BY (reloads + invalidations) DESC;

SGA breakdown & free memory

SELECT pool, name, bytes
FROM   v$sgastat
ORDER  BY pool NULLS LAST, bytes DESC;

PGA efficiency (work areas)

SELECT name, value
FROM   v$pgastat
WHERE  name IN ('total PGA inuse', 'total PGA allocated',
                'bytes processed', 'extra bytes read/written');

Which files are hottest?

SELECT filetype_name,
       small_read_megabytes + large_read_megabytes AS read_mb,
       small_write_megabytes + large_write_megabytes AS write_mb,
       small_read_reqs + large_read_reqs AS read_reqs,
       small_write_reqs + large_write_reqs AS write_reqs
FROM   v$iostat_file
ORDER  BY (read_mb + write_mb) DESC;

Temp usage by session

SELECT s.sid, s.serial#, u.tablespace, u.blocks, u.segtype
FROM   v$session s
JOIN   v$tempseg_usage u ON s.saddr = u.session_addr
ORDER  BY u.blocks DESC;

Dealing with legacy guidance in this lesson

• Buffer hit ratio: can look “great” while users wait on I/O or latches. Use wait analysis first.
• SORT_AREA_SIZE: superseded by workarea_size_policy = AUTO and PGA_AGGREGATE_TARGET. Size PGA for your concurrency and workload; validate with V$PGASTAT and temp usage.
• “Number of disks” heuristics: obsolete; let metrics and AWR baselines guide changes.

Workflow: investigate, fix, verify

1. Identify dominant waits (system/session).
2. Map to specific SQL, objects, or files.
3. Apply focused changes (e.g., indexes, SQL rewrites, memory/parallelism, I/O layout).
4. Re-measure using the same queries; capture before/after evidence.

ASH (Active Session History) and AWR (Automatic Workload Repository)

Key Differences: AWR vs. Statspack

It is important to understand the distinctions between the licensed AWR and the free Statspack: