enq: HW - contention

Oracle数据库性能优化

最新推荐文章于 2024-08-06 23:02:37 发布

原创最新推荐文章于 2024-08-06 23:02:37 发布 · 498 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

11 篇文章

订阅专栏

本文分析了Oracle数据库中enq:HW-contention等待事件严重的问题，通过查询表空间发现多个表空间使用率过高，并采取措施扩展了SOE表空间，从15GB调整至150GB，有效缓解了数据库性能瓶颈。

SQL> l
1 select s.event, count(s.event)
2 from v$session s, v$process p
3 where s.PADDR = p.ADDR
4 group by s.event
5* order by 2 desc
SQL>
EVENT                               COUNT(S.EVENT)
---------------------------------------------------------------- --------------
enq: HW - contention                           1300
PL/SQL lock timer                           100
rdbms ipc message                           26
Space Manager: slave idle wait                       7
DIAG idle wait                               2
Streams AQ: qmn slave idle wait                    2
Streams AQ: qmn coordinator idle wait                   1
Streams AQ: waiting for time management or cleanup tasks       1
VKTM Logical Idle Wait                           1
SQL*Net message to client                       1
smon timer                               1
pmon timer                               1
SQL*Net message from client                       1
log file parallel write                        1

14 rows selected.

SQL>

由以上可见enq: HW - contention等待事件严重
TABLESPACE_NAME    SIZE_G   FREE_G   USED_PCT
------------------------------ --------------- --------------- ----------
SYSTEM                   0.75       0.01   98.81%
SOE                   91.19       3.72   95.92%
SYSAUX                   0.74       0.03   95.90%
EXAMPLE                0.34       0.04   89.58%
USERS                   0.00       0.00   81.25%
UNDOTBS1               30.77       13.56   55.91%

6 rows selected.

查询表空间发现表空间使用率很高

扩展表空间
SQL> alter database datafile '/oradata/ccictdb/ccictdb/soe.dbf' resize 150g;

Database altered.

SQL>