Oracle 等待事件：从时间归因到问题闭环的排查方法论 – 刘子毅 – 博客园

1. First establish the theoretical coordinates: what exactly is the waiting event measuring?

Oracle’s performance diagnosis is essentially answering a question:Where does the database time go?。

From the response time model, the time taken by a user request can be divided into two parts:

Response Time = Service Time + Wait Time

Service Time is the time when the process actually takes up CPU work, and Wait Time is the time when the process is forced to hang because a certain resource is unavailable. What Oracle’s Wait Interface does is to explicitly expose the latter: once the session enters waiting, the database records “what it is waiting for, how long it is waiting, and which resource it is waiting for.”

Let’s take a closer look at the instance-level time model:

DB Time = DB CPU + foreground session non-idle waiting time

DB Time measures the sum of time spent within the database by all foreground sessions. It is not equal to wall clock time – when multiple sessions are waiting in parallel, DB Time will be greater than the actual elapsed time. Therefore, to judge whether the system is “busy”, you can’t just look at the CPU, but the multiple of DB Time relative to Elapsed Time, and the ratio of DB CPU to waiting time:

DB has a high CPU ratio and low waiting ratio: it is CPU-intensive, focusing on SQL logical reading, parsing, and calculation;
High proportion of waiting: the system is “waiting” slowly and needs to locate resources along the waiting events;
Both are high: there is often an amplification effect of resource contention, solve the wait first, and then optimize the SQL.

Here is a theoretical premise that is easily overlooked:Waiting for events is a symptom, not the cause。db file sequential read High does not mean “the disk is broken”, it only means that the session is doing a single block read and misses the cache; the real root cause may be improper index selection, expired statistics, insufficient cache, or storage delay. The value of waiting for an event isNarrow the scope of investigation from “the entire database” to “a certain type of resource, a certain batch of sessions, and certain SQL statements”。

Queuing theory can also explain why waiting suddenly worsens at a certain point. When the resource utilization rate ρ increases, the average queuing time increases approximately according to ρ/(1−ρ): the utilization rate increases from 70% to 90%, and the waiting time does not increase by 28%, but increases exponentially. This explains a common phenomenon in production – the CPU and I/O appear to be “not yet full”, but the waiting time has increased significantly.Don’t wait for resources to be full before processing, but look at the trend of waiting time and baseline deviation.

2. Observation system: three layers of evidence from instantaneous to historical

The waiting observation method provided by Oracle can be divided into three layers according to time granularity, each answering different questions.

level	main view	Answered questions	Features
Instantaneous	`V$SESSION`、`V$SESSION_WAIT`、`V$SESSION_WAIT_HISTORY`	Who is waiting now, waiting for what?	Real-time, suitable for catching the scene
Grand total	`V$SYSTEM_EVENT`、`V$SESSION_EVENT`	Which type of wait is the heaviest since the instance started?	Cumulative value, interval difference needs to be done
Sampling/History	`V$ACTIVE_SESSION_HISTORY`、`DBA_HIST_ACTIVE_SESS_HISTORY`、AWR	Time distribution of a certain period in the past	1 second sampling, suitable for attribution

Several key understandings:

V$SESSION_WAIT is the current or last waitnot a historical summary. It’s suitable for answering “Where was this session stuck?” but not for “Where was it slow in the past hour?”
V$SYSTEM_EVENT is the cumulative value since the instance was starteddirectly looking at it will be affected by the startup time. The correct way is to take the difference between two time points, or directly use the interval statistics of AWR.
ASH is sampling rather than full recording. It samples active sessions once per second, and only records non-idle waiting and ON CPU sessions. Idle sessions are filtered out. Therefore, the number of samples in ASH is approximately equal to “number of active sessions × number of seconds”, which is suitable for time proportion attribution, but there is a sampling bias for extremely short waits.
AWR is the persistence of ASH. ASH scrolls circularly in the memory, and AWR flushes the data into the history table according to the snapshot cycle, and retains it for a period of time by default.DBA_HIST_ACTIVE_SESS_HISTORY It is a common entry point for historical ASH.

