Shulou(Shulou.com)06/01 Report--

Oracle interview Treasure Book-waiting events

What is the role of waiting events in the Oracle database?

Waiting for the origin of the event

Because of the development of the index system, it leads to the introduction of waiting events. To sum up, the index system of Oracle has roughly gone through the following three stages:

(1) take the hit rate as the main reference index.

Take various hit rates as the main basis for optimizing the entrance, the common ones are "library cache hit radio" and so on. But this approach has great drawbacks. A system with a hit rate of 99% is not necessarily better than 95% of the system. In the old version of Oracle, this approach is often used, such as 8i, 9i and so on.

Hit rate is a point of view of system performance optimization nearly 20 years ago, and later this view has been proved to have a certain deviation and gradually abandoned by peers, can not be said to be completely wrong, hit rate can only be said to be a reference index.

Today, 20 years later, the theory, practice and means of performance optimization have been greatly accumulated and developed compared with before. Now, for the analysis and diagnosis of performance problems, we pay more attention to the comprehensive analysis of all aspects of information, rather than simply looking at an index. More typical, for example, the comprehensive performance of the system can be analyzed and determined by viewing various reports such as ash, awr, addm or osw. The performance of a single SQL statement is also mainly combined with specific SQL, execution plan and data environment for analysis. There are many ways to obtain the implementation plan, but it is more or less the same in nature.

As for the ideas and methods of performance analysis and diagnosis, it is generally from the whole to the local, gradually refined methods and steps.

(2) take waiting events as the main reference index.

Take various waiting events as the basis for optimizing the entrance, the common ones are "db file sequential read" and so on. It is more intuitive to understand that the database has mainly experienced those waits over a period of time. These "bottlenecks" are often the starting point of our optimization. In 10g and 11g versions, it is widely used.

(3) taking the time model as the main reference index.

Take the overall consumption of various resources as the basis for optimizing the entrance. You can understand the consumption of the database over a period of time from an overall point of view. It is more general than the way of waiting for an event. Common ones such as "DB Time". Oracle is constantly strengthening its work in this area.

As can be seen from the above three stages, the introduction of waiting events is precisely to solve many disadvantages of taking the hit rate as the index. Compared with the later time model, waiting events observe the behavior of Oracle in a more intuitive and fine-grained way, which is often used as an important entrance for optimization. The time model focuses more on the overall and systematic understanding of the running state of the database. The emphasis of the two is different.

What types of wait events are there in the Oracle database?

Waiting events can be divided into two parts: idle and non-idle. In non-idle waiting events, it can be further divided into detailed categories.

Idle waiting:

Idle wait event means that Oracle is waiting for some kind of work, such as logging in with sqlplus, but no further command is issued. At this time, the session is in the state of SQL*Net message from/to client waiting for the event, waiting for the user to issue a command. Generally, you don't have to pay too much attention to this part of the event when diagnosing and optimizing the database.

Not idle waiting:

Non-idle wait events, which are specifically aimed at Oracle activities, refer to the waits that occur when database tasks or applications are running. These wait events should be paid attention to and studied when adjusting the database.

Wait event classification description:

Select wait_class, wait_class_id, count (*)

From v$event_name

Group by wait_class, wait_class_id

Order by 1

-Database version Oracle 19C, with 1920 waiting events (872 waiting events in Oracle 10g and 1116 waiting events in 11g).

Management Class-Administrative

Such wait events are caused by DBA's administrative commands, which require the user to wait (for example, to rebuild the index).

For example:

Application Class-Application

Such wait events are caused by the code of the user's application (for example, lock waiting).

For example:

Cluster class-Cluster

Such wait events are related to Oracle RAC resources (for example, gc cr block busy wait events).

For example:

Submit confirmation class-Commit

Such wait events contain only one type of wait event-- waiting for a redo log write confirmation after a commit command has been executed.

For example:

Concurrent class-Concurrency

Such wait events are caused by internal database resources (such as latches).

For example:

Configuration class-Configuration

Such wait events are caused by improper configuration of the database or instance (for example, the size of the redo log file is too small, the size of the shared pool, etc.).

For example:

Free class-Idle

