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
Printable View
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
if you don't mind can i know,
how you identify which update statement gets locked up.
thank you
-Raja
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???Quote:
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
thank you Rohit!
Have you worked out why this operation is so prone to getting locked out?
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..
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.
Good catch!Quote:
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"
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 :D
That code is simply useless, what do u think that code tells .. blocking session?? -- No way..Quote:
Originally posted by OracleDoc
People might hurt thier fingers typing out that much code Rohit :D
It jus tells u what objects are locked due any DML & the SQL Statement involved..
Abhay.