To monitor the operational status of Oracle, we utilize dynamic performance views that disclose memory-based statistical information in real time.
Required Privileges and Environment
To query the views below, you must have SYSDBA privileges, or either the SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE role. In a multitenant environment (CDB/PDB), connect to the target pluggable database (PDB) and execute the SQL. The SQL commands from the original article are used directly in this explanation.
- 1. How to Read v$session
- 2. How to Read v$session_event
- 3. How to Read v$system_event
- 4. Supplementary Note on v$session_wait
- 5. Notes on RAC (Real Application Clusters) Environments: Utilizing gv$ Views
- [Practical Example] Verification Steps: Generating Lock Contention to Verify Wait Events
- Operations, Monitoring, and Security Notes
- FAQ: Frequently Asked Questions
- Summary: 4 Golden Rules of Wait Event Analysis
1. How to Read v$session
You can check the real-time status (ACTIVE/INACTIVE) of each current session and the specific wait event it is encountering right now.
SELECT sid, serial#, status, event, wait_class, state
FROM v$session
WHERE username IS NOT NULL;
SQL Intent and Result:
This retrieves a list of general user sessions, excluding background processes. You can identify the wait event name (event) that each session (sid) is currently waiting on, its broad category (wait_class), and the details of the wait state (state).
2. How to Read v$session_event
This view maintains cumulative statistics on how many times and on which events currently connected sessions have been forced to wait from the past up to the present moment.
SELECT sid, event, total_waits, time_waited
FROM v$session_event
WHERE event NOT LIKE 'SQL*Net%';
SQL Intent and Result:
By filtering out SQL*Net% (which indicates idle events waiting for communication with the client), this lists the cumulative number of waits (total_waits) and cumulative wait time (time_waited) where each session encountered bottlenecks during practical processing.
3. How to Read v$system_event
This view stores cumulative statistical information for all wait events that have occurred across the entire instance since the database was started.
SELECT event, total_waits, time_waited, average_wait
FROM v$system_event
WHERE event NOT LIKE 'SQL*Net%';
SQL Intent and Result:
This identifies comprehensive, system-wide bottlenecks. By pinpointing events with abnormally long average wait times (average_wait), you can macroscopically evaluate storage I/O performance deficiencies or overall flaws in application design.
4. Supplementary Note on v$session_wait
-- (Reference: For compatibility with older environments of Oracle 10g or earlier)
-- Since Oracle 10g, identical columns have been integrated into v$session, so v$session is typically sufficient.
5. Notes on RAC (Real Application Clusters) Environments: Utilizing gv$ Views
In a RAC environment, multiple database instances run simultaneously. To run a cross-sectional search across the entire cluster instead of just a single specific instance, use the global dynamic performance views (gv$ views).
SELECT inst_id, sid, serial#, event, wait_class, state
FROM gv$session
WHERE username IS NOT NULL;
SQL Intent and Result:
This extracts sessions across all instances in the cluster. By checking the inst_id (instance ID) in the output results, you can immediately identify which node (server) the load or wait events are concentrated on.
[Practical Example] Verification Steps: Generating Lock Contention to Verify Wait Events
To deeply understand the behavior of wait events, these steps deliberately cause “row lock (TX lock) conflicts” on an actual machine to verify what data is recorded in the dynamic performance views.
1. Creating a Test Table
First, prepare a test table and seed data in a clean validation environment.
CREATE TABLE lock_test (
id NUMBER PRIMARY KEY,
name VARCHAR2(50)
);
INSERT INTO lock_test VALUES (1, 'taro');
COMMIT;
2. Updating in Session A to Hold a Lock
Open Terminal 1 (Session A) using a command-line tool (such as SQL*Plus) and execute the following UPDATE statement. Note: Do not execute a COMMIT or ROLLBACK yet.
UPDATE lock_test SET name = 'jiro' WHERE id = 1;
Screen Output Result for Session A:
SQL> UPDATE lock_test SET name = 'jiro' WHERE id = 1; ★Execute UPDATE (do not commit)
1 row updated.
SQL>
Current State: Session A has acquired an “exclusive lock (row lock)” on the row where id = 1, and remains in that state while the transaction continues.
3. Updating the Same Row in Session B to Force a Lock Wait
Open a new command-line tool window in a separate terminal and connect to the database as Terminal 2 (Session B). Attempt to update the row where id = 1 to a different value while Session A still holds the lock.
UPDATE lock_test SET name = 'saburo' WHERE id = 1;
Screen Output Result for Session B:
SQL> UPDATE lock_test SET name = 'saburo' WHERE id = 1; ★The command does not complete and waits
Current State: Because the preceding Session A has not issued a commit, Session B cannot obtain the right to update the row. Processing is fully blocked internally as it waits for the lock to be released.
4. Verifying Wait Events for Session B (Monitoring Session)
Open yet another terminal, Terminal 3 (a separate session for monitoring), and query the v$session view to check the status under which Session B is blocked. (Change 'Target Username' in the search criteria to the appropriate schema name, such as 'USER1', based on your environment.)
SELECT sid, event, wait_class, state
FROM v$session
WHERE username = 'Target Username';
Monitoring Result Output Sample:
SQL> SELECT sid, event, wait_class, state
2 FROM v$session
3 WHERE username = 'USER1';
SID EVENT WAIT_CLASS STATE
---------- ------------------------------ ------------------------- --------------------
62 SQL*Net message from client Idle WAITING
82 enq: TX - row lock contention Application WAITING ★
Explanation of Analysis Results
SID = 62 (Session A side) is in an idle state (SQL*Net message from client), simply waiting for the next input command from the client.
Meanwhile, SID = 82 (Session B side) displays the wait event enq: TX - row lock contention, the wait class Application, and the state WAITING. This captures clear data proving that processing is entirely blocked by a row lock conflict originating from the transaction design of the application.
Post-Processing Execution Steps: After completing the verification, execute COMMIT; or ROLLBACK; on the Terminal 1 (Session A) side. The moment the lock is released, the processing in Terminal 2 (Session B) will complete immediately. (After it completes, perform a COMMIT; on the Session B side as well to finish.)
Operations, Monitoring, and Security Notes
Safe Countermeasure Approach During Lock Investigations (Prioritizing Querying Views)
If you discover severe lock contention like enq: TX - row lock contention in an actual production environment, you can forcibly release the subsequent waiting processes by terminating the preceding session causing the issue (such as a session that holds a lock and has stopped responding) using commands like ALTER SYSTEM KILL SESSION.
However, before performing a termination, always use query-only views like v$lock or v$blocking_session to pinpoint the exact parent-child relationship (lock tree) showing “which session (SID) is blocking which session.” Forcibly terminating an important online process or batch session by mistake risks causing secondary damage, such as database overhead associated with transaction rollbacks or data inconsistencies.
Excluding “Idle Events” from Statistical Interpretation
When analyzing views like v$system_event, idle events like SQL*Net message from client or rdbms ipc message (events indicating that the database has no work to do and is simply waiting for requests or instructions from users) will with high probability occupy the top slots for cumulative time. Because these are not system bottlenecks, they must be excluded from tuning evaluations. You must focus your monitoring on numerical values from wait classes that involve actual work, such as User I/O or Concurrency.
FAQ: Frequently Asked Questions
Q1. db file sequential read events are occurring frequently. Do I need to upgrade disk performance?
A1. No, upgrading hardware is not necessarily the correct answer. This event indicates a delay in index-based access (single-block reads), but the root cause is often that “index selectivity is poor, resulting in inefficiently reading a large number of rows block-by-block.” Your first step should be to check the execution plan of the target SQL, review and optimize the index design, or optimize the sizes of the SHARED_POOL and BUFFER CACHE.
Q2. Are wait event times in milliseconds or seconds?
A2. The units for the time_waited column in dynamic performance views vary depending on the version and view specifications, but in principle, they are often maintained in units of hundredths of a second (centiseconds). However, columns in views like v$session_history (which allow more detailed time measurement) or specific related views in recent versions record data in microseconds (such as time_waited_micro). Check the definitions of individual columns when performing accurate evaluations.
Q3. If I use v$session in a RAC environment, will sessions on other nodes be invisible?
A3. Yes, they will be invisible. v$session displays session information only within the specific instance you are connected to. If you want to check the full volume of activity, including behaviors on other instances comprising the cluster, always query using gv$session (which begins with a ‘G’), as shown in the script examples in this article.
Summary: 4 Golden Rules of Wait Event Analysis
- Verbalize and Quantify System “Slowness” Using Event Names (Do Not Rely on Guesswork).
- Choose the Appropriate View Based on the Situation (
v$sessionfor Real-Time,v$system_eventfor Overall Trends). - Always Obtain a Bird’s-Eye View of the Entire Cluster Using
gv$Views in RAC Environments. - Leverage Deliberate Replication Tests to Understand View Behavior Prior to Actual Issues.
This article is explained based on Oracle Database 19c (screens or default values may vary in other versions).
[reference]
Descriptions of Wait Events
コメント