Tuesday, January 15, 2013

Don’t forget to index your Oracle foreign keys

This is another note-to-myself blog post. Like PostgreSQL, Oracle does not create indexes on foreign key columns. This can lead to both poor performance and deadlocks. This is because when the parent table’s column where the foreign key points to is updated Oracle needs to verify the child table’s FK constraints. In the absence of an index, this means a full table scan and a table lock. Normally foreign keys point to primary keys in the parent table, and you rarely need to change a primary key, so you’d think this should not happen often. But deleting rows from the parent table also has the potential of violating FK constraints. So operations that delete rows in the parent table can result in deadlocks in some cases.
Anyway, here’s the Oracle script to identify unindexed foreign keys:

Friday, January 4, 2013

JmsTemplate is not evil

A while back, I watched a presentation on JMS messaging where the presenter (Mark Richards) declared that Spring’s JmsTemplate is “evil,” and he had the benchmarks to prove it. Since Mark was kind enough to provide the source code (based on ActiveMQ), I decided to dig a little deeper to determine the cause. It turns out that the apparent poor performance of JmsTemplate was not due to anything intrinsic to this class, and you can indeed get message sending performance with it comparable to using the standard JMS API. It’s not evil. Really.

Read on if you want the details.