Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the common waiting events in ORACLE". In the daily operation, I believe many people have doubts about the common waiting events in ORACLE. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "what are the common waiting events in ORACLE?" Next, please follow the editor to study!

1 、 db file sequential read

The wait time for reading data into contiguous memory is caused by performing SQL statements (user and recursion) that perform single-block read operations on indexes, rollback (undo) segments, and tables (when accessed through rowid), and control file and data file headers.

The optimization method of db file sequential read:

From reading, increase the size of the buffer cache in SGA to avoid reading from the hard drive every time.

Optimize sql statements to reduce unnecessary block reads

Db file scattered read

Db file scattered read issues discrete reads, which read consecutive blocks of data on storage into multiple discontiguous memory locations. This event indicates that the user process is reading the data into the Buffer Cache and waiting until the wait caused by the return of the I _ swap O call.

2. Direct path read/write (direct path read / write):

Direct path reading (direct path read) usually occurs when Oracle reads data directly to the process PGA, which does not need to go through SGA.

DB file Sequential ReadDB file Scattered ReadDirect Path Read

This type of read is usually used in the following situations:

1. Disk sort IO operation

two。 Parallel query slave process

3. Pre-read operation.

Direct path writing (direct path write) usually occurs when Oracle writes data directly from PGA to data files or temporary files, which can bypass SGA.

Such write operations are typically used in the following situations:

1. Direct path loading

two。 Parallel DML operation

3. Disk sorting

Optimization method: 1. Add pga_aggregate_target 2. Parallel queries cause performance problems and modify parallelism

3. Buffer busy waits-hot block

The generation of this wait event only indicates that a session is waiting for a Buffer (data block), which occurs when multiple users frequently read or modify the same data block. If the waiting time is long, we can see it in the AWR or statspack report.

This wait event has three parameters. To see how many parameters we can use the following SQL:

SQL > select name,parameter1,parameter2,parameter3,wait_class

From v$event_name

Where name='direct path write'

P1 P2 P3 represents file number, starting block number, number of blocks

The solutions to hot block are:

1. This situation may be adjusted in several ways: increasing the data buffer

2. The purpose of increasing freelist and decreasing pctused; is to reduce the space available on a block, so that less data is stored on a block, which can improve the access concurrency rate of the application and reduce the generation of hot block.

3. Increase the number of rollback segments, increase initrans, consider using LMT, and confirm whether it is caused by hot blocks (if you can use inverted indexes, or smaller block sizes)

3. You can set up a tablespace with smaller block. See hotspot objects move to this tablespace.

4. Optimize the application, optimize the index and improve the hit rate of the index.

The ◎ Oracle session is waiting to be pinned to a buffer. The buffer must be pinned before it is read or modified. Only one process can be pinned to a buffer at any one time.

◎ buffer busy waits indicates read / read, read / write, write / write contention.

The appropriate action taken by ◎ depends on the reason code in the P3 parameter.

A. if the wait is at the head of the field, increase the number of groups in the free list (freelist), or increase the distance between pctused and pctfree.

B. if you are waiting for a undo header block, you can add a rollback segment (rollback segment) to solve the buffer problem.

C. If you are waiting on a non-header block in the fallback segment (undo), you need to reduce the data density in the table that drives consistent reads, or increase the DB_CACHE_SIZE.

D. if you are waiting in a block, you can move the data to another block to avoid the "hot" block, add a free list in the table, or use the LMT tablespace

E. if you are waiting in an index block, you should rebuild the index, split the index, or use the reverse key index.

4 、 log file sync

The wait time occurs during redo log writes from log buffer to log file.

After the user issues a commit or rollback declaration, the submit command will do log synchronization, that is, write the log to the log file, and the user will see the wait event before the commit command is completed.

Solution:

When the log file sync wait occurs, you can determine whether it is caused by the slow log Imax O, and you can check the waiting time of the two waiting events. If it is relatively close, it proves that the log Imax O is slow or there are too many redo logs. In this case, the cause of log file sync is because of log file parallel write.

If the wait time for log file sync is high, and the wait time for log file parallel write is not high, it means that the reason for log file sync is not slow log I and O, but too many submissions by users.

When the waiting time of log file sync is basically the same as that of log file parallel write, it means that the log file sync wait event is caused by IO problems.

5. Log File Switch wait event

When this wait occurs, it means that all commit requests need to wait for the "log file switch" to be completed.

Log file Switch consists of two main sub-events:

Log file switch (archiving needed)

Log file switch (checkpoint incomplete)

Where log file switch (archiving needed)

This wait event usually occurs because the first log archive has not been completed after the log group cycle is full. The wait may indicate that there is a problem with io. Solution:

You can consider increasing log files and adding log groups

Move archive files to fast disk

Adjust log_archive_max_processes

While log file switch (checkpoint incomplete)-log switch (checkpoint not completed)

When your log group is full, LGWR tries to write the first log file, and if the database does not finish writing out the dirty block recorded in the first log file (for example, the first checkpoint is not completed), wait for the event to occur.

This wait event usually indicates that your DBWR is too slow to write or that there is a problem with your IO.

To solve this problem, you may need to consider adding additional DBWR or increasing your log group or log file size.

6 、 log buffer space

This wait occurs when log buffers (log buffer) generate redo logs faster than LGWR writes, or when log switching (log switch) is too slow. When this wait occurs, it usually indicates that the redo log buffer is too small. To solve this problem, consider increasing the size of the log file or increasing the size of the log buffer.

Another possible reason is that there is a bottleneck in disk Ipool O, so you can consider using a disk that writes faster. Under permissible conditions, you can consider using bare devices to store log files to improve writing efficiency. In a general system, the lowest standard is not to store log files and data files together, because log files are usually written but not read, and separate storage can improve performance.

At this point, the study of "what are the common waiting events in ORACLE" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.