5

Chapter 5 Using Oracle's Wait Events

Detect performance bottlenecks

In the Oracle database system, some performance problems often occur, even a system with good performance, sometimes
exist
There will also be bottlenecks, so optimization is a job that DBAs often face. Processes performing operations may often encounter
When you need to wait for resources, for example, the data required by the process may already be in the SGA, but it is also likely to be on the disk;
A latch needed to perform an operation (to ensure that a single process executes a particular piece of kernel code) may be acquired by another requirement;
The queue resources (to access some resources of Oracle) have been occupied by other processes; waiting for user input or other situations. bottle
The neck always exists, but the bottom line that everyone can accept is different. A session may always be waiting for something, possibly a wait
The beginning of what is waiting, or the end of what is waiting, and the DBA can understand this through the statistical information of the session waiting event.
What the hell is this session waiting for.
In the past, judging the performance of Oracle mainly relied on performance indicators (such as the hit rate of data blocks, etc.). Now, these refer to
Standards are no longer the only way to judge, of course, they are not very good way. The method used in this chapter is
The analysis method of session waiting event, this method will be able to provide DBA with more in-depth performance analysis.
The method of using various ratio indicators for Oracle tuning needs to be very familiar with a large number of performance indicators, requiring the DBA to be
“experts” for this indicator. For example, the ratio of the number of on-disk sorts to the total number of sorts, the obvious need to minimize on-disk sorts,
so that this ratio is close to 0. In order to optimize this sorting, it is necessary to obtain some ratios of Oracle in this regard, plus enough
experience using these ratios, and sufficient application experience, combined with application implementation experience, to be able to take advantage of
Use these ratios to optimize performance. There are many performance articles, instructions, etc. that cover how to use these ratios to tune data
However, due to the large number of index ratios involved in these documents, most of them are very lengthy and complicated. so to be able to really master the us
There are not many people who perform Oracle performance optimization on various ratio indicators.
Starting from Oracle 7, Oracle began to provide session wait event information to help DBA get a more accurate database
performance information. In older versions, administrators still need to analyze performance with the help of relatively inefficient ratios.
Oracle uses internal triggers to record and display some views to get certain resources (Latch, I/O, data in memory or queue
etc.) but no process-related information is immediately available. Anyone can get accurate information about Oracle from this view of V$
Exact information like what resource the process is waiting for. This performance view is the session wait event view: v$system_event,
v$session_event and v$session_wait.
An obvious advantage of the session wait event is that it is no longer associated with a single application, that is, the session wait event is
application independent. Any application with high wait events for full table scans means I/O is having problems. Of course, real

46 Oracle Database Performance Optimization

The actual root cause could be bad SQL statements, bad I/O system architecture, or something else, but not
In any case, it must be a problem related to I/O, so the next step is to continue to judge the real problem based on other information.
what is the reason.
Because the session wait event is application-independent, it can be used as the basis for a good performance monitoring tool. base
The performance monitoring tool based on the session waiting event will be a more accurate, more convenient and simpler tool.

5.1 Judging related views of waiting events

There is not one but a set of session views on waiting events, each with a specific objective describing the
Detailed wait information at the level or session level. These views include v$system_event, v$session_event and
v$session_wait. These views will be introduced separately below.

5.1.1 System-level statistical information v$system_event

v$system_event is used to view the performance of the entire system level or the highest level of the overall system. there is no
information about each session, but for each event sums up what has happened across all sessions since the system was started, like
Statistics total the same across the instance and are recounted after each system restart.
v$system_event has 5 fields: Event: This field lists the names of all events that occurred. There may be many waiting events to happen, the most common one

waits, db file scattered read, db file sequential read, enqueue wait, buffer busy wait, log file

ll l df b ff ll b d dh d f l
parallel write and free buffer waits. Some common waiting events will be introduced here, and of course more waiting event columns
table, you still need to consult the relevant documents of Oracle. Total_waits: The total number of waiting events from the database startup to the present. Total_tim

After getting the resources needed by the lock, it ends. Some waits will continue to retry when they do not get resources and time out, and some wait for timeouts.
After that, there will be no more waits, but some waits will continue to wait without a timeout. Time_waited: The total waiting time in centiseconds (hundredths of s

A wait event has occurred in all sessions (including those that have ended and those that are still connected) since the database was started.
words) the sum of the total wait events. Average_wait: The average wait event, in centiseconds (hundredths of a second). Indicates starting from the database with

The average waiting time for a waiting event to occur in all sessions, that is, the value of total_waits / time_waited.
In order to see the situation of waiting events more clearly, you can compare the information of v$system_event for a period of time, and get
The most waiting events occurred during this period, that is, what are the most important waiting events during the current period.
Figure 5-1 shows the system details recorded in v$system_event since startup. It can be seen that starting from the system
Since , 69.61% of the time the session is waiting for Oracle to read data from disk. Of course, only based on the
Waiting for the main waiting event of the full table scan related to I/O (Wait Event: db file scattered read) is uncertain
The session's current wait is caused by a full table scan. To judge the current system bottleneck, what is needed is the recent system
Performance changes, this kind of data increment over a period of time, especially the performance changes collected during the busy phase of the business, is m
A good basis for judging performance.

