Instance tuning – 第3页 – Ludatou's data life

latch:cache buffers chains

官网说明：

Latch: cache buffers chains
Identifier:
Registered In:

Description:
Blocks in the buffer cache are placed on linked lists
(cache buffer chains) which hang off a hash table.
The hash chain that a block is placed on is based on the DBA
and CLASS of the block. Each hash chain is protected by a
single child latch. Processes need to get the relevant latch
to allow them the scan a hash chain for a buffer so that the
linked list does not change underneath them.

Contention: Contention for these latches can be caused by:

– Very long buffer chains.
There is a known problem that can result in long
buffer chains –
– very very heavy access to a single block.
This would require the application to be reviewed.

To identify the heavily accessed buffer chain look at
the latch stats for this latch under
and match this to .

*** IMPORTANT: As of Oracle8i there are many hash buckets
to each latch and so there will be lots
of buffers under each latch.
In 8i the steps below will not help much.

Eg: Given ADDR from V$LATCH_CHILDREN for a heavily contended
child latch:
select dbafil, dbablk, class, state
from X$BH where HLADDR=’address of latch’;

One of these is ‘potentially’ a hot block in the database.

**Please see Note 163424.1 How To Identify a Hot Block Within The Database
to correctly identify this issue

Once the object/table is found you can reduce the number of blocks requested
on the particular object/table by redesigning the application or by
spreading the hits in the buffer cache over different hash chains.
You can achieve this by implementing PARTITIONING and storing segements of
the same table/object in different files.

*NOTE* IF YOU ARE RUNNING 8.1.7:

Please see Note 176129.1 ALERT: LATCH FREE And FREE_BUFFER_WAITS
Cause Performance Degradation/Hang

查找热点块所属对象的方法：

1、执行sql：

select CHILD#  "cCHILD"
,      ADDR    "sADDR"
,      GETS    "sGETS"
,      MISSES  "sMISSES"
,      SLEEPS  "sSLEEPS"
from v$latch_children
where name = 'cache buffers chains'
order by 5, 1, 2, 3;

找到sleep count较高的地址（ADDR），再执行下面的语句

column segment_name format a35
select /*+ RULE */
  e.owner ||'.'|| e.segment_name  segment_name,
  e.extent_id  extent#,
  x.dbablk - e.block_id + 1  block#,
  x.tch,
  l.child#
from
  sys.v$latch_children  l,
  sys.x$bh  x,
  sys.dba_extents  e
where
  x.hladdr  = 'ADDR' and
  e.file_id = x.file# and
  x.hladdr = l.addr and
  x.dbablk between e.block_id and e.block_id + e.blocks -1
  order by x.tch desc ;

2、由于上面的方法执行起来可能比较慢，而且有时候是针对某个session的事件进行查找的，因此可以用下面的方法：

v$session_wait中找到P1RAW地址，然后执行：

SELECT FILE# , dbablk, class, state ,tch
FROM x$bh WHERE hladdr='' order by tch;

找出touch较高的文件及数据块，最后执行

select * from dba_extents where file_id= and   between block_id and block_id + blocks -1

Buffer cache Instance tuning Lock latch and pin Lock,Latch,Pin 等待事件 | Guang Cai Li | 2014-09-27

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

整理的DBA常用脚本

