Oracle – 第50页 – Ludatou's data life

Troubleshooting 'latch: cache buffers chains' Wait Contention

最近在好几个项目上遭遇LCBC无外乎都是CPU异常导致，先把这方面官方诊断的文章共享出来，后面描述一些极端场景的案例。

If you have high contention, you need to look at the statements that perform the most buffer gets and then look at their access paths to determine whether these are performing as efficiently as you would like.

Typical solutions are:-

Look for SQL that accesses the blocks in question and determine if the repeated reads are necessary. This may be within a single session or across multiple sessions.
Check for suboptimal SQL (this is the most common cause of the events) – look at the execution plan for the SQL being run and try to reduce the gets per executions which will minimize the number of blocks being accessed and therefore reduce the chances of multiple sessions contending for the same block.

Further information can be found in:

Note:390374.1 Oracle Performance Diagnostic Guide (OPDG) (Doc ID 390374.1)
Note:163424.1 How To Identify a Hot Block Within The Database Buffer Cache.
Note:62172.1 Understanding and Tuning Buffer Cache and DBWR (Doc ID 62172.1)

Worked example:

Problem: Database is slow and ‘latch: cache buffers chains’ is high in the waits in AWR.

Start with Top 5 Waits:

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time Wait Class

—————————— ———— ———– —— —— ———-

latch: cache buffers chains 74,642 35,421 475 6.1 Concurrenc

CPU time 11,422 2.0

log file sync 34,890 1,748 50 0.3 Commit

latch free 2,279 774 340 0.1 Other

db file parallel write 18,818 768 41 0.1 System I/O

————————————————————-

High cache buffers chains latch indicates that there is likely to be something reading a lot of buffers. Typically the SQL with the most gets is likely to be that which is contending:

SQL ordered by Gets DB/Inst: Snaps: 1-2

-> Resources reported for PL/SQL code includes the resources used by all SQL

statements called by the code.

-> Total Buffer Gets: 265,126,882

-> Captured SQL account for 99.8% of Total

Gets CPU Elapsed

Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id

————– ———— ———— —— ——– ——— ————-

256,763,367 19,052 13,477.0 96.8 ######## ######### a9nchgksux6x2

Module: JDBC Thin Client

SELECT * FROM SALES ….

1,974,516 987,056 2.0 0.7 80.31 110.94 ct6xwvwg3w0bv

SELECT COUNT(*) FROM ORDERS ….

The Query with SQL_ID a9nchgksux6x2 is reading 100x more buffers than the 2nd most ‘hungry’ statement and CPU and Elapsed are off the ‘scale’ of the report. This is a prime candidate for the cause of the CBC latch issues.

You can also link this information to the Top Segments by Logical Reads:

Segments by Logical Reads

-> Total Logical Reads: 265,126,882

-> Captured Segments account for 98.5% of Total

Tablespace Subobject Obj. Logical

Owner Name Object Name Name Type Reads %Total

———- ———- ——————– ———- —– ———— ——-

DMSUSER USERS SALES TABLE 212,206,208 80.04

DMSUSER USERS SALES_PK INDEX 44,369,264 16.74

DMSUSER USERS SYS_C0012345 INDEX 1,982,592 .75

DMSUSER USERS ORDERS_PK INDEX 842,304 .32

DMSUSER USERS INVOICES TABLE 147,488 .06

————————————————————-

The top object read is SALES and the top SQL is a select from SALES which appears to correlate towards this being a potential problem select.

This SQL should be investigated to see if the Gets per Exec or the Executions figure per hour has changed in any way (comparison to previous reports would show this) and if so the reasons for that change investigated and resolved.

In this case the statement is reading > 10,000 buffers per execution and executing > 15,000 times
so both of these may need to be adjusted to get better performance.

Note: This is a simple example where there is a high likelihood that the ‘biggest’ query is the culprit but it is not always the ‘Top’ SQL that causes the problem. For example, contention may occur on a statement with a smaller total if it is only executed a small number of times so that it may not appear as the top sql. It may still make millions of buffer gets, but will appear lower in the list because other sqls are performing many times, just not contending.

So, if the first SQL is not the culprit then look at the others.

Instance tuning Lock latch and pin Oracle performance tuning 等待事件 | Guang Cai Li | 2014-04-15

11.2.0.3 Apply 11.2.0.3.8 PSU Encounter bug:Clusterware home location does not exist

