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
