how to reduce dml locks? [message #555512] |
Thu, 24 May 2012 12:24 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/8485c6f7790073d2e80643bb83e6c33c?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/da93b8d9f57f4be8145750e5748ac9cd?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68659 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
|
|
|