enqueues and locks

最新推荐文章于 2022-03-04 10:41:19 发布

oraclehu

最新推荐文章于 2022-03-04 10:41:19 发布

阅读量680

点赞数

分类专栏： Oracle ERP/DBA 文章标签： table sql module action terminal command

本文链接：https://blog.csdn.net/oraclehu/article/details/563199

版权

Oracle ERP/DBA 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

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