tag:blogger.com,1999:blog-37174762646153861092024-02-19T02:51:03.133+01:00Alen OblakAlen Oblakhttp://www.blogger.com/profile/07343361836188211697noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-3717476264615386109.post-89935170950636636472009-07-09T21:02:00.002+02:002009-10-24T19:21:03.355+02:00enq: TM - contention<div style="text-align: justify;">Every now and then I'm being asked by my fellow coworkers to diagnose locking issues, aka. "the application is frozen". A quick glance at <span style="font-weight: bold;">gv$session</span> or <span style="font-weight: bold;">gv$active_session_history</span> shows a lot of sessions waiting for event "<span style="font-weight: bold;">enq: TM - contention</span>".<br /></div><pre class="brush:sql"><br />select inst_id, sid, serial#, event<br />from gv$session<br /><br />select inst_id, sample_time, session_id, session_serial#, event<br />from gv$active_session_history<br /></pre><br /><div style="text-align: justify;"><a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref759">Documentation</a> tells us that this kind of wait event can be caused by missing indexes on foreign key constraints. <a href="http://asktom.oracle.com/tkyte/unindex/unindex.sql">Scripts</a> exists to find those constraints but it certainly doesn't make sense to blindly create indexes for all foreign keys. For example: you have a code table for currencies (USD, EUR,...) and a table for item prices with a foreign key constraint pointing to currency. You rarely delete a currency or query item prices by currency, so there really is no point in indexing the currency column. Additional space will be consumed for the index and additional work will be done on insert/update/delete of item prices to update the index.<br /></div><br />When locking takes place basically you want to know two things:<br /><ul><li>which index on which table is missing and<br /></li><li>which sql statement caused the locking.<br /></li></ul><br /><h3>Step # 1 - find the table</h3><br /><div style="text-align: justify;">First, you query the ASH (active session history) and find the object id, which is the table that was locked due to a missing index. The object id is reported in <span style="font-weight: bold;">p2</span> column.<br /></div><pre class="brush:sql"><br />select inst_id, sample_time, session_id, session_serial#, p2 object_id<br />from gv$active_session_history<br />where event = 'enq: TM - contention'<br /><br />INST_ID SAMPLE_TIME SESSION_ID SESSION_SERIAL# OBJECT_ID<br />1 9.7.2009 19:31:30,419 28 38 15705<br />1 9.7.2009 19:31:29,419 28 38 15705<br />1 9.7.2009 19:31:28,419 28 38 15705<br />1 9.7.2009 19:31:27,410 28 38 15705<br />1 9.7.2009 19:31:26,409 28 38 15705<br />1 9.7.2009 19:31:25,409 28 38 15705<br />1 9.7.2009 19:31:24,409 28 38 15705<br /></pre><br />You get the table name from all_object view.<br /><pre class="brush:sql"><br />select object_name<br />from all_objects<br />where object_id = 15705<br /></pre><br /><div style="text-align: justify;">Now that you know the table, you can focus on the foreign key constraints of that particular table. The ones that don't have indexes are candidates for the next step - finding out the sql that started the locking.<br /></div><h3>Step # 2 - find the sql</h3><br /><div style="text-align: justify;">All the sql statements that haven't aged out of the SGA are in the <span style="font-weight: bold;">gv$sql</span> view. Chances are, you will find the statement that caused the lock. By searching for delete operations on referenced tables, you will soon find the statement.<br /></div><pre class="brush:sql"><br />select inst_id, sql_text, sql_id, first_load_time, parsing_user_id, module, action, program_id<br />from gv$sql<br />where upper(sql_text) like 'DELETE%[REFERENCED TABLE]%'<br />order by first_load_time desc<br /></pre><br /><div style="text-align: justify;">Since the statement must have been run before the locking took place, the first_load_time must be sometime before the first sample_time from ASH. From my experience, the difference is always about a second:<br /></div><ul><br /><li>first_load_time in gv$sql is 9.7.2009 19:31:23<br /></li><li>earliest sample_time in v$active_session_history is 9.7.2009 19:31:24,409<br /></li></ul><br /><div style="text-align: justify;">Of course, this is not always the case. The delete operation could happen minutes or even hours ago but the transaction is still active, thus still holding the lock on the table. By that time, the statement could have been removed from the SGA. Despite this, I was able to track down the sql statement and the missing index successfully with this method.</div><br /><br /><div style="text-align: justify;">Once you know which sql is causing the problem, it's up to the developers to create that index or to prevent the users to issue the delete statement. A solution is to flag the row as deleted and then delete it during night time.</div>Alen Oblakhttp://www.blogger.com/profile/07343361836188211697noreply@blogger.com1