oracle用户权限检查
1. 系统权限记录在 dba_sys_privs 视图中：
select grantee, privilege, admin_option
from dba_sys_privs
where grantee = ‘USERNAME’ ;
2. 角色权限记录在 dba_role_privs 视图中：
select grantee, granted_role, admin_option
from dba_role_privs
where grantee = ‘USERNAME’ ;
3. 对象权限记录在 dba_tab_privs 视图中，虽然视图名看起来像是表权限，但是实际包含了包，存储过程等等对象级的权限：
select grantee, owner || ‘.’ || table_name obj_name, privilege, grantable
from dba_tab_privs
where grantee = ‘USERNAME’;
4. 其他还有部分权限，如表空间的quota权限，记录在 dba_ts_quota中：
select username,
tablespace_name,
bytes / 1024 / 1024 “used space (m)”,
decode(decode(max_bytes, -1, 0, max_bytes) / 1024 / 1024,
0,
‘unlimited’,
max_bytes / 1024 / 1024) “max space(m)”,
blocks used_blocks,
max_blocks
from dba_ts_quotas
where username = ‘USERNAME’;
select * from session_privs; 当前用户拥有的权限
SELECT * FROM DBA_SYS_PRIVS; 查询每个用户的权限
锁的检查
select b.owner,
b.object_name,
l.SESSION_ID,
DECODE(L.LOCKED_MODE,
0,
‘None’,
1,
‘Null’,
2,
‘Row-S (SS)’,
3,
‘Row-X (SX)’,
4,
‘Share’,
5,
‘S/Row-X (SSX)’,
6,
‘Exclusive’,
TO_CHAR(L.LOCKED_MODE)) MODE_HELD,
s.USERNAME
from dba_objects b, v$locked_object l, V$SESSION S
where b.object_id = l.object_id
and l.SESSION_ID = s.SID
查看一个长查询的进度
SELECT *
FROM ( select username,opname,sid,serial#,
context,b.sql_text,sofar,totalwork,
round(sofar/totalwork*100,2) “% Complete”,
elapsed_seconds
from v$session_longops , v$sql b
where sql_hash_value=b.hash_value
and sql_address = address
and totalwork <> 0
)
WHERE “% Complete” <> 100 ;
查看ACTIVE SESSION的等待事件
select a.event,
sum(decode(wait_Time, 0, 0, 1)) “Prev”,
sum(decode(wait_Time, 0, 1, 0)) “Curr”,
count(*) “Tot”
from v$session_wait a, v$session b
where a.sid = b.sid
and b.status = ‘ACTIVE’
group by a.event
— having count(*) > 10
order by 4;

在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
通过DBMS_METADATA包得到对象的DLL语句
a.获取单个的建表和建索引的语法,其他对象类似：
select dbms_metadata.get_ddl(‘TABLE’,’TABLE_NAME’,’TABLE_OWNER’) from dual;
select dbms_metadata.get_ddl(‘INDEX’,’INDEX_NAME’,’INDEX_OWNER’) from dual;

b.获取一个SCHEMA下的所有建表的语法，以scott为例，其他对象类似：
SELECT DBMS_METADATA.GET_DDL(‘TABLE’, u.table_name, u.owner) || ‘;’
FROM DBA_TABLES u
where owner = ‘SCOTT’ ;
查看回滚段名称及大小
select segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) InitialExtent,
(next_extent / 1024) NextExtent,
max_extents,
v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name;

查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

查看数据库的版本
Select version
FROM Product_component_version
Where SUBSTR(PRODUCT, 1, 6) = ‘Oracle’;

查看数据库的创建日期和归档方式
Select Created, Log_Mode From V$Database;

