enqueues and locks

statspack dump

Top 5 Wait Events
~~~~~~~~~~~~~~~~~                                             Wait
% Total
Event                                               Waits  Time (cs)
Wt Time
-------------------------------------------- ------------ ------------
-------
enqueue                                             5,810    1,730,154
 74.95
PL/SQL lock timer                                   4,209      473,702
 20.52
pipe put                                              271       27,826
  1.21
latch free                                         36,676       16,935
   .73
db file sequential read                            31,151       11,278
   .49

Enqueue activity for DB: PROD  Instance: PROD  Snaps: 294 -306
-> ordered by waits desc, gets desc

Enqueue            Gets      Waits
---------- ------------ ----------
TX               98,227         98

result : TX enqeues locks
find it lock objects and which sql lock it

select * from v$system_event se where UPPER(se.EVENT)='ENQUEUE'
EVENT     TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
----- ----------- -------------- ----------- ------------
enqueue    3181           2846      880619 276.83715812

when total_waits adding

 select * from v$session s where s.USERNAME='CRG' and s.LOCKWAIT is not
null;

SADDR           SID    SERIAL#     AUDSID PADDR         USER# USERNAME
                        COMMAND    OWNERID TADDR    LOCKWAIT STATUS
SERVER       SCHEMA# SCHEMANAME                     OSUSER
           PROCESS   MACHINE
              TERMINAL                       PROGRAM
                      TYPE       SQL_ADDRESS SQL_HASH_VALUE
PREV_SQL_ADDR PREV_HASH_VALUE MODULE
       MODULE_HASH ACTION                           ACTION_HASH
CLIENT_INFO
FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK#
ROW_WAIT_ROW# LOGON_TIME  LAST_CALL_ET PDML_ENABLED FAILOVER_TYPE
FAILOVER_METHOD FAILED_OVER RESOURCE_CONSUMER_GROUP
PDML_STATUS PDDL_STATUS PQ_STATUS

select * from v$lock l where l.TYPE='TX'

link l.kaddr = s.lockwait

sql casue lock
================
select * from v$sqltext st where st.hash_value =  $l.sql_hash_value

object be locked
=================
select * from v$dba_dml_locks ddl where ddl.sid= $s.sid

TX enqeues raise because
1: the table has bm index be updated
2: unqion index table be updated long time wihtout commit;
3: many user operate a block ; fix : need more itl ,bigger  initrans
and maxtrans of table, increase pctfree valuse of table.


    oraclehu  2005-10-28 03:39 PM

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值