What is the Oracle GV$ View? Differences from V$ Views and Utilization in RAC Environments

In the operations management and performance analysis of Oracle Database, leveraging dynamic performance views such as V$SESSION is indispensable. However, in an Oracle RAC environment composed of multiple instances, simply viewing information from the connected node does not provide an accurate assessment of the entire system status.

This article provides an easy-to-understand explanation for beginners to intermediate Oracle engineers regarding GV$ views (Global Dynamic Performance Views), which enable centralized management of multi-instance information. It covers their critical differences from individual instance V$ views, internal structures, and practical SQL execution examples that can be used immediately in production.

1. Conclusion / Shortest Steps (Checklist for Grasping Cluster-Wide Status)

In an Oracle Real Application Clusters (Oracle RAC) environment, the shortest procedure to collectively check and analyze the operational status and session information of all instances is as follows:

• Verify System Privileges: Connect to the database using a user that possesses read privileges for dynamic performance views, such as the SYSDBA administrative privilege or the SELECT_CATALOG_ROLE role.
• Confirm Connection Context: In a multitenant environment (CDB/PDB), connect to the CDB root (CDB$ROOT) as a general rule.
• Utilize the INST_ID Column: Always include INST_ID (Instance ID) in the SELECT or WHERE clauses of your SQL statements to identify which node the extracted data belongs to.
• Filter Specific Instances: If necessary, specify conditions such as WHERE inst_id = 1 to narrow down the target nodes for investigation.

2. Background and Fundamentals: What is a GV$ View?

Definition of GV$ Views

A GV$ view (Global V$ View) is a special view in an Oracle RAC environment that allows you to collectively retrieve dynamic performance information from all instances comprising the cluster.

Critical Differences from V$ Views

A standard V$ view can only reference internal information of the instance to which the current session is connected. In contrast, a GV$ view aggregates information equivalent to the V$ views of all instances and provides them bundled together as a single global view.

One-Minute Note for Beginners

You might think, “Isn’t it the same if I log into each instance sequentially and check the V$ views?” However, doing so makes it difficult to correlate transient lock contentions or high-load SQL queries in real time. Because GV$ views utilize the database’s internal parallel query mechanism to collect information simultaneously from all nodes, you can capture an accurate, lag-free overview of the entire system at once.

3. Structure of GV$ Views (Text-Based Diagram)

Internally, a GV$ view aggregates individual dynamic performance information existing on each instance. It features a structure where an INST_ID (Instance Identifier) column is appended to the common columns.

 ┌──────────────┐     ┌──────────────┐     ┌──────────────┐
 │  Instance 1  │     │  Instance 2  │ ... │  Instance N  │
 │ (v$session)  │     │ (v$session)  │     │ (v$session)  │
 └──────┬───────┘     └──────┬───────┘     └──────┬───────┘
        │                    │                    │
        ▼                    ▼                    ▼
 ┌────────────────────────────────────────────────────────┐
 │                 gv$session (Unified View)              │
 │  * The INST_ID column is automatically added           │
 │    to enable node identification.                      │
 └────────────────────────────────────────────────────────┘

Comparison with Viewing V$ Views on Each Instance Individually

Evaluation Item	Referencing V$ Views Individually on Each Instance	Referencing GV$ Views Collectively
Access Method	Requires connecting and disconnecting individually for each instance.	Can access all nodes with a single connection and a single SQL statement.
Data Concurrency	Time lags occur in retrieval timing (Inaccurate).	Simultaneously aggregates the latest information from across the entire cluster (Accurate).
Aggregation Work	Manual merging using spreadsheet software, etc., is required.	Automatic aggregation via SQL is possible using GROUP BY or ORDER BY.
Instance Identification	The INSTANCE_ID is not included in the output result itself.	Identifiable at a glance via the INST_ID column.

4. Procedure / Implementation: Steps for Utilizing GV$ Views

These are the steps to safely and effectively utilize GV$ views in real-world operations.

Prerequisites (Verifying Privileges and Environment)

To reference dynamic performance views, log in with a user possessing sufficient privileges (SYS, SYSTEM, or a user granted monitoring roles).

Switching Connection Context (For Multitenant Environments)

When employing a CDB/PDB configuration, querying a GV$ view while connected to a PDB will, as a general rule, target only information from “instances where that PDB is open.” To gain an overview of the entire physical structure or all sessions across the cluster, ensure you connect to the CDB root.

Constructing the SQL Statement

To make information easily identifiable, always specify INST_ID at the beginning of the output columns (SELECT clause).

5. Execution Examples: Frequently Used GV$ Views and SQL Snippets

These are concrete SQL execution examples that can be used as-is for troubleshooting on actual machines or during routine monitoring.

Prerequisites

• Target Version: Oracle Database 19c (CDB/PDB configuration)
• Execution Schema: SYS (Connected with SYSDBA privileges)
• Target OS: Oracle Linux 7.9 / 8.x (RAC configuration)
• Note: To prevent environment-dependent issues, multi-byte characters (such as Japanese comments) are omitted from the SQL statements.

① Retrieving the Instance List and Operational Status

Displays a list of the names, hostnames, and startup statuses of all instances comprising the cluster.

Execute the following SQL statement to check the basic operational status of the entire cluster:

SELECT inst_id, instance_name, host_name, version, status
FROM gv$instance
ORDER BY inst_id;

SQL Intent and Results:

This SQL retrieves the basic status of all nodes sorted by instance ID. You can instantly determine whether a specific node is down or remaining in a MOUNTED state.

② Checking Active Sessions Across All Instances