Such wait events mean that the session is inactive and waiting for work (for example, sql * net messages from client).

For example:

Network class-Network

Some wait events related to the network environment (such as sql* net more data to dblink).

For example:

Other classes-Other

Such wait events are usually rare.

For example:

Scheduling class-Scheduler

Such wait events are related to resource management.

For example:

System I-System O class-system I-map O

Such wait events are caused by the background process's I _ db file paralle write O operation (such as DBWR wait-wait).

For example:

User iMab O class-User iMaple O

This type of wait event is usually caused by the user's Iamp O operation (such as db file sequential read).

For example:

Please describe the 10 waiting events that you often encounter?

One: buffer busy wait

Type: concurrent class

Cause of occurrence:

When a session reads blocks of data from disk into memory, it needs to find free memory space in memory to store those blocks, and this wait occurs when there is no free space in memory. In addition, there is another situation where the session needs to construct a pre-image of the data block at some point when doing a consistent read. At this point, you need to apply for memory blocks to hold these newly constructed data blocks, and this wait event will occur if such memory blocks cannot be found in memory.

Direction of optimization:

The direction of optimization varies depending on the category in which this wait event is generated.

How do I find out the object and object type that generated this wait event?

Query the ROW_WAIT_OBJ# value in V$SESSION when buffer busy waits appears. For example:

SELECT row_wait_obj# FROM V$SESSION WHERE EVENT = 'buffer busy waits'

To identify contentious objects and object types, you can query DBA_OBJECTS using the value of ROW_WAIT_OBJ# returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type

FROM DBA_OBJECTS

WHERE data_object_id = & row_wait_obj

Or find the corresponding block number, file number and type through SID

Select event, sid, p1, p2, p3

From v$session_wait

Where sid in (69,75)

And event like'% buffer busy waits%'

-

P1: File ID

P2: Block ID

P3: Class ID

The p1, p2 parameters and dba_extents are jointly queried to get the segment name and segment type where block is located.

Object types: data block

One or some data blocks are read and written by multiple processes at the same time and become hot chunks. This problem can be solved by the following ways:

(1) reduce the concurrency of the program. If the parallel query is used in the program, reduce the parallel degree, so as to prevent multiple parallel slave from accessing the same data object at the same time, resulting in waiting to degrade performance.

(2) adjust the application so that it can read fewer data blocks to get the required data, reducing buffer gets and physical reads

(3) reduce the number of records in the same block so that records are distributed in more data blocks, which can be achieved in several ways: you can adjust the pctfree value of segment objects, you can rebuild segment into table spaces with smaller block size, and you can use alter table minimize records_per_block statements to reduce the number of records in each block.

(4) if the hotspot block object is an index similar to the self-increasing id field, the index can be converted into an inverted index to break up the data distribution and disperse the hotspot blocks.

Optimization direction: the general optimization direction is to optimize SQL to reduce logical and physical reads, or to reduce the size of single-block storage data.

Object types: segment header

Processes usually visit data segment header frequently for two reasons

(1) obtain or modify process freelists information

Frequent access to process freelists information by a process leads to freelist contention. We can increase the storage parameters freelist or freelist groups of the corresponding segment object. If the process frequently modifies freelist due to frequent data blocks entering and leaving the freelist, we can set a large gap between the pctfree value and the pctused value, so as to prevent data blocks from entering and leaving the freelist frequently.

(2) extended high water mark

Because the segment consumes very fast space, and the set next extent is too small, the high water mark is frequently extended. The solution is to increase the storage parameter next extent of the segment object or directly set extent size uniform when creating the tablespace.

Optimization direction: add FREELISTS and FREELIST GROUPS. Make sure the gap between FCTFREE and PCTUSED is not too small, so that the block loop of FREELIST can be minimized.

Object types: undo block

Undo block contention is due to the fact that the data is read and written at the same time in the application, and the reading process needs to go to undo segment to obtain consistent data. The solution is to stagger the time for the application to modify data and query a large number of data.

Direction of optimization: applications, off-peak use of data objects.

Object type: undo segment header

