Blog » Blog Entry

Oracle needed an enema

November 28, 2007

Are you using Oracle with Ruby on Rails? The Oracle 10g (or so) version that has the new recyclebin feature? If so, you might have run into this problem I had recently.

My problem was just a simple slowness experienced when running a deployment over SSH using Capistrano. As I worked on my application for several months, the deployment was getting slower and slower.

To diagnose the problem better I started running rake db:migrate with the --trace option. That showed me that a lot of time was being spent on db:schema:dump right at the end of my db:migrate command:

** Execute db:schema:dump

My first idea to fix the issue was to disable the db:schema:dump. I never use the schema.rb file it creates, and I really didn't care if it was created or not, especially in production.

I Google'd up two different possible solutions to disable the schema.rb creation:

http://blog.andrewbeacock.com/[...]

http://pastie.caboo.se/51994

The first one wouldn't work anywhere I placed the code, and the second one threw errors about Rake.application not existing. *shrug*

A few minutes later I got an email reply from my Oracle database admin guy. He showed me that I had a bunch of Oracle "Check Constraints" had built up, and explained that he'd never seen that many in a database before.

46 Tables
74 Insert Triggers
804 Primary Keys
4,569 Check Constraints

Upon further investigation I learned those 4,569 Check Constraints represented a bunch of :null => false constraints I had placed on roughly 400 or so fields in my 46 tables. And everytime I ran `cap deploy` and dropped tables, more of these got created.

Oracle needed an enema.

Turns out it was fixable. To clear out the Check Constraints on each deployment, I added execute 'purge recyclebin' to my 001_* migration in the self.down method:

def self.down
  drop_table :sessions
  execute 'purge recyclebin'
end

Tags:

rubyonrails, rake, oracle, capistrano

« Clean your Ruby on Rails ActiveRecords automatically

Build the latest Emacs on Mac OS X Leopard »