Chapter 5 Detecting Performance Bottlenecks Using Oracle's Wait Events 47

SQL> @syssum.sql

Wait Event Time Waited %Time w Waits %Waited

----------------------------------- ----------- ---- --- --------- -------
db file scattered read 1463 69.61 17528 94.96
buffer busy waits 291 13.84 65 0.35
PL/SQL lock timer 160 7.60 307 1.66
latch free 130 6.18 127 0.69
log file parallel write 37 1.76 269 1.46
db file sequential read 20 0.96 161 0.87
log file switch completion 1 0.02 0 0.00
log file sync 0 0.02 1 0.00
library cache pin 0 0.01 0 0.00
log file sequential read 0 0.01 0 0.00
log file single write 0 0.00 0 0.00
process startup 0 0.00 0 0.00
db file single write 0 0.00 0 0.00
LGWR wait for redo copy 0 0.00 0 0.00
enqueue 0 0.00 0 0.00
library cache load lock 0 0.00 0 0.00
db file parallel write 0 0.00 2 0.01
direct path write 0 0.00 0 0.00
Figure 5-1 System details

Figure 5-2 shows the changes in the session waiting events over a period of time, where the session is not mainly waiting for the full table
Scanning, but waiting for the write operation of the redo log (Wait Event: log file parallel write).
SQL> @cr_base.sql

SQL> @in_base.sql

Execute user application...

SQL> @re_base.sql

Wait Event Time Waited %Time w Waits %Waited

----------------------------------- ----------- ---- --- -------- -------
log file parallel write 4.360 73.15 598 72.57
buffer busy waits 0.970 16.28 189 22.94
db file sequential read 0.160 2.68 7 0.85
latch free 0.130 2.18 9 1.09
log file sync 0.060 1.01 1 0.12
enqueue 0.000 0.00 7 0.85
LGWR wait for redo copy 0.000 0.00 1 0.12
direct path write 0.000 0.00 1 0.12
db file scattered read 0.000 0.00 0 0.00
db file single write 0.000 0.00 0 0.00
direct path read 0.000 0.00 0 0.00
library cache load lock 0.000 0.00 0 0.00
library cache pin 0.000 0.00 0 0.00
log file sequential read 0.000 0.00 0 0.00
log file single write 0.000 0.00 0 0.00
log file switch completion 0.000 0.00 0 0.00
process startup 0.000 0.00 0 0.00
Figure 5-2 Changes of session waiting events
 48 Oracle Database Performance Optimization

5.1.2 Session-level statistics v$session_event

The v$session_event view shows similar information to v$system_event, but these waiting information are for each
Wait for the statistics of events in each session, so there are only more fields about session id. In this view, a session re-
Statistics will be reset to 0 on build.
This view is very useful in determining what resources a session is waiting for. For example, if it is found that the user of session 10
If there is an obvious problem with user performance, check this view before the user executes the application, and then let the user execute the application, and a
Check this view again after completion, compare the results of the two queries, and see which waiting events are the most important waiting events during this pe
items, then these waiting events are the bottleneck of the user application.

5.1.3 Session detailed performance information v$session_wait

v$session_wait is the most complex and least understood session wait view. It is related to v$system_event and v$session_event
There are obvious differences, this view only contains the waiting events of the session that is currently connected, and it contains
The meaning of the field is more obscure, such as P1, P2 and so on. This view does not store total information, but provides the current
available information on events that are occurring or are occurring.
In addition to displaying the waiting information that has occurred in real time, v$session_wait also provides information about the currently occurring waiting
pending event information. For example, it directly depicts a session that is contentioning for a latch, waiting on that latch at the moment.
Or, a session is waiting for access to a data block, the file number where the block is located, the block number, how many numbers have been accessed
All of this information is available on the block.
The v$session_wait view also shows detailed timing information, just like the v$system_event and v$session_event views
Same. However, because v$session_wait displays real-time information, the meaning of the time it displays depends on the
To be determined by the status. There will be a more detailed explanation below.
The v$session_wait view includes the following fields: SID: ID of the session. Same as session ID in v$session_event or v$session. SEQ # : The internal sequen

db file sequential read, enqueue wait, buffer busy wait and free buffer waits, etc. P[1-3]: These three parameters are used to provide more detailed information abou

The foreign key field of the parameter value is related to the specific waiting event. For example, for a latch wait, P2 represents the latch number,
Can be associated with v$latch query. For db file sequential read (indexed read), P1 represents the file number,
It can be queried jointly with v$filestat or dba_data_files, and P2 represents the data block number, which can be combined with dba_extents,
sys.uet$ Union query. In order to use these parameters, you should try to understand the meaning of these parameters as much as possible, which can be found in
to find these. The P[1-3]TEXT fields in the view can also provide some information, but don't count on them too much. P[1-3]RAW: The value of P[1-3] described in h

SECONDS_IN_WAIT. If you don't understand the meaning of STATE correctly or ignore it, you may be understanding WAIT_TIME
and SECONDS_IN_WAIT cause ambiguity. The STATE field has the following 4 meanings:

Chapter 5 Detecting Performance Bottlenecks Using Oracle's Wait Events 49

