Oracle Foreign Key without Index Test

We've been having some Oracle deadlock issues that have been hard to reproduce locally. After a lot of investigation and solving of important problems that happened not to be THE problem we figured out that while we've been pretty good creating integrity constraints in the database we have not been very good about making sure that every foreign key has a corresponding index. And that can lead to problems.

So we had a situation were our documents table had a foreign key on the accounts table that was not indexed. So updating an account row lead to a whole table lock on documents (instead of just a row lock which would have happened if there was an index) and that was very bad when we had two separate processes where one was doing a bunch of accounts stuff and the other was doing a lot of documents stuff. Deadlocks for everyone!

The sad thing is that if we had just drank the Rails cool-aid about having no integrity constraints in the db we would have been fine but we got into trouble by only implementing constraints and not the indexes they work much better with.

So we found this cool bit of SQL that helped us find all the foreign keys missing an index here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4530093713805#26568859366976

And that help us solve the deadlock problem. But what about the future? If only there was a way to run this check periodically and automatically... Time for a test!



As an additional bonus, the failing output of the test tells you how to write the index you need. Big thanks to Dave Bortz for tracking down this problem -- I just swooped in and wrote the test. And took credit in this here blog.

Comments

Dean R said…
Wow, I should have read this before running my mouth off today !

One minor point - the indexed case in oracle still acquires a table lock on the child (referring) table - it's just that its a shared lock in the case of the indexed FK, and an shared row exclusive one in the non-indexed case. Who'd have thunk !
December 7, 2010 at 5:20 PM
Teracomp DBA said…
Hello Jake

Very good post and script.

I did some improvements in this scripts to check for this issue in all database schemas.

set line 1000
set head off
set pagesize 0

select 'CREATE INDEX '||owner||'.'||substr(constraint_name,1,28)||'_I ON '||owner||'.'||table_name||'('||
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)||');'
from (select b.owner,
b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select owner,
substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from dba_cons_columns ) a,
dba_constraints b
where a.owner = b.owner
and a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.owner, b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from dba_ind_columns i
where i.index_owner = cons.owner
and i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
order by owner, table_name;

set head on
set pagesize 10

I hope that helps!

My best regards,
January 12, 2012 at 5:54 PM
Post a Comment

Popular posts from this blog

What's a Good Flog Score?

I've been using Flog to measure the complexity of my Ruby code and I was wondering how others interpret Flog numbers. I'll go first with my opinions: Score of Means 0-10 Awesome 11-20 Good enough 21-40 Might need refactoring 41-60 Possible to justify 61-100 Danger 100-200 Whoop, whoop, whoop 200 + Someone please think of the children (note: these are scores for an individual method) The 20 - 60 range is interesting to me. Sometimes the complexity of what you're doing will justify the score and other times the method is in desperate need of refactoring. The highest Flog score I've seen in the wild was in the 300's. What's you're personal high and what do you think of my rankings? In other news, I've redesigned the look of my blog so if you only see my post through a reader you might want to stop on by, take a look, and tell me what you think.
Read more

SICP Wasn’t Written for You

The number of software luminaries who sing the praises of “ Structure and Interpretation of Computer Programs ” (referred to as SICP) is such a long list that you might think only a crazy person would take issue with it. However, to ignore SICP’s problems and continue to blindly recommend it seems just as crazy. SICP was the textbook for MIT’s introductory programming class and was a bit of a departure from other into to computer science textbooks at the time.  Wikipedia sums it up nicely:  “Before SICP, the introductory courses were almost always filled with learning the details of some programming language, while SICP focuses on finding general patterns from specific problems and building software tools that embody each pattern.”  Which sounds awesome, but does essentially say that abstract principles will be introduced before the nuts and bolts of a language.  If you think about that for a minute, you may see where the problems will be. When I was training to be a...
Read more

Point Inside a Polygon in Ruby

Recently my team needed to find out if a point was inside a given polygon and, as usual, we found some code on the internet that did what we wanted. And, as usual, there wasn't much explanation as to what it was doing. Here's the code (after we converted it to Ruby): 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 def contains_point? (point) c = false i = -1 j = self .size - 1 while (i += 1 ) < self .size if (( self [i].y <= point.y && point.y < self [j].y) || ( self [j].y <= point.y && point.y < self [i].y)) if (point.x < ( self [j].x - self [i].x) * (point.y - self [i].y) / ( self [j].y - self [i].y) + self [i].x) c = !c end end j = i end return c end end So, you know, it works and I'm grateful to somebody for posting it but we had no idea how it works. So we wrapped 2 tests around it (one where the point is not in the polygon and another where it is...
Read more