script/console上で、ActiveRecordが実行するSQLを確認するには、script/consoleで以下を実行する。

>> ActiveRecord::Base.logger = Logger.new(STDOUT)

ただし、config.cache_classes = true だと、表示されない。

$ ./script/console 
Loading development environment (Rails 2.3.5)
>> ActiveRecord::Base.logger = Logger.new(STDOUT)
=> #<Logger:0x101672210 @level=0, @progname=nil, @logdev=#<Logger::LogDevice:0x101671fe0 @shift_size=nil, @shift_age=nil, @filename=nil, @mutex=#<Logger::LogDevice::LogDeviceMutex:0x101671f68 @mon_entering_queue=[], @mon_count=0, @mon_owner=nil, @mon_waiting_queue=[]>, @dev=#<IO:0x100183bb0>>, @formatter=nil, @default_formatter=#<Logger::Formatter:0x101672198 @datetime_format=nil>>
>> customer = Customer.new
  SQL (0.1ms)   SET NAMES 'utf8'
  SQL (0.1ms)   SET SQL_AUTO_IS_NULL=0
  Customer Columns (1.3ms)   SHOW FIELDS FROM `customers`
=> #<Customer id: nil, name: nil, created_at: nil, updated_at: nil>
>> customer.save!
  SQL (0.1ms)   BEGIN
  Customer Create (0.3ms)   INSERT INTO `customers` (`name`, `created_at`, `updated_at`) VALUES(NULL, '2010-01-22 03:42:43', '2010-01-22 03:42:43')
  SQL (0.5ms)   COMMIT
=> true

~/.irbrc に以下のように設定しておくといい。

if ENV['RAILS_ENV']
  # Called after the irb session is initialized and Rails has been loaded
  IRB.conf[:IRB_RC] = Proc.new do
    logger = Logger.new(STDOUT)
    ActiveRecord::Base.logger = logger
    ActiveResource::Base.logger = logger
  end
end

Maintainable Software: Rails Logging Tips