Home » RDBMS Server » Performance Tuning » how to reduce dml locks? (oracle 11.2.0.1.0,windows server 2008 R2)
how to reduce dml locks? [message #555512] Thu, 24 May 2012 12:24 Go to next message
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 #555514 is a reply to message #555512] Thu, 24 May 2012 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is it any performance prolem?
no

please Read The Fine Manual for all performance related issues

http://docs.oracle.com/cd/E11882_01/server.112/e16638/toc.htm
Re: how to reduce dml locks? [message #555517 is a reply to message #555512] Thu, 24 May 2012 12:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
After ran db health check, my database report gives the following details


Through away this script or tool... TOAD I bet.

Regards
Michel
Re: how to reduce dml locks? [message #555522 is a reply to message #555517] Thu, 24 May 2012 12:47 Go to previous messageGo to next message
vijenderkdba
Messages: 28
Registered: May 2012
Location: mumbai
Junior Member
yes...from TOAD.
Re: how to reduce dml locks? [message #555526 is a reply to message #555522] Thu, 24 May 2012 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So through away TOAD, it is just b... and you post one more proof.

Regards
Michel
Re: how to reduce dml locks? [message #559111 is a reply to message #555512] Thu, 28 June 2012 14:45 Go to previous messageGo to next message
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
Michel Cadot
Messages: 68624
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
Previous Topic: Tuning Queries
Next Topic: Performance issue
Goto Forum:
  


Current Time: Thu Mar 28 05:23:57 CDT 2024