Wait for the event wait event

Measure the database status and optimize the database.

After a process is connected to the database, all the waits for the process are recorded and displayed in a series of performance views. By waiting for the event, the user can quickly find the performance bottleneck of the database, so as to optimize and analyze the performance.

V $ event_name

 

There are two main types of Oracle wait events: idle and non-idle)

Idle waiting: Oracle is waiting for a job. When diagnosing and optimizing the database, you don't need to pay too much attention to this part of the event.

Non-idle waiting: a dedicated activity for Oracle refers to the waiting occurred during database tasks or application running. These waiting events should be paid attention to and researched when adjusting the database.

Discover bottlenecks from wait events

V $ session: records the session information of the current connection.

V $ session_wait records the resources or events that are waiting for the Active session of the current database connection.

V $ system_event records the summary of all wait events since the database was started.

V $ sqltext. When a database has a bottleneck, you can use v $ session_wait to find the session waiting for the resource and use the session Sid, combined with the V $ session and V $ sqltext views, you can capture the SQL statements being executed by these sessions.

For example:

1. Select Sid, event, P1, P1 text from V $ session_wait;

2. Select SQL _text

From v $ sqltext

Where a. hash_value = (select SQL _hash_value from V $ session B where B. Sid = '& SID ')

Order by piece ASC

3. Use this user to connect and check the SQL Execution Plan

Set autotrace trace explain

Execute this (problematic) SQL statement

Identify problems, such as full table scan, and further analyze the causes. For example, if no index is found on a column, the problem is solved.

4. Check whether the problem is resolved.

Select Sid, event, P1, P1 text from V $ session_wait;

After 10 Gb, the V $ session_wait_history view is added, so that you can check whether there are serious waiting events on the previous day, or compare the waiting events on the previous day with those on the current day.

New Features of ash (fundamental changes)

If V $ session_wait_history is a small step, ash is a huge step. Based on V $ session, ash samples the event every second and records the event waiting for the Active session.

Top-level wait event

Use the V $ system_event View

Select * from (select event, time_waited from V $ system_event order by time_waited DESC)

Where rownum <10;

Query the first 10 most serious wait events and find the root cause of the problem for processing. (the top 5 in the statspack report is the reference)

Important wait events

DB file sequential read: reads data files sequentially, user I/O class. If this wait event is significant, it may indicate that the table connection sequence is faulty in Multi-Table connections.

DB file scattered read discrete data file reading, user I/O class, usually a large number of DB file scattered read wait may mean application problems or missing indexes.

Direct path read/write, disk sorting, etc. will be triggered, the use of temporary tablespace must be frequent (Sorting must use temporary tablespace), at this time you can increase the temporary tablespace, modify parameters.

Enqueue: queue waiting, locking the shared resources, for example, Enq: PW-flush prewarm Buffers

Latch free: latch release