(1) Waiting: The session is waiting for this event.
(2) Waited unknown time: Since timed_statistics is set to false, the time cannot be obtained
Information.
(3) Waited short time: Indicates that the session has been waiting, but the waiting time is very small, no more than one time unit
(one clock tick), so there is no record.
(4) Waited known time: Once the session waits for the resource and then gets it, the state will enter from waiting
waited known time. WAIT_TIME: The value of this field is related to STATE.

(1) If the STATE field value is Waiting, then the WAIT_TIME value is useless.
(2) If the STATE field value is Waited unknown time, then the WAIT_TIME value is also useless.
(3) If the STATE field value is Waited short time, then the WAIT_TIME value is also useless.
(4) If the value of the STATE field is Waited known time, then this is the actual waiting time, in seconds
unit. Being able to see this value is not very easy, because if the session starts waiting for a resource, the status will again become
waiting, the value of this field will become useless (bogus). SECONDS_IN_WAIT: This value also depends on the STATE field.

(1) If the STATE field value is Waiting, then this value is the actual waiting time, with seconds as the time unit. if
If you see a value, you will see a different value when you query again, because each time you see is the current waiting time.
If you frequently query this view, you will get information about when a session is waiting and what it is waiting for.
(2) If the STATE field value is Waited unknown time, then the SECONDS_IN_WAIT value is also useless.
(3) If the STATE field value is Waited short time, then the SECONDS_IN_WAIT value is also useless.
(4) If the STATE field value is Waited known time, then the SECONDS_IN_WAIT value is also useless.
This view is very useful for determining which wait events occurred or the reason for the wait events. For example, if the system has

h f f ll b d l d $ df h
500 sessions, then 500 rows of session information will be displayed in v$session_event. And for most DBAs, this
Too much information will appear blurred and it is difficult to distinguish the information that is really needed. Then more valuable information should be provided t
session information, what the DBA needs to do at this time is only to find out which sessions have obvious waits through this view, so as to further
Determine which wait events for which sessions.
For example, in Figure 5-3 you can see that most sessions are waiting for I/O related (Wait Event: db file scattered
read) full table scan.
SQL> @seswa.sql Num.Sess.

Wait Event Waited So Far ( sec ) Waiting

---------------------------------------------------- --------- ----------
rdbms ipc message 1,012 5
latch free 0 2
smon timer 301 1
buffer busy waits 0 1
db file scattered read 0 165
pmon timer 0 1

6 rows selected.
Figure 5-3 Main session waiting events

50 Oracle Database Performance Optimization

Then the next thing to do is to take a closer look at which data blocks these specific sessions are waiting for access, as shown in the figure
5-4 shown.
SQL> select sid,event,p1,p2,p3 2 from v$session_wait 3 where event like 'db%file%scat%'; ID Wait Event P1 P2 P3

---- ----------------------------------- ----------- - --------- ----- 8 db file scattered read 8 6572 16 10 db file scattered read 8 6413 16

Figure 5-4 Resources the session is waiting for

Remember that the wait event recorded in v$session_event is recorded in the currently waiting resource (recorded in v$session_wait)
It will not be updated until it is obtained. So if a wait event (eg SQL*Net message [from, to] client)
If the waiting time is very long, it will be obvious that the statistics in v$session_wait will continue to increase and the corresponding
The information in v$session_event remains unchanged.

5.1.4 Relationship between related views of session waiting events

In Oracle Database, time information is very important because events happen very quickly. For example, for SQL*Net
The message event study found that there are many important timing information. Here's what happens at this moment: There are always some session-related w

Then SQL * Net message from client is waiting for the event. Only one wait event occurs in a session at a time. If you see other wait events, it only means that the n

A wait occurs on a time slice, and only one wait exists at a time. The time waited and seconds in wait field values ​of v$session_wait are in seconds, and v$session

The time waited and average time waited field values ​are in centiseconds (hundredths of seconds). After the waiting event of v$session_wait ends, the statistical i

When waiting, the information in the v$session_event view will be increased, and the latest waiting time information will be added to the original statistics
in the counting information. When the wait event in v$session_wait ends, the value of v$session_wait.seconds_in_wait is copied

to the v$session_event.time_waited field, and the v$session_event.average_time field is also modified at the same time.

5.2 How to consider optimization

As mentioned above, using session wait event statistics provides a performance judgment method, which helps to find poor performance.
different, to find the cause of competing conflicts. How to use these waiting event views in a real environment, how to implement basic competition
The judgment method will be discussed below. Performance problems can usually be solved by checking the performance of the system or collecting the perform
indicators to discover.
 Chapter 5 Detecting Performance Bottlenecks Using Oracle's Wait Events 51
