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