11g的psu的安装默认都是用auto的方式来安装，但是在11.2.0.3这个版本aix平台上，我很不幸的遭遇了auto无法安装的bug 16835171，报错如下：
Using configuration parameter file: /opt/app/11.2.0/grid/crs/install/crsconfig_params Either does not exist or is not readable Make sure the file exists and it has read and execute access Clusterware home location does not exist

解决办法就是使用手工安装的方式，比较繁冗，官方描述如下：
Solution At the time of this writing, the bug is still being worked by Development. The workaround is to apply the patch manually. The manual instruction is part of patch readme。

手工安装psu的方式参考以下网页：
http://www.ludatou.com/index.php/archives/1069

Database Oracle Oracle 排障配置与调整 | Guang Cai Li | 2014-04-15 | Clusterware home location does not exist

show_space脚本：快速检测表的存储信息

Tom在很多年前写了一个脚本工具showspace用来分析空间使用情况，此工具使用起来很方便可以快速了解表的存储信息。具体如下：
create or replace procedure show_space ( p_segname_1 in varchar2, p_owner_1 in varchar2 default user, p_type_1 in varchar2 default 'TABLE', p_space in varchar2 default 'AUTO', p_analyzed in varchar2 default 'Y' ) as p_segname varchar2(100); p_type varchar2(10); p_owner varchar2(30);


l_unformatted_blocks number;

l_unformatted_bytes number;

l_fs1_blocks number;

l_fs1_bytes number;

l_fs2_blocks number;

l_fs2_bytes number;

l_fs3_blocks number;

l_fs3_bytes number;

l_fs4_blocks number;

l_fs4_bytes number;

l_full_blocks number;

l_full_bytes number;
l_free_blks number;

l_total_blocks number;

l_total_bytes number;

l_unused_blocks number;

l_unused_bytes number;

l_LastUsedExtFileId number;

l_LastUsedExtBlockId number;

l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number )

is

begin

dbms_output.put_line( rpad(p_label,40,'.') ||

p_num );

end;

begin

p_segname := upper(p_segname_1); -- rainy changed

p_owner := upper(p_owner_1);

p_type := p_type_1;
if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed

p_type := 'INDEX';

end if;
if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed

p_type := 'TABLE';

end if;
if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed

p_type := 'CLUSTER';

end if;
dbms_space.unused_space

( segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

total_blocks => l_total_blocks,

total_bytes => l_total_bytes,

unused_blocks => l_unused_blocks,

unused_bytes => l_unused_bytes,

LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,

LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,

LAST_USED_BLOCK => l_LAST_USED_BLOCK );
if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then

dbms_space.free_blocks

( segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

freelist_group_id => 0,

free_blks => l_free_blks );
p( 'Free Blocks', l_free_blks );

end if;
p( 'Total Blocks', l_total_blocks );

p( 'Total Bytes', l_total_bytes );

p( 'Unused Blocks', l_unused_blocks );

p( 'Unused Bytes', l_unused_bytes );

p( 'Last Used Ext FileId', l_LastUsedExtFileId );

p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );

p( 'Last Used Block', l_LAST_USED_BLOCK );
/*IF the segment is analyzed */

if p_analyzed = 'Y' then

dbms_space.space_usage(segment_owner => p_owner ,

segment_name => p_segname ,

segment_type => p_type ,

unformatted_blocks => l_unformatted_blocks ,

unformatted_bytes => l_unformatted_bytes,

fs1_blocks => l_fs1_blocks,

fs1_bytes => l_fs1_bytes ,

fs2_blocks => l_fs2_blocks,

fs2_bytes => l_fs2_bytes,

fs3_blocks => l_fs3_blocks ,

fs3_bytes => l_fs3_bytes,

fs4_blocks => l_fs4_blocks,

fs4_bytes => l_fs4_bytes,

full_blocks => l_full_blocks,

full_bytes => l_full_bytes);

dbms_output.put_line(rpad(' ',50,'*'));

dbms_output.put_line('The segment is analyzed');

p( '0% -- 25% free space blocks', l_fs1_blocks);

p( '0% -- 25% free space bytes', l_fs1_bytes);

p( '25% -- 50% free space blocks', l_fs2_blocks);

p( '25% -- 50% free space bytes', l_fs2_bytes);

p( '50% -- 75% free space blocks', l_fs3_blocks);