When the system is found to be slow, first collect it from the system level through v$system_event. It should be noted that it is not only tired
The calculated system wait event information, and more importantly, the wait time increment over a period of time, which can better describe the wait event
the severity of the occurrence. It is generally recommended to collect and compare at intervals (for example, collect once every half hour). through this
This method can quickly determine where the main bottlenecks in the entire system are.
Of course, there is still a problem here, that is, although the bottleneck has been determined, where does the real bottleneck occur?
The location of the cut is still unclear, for example, which latch, file, or access to which block the wait occurred on.
It is because the real object that causes the bottleneck cannot be obtained at the system level, so further collection and analysis is required, by viewing session-lev
The waiting event information of the v$session_wait view can further determine the cause of the bottleneck, for example, determine
Find out on which latch the contention occurred, or which data file access caused the I/O contention. if found to be
File I/O competition can be further found out, whether the cause of the competition is due to access to the index or a large number of full table scans. if
If there is competition on the block, it can be further determined that the block access on which segment of which file has competition. these messages
It is very useful to determine the cause of the performance bottleneck and choose the appropriate solution.
When you see the hit rate of the data cache area is only 60% again, don't worry, see if there is any relevant
Wait for the event, if not, then don't worry, because the database performance is good, it is not high with the data when any operation is performed
Cache-related wait events. Conversely, if the data cache hit rate reaches 99%, but it is found that there is
With a lot of waiting, the unfortunate news is that the high hit rate has brought about a decrease in performance (the reason may be
The data cache area is too large, exceeding the size of physical memory, causing a lot of paging, etc.), of course, there is a good news
The message is that now you can judge what caused the problem by waiting for the event.

5.3 Main waiting events

There are already many wait events that are documented and should be easy to understand. However, some wait events are in Oracle's documentation
It is not very clear in the description, here, I will introduce some of my views on these waiting events, and provide some benefits for everyone.
The idea and method of solving competition problems by waiting for events. First understand the general classification of waiting events and see which ones shou
The heart waits for the event. There are two main types of wait events in Oracle: idle waits and non-idle waits.

1. idle waiting

Idle waiting means that Oracle is waiting for some action to occur. Sometimes Oracle's processes are waiting when they are not actually
It is waiting because it is busy, but it is waiting because there is nothing to do. For example, smon timer, some operations of the SMON process are performed ever
Polling is performed at intervals. When the system is not busy, this kind of operation will not happen immediately, but wait for the timer to reach a certain value.
Execute at a certain time, at this time, a smon timer waiting event will be generated, but the system actually has no performance problems. only
Very few idle wait events are performance related, most of the time such things have little impact on performance.
Typical waiting events of the idle class are: client message (client message), null event (null event), pipe get (pipe
access operation), SQL * Net message from client (message from client), SQL * Net message to client (sent to
client message), rdbms ipc message (database ipc message), virtual circuit status (virtual circuit status information),
smon timer (smon timer), pmon timer (pmon timer), dispatcher timer (scheduler timer), etc.

2. non-idle wait event

Non-idle waits usually occur when there is contention in the database. This wait mostly means that there is a race in the system,

52 Oracle Database Performance Optimization

When a certain operation occurs, the resources required by the operation are being occupied by other operations, and this exclusive resource cannot be used by su
The operation request is obtained immediately, and the operation request is blocked and waiting occurs. Non-idle waiting events mainly include the following.
buffer busy waits (data cache busy waiting), db file scattered read (data file discrete read), db file
Sequential read (sequential reading of data files), db file parallel write (parallel writing of data files), db file single write
(data file single write), enqueue (queue), free buffer inspected (free data buffer detection), free buffer waits
(idle buffer waiting), latch free (pin idle waiting), log file parallel write (log file parallel writing), log file sync
(log file synchronization), log buffer space (log buffer space allocation), log file single write (log file single write),
log file switch (archiving needed), log file switch (checkpoint incomplete), direct path read (direct
Direct path read), direct path write (direct path write), library cache load lock (library cache load lock), library
cache lock (library cache lock), library cache pin (library cache execution lock), timer in sksawat (archiving too slow),
transaction (transaction blocking), undo segment extension (rollback segment dynamic extension), etc.
The main impact on performance is the non-idle waiting event, so here we mainly introduce the basics of common non-idle waiting events
Meaning, as shown in Table 5-1.

Table 5-1 Basic meaning of non-idle wait events

wait event wait event description
buffer busy waits Indicates that it is waiting for access to the data cache area. This wait event usually occurs when the session reads data
When entering the buffer or modifying the data in the buffer, for example, DBWR is writing some data blocks to the data
While reading the file, other processes need to read the corresponding data block. It may also be indicated on the table
The set of free lists is too small to support a large number of concurrent Insert operations. In v$session_wait view
The P1 field value of the figure indicates the file number where the relevant data block is located, and P2 indicates the block number on the file.
Through the combined query of these information and dba_data_files and dba_extents, the source can be quickly located.
Competing storage objects to further determine the source of the problem
db file parallel write Waits associated with the DBWR process generally represent a problem with I/O capabilities. Usually configured with
 It is related to multiple DBWR processes or the number of I/O slaves of DBWR, of course, it may also mean that in
There is I/O contention on the device
db file scattered read Indicates that a wait related to a full table scan occurred. Usually means too many full table scans, or I/O capacity
Insufficient, or I/O contention
db file sequential read Indicates that a wait related to an index scan occurred. Also means that there is a problem with I/O, which usually means I/O
Contention or too many I/O demands
db file single write Represents waits associated with file header writes while a checkpoint occurs. Data files are usually synchronized with checkpoints
The disorder of the file number at the header is related to
direct path read Indicates waits associated with direct I/O reads. When directly reading data to the PGA memory, direct path read out
now. This type of read request typically occurs as: sort IO (when the sort cannot be done in memory),
Parallel Slave query or read-ahead request, etc. Usually this wait is related to I/O capacity or I/O contention
direct path write Same as direct path read, but the operation is write
enqueue Represents waits related to internal queuing mechanisms, such as requests for locks protecting internal resources or components
etc., a concurrency protection mechanism
free buffer inspected Indicates waiting for the process to find a large enough memory space while reading data into the data cache. Pass
Usually this kind of wait indicates that the data cache area is too small

