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

Background of the event:

An outage occurred in a customer's database and looked at the awr report of the database. It turned out that a large number of latch: cache buffers chains waiting events caused the system to consume a large amount of CPU, which eventually led to the system living in hang.

Description:

To understand latch: cache buffers chains and solve this problem, you need to have an in-depth understanding of Buffer Cache and its principles.

1. Overview of Buffer Cache:

Buffer Cache is a part of SGA, and the ultimate goal of Oracle using Buffer Cache to manage data block,Buffer Cache is to reduce the disk Icano as much as possible. There are three main structures in Buffer Cache to manage Buffer Cache:Hash Bucket, Hash Chain List and LRU List.

Hash Bucket & Hash Chain List: Hash Bucket and Hash Chain List are used to realize the rapid positioning of data block.

LRU List: contains information that points to a specific free buffer, pinned buffer, and dirty buffer that has not been moved to write list. The so-called free buffer refers to the buffer that does not contain any data, and the so-called pinned buffer refers to the buffer that is currently being accessed.

Write (Dirty) List: hangs information that points to a specific dirty block. The so-called dirty block refers to the block that has been modified in buffer cache but has not been written to disk.

2. The principle of Hash Bucket:

If all Buffer in all Buffer Cache are managed through the same structure, when you need to determine whether a certain Block exists in Buffer, you will need to traverse the entire structure, and the performance will be quite low.

In order to improve efficiency, Oracle introduces the data structure of Bucket. Oracle stores all the Buffer managed by an internal Hash algorithm in different Hash Bucket, so that after being segmented by Hash Bucket, a large number of Buffer are distributed to a certain number of Bucket. When users need to locate the existence of data in Buffer, they only need to obtain the hash value through the same algorithm, and then look for a small amount of Buffer in the corresponding Bucket. The Bucket stored in each Buffer is determined by the block address operation of the Buffer; (this algorithm gets very little information from the ORACLE official and can be understood with the knowledge of the index.) inside the Bucket, all Buffer are linked through Buffer Header information through Cache Buffer Chain.

To protect the data in Bucket, you need to get the latches in memory before accessing each access. The structure of the entire access is shown in the figure:

The order in which the user reads the data in memory:

A) apply the Hash algorithm to the Block to get the hash value.

B) obtaining access to cache buffers chains latch

C) search the corresponding Hash Bucket for the corresponding Buffer Header

B) obtain cache buffers chains latch

D) if you find the corresponding Buffer Header, then determine the state of the Buffer to see if you need to construct the CR Block, or if the Buffer is in the state of pin, and finally read it. E) if it cannot be found, it is read from disk into Buffer Cache.

3. Latch:cache buffers chains wait event

Before Oracle9i, if another user process had already obtained the latch, the new process had to wait until the user process had finished searching (after the search finished, the latch would be released). Starting from Oracle9i, cache buffers chains latch can be read-only sharing, that is, user process An accesses the Block in a read-only manner, and user process A gets the latch at this time, while user process B also accesses Block in a read-only manner, so user process B can also get the latch because it is read-only. However, if user process B wants to access the Block exclusively, user process B waits for user process A to release the latch, and Oracle marks user process B with a latch:cache buffers chains wait event.

4. The reasons for the emergence of latch:cache buffers chains

4.1Unoptimized SQL.

A large number of logical SQL statements can lead to very serious latch:cache buffers chains waits, because each time you access a block, you need to get that latch, and because there are a large number of logical reads, it increases the probability of latch:cache buffers chains contention. For running SQL statements that cause very serious latch:cache buffers chains contention, you can use the following SQL to view the execution plan and try to optimize the SQL statement.

Select * from table (dbms_xplan.display_cursor ('sql_id',sql_child_number))

If SQL has finished running, let's look at SQL Statistics- > SQL ordered by Gets- > Gets per Exec in the AWR report to try to optimize these SQL.

4.2 Hot spot block contention

1) find out whether there is contention for latch in the database

Select sid,event,p1text,p1raw from v$session_wait where event='latch: cache buffers chains'

2) the following query finds out the contended latch address of Top 5.

Select * from (select CHILD#,ADDR,GETS, MISSES,SLEEPS from v$latch_children where name = 'cache buffers chains' and misses > 0 and sleeps > 0 order by 5 desc, 1,2,3) where rownum create table john (no int,object_name varchar2 (50))

5.2 insert data S

QL > declare I int;beginfor I in 1.. 5 loopinsert into john select rownum as no,object_name from dba_objects;end loop;end;/

5.3 create stored procedure SQL > create or replace procedure p_john isi int;icount int;beginfor I in 1.. 1000 loopselect count (*) into icount from john;end loop;end;/

5.4 Analog 20 concurrent full table scan

SQL > var job_no number;S

QL > beginfor idx in 1.. 20 loopdbms_job.submit (: job_no,'p_john;'); commit;end loop; end;/

5.5 check contention

SQL > select sid,event,p1text,p1raw from v$session_wait where event='latch: cache buffers chains'

Show a large amount of latch waiting

5.6 latch: characteristics of cache buffers chains wait events in awr reports

Summarize the above characteristics:

A) consume a lot of CPU resources

B) logical reading is much more than normal

C) there must be latch: cache buffers chains in the waiting event

D) the hit rate of Latch is generally less than 95% and serious less than 90%

6. The problem that latch:cache buffers chains's personal solution is often used is actually caused by the quality of SQL. Many DBA complains that DBA and development are different departments, so it is difficult and feasible for developers to work together for SQL tuning. I disagree with this view: when there is a big problem in the system, it will lead to system performance degradation, or even downtime, so if the system is important, then DBA can send the cause and solution of the problem to the developer, and copy it to the company leader, and explain the reason and the solution, because at this time DBA is the only one who knows the root cause of the problem and the solution. So the leader will support you, too. In addition: SQL tuning is the most effective tuning method. It is suggested that DBA should not deal with it from a systematic point of view to avoid filling one hole and another.

+

Author: JOHN

ORACLE technology blog: ORACLE hunter's note database technology group: 367875324 (please note ORACLE management)

+

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.