What is the solution of Cache Buffers Chains?
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.