Chapter 5 Detecting Performance Bottlenecks Using Oracle's Wait Events 53

Continuation
wait event wait event description
free buffer waits Indicates that the data cache area is short of memory space. Usually associated with data cache memory that is too small or dirty
It is said that it is written too slowly. In this case, you can consider increasing the data cache area or setting more
Multi-site DBWR to increase the ability to write dirty data
latch free Indicates that a race has occurred on a latch. First of all, you should ensure that enough Latch numbers have been provided,
If this wait event still occurs, then it should be further determined that the contention on that latch
(The P2 field on v$session_wait indicates the label of the latch), and then judge what trigger
caused this latch race. Most latch races are not simply caused by latches, but by
The components related to the latch are caused, so it is necessary to find the root cause of the specific competition. For example, if
If there is a library cache latch competition, it usually means that the configuration of the library cache is unreasonable.
Or the writing of the SQL statement is unreasonable, which brings a lot of hard parsing
library cache load lock Indicates that a wait occurred while loading an object into the library cache. Such an event usually represents the occurrence of a heavy load
Heavy statement overloading or loading may be caused by the fact that the SQL statement is not shared or the shared pool area is too small
library cache lock Represents waits associated with multiple concurrent processes accessing the library cache. Usually indicates an unreasonable shared pool size
library cache pin This wait event is also related to the concurrency of the library cache, when objects in the library cache are modified or
Occurs when a person is detected
log buffer space Indicates that a space wait event occurred in the log buffer. This wait event means that when writing the log buffer
When the corresponding memory space cannot be obtained, it usually occurs when the log buffer is too small or the LGWR process is too small.
when slow (could be caused by other reasons)
log file parallel write Indicates waiting for LGWR to request I/O from the operating system to start until the IO is completed. When triggering LGWR write
Cases like 3 seconds, 1/3, 1MB, DBWR before write may happen. This event usually occurs
indicates that the log file has I/O contention or that the drive on which the file resides is slow
log file single write Indicates that a wait occurred while writing the log file header block. Usually occurs when a checkpoint occurs
log file switch (archiving Waits that occur because the log cannot be switched because the archive is too slow. The reason for this wait event may be compared
needed) Many, the main reason is that the speed of archiving cannot keep up with the speed of log switching. Possible causes include heavy
The log file is too small, the redo log group is too small, the archiving capacity is too low, and I/O competition occurs in the archived file.
The archiver process hangs, or the archived log file is placed on a slow disk device, etc.
log file switch (checkpoint
Indicates that the checkpoint on the corresponding file was not completed at the time of the log switch. Generally means that the log file is too small
incomplete) The log switch is too fast or other reasons (such as DBWR has not finished writing dirty data) cause the checkpoint to be too slow
log file sync Indicates that when the service process issues a commit or rollback command, it will wait until LGWR completes the relevant log writing
The operation waits for this period of time. If there are multiple service processes issuing such commands at the same time, LGWR cannot
Completing the write operation of the log in time may cause this waiting
transaction Indicates that a wait that blocked the rollback operation occurred
undo segment extension Indicates that the dynamic expansion of the rollback segment is waiting. This indicates that the transaction volume may be too large, and it also means t
The initial size of the roll segment is not optimal, and MINEXTENTS is set too small. Consider reducing transactions, or
Use a rollback segment with a larger minimum number of extents

Most idle waits have little to do with performance, so idle waits seen in wait events are mostly negligible, here
Only one kind of idle waiting event SQL*Net message from client is introduced, which may reflect the waiting of the problem to a certain extent.
 54 Oracle Database Performance Optimization
The SQL * Net Message From Client wait event can also be ignored most of the time, it means that the service process is
Waiting for the client process to return some response information, due to some normal reactions, calculations, operations and other actions of the client and serv
execution, so under normal circumstances, this wait may occur frequently during connection interactions. But if you find this waiting event stack
If the accumulation is very large, you need to pay attention. It is very likely that there is a problem with network transmission, or there is competition for network ba
It leads to the accumulation of a large amount of response information, and then it needs to be optimized.

5.4 Case Study

Just knowing some waiting events and the performance views related to waiting events does not mean that you have been able to pass
This information is used to help solve the performance problems reflected by waiting events. The above mentioned are all about analyzing waiting events and optim
How to use them to solve these problems is more important, the following is a simple
Cases are used to illustrate how to use these tools to assist in analyzing, discovering and solving problems.
In order to fully understand and analyze the waiting events that occur in the system, two methods need to be used to collect relevant waiting information at the
In order to accurately analyze the real causes of these waiting or performance problems.
First of all, it is necessary to understand from the perspective of the entire system, and find out what is the most important waiting event since the system was
For the most important waiting events in the system, query the v$system_event dynamic performance view, as shown in Figure 5-5.
SQL> @syssum.sql

Wait Event Time Waited %Time w Waits %Waited

