Posts Tagged ‘“APPEND HINT”’

i have seen some slowness issues after upgrade from 10g to 11g and “TM lock contention lock” is worth to mention although it been long time since upgrade is done. Below is the graph after upgrade and the red portion is showing that there is some serious issue with locking. This was not the case prior to upgrade.

Locking

Looking into the details of this locking issue, i have seen that main reason for this locking is because of “TM lock contention”. Below graph is showing that there is “TM locking issue” with the database because whatever session is responsible for locking is having the “TM lock contention” wait class.

TMContentionInitial investigation revolves around the fact that this behaviour is seen when the indexes were missing on foreign key column of child tables. There’s very good explanation for this event in articles below. These are excellent source of information.

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

http://richardfoote.wordpress.com/2010/11/10/oracle11g-new-locking-modes-when-policing-fk-constraints-a-wolf-at-the-door/

So, all the missing indexes on foreign key columns created and that helped little but the issue still remains same. Below are some handy links to identify the unindex foreign keys.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4530093713805#26568859366976

http://bluefrog-oracle.blogspot.in/2013/08/indentify-unindexed-foreign-key-columns.html

After spending a lot of time finally the reason was found. This existed in 10g as well but some how it’s not causing any issue. Problem was with the APPEND hint that changed it’s behaviour from 10g to 11g. Documentaion says that direct path inserts (With APPEND hint) takes a exclusive lock at table level. Below screen is from 10g documentation itself and link of documentation is also mentioned.

docum

http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#i1009887

So, removing the APPEND hint from insert statements helped in this issue and all the queries return to normal. Oracle support says that APPEND hint in 10g (10.2.0.4) was being ignored so no issue there but in 11g it’s started following the hint and problems are seen.

There’s a very good support note 1317447.1 which explain about the change in behaviour of APPEND hint with different versions and also explained that APPEND_VALUE hint is available.

Hoping that this is save someone’s time in finding the solution for “TM Lock contention” issue 🙂

Advertisements