p( '50% -- 75% free space bytes', l_fs3_bytes);

p( '75% -- 100% free space blocks', l_fs4_blocks);

p( '75% -- 100% free space bytes', l_fs4_bytes);

p( 'Unused Blocks', l_unformatted_blocks );

p( 'Unused Bytes', l_unformatted_bytes );

p( 'Total Blocks', l_full_blocks);

p( 'Total bytes', l_full_bytes);
end if;

end; /

使用方法参考：
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5350053031470

Data access tuning Data model Oracle performance tuning 数据技术 | Guang Cai Li | 2014-03-16 | show_space

Oracle最新的PSU以及SPU(CPU）,Bundle Patches and Patchsets

Database Oracle Oracle security Oracle 排障配置与调整 | Guang Cai Li | 2013-12-02 | CPU psu

WebLogic Server Patch Set Updates (PSUs)

官方关于wls的psu集合列表：

12.1.1 Patch Set Updates

PSU	Description	Patch Download	Patch Availability Document	Smart Update Patch ID	Bugs fixed in this PSU
12.1.1.0.6	12.1.1.0.6 Patch Set Update (PSU) for WebLogic Server 12.1.1.0	Patch 17071664	My Oracle Support Note 1571391.1	HYQC	My Oracle Support Note 1589783.1
12.1.1.0.5	12.1.1.0.5 Patch Set Update (PSU) for WebLogic Server 12.1.1.0	Patch 16619895	My Oracle Support Note 1548709.1	LNQI	My Oracle Support Note 1567744.1
12.1.1.0.4	12.1.1.0.4 Patch Set Update (PSU) for WebLogic Server 12.1.1.0	Patch 16083652	My Oracle Support Note 1525152.1	YJCB	My Oracle Support Note 1544310.1
12.1.1.0.3	12.1.1.0.3 Patch Set Update (PSU) for WebLogic Server 12.1.1.0	Patch 14736141	My Oracle Support Note 1502461.1	ZFHT	My Oracle Support Note 1519594.1
12.1.1.0.2	12.1.1.0.2 Patch Set Update (PSU) for WebLogic Server 12.1.1.0	Patch 14331523	My Oracle Support Note 1477727.1	SELV	My Oracle Support Note 1496318.1
12.1.1.0.1	12.1.1.0.1 Patch Set Update (PSU) for WebLogic Server 12.1.1.0	Patch 14142554	My Oracle Support Note 1455387.1	N9JH	My Oracle Support Note 1466024.1

10.3.6 Patch Set Updates

PSU	Description	Patch Download	Patch Availability Document	Smart Update Patch ID	Bugs fixed in this PSU
10.3.6.0.6	10.3.6.0.6 Patch Set Update (PSU) for WebLogic Server 10.3.6.0	Patch 17071663	My Oracle Support Note 1571391.1	BYJ1	My Oracle Support Note 1589769.1
10.3.6.0.5	10.3.6.0.5 Patch Set Update (PSU) for WebLogic Server 10.3.6.0	Patch 16619891	My Oracle Support Note 1548709.1	L51R	My Oracle Support Note 1567735.1
10.3.6.0.4	10.3.6.0.4 Patch Set Update (PSU) for WebLogic Server 10.3.6.0	Patch 16083651	My Oracle Support Note 1525152.1	D33T	My Oracle Support Note 1544308.1
10.3.6.0.3	10.3.6.0.3 Patch Set Update (PSU) for WebLogic Server 10.3.6.0	Patch 14736139	My Oracle Support Note 1502461.1	HYKC	My Oracle Support Note 1519561.1
10.3.6.0.2	10.3.6.0.2 Patch Set Update (PSU) for WebLogic Server 10.3.6.0	Patch 14331527	My Oracle Support Note 1477727.1	MYFD	My Oracle Support Note 1496334.1
10.3.6.0.1	10.3.6.0.1 Patch Set Update (PSU) for WebLogic Server 10.3.6.0	Patch 14142550	My Oracle Support Note 1455387.1	JSES	My Oracle Support Note 1466022.1

10.3.5 Patch Set Updates

PSU	Description	Patch Download	Patch Availability Document	Smart Update Patch ID	Bugs fixed in this PSU
10.3.5.0.7	10.3.5.0.7 Patch Set Update (PSU) for WebLogic Server 10.3.5.0	Patch 16088411	My Oracle Support Note 1525152.1	L5TD	My Oracle Support Note 1544306.1
10.3.5.0.6	10.3.5.0.6 Patch Set Update (PSU) for WebLogic Server 10.3.5.0	Patch 14736140	My Oracle Support Note 1502461.1	SYJ2	My Oracle Support Note 1519558.1
10.3.5.0.5	10.3.5.0.5 Patch Set Update (PSU) for WebLogic Server 10.3.5.0	Patch 14331529	My Oracle Support Note 1477727.1	Z6MQ	My Oracle Support Note 1496336.1
10.3.5.0.4	10.3.5.0.4 Patch Set Update (PSU) for WebLogic Server 10.3.5.0	Patch 14142291	My Oracle Support Note 1455387.1	4D2C	My Oracle Support Note 1466020.1
10.3.5.0.3	10.3.5.0.3 Patch Set Update (PSU) for WebLogic Server 10.3.5.0	Patch 13568070	My Oracle Support Note 1406574.1	N5G9	My Oracle Support Note 1435773.1
10.3.5.0.2	10.3.5.0.2 Patch Set Update (PSU) for WebLogic Server 10.3.5.0	Patch 13108890	My Oracle Support Note 1374524.1	USGW	My Oracle Support Note 1377996.1
10.3.5.0.1	10.3.5.0.1 Patch Set Update (PSU) for WebLogic Server 10.3.5.0	Patch 12617751	My Oracle Support Note 1346104.1	V9XT	My Oracle Support Note 1348978.1

10.3.4 Patch Set Updates

PSU	Description	Patch Download	Patch Availability Document	Smart Update Patch ID	Bugs fixed in this PSU
10.3.4.0.5	10.3.4.0.5 Patch Set Update (PSU) for WebLogic Server 10.3.4.0	Patch 13568073	My Oracle Support Note 1406574.1	PQ4Y	My Oracle Support Note 1435222.1
10.3.4.0.4	10.3.4.0.4 Patch Set Update (PSU) for WebLogic Server 10.3.4.0	Patch 13108901	My Oracle Support Note 1374524.1	XZNF	My Oracle Support Note 1377995.1
10.3.4.0.3	10.3.4.0.3 Patch Set Update (PSU) for WebLogic Server 10.3.4.0	Patch 12770299	My Oracle Support Note 1346104.1	GJYS	My Oracle Support Note 1348977.1
10.3.4.0.2	10.3.4.0.2 Patch Set Update (PSU) for WebLogic Server 10.3.4.0	Patch 12357891	My Oracle Support Note 1323616.1	H94Y	My Oracle Support Note 1328645.1
10.3.4.0.1	10.3.4.0.1 Patch Set Update (PSU) for WebLogic Server 10.3.4.0	Patch 11677325	My Oracle Support Note 1291877.1	Q8SV	My Oracle Support Note 1296462.1

Conflict Resolution

Oracle | Guang Cai Li | 2013-12-02 | weblogic

Oracle - 50. page

Troubleshooting 'latch: cache buffers chains' Wait Contention

Worked example:

Problem: Database is slow and ‘latch: cache buffers chains’ is high in the waits in AWR.

11.2.0.3 Apply 11.2.0.3.8 PSU Encounter bug:Clusterware home location does not exist

show_space脚本：快速检测表的存储信息

Oracle最新的PSU以及SPU(CPU）,Bundle Patches and Patchsets

WebLogic Server Patch Set Updates (PSUs)

12.1.1 Patch Set Updates

10.3.6 Patch Set Updates

10.3.5 Patch Set Updates

10.3.4 Patch Set Updates

Conflict Resolution

Oracle 恢复工具 Mdata 5.0.1 版本发布

近期文章

分类目录

扫码关注微信公众号:Oracle运维那些事获取定期发布的数据库运维的有趣事情!

近期活动

Worked example:

Problem: Database is slow and ‘latch: cache buffers chains’ is high in the waits in AWR.

12.1.1 Patch Set Updates

10.3.6 Patch Set Updates

10.3.5 Patch Set Updates

10.3.4 Patch Set Updates

Conflict Resolution

Oracle 恢复工具 Mdata 5.0.1 版本发布

近期文章

分类目录

扫码关注微信公众号:Oracle运维那些事 获取定期发布的数据库运维的有趣事情!

近期活动

扫码关注微信公众号:Oracle运维那些事获取定期发布的数据库运维的有趣事情!