This also explains why “Looking at the Top 5 of AWR” is a common starting point, but it cannot be the end point: the Top 5 only tells you which type of wait contributes the most DB time.I won’t tell you which SQL, which object, and which blocker.. Attribution must continue to drill down.

3. Translate event names into resource models

Oracle’s wait event button WAIT_CLASS Divided into more than a dozen categories, the non-idle category mainly includes:User I/O、System I/O、Concurrency、Application、Commit、Network、Cluster、Configuration、Administrative、Scheduler、Queueing wait;Idle The class is idle waiting.

Instead of memorizing event names, it is better to establish a mapping of “event → resource → contention mechanism”:

Resource domain	Typical events	Mechanism points
Data file I/O	`db file sequential read`、`db file scattered read`、`direct path read/write`	Single block read, multi-block read, direct path I/O bypassing SGA
Buffer Cache	`buffer busy waits`、`free buffer waits`、`latch: cache buffers chains`	Block occupied, no free buffer, hash chain contention
Redo and commit	`log file sync`、`log file parallel write`、`log buffer space`、`log file switch ...`	Submit waiting for LGWR, log write bandwidth, log switching
Locks and concurrency	`enqueue`、`library cache lock/pin`various types `latch:`	Transaction locks, library cache object locks, internal latches
control file	`control file parallel/sequential read/write`	Multi-copy synchronization, log switching, backup
network	`SQLNet message from/to client`、`SQLNet more data ...`	Client interaction, result set transmission, network quality
cluster	`gc ...` class event	RAC inter-node cache fusion and global resources

Here we need to distinguish two concepts that are often confused:

Enqueue is a lockhas a waiting queue, a name and a mode (such as TX, TM), corresponding to transaction-level or object-level resources;
Latch is a latchis a lightweight serialization mechanism inside SGA. It usually spins and short sleeps, and does not have a queuing structure like enqueue.

enqueue A long waiting time indicates that the transaction is blocked;latch: High wait indicates intense concurrent access to the memory structure. The processing directions of the two are completely different.

4. Six-step troubleshooting method: from phenomenon to closed loop

Step 1: Define windows and baselines

First clarify the “slow” time period, the affected business, and whether it is accompanied by changes (release, batch processing, backup, statistical information collection, parameter adjustment). Waiting numbers are meaningless without a baseline – one that occurs at a fixed time each day db file scattered readit may just be a night report; if the same value appears at the peak of trading, it is a problem.

Step 2: Look at the total amount and determine whether it is CPU-limited or waiting-limited.

First look at the ratio of DB Time to DB CPU and waiting time, and then look at whether the host CPU, I/O, memory, and network are synchronized abnormally. If the waiting proportion is significantly higher than the baseline, enter waiting attribution; if the DB CPU is high but the waiting is not high, you should turn to SQL parsing, logical reading, and calculation volume.

Step 3: Look at Top and wait, but look at “contribution” instead of “number of times”

To evaluate whether a wait event is worth processing, look at at least four quantities:

total waiting time: Contribution to DB Time;
average wait: Whether a single wait is abnormal;
Wait times per second: Is it high-frequency small waiting or low-frequency large waiting;
%DB Time: Proportion in AWR Top Events.

For example log file sync If the average exceeds 10ms, or enters the Top 3, it should be regarded as an independent hidden danger; if the average is less than 2ms and the number of waits per second is very small, it can usually be ignored.buffer busy waits If it enters the Top 5 or accounts for more than 20%, the specific thermal block must be located. The threshold is just a screening line, which must ultimately be combined with the business baseline.

Step 4: Drill down to Session, Module, SQL, Object

Use ASH to converge layer by layer by pressing “Event → Session/Module/Action → SQL_ID → Object → Blocker”. Typical paths are:

