[20140311]等待事件enq HW - contention.txt
生产系统业务高峰时出现enq: HW - contention，一般这个主要是插入记录非常密集的情况下出现，自己对系统分析看看主要是那些对象
引起的问题。
SQL> @ver
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SELECT event,
sql_id,
TO_CHAR (sample_time, 'hh24') hours,
COUNT (*)
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE event LIKE 'enq: HW - contention'
GROUP BY event, sql_id, TO_CHAR (sample_time, 'hh24')
ORDER BY COUNT (*) DESC;
EVENT SQL_ID HO COUNT(*)
--------------------- ------------- -- ------------
enq: HW - contention dhs7skn7kdxdr 11 53
enq: HW - contention dhs7skn7kdxdr 10 39
enq: HW - contention dhs7skn7kdxdr 16 23
enq: HW - contention dhs7skn7kdxdr 17 23
enq: HW - contention dhs7skn7kdxdr 09 12
enq: HW - contention 66mu5zrxm7u61 11 3
enq: HW - contention dhs7skn7kdxdr 15 3
enq: HW - contention dhs7skn7kdxdr 12 3
enq: HW - contention 66mu5zrxm7u61 16 2
enq: HW - contention dhs7skn7kdxdr 08 2
enq: HW - contention 66mu5zrxm7u61 15 1
enq: HW - contention 66mu5zrxm7u61 09 1
enq: HW - contention 66mu5zrxm7u61 08 1
13 rows selected.
--很明显主要出现在10,11,16,17点的业务高峰，sql_id='dhs7skn7kdxdr'.
--但是奇怪我查询相关视图v$sql,dba_hist_sql*都没有查询到对应的sql语句。
SQL> select * from v$sql where sql_id in ('dhs7skn7kdxdr','66mu5zrxm7u61');
no rows selected
SQL> select * from v$event_name where name='enq: HW - contention';
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
------------ ------------ -------------------- -------------------- -------------------- -------------------- ------------- ------------ --------------------
180 1645217925 enq: HW - contention name|mode table space # block 3290255840 2 Configuration
SELECT event, sql_id, sample_time, p2, p3 FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE event LIKE 'enq: HW - contention' AND sql_id = 'dhs7skn7kdxdr' AND ROWNUM
EVENT SQL_ID SAMPLE_TIME P2 P3
--------------------- ------------- -------------------------- ------------ ------------
enq: HW - contention dhs7skn7kdxdr 04-MAR-14 11.51.44.145 AM 6 25176955
--看了一些文档才知道参数3是指块地址RBA而非块。
select dbms_utility.data_block_address_file(&1) rfile#, dbms_utility.data_block_address_block(&&1) block# from dual;
RFILE# BLOCK#
------------ ------------
6 11131
SQL> select segment_name,file_id,block_id from dba_extents where file_id = 6 and 11131 between block_id and block_id + blocks - 1;
SEGMENT_NAME FILE_ID BLOCK_ID
-------------------------- ------------ ------------
SYS_LOB0000059813C00002$$ 6 11129
SQL> select segment_name,header_file,header_block from dba_segments where segment_name='SYS_LOB0000059813C00002$$';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------------- ------------ ------------
SYS_LOB0000059813C00002$$ 6 11131
--很明显是lob段的段头在增加分配空间时存在争用。
--换一个思路查询，V$open_cursor视图看看。
SQL> select distinct sql_id,sql_text from V$OPEN_CURSOR where sql_id in ('dhs7skn7kdxdr','66mu5zrxm7u61');
SQL_ID SQL_TEXT
------------- ------------------------------------------------------------
66mu5zrxm7u61 table_e_a_e981_5_0_0
dhs7skn7kdxdr table_e_a_e9a5_2_0_0
SQL> select count(*),KGLNAOBJ,kglobt03 from x$kglcursor where kglobt03 in ('dhs7skn7kdxdr','66mu5zrxm7u61') group by
KGLNAOBJ,kglobt03;
COUNT(*) KGLNAOBJ KGLOBT03
------------ ---------------------------------------- -------------
5 table_e_a_e981_5_0_0 66mu5zrxm7u61
5 table_e_a_e9a5_2_0_0 dhs7skn7kdxdr
-- 确实是lob类型的数据。而且在业务高峰插入密集。
SYS@test> @16to10 e981
16 to 10 DEC
------------
59777
SYS@test> @16to10 e9a5
16 to 10 DEC
------------
59813
--使用select * from dba_objects where object_id in (59777,59813);可以确定是那个表。table_e_a_e981_5_0_0 ，5表示字段的顺序。
--仅仅分析其中一个看看。
SELECT * FROM dba_lobs WHERE (OWNER, TABLE_NAME) IN (SELECT owner, object_name FROM dba_objects WHERE object_id = 59813);
SQL> SELECT SEGMENT_NAME,INDEX_NAME FROM dba_lobs WHERE (OWNER, TABLE_NAME) IN (SELECT owner, object_name FROM
dba_objects WHERE object_id = 59813);
SEGMENT_NAME INDEX_NAME
------------------------------ ------------------------------
SYS_LOB0000059813C00002$$ SYS_IL0000059813C00002$$
--64k=>1M->8M->64M.
--解决方法也许只能预先给这个段分配好空间，其他好像也没有什么好方法。
alter table .
google 找到如下连接：
http://dbasolved.com/2014/03/05/combat-with-an-enqueue-wait-event-enq-hwcontention/
http://www.oracledatabase12g.com/archives/%E5%88%A9%E7%94%A844951-event%E8%A7%A3%E5%86%B3lob-space-enq-hw-contention%E7%AD%89%E5%BE%85%E4%BA%89%E7%94%A8.html
Opening the SR has lead me to dig a bit and do some research on extent allocation and LOBs while waiting on Oracle's
answer. Prior to Oracle Database 11g (11.2.0.1), there is a known bug (6376915). This bug is related to high watermark
enqueue contention for ASSM LOB segments (Note ID: 6376915.8). This bug was actually fixed in release 11.2.0.1 but it
needs to be "turned on" in later releases. To turn the fix for this bug on in 11.2.0.2+, an event needs to be set in the
spfile.
SQL> ALTER SYSTEM SET EVENT="44951 TRACE NAME CONTEXT FOREVER, LEVEL " scope=spfile;
By setting this event between 1 and 1024 will identify the number of chunks to be cleaned up each time a reclamation
operation is performed. In turn this reduces the number of requests against the high watermark enqueue.
Once the event has been set in the spfile, the database instance needs to be rebooted to make the change effective.
阅读(2461) | 评论(0) | 转发(0) |
0
上一篇：[20140301]直方图与优化.txt
下一篇：[20140311]toad 12 alert log viewer.txt
相关热门文章
- ORACLE 变异表解决方法
- ORA-00600错误分析
- 初识ORACLE的审计功能
- Oracle的OLEDB
- 学ORACLE随笔
- PL/SQL Challenge 每日一题：2...
- 如何读懂SQL PROFILE 里产生的...
- 如何调用标准程序打印财务凭证...
- 关于Query查询到数据不全的问...
- mysql查询information_schema...
给主人留下些什么吧！~~
评论热议
请登录后评论。
登录 注册
盛拓传媒简介 | 关于IT168 | 合作伙伴 | 广告服务 | 使用条款 | 投稿指南 | 诚聘精英 | 联系我们 | 苹果论坛 | 网站导航 | 往日回顾
北京皓辰网域网络信息技术有限公司. 版权所有 京ICP证:060528号 北京市公安局海淀分局网监中心备案编号：1101082001
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员 测绘资质证书（乙测资字11005067） 网络文化经营许可证
感谢所有关心和支持过ITPUB的朋友们