The reason for undo segment header contention is that there is not enough undo segment in the system, and enough undo segment needs to be added. According to the management method of undo segment, if you are in manual management mode, you need to modify rollback_segments initialization parameters to increase rollback segment. If you are in automatic management mode, you can reduce the value of transactions_per_rollback_segment initialization parameters to make oracle automatically increase the number of rollback segment.

Direction of optimization: if the database system manages the UNDO segment, there is generally no need for intervention. If it is self-managed, you can reduce the number of transactions per rollback segment.

Two: db file sequential read

Type: user Imax O class

Cause of occurrence: the db file sequential read event is related to Single Block IMaple O.

The wait event reads data to contiguous memory (in this case, to connected memory, not to contiguous blocks of data). In most cases, reading an index block or reading a data block through an index records the wait. It may show that the join order of the table is poor, or indexed indiscriminately. For a large number of transactions, well-adjusted systems, this value is mostly normal, but in some cases, it may imply a problem in the system. This wait statistic should be linked to known problems in the performance report, such as the less efficient SQL. Check index scans to ensure that each scan is necessary and check the join order of multiple table joins.

Parameter meaning:

File#: represents the absolute file number of the file to be read by oracle

Block#: the starting block number read from this file

Blocks: the number of block read. Usually 1, which represents a single block read.

Direction of optimization:

This waiting incident does not necessarily mean that there is something wrong with it. If you can determine that there is a problem, you can follow the following optimization ideas.

1 modify the application to avoid a large number of IO sql, or reduce its frequency or optimize SQL.

(2) increase the data buffer and improve the hit rate.

(3) adopt a better disk subsystem to reduce the response time of a single IO and prevent physical bottlenecks.

Three: db file scattered read

Type: user Imax O class

Cause: when Oracle performs full table scan (Full Table Scan,FTS) and index fast full scan (Index Fast Full Scan), in order to ensure performance, try to read multiple blocks at one time, which is called Multi Block Imax O. Each time you execute Multi Block iCandle O, it waits for the end of the physical iUnip O and the db file scattered read event at this time. Here scattered refers to how the read data blocks are stored in memory. After they are read into memory, they are stored in memory in a decentralized manner rather than contiguously.

Parameter meaning:

File#: represents the absolute file number of the file to be read by oracle.

Block#: the starting block number read from this file.

Blocks: the number of block read.

Direction of optimization:

This situation usually shows the wait associated with a full table scan. When full table scans are limited to memory, they rarely enter contiguous buffers, but are scattered throughout the buffer memory. If this number is large, it means that no indexes can be found for the table, or only a limited number of indexes can be found. Although performing full table scans may be more efficient than index scans under certain conditions, if such a wait occurs, it is best to check whether these full table scans are necessary. If it is caused by some SQL, such as inaccurate statistics, no index, or the use of inefficient indexes, you can reduce the db file scattered read by optimizing SQL.

Four: direct path read

Type: user Imax O class

Cause of occurrence:

This wait event occurs when the session reads the data block directly into the PGA rather than the SGA, which is usually private to the session, so it doesn't need to be put into the SGA as shared data, because it doesn't make sense. These data usually come from temporary data, such as the sorted data of SQL in a session, the data generated during parallel execution, and the sorted data generated by Hash join and Merge join. Because these data are only meaningful to the SQL operation of the current session, they do not need to be put into SGA. When a direct path read wait event occurs, it means that a large amount of temporary data is generated on disk, such as sorting, parallel execution, and so on, or that there is not enough free space in the PGA.

In 11g, a full table scan may use direct path read mode, bypassing buffer cache, so a full table scan is a physical read. In 10g, it is all read through buffer cache, so there is no problem with direct path read.

Parameter meaning:

File#: file number

First block#: the starting block number of the read

Block count: the number of physical blocks read continuously starting with first block

Direction of optimization:

With this wait event, several situations need to be distinguished. One direction is to increase the sort area and other means, and the other is to reduce the amount of IO read or to determine whether to read through the buffer is more efficient.

Possible problems with direct path read:

A new feature in Oracle 11g is that in order to protect data that is already cached in buffer cache, the size of the table is determined when a full table scan query appears. If the table is too large, use direct path reading (Direct Path Read) to get the data. Avoid the impact of a large number of cold data on Buffer Cache. Bypass SGA by direct path reading to get data from storage. Because there is no cache of SGA, each query needs to be read from the storage, resulting in a large number of physical reads, which may cause the load of ISGA to be too high.

