So… so you think you are ready to migrate to Oracle 11g ?

I tought I was … perfect migration (from Ora9i) in test system with the good old export/import , all tests ok,  perfect migration in production system.. . ready, set, GO .

And then I started to notice red mountains on the Active-sessions chart … locks … lots of them … customer calling and slowly slipping into panic while I am trying to understand what the f**k is going on.

As printed on HGTTG  (and on a print behind my desk) DON’T PANIC .

CLUES:

• sessions locking themselves on tables they don’t interact directly
• sessions locked waiting on event “enq: TM – contention”
• the only link between the locked instruction and the locking object is a network of few triggers and several foreign keys constraints (yes, sometimes programmers use them)

And here I have to thank :

• Mr Charles Hopper ( it will take me a new life to follow his blog but I’ll do it )
• Mr Jonathan Lewis
• Mr Thomas Kyte
• Google

And to link the following pages that led me to a solution:

http://hoopercharles.wordpress.com/2011/06/19/addressing-tm-enqueue-contention-what-is-wrong-with-this-quote/

http://hoopercharles.wordpress.com/2010/01/07/deadlock-on-oracle-11g-but-not-on-10g/

http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html (Actually I found this 1st : http://oraqa.com/2006/02/12/how-to-locate-unindexed-foreign-keys/ )

The moral of the story is :

WHEN YOU WILL MIGRATE YOUR DB TO ORACLE 11g  IS HIGHLY PROBABLE THAT UNINDEXED FOREIGN KEYS WILL GENERATE LOCKS….  SO BEFORE GOING LIVE DON’T FORGET TO PUT INDEXES ON ALL YOUR FK!

Here is my easy review of  Tom’s script to create the missing indexes for the connected user (this will generate the commands as output then use them as you like changing the names of  indexes, tablespaces etc. ) :

select 'CREATE  INDEX  I_FK'||rn||'_'||table_name||' ON '||
table_name||' ('||ccc||') TABLESPACE MY_INDEX_TBS;' CMD
from
(
select decode( b.table_name, NULL, '****', 'ok' ) Status,
    a.table_name
    , a.columns ccc
    , b.columns
    , row_number() over (partition by a.table_name order by a.columns) rn
from
( select substr(a.table_name,1,30) table_name,
   substr(a.constraint_name,1,30) constraint_name,
      max(decode(position, 1,     substr(column_name,1,30),NULL)) ||
      max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
      max(decode(column_position, 1,     substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
      max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
    from user_ind_columns
   group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'
  )
  where status != 'ok'

Of course it worked for me but there is no warranty that your server will not explode when you use this query or the commands generated 😉