A certain type of event accounts for the highest proportion;
The event is concentrated on a few SESSION_ID/MODULE;
Corresponds to the specific SQL_ID;
pass again CURRENT_OBJ#、ROW_WAIT_OBJ# Or wait for parameters to locate tables, indexes, and partitions;
If there is a blockage, along BLOCKING_SESSION Find the root session.

Step 5: Decode the waiting parameters and verify the resource layer

waiting for event P1/P2/P3 It is the key to locating resources. for example:

db file sequential/scattered read：P1 file number,P2 Starting block number,P3 number of blocks;
buffer busy waits：P1 file number,P2 block number,P3 category;
enqueue：P1 Encoded lock name and mode;
latch:：P1 latch address,P2 latch number,P3 number of attempts;
log file sync：P1 The redo buffer number to be written.

After getting the file number and block number, combine them DBA_OBJECTS、DBA_EXTENTS Map to objects; also use the operating system side iostat、sar、topstorage delay, network RTT for cross-validation.The wait on the database side and the resources on the host side must match, otherwise the root cause judgment will not be established.

Step 6: Dispose, verify, and prevent recurrence

Only change one variable at a time, and compare the total waiting time, average waiting, number of waits per second, and business response time before and after modification in the same business window. Also observe side effects: for example, increasing the log buffer may reduce log buffer spacebut may also be elongated log file sync; Increasing parallelism may reduce the I/O wait of a single SQL statement, but may shift the pressure to the CPU and other sessions.

5. Mechanism and handling of typical incidents

5.1 Data file I/O class

db file sequential read(single block read)

Mechanism: The session only reads one data block per I/O, most commonly used in index scans, ROWID access, rollback operations, and control file header reading. The sequential in the name refers to the continuous storage of blocks in memory, not a certain order of disk access.

Troubleshooting: See if the average wait is significantly higher than the storage baseline; passed P1/P2 Locate files and blocks, and then map them to objects; combined with the SQL execution plan, determine whether there are too many index return tables, poor index selection rate, or a decrease in cache hit rate.

Disposal: Optimize SQL and indexes, reduce unnecessary table returns, improve buffer hits, check storage latency and I/O queues. If a large number of single-block reads occur during a full table scan, focus on checking whether the execution plan goes to the wrong index.

db file scattered read(multi-block reading)

Mechanism: One I/O reads multiple consecutive blocks. Typical scenarios are full table scan (FTS) and index fast full scan (IFFS). Scattered means that blocks are read into memory and then stored scatteredly, not randomly read.

Troubleshooting: Confirm whether it is a full scan of a large table, whether there is a lack of suitable indexes, whether the distortion of statistical information leads to the degradation of the execution plan, and whether the parallelism setting is improper.

Solution: Supplement indexes or rewrite SQL, partition pruning, adjust parallelism, and avoid unnecessary full table scans; if all report types are scanned and I/O is the bottleneck, storage bandwidth and caching strategies can be considered instead of blindly adding indexes.

direct path read/write(direct path I/O)

Mechanism: Data is transferred directly between PGA and disk, bypassing SGA. Commonly used in sorting, Hash Join, temporary data generated by parallel execution, and APPEND Direct loading and parallel DML.

Troubleshooting: Check whether it is accompanied by a large number of sorting, parallelism, and temporary table space growth; check whether the PGA is insufficient and whether temporary table space I/O has become a bottleneck.

Disposal: Optimize sorting and connection methods, adjust PGA/temporary table space, evaluate whether the parallelism is too high, and put the temporary table space into low-latency storage. Don’t mistake it for “ordinary data file reading”.

5.2 Buffer Cache class

buffer busy waits

Mechanism: The block to be accessed by the session is being held (pinned) in an incompatible manner by other sessions and can only wait. It is common in hot blocks, high-concurrency DML, and segment header/UNDO block competition. After 10g, the “block is being read into memory” scenario has been independently read by other session。

