- 浏览: 398990 次
- 性别:
- 来自: 上海
最新评论
-
liuwenlong62555:
...
Linux防火墙的关闭和开启 -
baolong101010:
永久关闭:chkconfig --level 2345 ipt ...
Linux防火墙的关闭和开启 -
lijie1819:
3)查看防火墙状态chkconfig iptables --l ...
Linux防火墙的关闭和开启 -
Annah:
总结的很好,谢谢
Vector和ArrayList区别 -
celavi:
非常好的文章,谢谢分享!
ORACLE SQL TUNING
在Oracle中,Latch的概念是非常重要的,v$latch表的每一行包括了对不同类型latch的统计,每一列反映了不同类型的latch请求的活动情况。不同类型的latch请求之间的区别在于,当latch不可立即获得时,请求进程是否继续进行。按此分类,latch请求的类型可分为两类:willing-to-wait和immediate。
latch free,相信跟大家并不陌生,在v$session_wait和Top5中会出现,当然出现类似的内容,就证明Latch产生了竞争,并且已经影响到了你的系统性能。
首先我们来列举一下Latch出现竞争的几种常见情况:
1、cache buffers chains
2、shared pool
3、library cache
当然,我们需要一个一个来进行解释和分析,首先我们先来说下cache buffers chains
关于LATCH产生得解释:
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 for these latches can be caused by:
- Very long buffer chains.
- very very heavy access to the same blocks.
现在对数据库的实际操作过程:
SQL> select count(*)
from v$latch_children
where misses > 0
and name = 'cache buffers chains';
COUNT(*)
----------
2048
SQL> select addr, name, misses
from v$latch_children
where misses > 0
and name = 'cache buffers chains'
order by misses desc;
ADDR NAME MISSES
-------- ---------------------------------------------------------------- ----------
69CC28BC cache buffers chains 1591
69A3CF1C cache buffers chains 1591
69CBDDFC cache buffers chains 1589
69B92DFC cache buffers chains 1586
69C5DEBC cache buffers chains 1585
69AB0354 cache buffers chains 1585
69A70F9C cache buffers chains 1585
69A81F54 cache buffers chains 1585
SQL> select bh.addr, obj.name obj_name, bh.tch touch
from x$bh bh,
sys.file$ f,
v$datafile fl,
sys.obj$ obj,
sys.ts$ ts
where fl.file# = f.file#
and bh.file# = fl.file#
and obj.dataobj# = bh.obj
and bh.ts# = ts.ts#
and bh.HLADDR in(
select addr from v$latch_children where misses>0 and name='cache buffers chains'
)
and bh.tch > 0
order by bh.tch desc;
ADDR OBJ_NAME TOUCH
-------- ------------------------------ ----------
B6FD3078 IDX_GCTID_IUID_GM634 24
B6FD3078 REG_LOG 8
B6FD2F9C AGENT_CARD_TYPE 7
B6FD3078 RESELLER_AGENTCARD_PRICE 6
B6FD3078 RESELLER_LOG 6
B6FD3078 IDX_ACL_AGENTID_LOGTIME 6
B6FD3078 RESELLER_LOG 6
就是上面涉及到的这些对象,造成LATCH
SQL> select COUNT(*)
from x$bh bh, sys.file$ f, v$datafile fl, sys.obj$ obj, sys.ts$ ts
where fl.file# = f.file#
and bh.file# = fl.file#
and obj.dataobj# = bh.obj
and bh.ts# = ts.ts#
and bh.HLADDR = [x$bh.addr] --物理地址
and bh.tch > 0;
COUNT(*)
----------
51
一段文档资料:
Under 8.0, the default was next_prime(db_block_buffers/4), and the
number of _db_block_hash_latches was 1:1 with the number of buckets.
Under 8i, the world changed a lot. The default number of hash buckets
is 2 * db_block_buffers, but the latches work differently. It's
really not necessary to have one latch per hash chain, so, Oracle made
them a pooled resource. When you need to interrogate a hash chain,
you grab a latch from the pool and assign it to a hash chain. That
prevents anyone else from modifying the chain or it's contents while
your process is using it. So, in 8i, the size of the latch pool is
dynamic but is set to 1024 for most cases. It's smaller for very
small buffer caches and larger for very large buffer caches. The
formula is:
if (db_block_buffers < 2052) then
db_block_hash_latches = 2^trunc(log(2,db_block_buffers - 4) - 1)
else if(2052 =< db_block_buffers <= 131075) then
db_block_hash_latches = 1024
else if(db_block_buffers > 131075)
db_block_hash_latches = 2^trunc(log(2,db_block_buffers - 4) - 6)
end if
So, under 8i, you probably don't need to touch _db_block_hash_buckets,
as 2 * db_block_buffers is almost certainly more than adequate. And
unless you're dealing huge numbers of concurrent users and a
relatively small buffer cache, you probably don't need to mess with
_db_block_hash_latches, either.
增大 _db_buffer_hash_latches 可以更快速的查找到 blocks 并且降低 cache buffer chains 等待
我的操作系统是9I,db_block_buffers 为DB_CACHE_SIZE,如果我调整此参数,那么我应该是采取
db_block_hash_latches = 2^trunc(log(2,DB_CACHE_SIZE - 4) - 6)
SQL> show parameter db_cache_size;
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_cache_size big integer 1073741824
SQL> select name,
value,
decode(isdefault, 'TRUE', 'Y', 'N') as "Default",
decode(ISEM, 'TRUE', 'Y', 'N') as SesMod,
decode(ISYM, 'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') as SysMod,
decode(IMOD, 'MODIFIED', 'U', 'SYS_MODIFIED', 'S', 'N') as Modified,
decode(IADJ, 'TRUE', 'Y', 'N') as Adjusted,
description
from ( --GV$SYSTEM_PARAMETER
select x.inst_id as instance,
x.indx + 1,
ksppinm as name,
ksppity,
ksppstvl as value,
ksppstdf as isdefault,
decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') as ISEM,
decode(bitand(ksppiflg / 65536, 3),
1,
'IMMEDIATE',
2,
'DEFERRED',
'FALSE') as ISYM,
decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 'FALSE') as IMOD,
decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE') as IADJ,
ksppdesc as description
from x$ksppi x, x$ksppsv y
where x.indx = y.indx
and substr(ksppinm, 1, 1) = '_'
and x.inst_id = USERENV('Instance'))
where name = '_db_block_hash_latches'
order by name;
NAME VALUE
------------------------------ ---------------
_db_block_hash_latches 2048
SQL> select power(2,trunc(log(2,1073741824 - 4) - 6)) from dual;
POWER(2,TRUNC(LOG(2,1073741824-4)-6))
-------------------------------------
8388608
总得来说,LATCH产生得原因还是从应用入手,不要期望通过调整某些参数达到立杆见影的效果,热块、大的逻辑读和物理读,全表扫描都是会导致产生LATCH得原因。
latch free,相信跟大家并不陌生,在v$session_wait和Top5中会出现,当然出现类似的内容,就证明Latch产生了竞争,并且已经影响到了你的系统性能。
首先我们来列举一下Latch出现竞争的几种常见情况:
1、cache buffers chains
2、shared pool
3、library cache
当然,我们需要一个一个来进行解释和分析,首先我们先来说下cache buffers chains
关于LATCH产生得解释:
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 for these latches can be caused by:
- Very long buffer chains.
- very very heavy access to the same blocks.
现在对数据库的实际操作过程:
SQL> select count(*)
from v$latch_children
where misses > 0
and name = 'cache buffers chains';
COUNT(*)
----------
2048
SQL> select addr, name, misses
from v$latch_children
where misses > 0
and name = 'cache buffers chains'
order by misses desc;
ADDR NAME MISSES
-------- ---------------------------------------------------------------- ----------
69CC28BC cache buffers chains 1591
69A3CF1C cache buffers chains 1591
69CBDDFC cache buffers chains 1589
69B92DFC cache buffers chains 1586
69C5DEBC cache buffers chains 1585
69AB0354 cache buffers chains 1585
69A70F9C cache buffers chains 1585
69A81F54 cache buffers chains 1585
SQL> select bh.addr, obj.name obj_name, bh.tch touch
from x$bh bh,
sys.file$ f,
v$datafile fl,
sys.obj$ obj,
sys.ts$ ts
where fl.file# = f.file#
and bh.file# = fl.file#
and obj.dataobj# = bh.obj
and bh.ts# = ts.ts#
and bh.HLADDR in(
select addr from v$latch_children where misses>0 and name='cache buffers chains'
)
and bh.tch > 0
order by bh.tch desc;
ADDR OBJ_NAME TOUCH
-------- ------------------------------ ----------
B6FD3078 IDX_GCTID_IUID_GM634 24
B6FD3078 REG_LOG 8
B6FD2F9C AGENT_CARD_TYPE 7
B6FD3078 RESELLER_AGENTCARD_PRICE 6
B6FD3078 RESELLER_LOG 6
B6FD3078 IDX_ACL_AGENTID_LOGTIME 6
B6FD3078 RESELLER_LOG 6
就是上面涉及到的这些对象,造成LATCH
SQL> select COUNT(*)
from x$bh bh, sys.file$ f, v$datafile fl, sys.obj$ obj, sys.ts$ ts
where fl.file# = f.file#
and bh.file# = fl.file#
and obj.dataobj# = bh.obj
and bh.ts# = ts.ts#
and bh.HLADDR = [x$bh.addr] --物理地址
and bh.tch > 0;
COUNT(*)
----------
51
一段文档资料:
Under 8.0, the default was next_prime(db_block_buffers/4), and the
number of _db_block_hash_latches was 1:1 with the number of buckets.
Under 8i, the world changed a lot. The default number of hash buckets
is 2 * db_block_buffers, but the latches work differently. It's
really not necessary to have one latch per hash chain, so, Oracle made
them a pooled resource. When you need to interrogate a hash chain,
you grab a latch from the pool and assign it to a hash chain. That
prevents anyone else from modifying the chain or it's contents while
your process is using it. So, in 8i, the size of the latch pool is
dynamic but is set to 1024 for most cases. It's smaller for very
small buffer caches and larger for very large buffer caches. The
formula is:
if (db_block_buffers < 2052) then
db_block_hash_latches = 2^trunc(log(2,db_block_buffers - 4) - 1)
else if(2052 =< db_block_buffers <= 131075) then
db_block_hash_latches = 1024
else if(db_block_buffers > 131075)
db_block_hash_latches = 2^trunc(log(2,db_block_buffers - 4) - 6)
end if
So, under 8i, you probably don't need to touch _db_block_hash_buckets,
as 2 * db_block_buffers is almost certainly more than adequate. And
unless you're dealing huge numbers of concurrent users and a
relatively small buffer cache, you probably don't need to mess with
_db_block_hash_latches, either.
增大 _db_buffer_hash_latches 可以更快速的查找到 blocks 并且降低 cache buffer chains 等待
我的操作系统是9I,db_block_buffers 为DB_CACHE_SIZE,如果我调整此参数,那么我应该是采取
db_block_hash_latches = 2^trunc(log(2,DB_CACHE_SIZE - 4) - 6)
SQL> show parameter db_cache_size;
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_cache_size big integer 1073741824
SQL> select name,
value,
decode(isdefault, 'TRUE', 'Y', 'N') as "Default",
decode(ISEM, 'TRUE', 'Y', 'N') as SesMod,
decode(ISYM, 'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') as SysMod,
decode(IMOD, 'MODIFIED', 'U', 'SYS_MODIFIED', 'S', 'N') as Modified,
decode(IADJ, 'TRUE', 'Y', 'N') as Adjusted,
description
from ( --GV$SYSTEM_PARAMETER
select x.inst_id as instance,
x.indx + 1,
ksppinm as name,
ksppity,
ksppstvl as value,
ksppstdf as isdefault,
decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') as ISEM,
decode(bitand(ksppiflg / 65536, 3),
1,
'IMMEDIATE',
2,
'DEFERRED',
'FALSE') as ISYM,
decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 'FALSE') as IMOD,
decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE') as IADJ,
ksppdesc as description
from x$ksppi x, x$ksppsv y
where x.indx = y.indx
and substr(ksppinm, 1, 1) = '_'
and x.inst_id = USERENV('Instance'))
where name = '_db_block_hash_latches'
order by name;
NAME VALUE
------------------------------ ---------------
_db_block_hash_latches 2048
SQL> select power(2,trunc(log(2,1073741824 - 4) - 6)) from dual;
POWER(2,TRUNC(LOG(2,1073741824-4)-6))
-------------------------------------
8388608
总得来说,LATCH产生得原因还是从应用入手,不要期望通过调整某些参数达到立杆见影的效果,热块、大的逻辑读和物理读,全表扫描都是会导致产生LATCH得原因。
发表评论
-
一次oracle无法open的解决
2009-01-16 13:59 3617这几天因为公司的复杂查询出现性能的问题(说实话本来就没设计好, ... -
Oracle10g 自动共享内存管理
2009-01-14 13:25 38815.6 自动共享内存管理 从Oracle 10g开始,Or ... -
如何改善Oracle的索引
2009-01-12 16:40 15271、速度因素 PARALLEL选项:当创建索引时,O ... -
PX Deq: Execute Reply 案例说明
2009-01-03 09:55 29981 背景:Oracle 数据库在执行sql时,会自动的选择较 ... -
MySQL优化经验——第一讲
2008-12-28 19:41 1351今天突然想起自己 ... -
oracle中对workarea_size_policy和sort_area_size的总结
2008-12-19 12:06 8832在实际的工作中,想必很多人会对SORT_AREA_SIZE和s ... -
Oracle专用服务器与共享服务器的区别
2008-12-19 11:51 3351在建立Oracle数据库的时候,应该会在数据库建立助手向导上面 ... -
CBO学习笔记
2008-12-18 23:09 1396cost of b-tree access 这 ... -
Oracle高级SQL调优:CLUSTER_FACTOR案例研究
2008-12-18 22:27 1809大家在大型数据库生产系统的运维中可能会遇到这样一个问题,一条查 ... -
Oracle分析函数RANK()|ROW_NUMBER()|LAG()使用详解
2008-12-16 14:58 3069ROW_NUMBER()的使用方法: ROW_NUMB ... -
index和rowid的一点关系
2008-12-16 14:18 1462相信很多朋友在rowid和index之间都会有些疑问,今天在w ... -
关于MySQL的查询缓存收
2008-12-13 21:21 1160关于MySQL的查询缓存收 原理 QueryCache(下面简 ... -
oracle 被锁,解锁,阻塞语句
2008-12-12 18:35 2636//查询被锁的表 select A.s ... -
通过Oracle10g的FLASHBACK_TRANSACTION_QUERY指定事务的历史信息
2008-12-12 13:05 3140在数据库操作中,我们经常会遇到余下情况: 1.莫名其妙数据被D ... -
对于Oracle中DML使用UNDO的一些看法
2008-12-11 17:53 1217insert操作回滚段中只记录这些记录的ROWID updat ... -
oracle中x$ksppi和x$ksppcv详解
2008-12-09 17:22 3352SQL> desc x$ksppi 名称 ... -
ORA-600 [2103]错误及CF enqueue竞争
2008-12-09 17:21 1200昨天,客户的一套Oracle 10.2.0.3 RAC环境遇到 ... -
Oracle的redo 和undo的区别
2008-12-05 15:26 2584redo--> undo-->datafile i ... -
从 v$session 视图获取客户端 IP 地址
2008-11-18 19:42 2615缺省从 v$session 中不能直接获得客户端 IP ... -
oracle中聚合函数RANK和dense_rank的使用
2008-04-18 17:23 1339聚合函数RANK 和 dense_rank ...
相关推荐
Latch free等待事件 Latch和lock的异同
Oracle中的Latch和Lock.pdf
Oracle性能诊断之——Latch free
Oracle Latch Contention
- latch是一种Oracle低级别的内存保护机制,用于序列化对于非常短时间的内存结构的并发访问。 - latch是一种简单的内存结构。 - latch的大小通常在100-200B左右(取决于Oracle的版本和Oracle运行的平台)。 - latch...
确定系统慢的原因: select * from v$session_wait where event != 'client message' and event not like '%NET%' and wait_time = 0 and sid > 5;
oracle latch描述以及使用
oracle latch介绍,介绍的非常非常非常非常非常非常非常非常非常非常非常非常非常好
以下是对Oracle中Lock与Latch的相同点以及不同点进行了详细的分析介绍,需要的朋友参考下
oracle动态性能表oracle动态性能表oracle动态性能表
自己总结的芯片latchup原理,初学者必看。
等待事件row cache lock,latch row cache objects处理过程
有关oracle当中锁的描述,Latch和Lock的区别,挺不错的。
本文章是关于fpga中latch简介。
深入解析Oracle -栓锁竞争与Latch优化实践
latch讲解,latch讲解,绝对经典
LATCH HA解决方案系统结构:两台主机A,B共享一个磁盘阵列,A为工作机,B为备份机。它们之间以一根心跳线来连接,这被称为“心跳检测”,主要通过一条 RS232检测链路来完成。LATCH HA也采用了网络ping检测来验证系统...
Latch up测试标准
国外论文,从最基本的角度讲述latch up原理