Oracle Latch

最新推荐文章于 2021-04-11 08:11:15 发布

cuiyan3320

最新推荐文章于 2021-04-11 08:11:15 发布

阅读量157

点赞数

什么是latch?
latch是一种低级别的序列化的内存锁,用于保护SGA中的共享内存结构.
(1)确保内核代码序列执行;
(2)防止因缓存区崩溃导致的物理块的坏块
任何内核代码在执行前,必须获取latch,执行完后释放latch.
latch通常通过两种方式来获取:immediate和willing to wait.当进程以immeidate方式请求latch失败时,控制权将转会到进程并产生一次immediate miss;当进程以willing to wait方式请求latch时将以”spinning and sleeping”的循环方式重复请求获取latch,spin一次如果仍然不能得到latch则sleep一定间隔(间隔时间成指数级增长)直至获得latch或达到spin_count次数(此时产生一次miss)

常见的两种latch: cache buffer chains latch 和library cache诊断

1.Cache buffers chains latch:
This latch is acquired whenever a block in the buffer cache is accessed (pinned).
Reducing contention for the cache buffer chains latch will usually require reducing logical I/O rates by tuning and minimizing the I/O requirements of the SQL involved. High I/O rates could be a sign of a hot block (meaning a block highly accessed).
See How To Identify a Hot Block Within The Database to correctly identify this issue

Data buffer chains—热点块
select CHILD# “cCHILD”
, ADDR “sADDR”
, GETS “sGETS”
, MISSES “sMISSES”
, SLEEPS “sSLEEPS”
from v$latch_children
where name = ‘cache buffers chains’
order by 4, 1, 2, 3;

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 = sADDR’ 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.library cache latch的诊断
查看latch信息：
select name,gets,misses,sleeps
from v$latch
where name like ‘library%’;
查看latch操作系统进程号
select a.name,pid from v$latch a , V$latchholder b
where a.addr=b.laddr
and a.name = ‘library cache%’;
2.查看
select count(*) number_of_waiters
from v$session_wait w, v$latch l
where w.wait_time = 0
and w.event = ‘latch free’
and w.p2 = l.latch#
and l.name like ‘library%’;

select * from v$session_wait
where event != ‘client message’
and event not like ‘%NET%’
and wait_time = 0
and sid > 5;

3.解决方法

i) FRAGMENTATION

The primary cause of library cache latch contention is fragmentation of the
shared pool. A common symptom is the ORA-04031 error. This can be diagnosed and
addressed. Please refer to to have detailed information on this topic.

ii) INCREASE SHARING

By increasing the amount of sharing that occurs on the system you can decrease
the amount of missing and loading that occurs in the library cache and
therefore the load on the library cache latch. This is done by identifying
statements that are not being shared as described in the fragmentation section
above.
To determine the percentage of sql statement parse calls that find a cursor to
share you can execute the following:

select gethitratio from v$librarycache where namespace = ‘SQL AREA’;

This value should be in the high nineties.

iii) REDUCE PARSING

Any time a SQL statement is executed a PARSE stage must be executed. When a
PARSING representation can be reused because is already loaded into the shared pool
a “soft parse” is issued. When is required to compile the statement and create the parse
representation a “hard parse” is issued. To reduce library cache latch contention you will
need to monitor and reduce “hard parse”
To identify the SQL statements that are receiving a lot of parse calls execute
the following query:

select sql_text, parse_calls, executions from v$sqlarea
where parse_calls > 100 and executions < 2*parse_calls;

To identify the total amount of parsing going on in the system execute the
following:

Oracle7:
select name, value from v$sysstat where name = ‘parse count’;

NAME VALUE————————————————————————————parse count &nbs ; 220267

Oracle8: (Oracle8.x keep record of the “hard parse”)
select name, value from v$sysstat where name like ‘parse count%’;

NAME VALUE———————————————————————-—————parse count (total) 220267
parse count (hard) 90345

If this value increases at a rate greater than about 10 per second then this
may be a problem.

iv) CURSOR_SPACE_FOR_TIME

Setting the init.ora parameter cursor_space_for_time to TRUE can reduce the
load on the library cache latch somewhat. However, setting this parameter may
add a lot of memory utilization, so before setting it to true make sure that
there is a lot of free memory on the system and that the number of hard page
faults per minute is very low or zero.
v) SESSION_CACHED_CURSORS
session_cached_cursors can be set that will help in situations where a user repeatedly parses
the same statements. This can occur in many applications including FORMS based
application if users often switch between forms. Every time a user switches to
a new form. all the SQL statements opened for the old form. will be closed. The
session_cached_cursors parameter will cause closed cursors to be cached within
the session so that a subsequent call to parse the statement will bypass the
parse phase. This is similar to HOLD_CURSORS in the precompilers. One thing
to be careful about is that if this parameter is set to a high value, the
amount of fragmentation in the shared pool may be increased.
vi) USING FULLY QUALIFIED TABLE NAMES
It can help to reduce the load on the library cache latch somewhat to use fully
qualified names for tables in SQL statements. That is, instead of saying
‘select * from emp’, say ‘select * from scott.emp’. This is especially helpful
for SQL statements that are parsed very frequently. If all users log onto the
database using the same userid then this may be of little or no use.