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

This article introduces the relevant knowledge of "how to understand the classification, discovery and optimization of Oracle waiting events". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Waiting for the origin of the event

Members may be wondering why we talked about the index system first when we talked about the waiting event. In fact, it is the development of the index system that leads to the introduction of waiting events. To sum up, the index system of Oracle has roughly gone through the following three stages:

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.

Take the waiting event 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.

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.

Second, waiting event classification

Let's start with the classification of waiting events and understand the waiting events. From a large classification, 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.

You can observe the number and approximate classification of wait events contained in the system by using the following methods (the following statements run in an 11g environment).

Wait events where WAIT_CLASS is "Idle" are idle, while others are non-idle wait events.

1. Distinction-idle and non-idle wait events

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.

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.

two。 Wait for event classification description

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).

Application Class-Application

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

Cluster class-Cluster

Such wait events are related to the resources that actually apply the cluster RAC (for example, gc cr block busy wait events).

Submit confirmation class-Commit

Such wait events contain only one type of wait event-- waiting for a redo log write confirmation (that is, log file sync) after a commit command has been executed.

Concurrent class-Concurrency

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

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.).

Free class-Idle

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

Network class-Network

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

Other classes-Other

Such wait events are usually rare (such as wait for EMON to spawn).

Scheduling class-Scheduler

Such wait events are related to resource management (such as resmgr: become active').

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).

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).

Third, understand waiting events

Each wait event indicates an active state of the database. As can be seen from the above query, there are many wait events built into the system, and each wait event can be understood through the data dictionary V$EVENT_NAME. The following is illustrated by one of the most common wait events.

This wait event "db file sequential read", literally translated as "data file sequential read", is a wait event that belongs to the category "User Icano". It is usually a read operation associated with a single data block, and in most cases this wait is recorded by reading an index block or reading a data block through an index. This event indicates a large number of waits on a single data block, which is usually due to poor join order between tables or the use of non-selective indexes. By linking this wait to other known problems in the statspack report, such as the inefficient sql, by checking to ensure that an index scan is necessary, and by ensuring that the join order of multi-table joins is adjusted, DBCACHESIZE can determine the frequency of this event.

The wait event contains three parameters, which are:

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

The starting block number that block#: starts reading from this file

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

With the above parameters, the associated data dictionary can determine the object on which the waiting event occurs (that is, the hot object is found). Then in view of different situations, targeted solutions.

The more we know about the waiting events, the more we can understand the database operation mechanism, and then improve the overall optimization ability. Later, I will introduce common waiting events.

4. Observe waiting events

The system has some built-in views, through which we can understand the overall (system level) and local (session level) waiting events and the classified statistics of all kinds of events. Here is an explanation for some of the main views.

1 、 v$event_name

For the waiting events supported by the system, you can view the category to which the waiting events belong, the meaning of the parameters and other information.

2 、 v$system_wait_class

Displays the instance-wide time totals for each registered wait class.

Wait for statistics for the event category (system level). From this view, you can understand that there is a lot of waiting for such operations in the system from a global point of view.

3 、 v$system_event

Statistics for waiting events (system level). In terms of expansion, it provides a summary of each wait event since the instance was started. It is often used to obtain historical images of system waiting information. By getting the increment of wait items through two snapshot, you can determine the wait items of the system during this period of time.

The main fields include:

TOTAL_WAITS

The total number of times this wait event has been waited since the database was started.

TIME_WAITED

The total wait time (in 1% seconds) for this wait event. This data represents the sum of the total wait events of this wait event in all sessions (including those that have ended and are staying connected) since the database was started.

AVERAGE_WAIT

The average wait time for this wait event (in 1% seconds).

Time_waited/total_waits

TOTAL_TIMEOUTS

The total number of wait timeouts for this wait event.

SQL-View top-level events by wait time

4 、 v$session_event

Similar to v$system_event, it records the cumulative value of each wait event of a session during its life cycle. Compared with the former, session_id information is added. This information will also be accumulated into the v$system_event at the same time. It is important to note that when a session is re-established, the statistics are set to 0.

5 、 v$session_wait 、 v$session

Resource or event information that the active session is waiting for. This view is merged with the v$session view at 10g. This is a key view for finding performance bottlenecks. It provides what session is currently waiting for in the database in any case. When there is a performance problem in the system, this view can be used as a starting point to indicate the direction of exploring the problem.

It should be noted that when waiting no longer exists, the previous history of waiting in the conversation will also disappear, making ex post diagnosis very difficult. V$SESSION_EVENT provides cumulative but not very detailed data. Historical information can be obtained from the historical view v$session_wait_history.

The main fields include:

EVENT

The event that the session is currently waiting for, or the last wait event.

WAIT_TIME

The time the session waits for the event (in 1% seconds).

Value > 0: the last waiting time (in 10ms), which is not currently waiting.

Value = 0: session is waiting for the current event.

Value =-1: the last waiting time is less than 1 statistical unit and is not currently waiting.

Value =-2: the time statistics status is not set to available and is not currently waiting.

STATE

Wait status (provides an explanation of the wait_time and second_in_wait fields)

1) waiting:

SESSION is waiting for this event.

2) waited unknown time:

The time information cannot be obtained because the timed_ statistics value is set to false. Indicates that a wait has occurred, but for a short time.

3) wait short time:

Indicates that a wait occurred, but because the time is very short and does not exceed one unit of time, it is not recorded.

4) waited knnow time:

If session waits and gets the required resources, it will enter this state from waiting.

WAIT_TIME/SECOND_IN_WAIT

The Wait_time and Second_in_wait field values are related to state.

1) state=waiting

Wait_time is useless, and the second_in_ wait value is the actual wait time in seconds.

2) state=wait unknow time