How to determine the size of the full table scan in the new features?

Let's look at an implied parameter: _ small_table_threshold

This parameter defaults to 2% of Buffer Cache, which is triggered if the table is greater than 5 times _ small_table_threshold. DPR is automatically used instead of FTS.

You can return to the mode before Oracle 11g by setting the 10949 event mask feature:

Alter session set events' 10949 trace name context forever, level 1'

The small table is affected by the implicit parameter: _ small_table_threshold. If the table is greater than 5 times the small table limit, DPR is automatically used instead of FTS. You can set the initialization parameter: _ serial_direct_read to disable serial direct path reading.

Five: db file single write

Type: user Imax O class

Cause: in one case, this wait event occurs when Oracle updates the header information of the data file (such as when a CheckPoint occurs). Consider that the number of data files in the database is so large that Oracle takes a long time to do all the header update operations (CheckPoint).

This wait event contains three parameters:

File#: the file number of the data file where the data block is to be read.

Block#: the starting block number of the read.

Blocks: the number of blocks to read. (generally speaking, it should be equal to 1 here)

Six: direct path write

Type: user Imax O class

Cause: this wait event is the opposite of direct path read, which occurs when oracle writes data directly from PGA to data files or temporary files, which can bypass SGA. Operations that can perform direct path writes include disk sorting, parallel DML operations, direct path insertion, parallel create table as select operations, and some LOB operations. In this case, you should find the data file that operates most frequently (if it is sorting, it is likely to be a temporary file) and spread the load.

Parameter meaning:

File#: file number

First block#: the starting block number of the read

Block count: the number of physical blocks written continuously starting with first block

Direction of optimization: reduce the size of IO writes.

Seven: log file sync

Type: submit class

Cause of occurrence:

This is a wait event caused by the user's session behavior. When a session issues a commit command, the LGWR process writes the redo log generated by the transaction from redo log buffer to redo log file disk to ensure that the information submitted by the user is safely recorded in the database. After the session issues the commit instruction, you need to wait for LGWR to successfully write the redo generated by the transaction to disk before you can proceed with the subsequent operation. This wait event is called log file sync. When there are a large number of log file sync waiting events in the system, you should check whether there are users in the database who are doing frequent commit operations. This wait event usually occurs on OLTP systems. There are many small transactions in OLTP system. If these transactions are committed frequently, it may cause a large number of log file sync waiting events.

Direction of optimization:

The following optimization recommendations help reduce log file sync waiting:

(1) optimize LGWR speed to achieve good disk throughput. For example: do not put redo log file on RAID 5 (you can consider RAID 0 or RAID 1: 0)

(2) if there are a large number of small things, it is best to submit them in batches and reduce the number of submissions.

(3) NOLOGGING / UNRECOVERABLE options can be considered for specific scenarios (use with caution)

(4) ensure that the redolog is large enough to ensure that the log switching interval is 15-20 minutes.

(5) use the stable version database to avoid bug. Refer to the version reference documentation for specific bug repair.

(6) in version 11.2.0.3, Oracle enables the _ use_adaptive_log_file_sync parameter by default, which allows the LGWR process to automatically choose between post/wait and polling, which may lead to serious log write waiting (the aPCge single wait time for log file sync is higher). It is recommended to disable this feature.

Reference command: alter system set "_ use_adaptive_log_file_sync" = FALSE

Eight: Log File Parallel Write

Type: system Ihamo

Cause of occurrence:

1. Log File Sync is the time from the start of the submission to the end of the submission. Log File Parallel Write is the time when LGWR starts writing Redo File and ends Redo File writing. With this clear, you can see that Log file sync contains log file parallel write. Therefore, as soon as the log file sync waiting time is out, you must first watch the log file parallel write. If the average wait time for log file sync (also known as commit response time) is 20ms and log file parallel write is 19ms, then the problem is obvious. Redo file I response O is slow, slowing down the commit process. 2. The time of Log File Sync is not just log file parallel write. The server process starts to submit, and when it notifies LGWR to write Redo, and when LGWR finishes writing Redo, the notification process is completed, and back-and-forth notification also consumes CPU. In addition to back-and-forth notification, Commit also has operations such as adding SCN, etc. If there is a large gap between log file sync and log file parallel write, it proves that there is no problem with CPU O, but it may be due to the shortage of CPU resources, resulting in insufficient CPU for back-and-forth notification of processes and LGWR or other operations that require CPU, resulting in delays.