Troubleshooting: use P1/P2 Locate files and blocks and map them to objects; determine whether it is a table block, index block, segment header or UNDO block; observe whether it is concentrated on a few blocks.

Disposition: Optimize access paths for hot blocks; reduce concurrent updates of the same batch of rows; check index right growth, sequence cache,INITRANS/PCTFREE Is it reasonable? If necessary, partition or split the application layer. Don’t increase the cache size as soon as you start. Hot block problems and caching are often ineffective.

free buffer waits

Mechanism: The session wants to read the block into memory, but cannot find a free buffer; or it cannot apply for memory when constructing a consistent pre-read image. The root cause is usually that the Buffer Cache is too small, or there are too many dirty blocks that DBWR cannot write.

Troubleshooting: Combine db file parallel write、write complete waits Take a look together; check whether DBWR is becoming a bottleneck, whether there are a large number of dirty blocks that have not been flushed, and whether I/O is saturated.

Solution: Increase the Buffer Cache appropriately, enable asynchronous I/O, increase write bandwidth, and reduce unnecessary full scans and dirty blocks. Simply adding caching without solving the DBWR write bottleneck may just postpone the problem.

latch: cache buffers chains

Mechanism: Accessing the hash chain of Buffer Cache requires obtaining a latch. If the chain is too long or the same block is accessed frequently, contention will occur.

Troubleshooting: Look sleeps、misses、spin Wait for statistics, combined with hot block positioning; distinguish whether “the chain is too long” or “single block is overheated”.

Disposal: Reduce logical reads, optimize hot block access, partition or split hot spots, and adjust the cache structure if necessary. For such problems, the priority is to start with SQL and access mode, and parameter adjustment is an auxiliary method.

5.3 Redo and submission classes

log file sync

Mechanism: User execution COMMIT After that, the session waits for LGWR to write the redo of the transaction from the log buffer to the disk, and then it can continue after confirming the persistence. It is a wait caused by user session behavior, not a background wait.

Common reasons: submission frequency is too high, I/O subsystem is slow, log buffer is too large or too small, and the number and size of log groups is unreasonable.

Solution: Reduce unnecessary submissions and merge multiple small transactions into reasonable work units; put redo on low-latency disks to avoid structures with high write penalties such as RAID 5; evaluation LOG_BUFFER with implicit parameters _LOG_IO_SIZE The relationship between large log buffer and small log buffer _LOG_IO_SIZE Will increase background writing, but may reduce log file sync. Understand the side effects before making adjustments and do not copy the parameters.

log file parallel write

Mechanism: LGWR writes the log buffer to multiple redo member files in parallel, which is a background wait. A high value usually means that the redo storage write bandwidth is insufficient, or that multiple members of the same group are placed on the same disk, causing contention.

Solution: Distribute members of the same group to different physical disks to increase write bandwidth and reduce log switching frequency. It is high alone and has limited impact on users, but if combined with log file sync、free buffer waits If they appear at the same time, it means that the overall I/O is already tight.

log buffer space

Mechanism: The redo generation speed exceeds the LGWR writing speed, and the log buffer has no space for reuse.

Disposal: Increase appropriately LOG_BUFFERimprove redo write performance and reduce redo peaks caused by high-frequency submission of short transactions. To be related to log file sync Linked evaluation avoids “increasing the buffer and pushing waiting to the submission end”.

log file switch (checkpoint incomplete)

Mechanism: During online log switching, the dirty blocks corresponding to the target log have not yet completed checkpoint and cannot be overwritten. The root cause is usually that the log file is too small, there are too few log groups, or DBWR writes too slowly.

Disposal: Increase the log file size or the number of log groups; check checkpoint related parameters and DBWR write capability; increase storage write bandwidth. If they appear at the same time log file switch (archiving needed)to check whether the archiving process and archiving destination are normal.

5.4 Lock and library cache classes

enqueue(TX/TM, etc.)

Mechanism: transaction lock waiting.enqueue: TX - row lock contention The most common reasons include: unique index conflict causing insertion wait, bitmap index updating the same segment, too many indexes or splits causing TX lock aggravation,INITRANS Insufficient triggers ITL wait.