查看还没提交的事务
select * from v$locked_object;
select * from v$transaction;
查找object为哪些进程
select p.spid,
s.sid,
s.serial# serial_num,
s.username user_name,
a.type object_type,
s.osuser os_user_name,
a.owner,
a.object object_name,
decode(sign(48 – command),
1,
to_char(command),
‘Action Code #’ || to_char(command)) action,
p.program oracle_process,
s.terminal terminal,
s.program program,
s.status session_status
from v$session s, v$access a, v$process p
where s.paddr = p.addr
and s.type = ‘USER’
and a.sid = s.sid
and a.object = ‘EMP’
order by s.username, s.osuser
怎样计算一个表占用的空间的大小？
select owner,
table_name,
NUM_ROWS,
BLOCKS * AAA / 1024 / 1024 “Size M”,
EMPTY_BLOCKS,
LAST_ANALYZED
from dba_tables
where table_name = ‘XXX’;

注意:执行以上语句前要先对表做统计分析

select sum(a.bytes) / 1024 * 1024 “size”
from dba_extents a
where a.segment_name = ‘GOV_FDDBR’
select a.bytes / 1024 * 1024 “size”, (a.blocks * 8192) / 1024 * 1024 “da”
from dba_segments a
where a.segment_name = ‘GOV_FDDBR’
如何查看最大会话数
SELECT * FROM V$PARAMETER WHERE NAME LIKE ‘proc%’;
SQL>
SQL> show parameter processes

NAME TYPE VALUE
———————————— ——- ——————————
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 4
log_archive_max_processes integer 1
processes integer 200

这里为200个用户。

select * from v$license;
其中sessions_highwater纪录曾经到达的最大会话数
如何在Oracle服务器上通过SQLPLUS查看本机IP地址

select sys_context(‘userenv’,’ip_address’) from dual;

如何将表、索引移动表空间
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;
ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;
如何才能得知系统当前的SCN号
select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;
如何在字符串里加回车
select ‘Welcome to visit’||chr(10)||’www.CSDN.NET’ from dual；
如何将小表放入keep池中
alter table xxx storage(buffer_pool keep);
如何查询做比较大的排序的进程？
SELECT b.tablespace,
b.segfile#,
b.segblk#,
b.blocks,
a.sid,
a.serial#,
¡¡ a.username,
a.osuser,
a.status ¡¡
FROM v$session a, v$sort_usage b ¡¡
WHERE a.saddr = b.session_addr ¡¡
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

如何删除重复记录？
DELETE FROM TABLE_NAME
WHERE ROWID != (SELECT MAX(ROWID)
FROM TABLE_NAME D
WHERE TABLE_NAME.COL1 = D.COL1
AND TABLE_NAME.COL2 = D.COL2)；
如何回滚段的争用情况
select name, waits, gets, waits / gets “Ratio” 　
from v$rollstat C, v$rollname D 　
where C.usn = D.usn;
如何监控表空间的 I/O 比例
select B.tablespace_name name,
B.file_name “file”,
A.phyrds pyr,
　 A.phyblkrd pbr,
A.phywrts pyw,
A.phyblkwrt pbw 　
from v$filestat A, dba_data_files B 　
where A.file# = B.file_id 　
order by B.tablespace_name;

如何监控文件系统的 I/O 比例
Select substr(C.file#, 1, 2) “#”,
substr(C.name, 1, 30) “Name”,
C.status,
C.bytes,
D.phyrds,
D.phywrts 　
from v$datafile C, v$filestat D 　
where C.file# = D.file#;
如何在某个用户下找所有的索引
select user_indexes.table_name,
user_indexes.index_name,
uniqueness,
column_name 　
from user_ind_columns, user_indexes 　
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name 　
order by user_indexes.table_type,
user_indexes.table_name,
　user_indexes.index_name,
column_position;
如何监控 SGA 中字典缓冲区的命中率
select parameter,
gets,
Getmisses,
getmisses / (gets + getmisses) * 100 “miss ratio”,
　(1 – (sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100 “Hit ratio” 　
from v$rowcache 　
where gets + getmisses <> 0 　
group by parameter, gets, getmisses;
如何监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pins) “Total Pins”,
sum(reloads) “Total Reloads”,
　sum(reloads) / sum(pins) * 100 libcache 　
from v$librarycache;

select sum(pinhits – reloads) / sum(pins) “hit radio”,
sum(reloads) / sum(pins) ¡¡¡¡”reload percent” ¡¡
from v$librarycache;
监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name,
gets,
misses,
immediate_gets,
immediate_misses,
Decode(gets, 0, 0, misses / gets * 100) ratio1,
Decode(immediate_gets+immediate_misses,0,0, immediate_misses / (immediate_gets + immediate_misses) * 100) ratio2 　
FROM v$latch
WHERE name IN (‘redo allocation’, ‘redo copy’);

监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value
FROM v$sysstat
WHERE name IN (‘sorts (memory)’, ‘sorts(disk)’);
如何监控当前数据库谁在运行什幺SQL语句？
SELECT osuser, username, sql_text
from v$session a, v$sqltext b 　
where a.sql_address = b.address
order by address, piece;
如何查看碎片程度高的表?
SELECT segment_name table_name, COUNT(*) extents 　
FROM dba_segments
WHERE owner NOT IN (‘SYS’, ‘SYSTEM’)
GROUP BY segment_name 　
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM dba_segments
GROUP BY segment_name);
如何知道表在表空间中的存储情况?
select segment_name, sum(bytes), count(*) ext_quan
from dba_extents
where tablespace_name = ‘&tablespace_name’
and segment_type = ‘TABLE’
group by tablespace_name, segment_name;
如何知道索引在表空间中的存储情况?
select segment_name, count(*)
from dba_extents
where segment_type = ‘INDEX’
and owner = ‘&owner’ 　
group by segment_name;

如何知道使用CPU多的用户session?
Select a.sid,spid,status,
substr(a.program,1,40)
prog,a.terminal,osuser,value/60/100 value
　 from v$session a,v$process b,v$sesstat c
　 where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr
order by value desc;
物理读和内存读较高SQL
SELECT t.HASH_VALUE,
t.EXECUTIONS,
t.DISK_READS,
round(t.DISK_READS / t.EXECUTIONS) AS perDiskReads,
t.BUFFER_GETS,
round(t.BUFFER_GETS / t.EXECUTIONS) AS perBufferReads,
t.ELAPSED_TIME,
round(t.ELAPSED_TIME / t.EXECUTIONS) AS perElayTime,
t.CPU_TIME,
round(t.CPU_TIME / t.EXECUTIONS) AS perCpuTime,
t.FIRST_LOAD_TIME,
t.SQL_TEXT
FROM v$sql t
WHERE (t.DISK_READS / t.EXECUTIONS > 500 OR
t.BUFFER_GETS / t.EXECUTIONS > 20000)
AND t.EXECUTIONS > 0
ORDER BY 6 DESC;
查看低效率的SQL语句
SELECT EXECUTIONS,
DISK_READS,
BUFFER_GETS,
ROUND((BUFFER_GETS – DISK_READS) / BUFFER_GETS, 2) Hit_radio,
ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run,
SQL_TEXT
FROM　 V$SQLAREA 　　
WHERE　EXECUTIONS > 0 　　
AND　　BUFFER_GETS > 0 　　
AND (BUFFER_GETS – DISK_READS) / BUFFER_GETS < 0.8 　　 ORDER BY EXECUTIONS DESC 或者 select executions exec, loads loads, parse_calls parse, disk_reads reads, buffer_gets gets, rows_processed rows_proc, sorts sorts, sql_text from v$sqlarea order by &sortkey desc; 找出oracle中从没有使用的索引 set pages 999; set heading off; spool run_monitor.sql select 'alter index '||owner||'.'||index_name||' monitoring usage;' from dba_indexes where owner not in ('SYS','SYSTEM','PERFSTAT') spool off; @run_monitor 等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图。 Select index_name,table_name,mon,used from v$object_usage; 在V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO。不幸的是，它不会告诉你Oracle使用了这个索引多少次，但是这个工具对于找出没有使用的索引还是很有用的。自己编写的脚本 select c.SID,c.STATUS,s.NAME,b.VALUE from v$statname s, v$sesstat b, v$session c where s.STATISTIC# = b.STATISTIC# and b.SID = c.SID and b.sid=&sid and s.NAME in ('consistent gets','physical reads','parse count (total)','parse count (hard)') V$sql:查询一条sql执行时间及消耗的cpu时间，被执行及被分析的次数 Parse_calls, Executions Cpu_time, Elapsed_time Alter system flush shared_pool 清空共享池

Instance tuning - 3. page

latch:cache buffers chains

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.

整理的DBA常用脚本

Oracle 恢复工具 Mdata 5.0.1 版本发布

近期文章

分类目录

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

近期活动

Worked example:

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

Oracle 恢复工具 Mdata 5.0.1 版本发布

近期文章

分类目录

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

近期活动

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