----------------------------------- ----------- ---- --- --------- -------
db file scattered read 1501 66.89 17703 92.82
buffer busy waits 309 13.77 78 0.41
PL/SQL lock timer 189 8.40 354 1.85
latch free 138 6.14 134 0.70
log file parallel write 51 2.25 304 1.59
db file sequential read 29 1.31 179 0.94
enqueue 17 0.76 0 0.00
free buffer waits 7 0.30 1 0.00
log file sync 1 0.05 1 0.01
write complete waits 1 0.05 0 0.00
log file switch completion 1 0.05 0 0.00
log buffer space 1 0.03 0 0.00
library cache pin 0 0.01 0 0.00
log file sequential read 0 0.01 0 0.00
log file single write 0 0.00 0 0.00
undo segment extension 0 0.00 314 1.65
LGWR wait for redo copy 0 0.00 0 0.00
process startup 0 0.00 0 0.00
db file single write 0 0.00 0 0.00
library cache load lock 0 0.00 0 0.00
db file parallel write 0 0.00 2 0.01
direct path write 0 0.00 0 0.00
direct path read 0 0.00 0 0.00
buffer deadlock 0 0.00 0 0.00

Figure 5-5 v$system_event dynamic performance view

Chapter 5 Detecting Performance Bottlenecks Using Oracle's Wait Events 55

It can be clearly seen that the most important waiting event in the system is the full table scan (Wait Event: db file scattered
read) related wait events. Of course, it should be noted that the waiting event seen here does not mean that it must be the current system performance
The absolute reason for the decline, what is shown here just means that this kind of waiting event has occurred the most since the system was started, so it needs
aspects to collect and analyze.
In addition to understanding the waiting events that have occurred from the perspective of the overall system, it is more important to find out what caused the
What is the real waiting event, which requires understanding what is the most important waiting during the execution of the application, query the system
The waiting events in the still need to be obtained from v$system_event. Unlike the above query, it is now necessary to execute the query twice
(Busy period), and compare the two queries to get the most important waiting events during the period of application execution, such as
As shown in Figure 5-6. It can be seen from Figure 5-6 that during this period, the most important waiting events are no longer related to full table scans,
Instead, it is a log file parallel write event that reflects a problem with redo log writing capabilities.
SQL> @cr_base.sql

SQL> @in_base.sql

Execute user application... After some time

SQL> @re_base.sql

Wait Event Time Waited %Time w Waits %Waited

----------------------------------- ----------- ---- --- -------- -------
log file parallel write 23.350 89.70 2164 96.78
free buffer waits 1.020 3.92 1 0.04
db file sequential read 0.390 1.50 14 0.63
latch free 0.080 0.31 5 0.22
log file sync 0.040 0.15 1 0.04
db file parallel write 0.000 0.00 23 1.03
direct path write 0.000 0.00 1 0.04
LGWR wait for redo copy 0.000 0.00 0 0.00
 buffer busy waits 0.000 0.00 0 0.00
buffer deadlock 0.000 0.00 0 0.00
db file scattered read 0.000 0.00 0 0.00
db file single write 0.000 0.00 0 0.00
direct path read 0.000 0.00 0 0.00
enqueue 0.000 0.00 0 0.00
library cache load lock 0.000 0.00 0 0.00
library cache pin 0.000 0.00 0 0.00
log buffer space 0.000 0.00 0 0.00
log file sequential read 0.000 0.00 0 0.00
log file single write 0.000 0.00 0 0.00
log file switch completion 0.000 0.00 0 0.00
process startup 0.000 0.00 0 0.00
undo segment extension 0.000 0.00 0 0.00
write complete waits 0.000 0.00 0 0.00

23 rows selected.

Figure 5-6 Main wait event

In order to understand the redo log writing capability of the current database, continue to check the current redo log buffer size, log group

56 Oracle Database Performance Optimization

