how to reduce dml locks? [message #555512] |
Thu, 24 May 2012 12:24 |
|
vijenderkdba
Messages: 28 Registered: May 2012 Location: mumbai
|
Junior Member |
|
|
Hi All,
After ran db health check, my database report gives the following details
Is it any performance prolem?
dml_locks OK. dml_locks = 3396, transactions = 849
Thanks,
Vij
|
|
|
|
|
|
|
Re: how to reduce dml locks? [message #559111 is a reply to message #555512] |
Thu, 28 June 2012 14:45 |
|
alan.kendall@nfl.com
Messages: 163 Registered: June 2012 Location: Culver City, California
|
Senior Member |
|
|
I run the following query to see sessions that are locked currently.
select sid,serial#,machine,
to_char(s.logon_time,'DD-MON-RR HH24:MI') login,i.instance_name db,
s.seconds_in_wait sec_wait,s.username,s.event,s.status,
s.program,s.machine,s.module,s.terminal
from gv$session s, gv$instance i where i.inst_id=s.inst_id
and s.status='ACTIVE' and s.username is not null
order by seconds_in_wait;
SEC_WAIT USERNAME EVENT
-------- ----------- -------------------------
0 ENWEBP1P PX Deq: Execution Msg
0 SITE_USER SQL*Net message to client
0 ENWEBP1P PX Deq: Execution Msg
0 SITE_USER PX Deq: Join ACK
0 ENWEBP1P PX Deq: Execution Msg
0 ENWEBP1P PX Deq: Execute Reply
0 SITE_USER SQL*Net message to client
0 ENWEBP1P PX Deq: Execution Msg
0 SITE_USER direct path read
0 SITE_USER gc cr request
1 SYS class slave wait
4 PUBLIC class slave wait
6 SYS class slave wait
I have been able to identify sessions that are using UNDO segments as candidates to kill when sessions are locked. I use the following query to determine what is using UNDO currently.
select s.sid,s.serial#,username,t.used_ublk "UndoBLKS", terminal, osuser,
t.start_time, r.name,
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr;
NAME SID SERIAL# USERNAME MACHINE USED_UBLK MEG_UNDO
---------- ----- ------- -------------- ---------- ---------- ----------
_SYSSMU10_ 455 55859 BATCH_NFL_USER prodetl01 1 .0078125
[Updated on: Thu, 28 June 2012 14:47] Report message to a moderator
|
|
|
Re: how to reduce dml locks? [message #559123 is a reply to message #559111] |
Thu, 28 June 2012 23:22 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
If you can kill sessions then why do you start them?
Locks are part of usage of multi-user databases, if you don't want any then run in single-user mode (and use MS/Access
Regards
Michel
|
|
|