Locking

[Rohit]

In one of our boxes a particular Update statement get locked up frequently and we usually kill the session. How do we go about resolving this.

Thanks

[rajabalachandra]

if you don't mind can i know,
how you identify which update statement gets locked up.

thank you

-Raja

[Rohit]

by using

select a.sid, a.serial#, a.sql_hash_value, substr(c.object_name,1,25) from v$session a, v$locked_object b, dba_objects c where a.sid = b.sessio
n_id and b.object_id=c.object_id

then use the hash value in

select sql_text,
USERS_OPENING,
EXECUTIONS,
USERS_EXECUTING,
LOADS,
FIRST_LOAD_TIME,
INVALIDATIONS,
PARSE_CALLS,
DISK_READS,
BUFFER_GETS
from v$sqlarea where hash_value=upper('&hash_value')


You can combine bothe quieries to directly fetch teh sql if you want to

[abhaysk]

Originally posted by Rohit
by using

select a.sid, a.serial#, a.sql_hash_value, substr(c.object_name,1,25) from v$session a, v$locked_object b, dba_objects c where a.sid = b.sessio
n_id and b.object_id=c.object_id

then use the hash value in

select sql_text,
USERS_OPENING,
EXECUTIONS,
USERS_EXECUTING,
LOADS,
FIRST_LOAD_TIME,
INVALIDATIONS,
PARSE_CALLS,
DISK_READS,
BUFFER_GETS
from v$sqlarea where hash_value=upper('&hash_value')


You can combine bothe quieries to directly fetch teh sql if you want to

I suppose u know what u r talking abt???

[rajabalachandra]

thank you Rohit!

[slimdave]

Have you worked out why this operation is so prone to getting locked out?

[Rohit]

Its from an Oracle Apps Application. We regularly Kill this session whihc runs this update and gets locked up. Can we resolve it without killing the session? Or should any modifiaction be done to the application? Pls do advice..

[abhaysk]

if u know a bit abt locking then, u will understand wass the difference in Locking & Blocking.. I hope u really dont mean "When Update Statement Is Run, Table/Object Involved Is Locked Up (ROWS Invloved)"

Abhay.

[OracleDoc]

Originally posted by abhaysk
if u know a bit abt locking then, u will understand wass the difference in Locking & Blocking.. I hope u really dont mean "When Update Statement Is Run, Table/Object Involved Is Locked Up (ROWS Invloved"

Good catch!

Another simple way to see if that process is blocking anyone else would be to use OEM's lock manager. People might hurt thier fingers typing out that much code Rohit

[abhaysk]

Originally posted by OracleDoc
People might hurt thier fingers typing out that much code Rohit

That code is simply useless, what do u think that code tells .. blocking session?? -- No way..

It jus tells u what objects are locked due any DML & the SQL Statement involved..

Abhay.