Lesson 1

In the previous module, you examined tuning with Oracle data structures (object-relational types and collections). This module shifts focus to a different tuning skill: using the Oracle Data Dictionary and dynamic performance views to monitor workload behavior and diagnose performance problems.

This concept remains essential in Oracle Database 23ai. Even with modern automation and “self-tuning” capabilities, Oracle’s monitoring stack still relies on the same foundation: metadata in catalog views and real-time metrics exposed through dynamic performance views. These views are the raw material behind dashboards and tooling (Performance Hub, SQL Monitor, AWR/ASH reports, and Enterprise Manager style alerting).

Two View Families Power Most Performance Monitoring

1. Dynamic Performance Views (V$ and GV$)

   The V$ views expose current, in-memory instance activity: sessions, SQL execution statistics, wait events, I/O, latch activity, and many other runtime signals. In RAC environments, GV$ provides the same information across all instances.

   Examples you will use throughout performance troubleshooting include: V$SESSION (session activity), V$SQLSTATS (high-resource SQL), and V$SYSTEM_EVENT (system wait profile).

2. Static Catalog Views (USER_, ALL_, DBA_)

   The catalog views describe the database structure: schema objects, storage allocation, indexes, privileges, and other metadata. While they are not “real-time performance counters,” they are critical for performance scripts because they explain why a database behaves a certain way (for example, missing indexes, tablespace pressure, or object growth).

   Oracle documents this as three common privilege scopes: USER_ (objects you own), ALL_ (objects you can access), and DBA_ (database-wide).

Why This Matters for Oracle 23ai

In Oracle 23ai, modern monitoring features still consume these same sources. For example, AWR collects and stores performance statistics for problem detection and self-tuning analysis. You may also encounter newer data modeling capabilities (such as JSON-relational duality views), but your monitoring baseline remains the data dictionary plus the dynamic performance views.

Module Objectives

By the time you complete this module, you should be able to:

1. Use Oracle performance views for monitoring (V$ and related views)
2. Write an SGA snapshot routine that queries dynamic performance views
3. Build a quick, effective alert monitor using dictionary-driven thresholds

Starter Examples You Will Expand Later

The following examples illustrate the style of queries you will build during this module. These are intentionally simple; later lessons will add filtering, baselines, and alert thresholds.

-- Active sessions (high-level)
SELECT username, status, machine, program, sql_id
FROM v$session
WHERE status = 'ACTIVE';

-- Top SQL by elapsed time (example columns vary by view)
SELECT sql_id, executions, elapsed_time, cpu_time, buffer_gets, disk_reads
FROM v$sqlstats
ORDER BY elapsed_time DESC;

-- System wait events profile
SELECT event, total_waits, time_waited
FROM v$system_event
ORDER BY time_waited DESC;

The next lesson introduces Oracle’s core toolset for viewing runtime events and workload characteristics and then shows how to turn those views into repeatable monitoring scripts.

With some exceptions, data dictionary and catalog views follow three common prefixes:

1. USER_
2. ALL_
3. DBA_

USER_ views show objects owned by the current schema. ALL_ views expand that scope to include objects you can access through privileges. DBA_ views provide database-wide visibility and typically require elevated privileges.

In keeping with the user focus of this module, the emphasis will be on USER_ views and other views accessible to non-DBA users, with ALL_ and DBA_ views introduced where appropriate.