Both wait_time and second_in_wait are useless.

3) state=wait short time

Both wait_time and second_in_wait are useless.

4) state=waiting known time

The wait_ time value is the actual wait time (in seconds), and the second_in_wait value is useless.

6 、 v$sessionwaithistory

Record the last n wait events in the session, that is, the history of the v$session_wait. The default is to record 10 times, which can be modified.

7 、 v$event_histogram

This view records the histogram distribution of waiting events so that you can learn more about the specific distribution of a waiting event. What is recorded in the v$session_event or v$system_event view is the cumulative information and the average of the wait, and there is no way to know how long the individual wait takes.

The relationship between session wait events and views is summarized as follows:

Only one wait event occurs in a session at a time. If you see other waiting events, it just means that the wait occurred on the next time slice. There is only one waiting at some point.

The wait_time and second_in_wait fields in v$session_wait are in seconds, while the time_waited and average_wait fields in v$session_event are in 1% seconds.

When the wait event for v$session_wait ends, the statistics for v$session_event will change.

The statistical information of v$session_wait is of little significance because the information changes in real time.

When the wait event in v$session_wait ends, the value of the seconds_in_wait field in v$session_wait is copied to the time_waited field in v$session_event, and the average_time field of the v$session_event view is also modified.

Common waiting events

There are a lot of wait events for Oracle, and there are some differences between different versions. Some common wait events are described below. I hope it will be helpful to everyone's daily work.

1 、 buffer busy waits

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.

Parameter meaning:

File#

Waiting to access the file id number where the data block is located

Blocks

Block number waiting for access

Id

Before 10g, this value indicates the reason for waiting for the event; after 10g, it indicates the category of waiting event.

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

Data block

The general optimization direction is to optimize SQL to reduce logical and physical reads, or to reduce the size of single blocks of stored data.

Data segment header

The general optimization direction is to add FREELISTS and FREELIST GROUPS. Make sure that the gap between FCTFREE and PCTUSED is not too small, thus minimizing the block cycle of FREELIST.

Undo block

The general optimization direction is the application, and the wrong peak uses the data object.

Undo the paragraph head

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.

2 、 buffer latch

Cause of occurrence:

The location of data blocks in memory is recorded in a Hash list. When a session needs to access a data block, it first searches the Hash list, obtains the address of the data block from the list, and then accesses the desired data block through this address. The list oracle uses a latch to protect its integrity. When a session needs to access the list, you need to get a latch. Only in this way can you ensure that the list will not change during the browsing of the session. If the list is too long, it will take too long for the session to search the list, leaving other sessions waiting. The same data blocks are accessed frequently, which is what we usually call the hot block problem.

Parameter meaning:

Latch addr

The virtual address of the latch requested by the session in SGA.

Chain#

The index value in the buffer chains hash list. When the value of this parameter is equal to 0xffffff, the current session is waiting for a LRU latch.

Direction of optimization:

The direction of optimization that can be considered is to use multiple buffer pool to create more buffer chains or to use the parameter db_block_lru_latches to increase the number of latch, so that more sessions can get latch, both methods can be used at the same time.

3 、 db file sequential read

Cause of occurrence:

Usually a read operation associated with a single block of data, which in most cases reads an index block or reads 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.

DB_CACHE_SIZE is also a determinant of the frequency of these waits. Problematic hash region (Hash-area) connections should appear in PGA memory, but they can also consume a lot of memory, resulting in a lot of waits during sequential reads. They may also appear in the form of direct path read / write wait.

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.

Modify the application to avoid a large number of IO sql, or reduce its frequency.

Increase the data buffer and improve the hit rate.

Adopt a better disk subsystem to reduce the response time of a single IO and prevent physical bottlenecks.

4 、 db file scattered read

Cause of occurrence:

This is a wait event caused by a user action, which occurs when a user issues a SQL operation such as a SQL operation that needs to read multiple blocks of data each time. The two most common cases are full table scan and index fast scan. The scattered in this name may lead many people to think that it reads the data block in the way of scattered, but on the contrary, when this wait event occurs, the operation of SQL reads the data block sequentially, such as FTS or IFFS. In fact, 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.

5 、 direct path read

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 gc buffer, so there is no problem with direct path read.

Parameter meaning:

File#

File number

First block#

Starting block number of 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.

6 、 direct path write

Cause of occurrence:

This happens when oracle writes data directly from PGA to data files or temporary files, which can bypass SGA. It is most common in disk sorting. 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#

Starting block number of read

Block count

The number of physical blocks written continuously starting with first block

Direction of optimization: reduce the size of IO writes.

7 、 library cache lock

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.

8 、 library cache pin

Cause of occurrence:

This wait event, like library cache lock, is a wait event caused by concurrent operations in a shared pool. In general, if oracle is to recompile objects such as pl/sql or views, you need to pin those objects into the shared pool. If this object is held by another object at this time, there will be a library cache pin wait.

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.

9 、 log file sync

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 log buffer to disk to ensure that the information submitted by the user is securely 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:

Improve LGWR performance and use fast disks as much as possible

Use batch submission

Appropriate use of options such as nologging/unrecoverable

10 、 SQL*Net message from client

Cause of occurrence:

Indicates that the foreground server process is waiting for the customer to respond. This wait event is caused by waiting for a response from the user process, and it does not indicate that there is something wrong with the database. If there is a network failure, this waiting time will occur frequently.

11 、 SQL*Net message to client

Cause of occurrence:

This wait event occurs when the server sends a message to the client. When the server sends messages to the client and waits, the possible reason is that the client is too busy to receive the message from the server in time, or the network problem may cause the message to be sent to the client from the server.

This is the end of the content of "how to understand the classification, discovery and optimization of Oracle waiting events". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.