number and size of group members. The size of the log buffer is 1MB, which is a relatively reasonable size, and generally does not cause waiting events, which is t
The redo log wait event is irrelevant. However, it is found here that there are only two redo log groups, and the file size of each group member is only 500KB.
Is this what caused the wait?
Analyze carefully, what is the log file parallel write waiting event related to? Looking at the previous description of the event, this
Waiting for an event means waiting for LGWR to ask the operating system for I/O to start until the I/O is complete. The occurrence of this event usually indicates th
The file is I/O-contended or the drive on which the file resides is slow. This shows that this kind of waiting and log switching and checkpoint execution are not
relationship, but directly reflects the writing ability of LGWR, so even if the number of log file groups is too small and the file size is too small, it is not the same as t
Events are not directly related, so increasing the number of log groups and log file size will not help solve the current performance problem.
So how to solve this problem now? The direct cause of this problem is mainly related to the writing ability of LGWR, but the simple
The writing ability of the LGWR process cannot be improved by multiple writing processes like the DBWR process, so it should be considered at this time
It is about how to make the amount of logs written not exceed the current LGWR writing capacity under the premise of a single LGWR process. This can be obtaine
On the one hand, it is necessary to consider whether too many meaningless redo logs are generated in the application, resulting in too much log generation,
As a result, the amount of log generation exceeds the write capacity of LGWR. If so, consider limiting the redo log by some means
generation. On the other hand, if the amount of log generation is determined, how to make the LGWR process write logs more efficiently
How much faster, this mainly depends on two aspects, one is whether I/O competition occurs when LGWR writes the log, and the other is
Is the speed of the disk where the redo log files are located too low? If it is caused by competition, move the redo log files to other disks
On the other hand, if it is caused by the disk speed, then choose a high-speed disk to store redo logs.
Through the previous analysis, it is found that the main waiting event is related to the writing ability of LGWR, and the redo log written by LGWR
are all generated by the DML statement executed by the user, so now it is time to further analyze and figure out which sessions the problem is in
Which SQL statement was executed caused it.
First of all, you need to find out which sessions have generated a large number of log file parallel write waiting events. In order to find the answer, you need
Check out some other session-related dynamic performance views, session-level views include v$session_event and v$session_wait,
Of course, since what we are looking for is a session that currently has a large number of log file parallel write waiting events, the view that is really needed
It should be the v$session_wait view that reflects the waiting information of the current session. Through this view, you can find out which sessions are leading
due to this wait.
Here, v$session_wait will be queried to get those that generate the most log file parallel write waiting events
session. Of course, I said earlier that the P1, P2, and P3 fields in this view are all very useful fields, but in this case,
For the log file parallel write event, these fields are not used. The query shown in Figure 5-7 shows the following
These 4 sessions generated a large number of log file parallel write waiting events.
SQL> @seswt.sql log SID Wait Event Wait Stat W'd So Far (secs) Time W'd (secs)

---- --------------------------- -------- ----------- ------ --------------- 13 log file parallel write WAITING 0 0 97 log file parallel write WAITING 0 0

4 rows selected.
Figure 5-7 A session with a major wait event

Now we have found which sessions are the main source of log file parallel write waiting, but we need to further define

Chapter 5 Detecting Performance Bottlenecks Using Oracle's Wait Events 57

bit to find out which statements are causing the wait. Find the executed SQL statements in the system generally through v$sql_area, from this view
In the figure, you can find all the SQL statements placed in the shared pool since the system started, as shown in Figure 5-8, according to the most resource-consu
The sequential order of these statements is shown.
SQL> @sqls1.sql 1000 1000

Stmt Addr Disk Rds Buff Gets Sorts Runs Body Loads
-------------- ------------ ------------ ------- ----- ------------
2188ED68 276,390,597 278,896,280 1 65,628 1
2187A230 42,435 42,315,278 11,021,345 1
21668D10 189,628 576,659 0 55 1
216DD948 92,147 1,054,969 0 3,267 1
2172F948 3,321 2,424,735 0 377,248 1
219BB408 15,231 70,876 0 48 1
2168C058 4,949 4,373 2 1 1
2168CBB8 4,295 4,387 1 1 1
216978EC 4,245 4,253 1 1 1
2181D5E8 4,179 4,249 1 1 1
2164CBB4 2,862 9,908 1 1 1
2198901C 1,611 91,968 0 48 1
...

1000 rows selected.

Figure 5-8 Sort in order of resource consumption

What needs to be found now are those SQL statements that cause a large number of log file parallel write waiting events, so the above query
The query is useless to us, so how do we get the sentences we care about? Those products that have been obtained before
The session information of the waiting event, then through the associated query of these session information and the v$sqltext view, you can get the relevant
The SQL statement. Randomly find one of the above 4 sessions, and see which statement executed by this session brings a lot of logs
file parallel write waits. For example, take session 13 and execute the following query to find the SQL statement executed by this session
address information, as shown in Figure 5-9.
SQL> @Sqlst.sql 13 SID User Nam CPU ( sec ) IO Read ( k ) IO Write ( k ) SQL Address

----- -------- -------- ----------- ------------ ------ ----- 13 WEBBER 0 11 5120 2168CBB8

1 rows selected.
Figure 5-9 Address information of the SQL statement

Through the previous query, the address of the relevant SQL statement is found to be 2168CBB8, and the address of this statement can be found in v$sqltext
Query in, and find this SQL statement, as shown in Figure 5-10.
The located statement is an update statement, so a large amount of redo log information will be generated. And this statement compares
The special place is to_char (STATUS) in the where condition. It is generally known that where with functions or expressions
statement, the index on the field will not be used, so the statement executed here will perform a full table scan, which also solves the
Explain why there are a lot of db file scattered read waiting.

58 Oracle Database Performance Optimization

SQL> @sqls2.sql 2168CBB8

SQL Statement Text

-------------------------------------------------- ---------------
UPDATE HITS SET STATUS=:b1 WHERE to_char ( STATUS ) = :b2

1 row selected.
Figure 5-10 Find the SQL statement