Extracts user sessions that are currently consuming resources and executing processing (ACTIVE) across the entire cluster. Background processes (BACKGROUND) are excluded, focusing solely on general users.

Execute the following SQL statement to identify active sessions currently putting a load on the system:

SELECT inst_id, sid, serial#, username, status, program
FROM gv$session
WHERE username IS NOT NULL
  AND status = 'ACTIVE'
ORDER BY inst_id, sid;

SQL Intent and Results:

You can verify the distribution of application sessions running across nodes. This is helpful for detecting load balancing imbalances, such as active sessions concentrating on one specific node.

③ Detecting Cluster-Wide Lock Contention (Wait States)

Detects sessions blocking other sessions’ processing (block = 1) or sessions waiting to acquire a lock (request > 0) across the entire cluster.

Execute the following SQL statement to investigate row lock or table lock contentions occurring between multiple instances:

SELECT inst_id, sid, type, lmode, request, block
FROM gv$lock
WHERE block = 1 OR request > 0
ORDER BY inst_id, sid;

SQL Intent and Results:

In a RAC environment, a session on Instance 1 can block a session on Instance 2 (Global Lock Blocking). This SQL allows you to pinpoint cross-node deadlocks or prolonged lock waits.

6. Troubleshooting

These are representative errors likely to occur when querying GV$ views, along with their causes and workarounds. Because these operations do not involve updating or deleting data, investigations can be conducted while minimizing system impact.

ORA Error Code	Cause of Occurrence	Verification Method & Workaround Steps
ORA-00942: table or view does not exist	The executing user lacks read privileges for the GV$ view.	Execute GRANT SELECT ON gv_$session TO username; as the SYS user or similar to grant privileges (Note that an underscore is included in the view name).
ORA-12850: Could not allocate slaves on all specified instances	A specific node is hung up, or there is an issue with the network (Interconnect), preventing information from being gathered from other nodes.	First, query the V$ view on the local node to check if it functions standalone. Afterwards, verify the communication status between nodes using the Oracle Clusterware status command (crsctl stat res -t), etc.

7. Operations, Monitoring, and Security Considerations

Advantages and Operational Pitfalls

• Advantage: Even without a centralized management console, you can grasp the health status of the entire cluster simply by executing a single SQL statement from any node.
• Performance Consideration (Pitfall): Internally, GV$ views issue parallel queries to all instances to scrape and gather data. For this reason, if you heavily execute GV$ views at extremely short intervals during high system load (such as when Interconnect bandwidth is constrained), the monitoring processing itself can worsen the database load.
• Workaround / Remediation: If the failure has been narrowed down to a specific node, instead of using GV$ views, switch to either filtering the conditions or connecting directly to that node to reference standard V$ views.

Behavior in Single-Instance (Standalone) Environments

Even in a single-instance environment not configured with RAC, GV$ views can be used without any issues. In this case, internal cross-node communication does not occur, and the INST_ID column of the retrievable data will all contain 1.

For systems where migration to a RAC configuration or scale-out is planned for the future, adopting GV$ views in operational scripts (such as monitoring shells) from the beginning can reduce modifications during migration.

8. FAQ: Frequently Asked Questions

Q. What kind of mechanism operates behind the scenes when querying a GV$ view?

A. When you access a GV$ view, Oracle Database internally uses the parallel query mechanism. The node that executed the query acts as the coordinator, requesting data from all other active instances via special background processes, aggregating them at the local node, and returning the results to the user.

Q. Where can I find the base tables or original definitions corresponding to GV$ views?

A. The reality of a GV$ view is an in-memory structure called a fixed table (X$ table). For example, GV$SESSION is constructed based on fixed tables such as X$KSUSE. The concrete definition of a view can be verified by querying the V$FIXED_VIEW_DEFINITION view.

Q. Are there any points to consider when granting read privileges for GV$ views to general users?

A. When granting privileges for dynamic performance views, caution is required regarding the base object name. Describing it as GRANT SELECT ON gv$session TO username; will result in an error. Specify gv_$session (the object with an underscore after the ‘v’), which is the source of the synonym, and execute it like GRANT SELECT ON gv_$session TO username;.

Q. If I view gv$session while connected to a PDB (Pluggable Database), will sessions from other PDBs also be visible?

A. No. When you connect to a PDB environment as a local user or similar and reference a GV$ view, the data is automatically filtered to information internal to that PDB only (however, instances across all nodes remain targeted). If you wish to gain an overview of the entire container (all sessions including other PDBs), connect to the CDB root (CDB$ROOT) using a common user before executing the SQL.

9. Summary: Key Points Checklist

• GV$ views (Global Dynamic Performance Views) are a mechanism in Oracle RAC environments to centrally retrieve dynamic information from all instances.
• The greatest difference from standard V$ views is that they retain the INST_ID column, which indicates which instance the data belongs to.
• Because they leverage parallel queries internally to aggregate information in real time from all nodes, lag-free, accurate cross-node analysis (such as investigating lock contention spanning across nodes) is possible.
• When executed in a single-instance (standalone) environment, they operate without causing an error, returning INST_ID = 1.
• Due to the characteristic of gathering information from all nodes, frequently firing queries repeatedly during ultra-high system load causes overhead on the Interconnect (inter-node communication); thus, consideration regarding monitoring frequency is necessary.

This article targets Oracle Database 19c for its explanations (other versions may differ in screens or default values).

[reference]
Introduction to Oracle RAC

Oracle 26ai RAC Setup Procedure ① 【GI・Grid Infrastructure】

Do you want to build the latest version of Oracle Database, Oracle 26ai Real Application Clusters (Oracle RAC), but find…

oracle-master00000.com

2026.02.14