Troubleshooting: use BLOCKING_SESSION Find the blocker and confirm the status of the blocked session, SQL, and whether it has not been submitted for a long time; use V$LOCK Check TYPE/ID1/ID2/LMODE/REQUEST;use P1 Decode lock name and mode.

Disposal: Let blocking transactions be committed or rolled back as soon as possible; correct application logic (missing commits, long transactions, exceptions not rolled back); evaluate unique indexes, bitmap indexes and INITRANS design; if necessary, handle it during low business peaks to avoid direct kill session Causing greater rollback.

library cache lock / library cache pin

Mechanism: Concurrent operations on the same object in the shared pool. DDL and DML concurrency, compilation and execution concurrency, will all cause this kind of wait.

Troubleshooting: Look P1 object address,P2 lock address,P3 Mode and namespace; confirm whether there is long-term DDL, compilation, statistics collection or centralized parsing of connection pools.

Disposal: Avoid doing DDL during business peaks; shorten compilation and parsing time; check whether the shared pool is insufficient and whether there is a hard parsing storm; use connection pools and binding variables to reduce concurrent parsing pressure when necessary.

5.5 Network category

SQL*Net message from client It is a typical idle waiting: after the server processes the request, it waits for the client to send the next instruction. If the client does not send it, the server keeps waiting. A high value does not mean that the database is slow, but it may mean that the application takes a long time to think and has many idle connections in the connection pool.

What really needs attention is:

SQL*Net more data to client: The result set is too large or the network bandwidth is insufficient;
SQL*Net break/reset to client: The network is unstable or the client is abnormally disconnected;
SQL*Net message to client: The server is blocked from sending messages, the client is busy or there is a network problem.

The processing direction is to reduce the number of returned rows, adjust the array fetch size, check the network quality and client load, rather than adjusting database parameters.

5.6 Idle waiting

SQL*Net message from client、pmon timer、smon timer、rdbms ipc message etc. belong to the Idle class. It should be filtered out first when diagnosing, otherwise the Top waiting list will be filled with idle events, masking the real problem.

6. Directly usable troubleshooting SQL

The following statements are applicable to Oracle 10gR2 and above; some fields are introduced in later versions and should be checked against the target version before use.

1) Database CPU to waiting time ratio

SELECT metric_name, value
FROM   v$sysmetric
WHERE  metric_name IN ('Database CPU Time Ratio', 'Database Wait Time Ratio')
AND    intsize_csec = (SELECT MAX(intsize_csec) FROM v$sysmetric);

2) Instance-level non-idle wait summary

SELECT event,
       wait_class,
       total_waits,
       ROUND(time_waited_micro / 1000000, 1) AS total_sec,
       ROUND(time_waited_micro / 1000 / GREATEST(total_waits, 1), 2) AS avg_ms
FROM   v$system_event
WHERE  wait_class <> 'Idle'
ORDER  BY time_waited_micro DESC;

3) Currently waiting for the session

SELECT sid, serial#, username, sql_id, event, wait_class,
       state, seconds_in_wait
FROM   v$session
WHERE  status="ACTIVE"
AND    wait_class <> 'Idle'
ORDER  BY seconds_in_wait DESC;

4) ASH’s top waiting events in the last 30 minutes

SELECT event,
       wait_class,
       COUNT(*) AS samples,
       ROUND(SUM(time_waited_micro) / 1000000, 1) AS total_sec
FROM   v$active_session_history
WHERE  sample_time > systimestamp - INTERVAL '30' MINUTE
GROUP  BY event, wait_class
ORDER  BY total_sec DESC;

5) ASH Top SQL in the last 30 minutes