Optimization direction: consider how to write no more logs than the current LGWR write capacity on the premise of a single LGWR process. This can be considered in two ways:

1: consider whether too many meaningless redo logs are generated in the application, resulting in too much log production, so that the log production exceeds the write capacity of LGWR. If so, consider some ways to limit the generation of redo logs.

2: consider how to enable the LGWR process to write more and faster logs if the log generation is determined, which mainly depends on two aspects: one is whether the LGWR has a competition for Imax O when writing the log, and the other is whether the disk speed of the redo log file is too low. If it is caused by the competition, move the redo log file to another disk, if it is caused by the disk speed. Then select a high-speed disk to store the redo log.

Nine: library cache lock

Type: concurrent class

Cause of occurrence:

This wait event occurs when different users compete for resources in the shared pool due to concurrent operations on the same database object. For example, when a user is doing a DDL operation on a table, if other users want to access the table, a library cache lock wait event will occur, and it will wait until the DDL operation is completed before continuing the operation.

Parameter meaning:

Handle address: the address of the object being loaded.

Lock address: the address of the lock.

Mode: the data fragment of the loaded object.

Namespace: the name of the namespace of the loaded object in the v$db_object_cache view.

Direction of optimization: the direction of optimization is to view locked objects and reduce contention.

Ten: SQL*Net Events

Type:

Application class:

SQL*Net break/reset to client

If the running code contains a possible error that is triggered during the call, the server-side local service process has an obligation to inform the remote client of this information, and the service process is waiting for SQL*Net break/reset to client during this notification until the client receives the problem message.

SQL*Net break/reset to dblink

This wait event is basically the same as the SQL*Net more data to client wait event, except that the wait occurs in a distributed transaction, that is, the local database needs to send more data to the remote database over dblink. SQL*Net more data to dblink wait events occur due to too much data being sent or network performance problems.

Free class:

SQL*Net vector message from dblink

SQL*Net vector message from client

SQL*Net message from client

Indicates that the server is waiting for the Cilent to send a request for it to process, and a SQL*Net message from client wait event occurs.

Network class:

SQL*Net more data from dblink

SQL*Net vector data to client

SQL*Net vector data from client

SQL*Net vector data to dblink

SQL*Net vector data from dblink

SQL*Net message from dblink

SQL*Net more data from client

The server waits for the client to send more data in order to complete the operation, such as a large SQL text, resulting in a SQL*Net packet cannot be transmitted, so the server will wait for the client to send the whole SQL text for processing.

SQL*Net more data to dblink

This wait event is basically the same as the SQL*Net more data to client wait event, except that the wait occurs in a distributed transaction, that is, the local database needs to send more data to the remote database through dblink. Waiting due to too much data sent or network performance problems.

SQL*Net more data to client

This shows that the database is constantly sending too much data to the client. If the network condition is not good, or the network traffic is too large, it may cause this wait to be very significant.

SQL*Net message to client

This wait event occurs when the server sends a message or data to the client, which usually means a network bottleneck or incorrect TCP connection configuration. Of course, it cannot be used as an accurate assessment or quantification of network delay.

SQL*Net message to dblink

This wait event occurs when the session is waiting for a confirmation message from a remote database to confirm whether the data sent by the remote database has been received. The data is sent through dblink, usually because the target server can not receive the information in time.

Reference:

Https://dbaplus.cn/news-10-777-1.html

Http://www.itpub.net/thread-2102514-1-1.html

Http://www.askmaclean.com/archives/db-file-sequential-read-wait-event.html

Http://www.itpub.net/thread-1777234-1-1.html

Https://www.linuxidc.com/Linux/2015-09/122732.htm

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

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.