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

This article will explain in detail what is the solution to Cache Buffers Chains. The content of the article is of high quality. Therefore, Xiaobian shares it with you as a reference. I hope you have a certain understanding of relevant knowledge after reading this article.

Latch free is a difficulty in Oracle performance tuning, mainly locating the statement that causes CBC. Foreign database experts have deep attainments in latch, such as andreynikolaev and Tanel Poder.

Here is one way to solve this problem:

1. Look at the p1,p2 and p3 values in the latch: caches buffers chain in v$event_name

p1: address; p2: number; p3: tries

2. Check the latch address with the most CBC through ASH

select * from (

select event,

trim(to_char(p1, 'xxxxxxxxxx')) latch_addr,

trim(round(ratio_to_report(count(*)) over ()*100, 1)) ||' % ' PCT,

count(*)

from v$active_session_history

where

event = 'latch: cache buffers chains'

and session_state =' WAITING'

group by event, p1

order by count(*) desc

)

where rownum @latchprofx.sql sid, name,sqlid, object % "&latch_addr' 10000 --Sample Rate

Use views within scripts:

x$ksup lat: information about the current latch holder,

Kernel Service, User ,Process LATch holder

x$ksuse: About session information: Kernel Service User SEssion

x$ksllw: latch Content: Kernel Service Latch context Where

In the output, we focus on the object address to know which object it is, and the SQLID to know what statement it is.

4. Find file and data block objects

select dbms_utility.data_block_address_file(to_number('&object', 'xxxxxxx') file#,

dbms_utility.data_block_address_block(to_number('&object', 'xxxxxxx') blk#

from dual

Knowing the files and blocks, we can either dump block to find out which object it is, or we can use the traditional dba_extents (which is very slow), or we can use x$BH, etc. Find out what the corresponding object name is.

5. View v$sqlarea through SQLID, or v$SQL method to get complete SQL statement

select sql_fulltext from v$sqlarea where sql_id='&sqlid';

6. Through the dbms_xplan.display_cursor script (see the previous article "How to use in Oracle 10

dbms_xplan Analyze execution plan ")

Set out a complete implementation plan. We focus on the object names identified in step 4 through the execution plan

In most cases,CBC is a change in SQL execution plan, usually causing the problem execution plan to appear NL, filter problem. Optimizing this statement basically solves this problem.

What is the solution to Cache Buffers Chains? I hope the above content can help you and learn more. If you think the article is good, you can share it so that more people can see it.

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.