SELECT *
FROM (
  SELECT sql_id,
         COUNT(*) AS samples,
         ROUND(SUM(time_waited_micro) / 1000000, 1) AS total_sec
  FROM   v$active_session_history
  WHERE  sample_time > systimestamp - INTERVAL '30' MINUTE
  AND    sql_id IS NOT NULL
  GROUP  BY sql_id
  ORDER  BY total_sec DESC
)
WHERE rownum <= 20;

6) The object with the longest waiting time in ASH

SELECT *
FROM (
  SELECT o.owner, o.object_name, o.object_type, a.event,
         COUNT(*) AS samples,
         ROUND(SUM(a.time_waited_micro) / 1000000, 1) AS total_sec
  FROM   v$active_session_history a
  JOIN   dba_objects o ON a.current_obj# = o.object_id
  WHERE  a.sample_time > systimestamp - INTERVAL '30' MINUTE
  GROUP  BY o.owner, o.object_name, o.object_type, a.event
  ORDER  BY total_sec DESC
)
WHERE rownum <= 20;

7) Blocking relationship positioning

SELECT sid, serial#, username, sql_id, event,
       blocking_session, blocking_session_status, seconds_in_wait
FROM   v$session
WHERE  blocking_session IS NOT NULL;

8) enqueue lock name and mode decoding

SELECT sid, event, p1, p2, p3,
       CHR(TO_CHAR(BITAND(p1, -16777216)) / 16777215)
         || CHR(TO_CHAR(BITAND(p1, 16711680)) / 65535) AS lock_name,
       BITAND(p1, 65535) AS lock_mode
FROM   v$session_wait
WHERE  event="enqueue";

9) Log switching frequency

SELECT TRUNC(first_time) AS day, COUNT(*) AS switches
FROM   v$log_history
WHERE  first_time > SYSDATE - 7
GROUP  BY TRUNC(first_time)
ORDER  BY 1;

10) Waiting details for a single SQL statement

When you need execution-level waiting, you can enable 10046 for the target session:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Close after analysis is complete:

ALTER SESSION SET EVENTS '10046 trace name context off';

The trace will include the execution plan, bind variables, waiting events and time consumption, which is suitable for verifying “whether a certain wait is really generated by this SQL”. The production environment should control the scope and duration to avoid excessively large trace files.

7. Seven common misunderstandings

Treat idle waiting as a bottleneck。SQL*Net message from client Ranking first usually means that the client is idle, not that the database is slow.
Only look at the average wait, not the total contribution. If an average wait of 2ms occurs hundreds of thousands of times per second, the total time may be far longer than the average 500ms but rarely occurs event.
Bundle db file parallel read When querying in parallel. It mainly occurs in recovery scenarios and has nothing to do with parallel queries.
Bundle scattered When reading randomly. It describes the scattered storage of blocks in memory, and actual I/O is often continuous reading of multiple blocks.
Confusing latch with enqueue. The former is an SGA internal latch, and the latter is a transaction/object lock, with different processing paths.
Treat the waiting event directly as the root cause. The wait event only shows “which resource is stuck”, and the root cause also falls to SQL, objects, configuration, storage or application logic.
Ignore version and sampling differences。V$SESSION_WAIT gradually in new versions V$SESSION Waiting field replacement; ASH only samples active sessions; AWR’s interval statistics rely on snapshots, and data across snapshot boundaries should be interpreted with caution.

8. Conclusion

The value of waiting event analysis does not lie in remembering how many event names, but in establishing a reusable reasoning chain:

First do time attribution (DB Time / DB CPU / Wait), then look at the top wait contribution and baseline offset, then use ASH to drill down to sessions, SQL, objects and blockers, then use P1/P2/P3 to cross-validate with host resources, and finally change only one variable and perform regression verification in the same window.

This chain is connected,log file sync It’s not just “slow submission”, but it can also fall into LGWR write delay, submission frequency or redo layout;buffer busy waits It’s not just “hot blocks” either, but can fall into specific objects, access patterns, and concurrency design. Waiting for events is the entrance, not the answer; the real answer is always at the intersection of resources, SQL and business access patterns.

Source link