Now the cause of the problem seems to have been found, but is it really over? Let’s review what we just mentioned
The main reason for the log file parallel write waiting event: the occurrence of this event usually indicates that the log file has I/O competition or
The drive where the file is located is slow, which may also mean that too many meaningless redo logs are generated in the application, resulting in too much log ge
big. So what is the real reason?
If collecting and analyzing data is limited to these dynamic performance views of the database, the real reason may not be true.
It is found that, in fact, when doing optimization work, the information of the operating system is also a very important kind of analysis data. then come on
See which information of the operating system is worthy of attention, and how should the DBA use the information of the operating system to analyze performanc
cause of the bottleneck.
Regarding the tools for collecting operating systems, system administrators should actually be more familiar with them, and what a DBA needs to master is ju
Some tools with relatively large help. The performance information that the DBA needs to care about mainly includes CPU, I/O, memory, and SWAP exchange.
On UNIX systems, commonly used tools for collecting such information include Top, Sar, iostat, vmstat, etc. In this case, the
Collect it through vmstat, and use the collected information to find the real cause of log file parallel write, etc.
The reason for the event to occur.
As shown in Figure 5-11, the information collected by using vmstat is not discussed in detail. It can be seen here that the system
There is still a lot of CPU in idle (the "id" column is about 80%), but about the blocking queue (the "b" column is the queue caused by I/O)
However, there are many, which means that I/O blocking has occurred at the operating system level, and this kind of system-level I/O blocking causes log
The real reason for the file parallel write wait event happens.
$ vmstat 10 10
 … procs memory swap io system cpu rbw swpd free buff cache si so bi bo in cs us sy id 2 0 0 44736 1480 4300 23972 0 27 124 80 1729 204 17 7 76 0 2 0 44736

...

Figure 5-11 Information collected by vmstat

Chapter 5 Detecting Performance Bottlenecks Using Oracle's Wait Events 59

5.5 Summary

Through the above analysis, we can see that there are two obvious waiting events in the system, one is the db related to the full table scan
File scattered read waits for events, which occur a lot throughout the system; the other is log file parallel write
Waiting for events, which are currently the main performance bottleneck. Further analysis found that some SQL statements in the session produced
A large amount of redo log information, which led to the occurrence of this waiting event. Through the analysis of SQL statements and operating system informatio
Finally, the following conclusions are obtained: Understand the application requirements and determine why the to_char function should be used to block the use o

do. Consider using NOLOGGING on the index to reduce the redo logs generated by the index maintenance corresponding to the data maintenance in the table. Red

on the disk. In the long run, increase the number of redo log file groups and the size of group member files to prevent other redo log related

Wait for the event to happen.

5.6 Appendix syssum.sql

SELECT EVENT "Wait Event", TIME_WAITED "Time Waited", TIME_WAITED / ( SELECT SUM ( TIME_WAITS ) FROM v$system_event ) "%Time waited", TOTAL_WAITS "Waits",

from v$system_event
order by 3 desc ; cr_base.sql

create table sys_b (

EVENT VARCHAR2 (64),
TIME_WAITED NUMBER,
TOTAL_WAITS NUMBER, );

create table sys_e (

EVENT VARCHAR2 (64),
TIME_WAITED NUMBER,
TOTAL_WAITS NUMBER, ); in_base.sql

insert into sys_b

select EVENT, TIME_WAITED, TOTAL_WAITS
from v$system_event; re_base.sql

60 Oracle Database Performance Optimization

insert into sys_e
 select EVENT, TIME_WAITED, TOTAL_WAITS
from v$system_event;

create table sys_dif

as
select e. EVENT e. TIME_WAITED - b. TIME_WAITED TIME_WAITED, e. TOTAL_WAITS - b. TOTAL_WAITS TOTAL_WAITS

from sys_b b , sys_e e

where b. EVENT = e. EVENT;;

SELECT EVENT "Wait Event", TIME_WAITED "Time Waited", TIME_WAITED / ( SELECT SUM ( TIME_WAITED ) FROM sys_dif ) "%Time waited", TOTAL_WAITS "Waits", TOTAL

from sys_dif
order by 3 desc;

drop table sys_dif;

drop table sys_d;
drop table sys_e;seswa.sql

select EVENT "Wait Event", count ( SECONDS_IN_WAIT ) "Waited So Far ( sec ) ", count ( SID ) "Num Sess Waiting"

from v$session_wait
group by EVENT;seswt.sql

SELECT SID, EVENT "Wait Event", STATE "Wait Stat", WAIT_TIME "W'd So Far (secs)", SECONDS_IN_WAIT "Time W'd (secs)"

FROM v$session_wait
WHERE EVENT LIKE '&a&'
ORDER BY 5; sqls1.sql

SELECT * FROM ( SELECT ADDRESS "Stmt Addr", DISK_READS "Disk Rds", BUFFER_GETS "Buff Gets", SORTS "Sorts", EXECUTIONS "Runs", LOADS "Body Loads" FROM V$SQ

Chapter 5 Using Oracle's Wait Events to Detect Performance Bottlenecks 61 ( select a.sid sid, a.username username

About the Author

Ora-600, currently the moderator of ITPUB OCP section, is good at Oracle DBA technology, demand analysis, system analysis and
Design, data modeling, code development, etc., 8i/9i OCP certification winner.
Participated in Land and Resources Land Management System, Land Consolidation Budget System, and Beijing Public Transport Card Project of the
Customer service system and ED card management system, business office automation system and power MIS system of Wuwei Electric Power Bureau,
Northwest Electric Power Design Institute business office automation system and other projects, responsible for Oracle database background design, pla
Development of reporting system, training and other work, has taught Oracle OCP courses in many training centers in Beijing, and
Provide technical services and support for customers in many industries across the country.