Rails - Mar 2006 - Oracle 8 to 9 strange performance issue

Hello,

Got Ruby 1.8.2, RAILS 1.0, Windows2kx and Ruby-OCI8.mswin (1.1.3)

I try a migration test drive for one legacy DB from an original Ora
win32 8.1.7 server to a better hardware running Win32 Oracle 9.2.

The two DB have 99,999% identical datas.

My rails app run on a independant serveur (W2003) using an Oracle
Client 8.1.7. I''ve the same results using IIS/FastCgi or script/server
(WEBrick). Also confirmed with a standalone workstation.

Rails performance using the 9.2 oracle server are many times slower
than original server under 8.17.

When big pure sql queries or bench scripts using ruby-oci8 gives a
much  realistic fact :

the 9.2 server is twice as fast than the 8.1 production server.

Here''s the timing for rendering a list page in the rails apps

First talking with production server (8.1)

Completed in 0.81300 (1 reqs/sec) | Rendering: 0.12600 (15%) | DB:
0.92300 (113%) (! 113% perhaps due to Fastcgi going up for first
query)
Completed in 0.26600 (3 reqs/sec) | Rendering: 0.10900 (40%) | DB: 0.12500 (46%)
Completed in 0.21900 (4 reqs/sec) | Rendering: 0.09500 (43%) | DB: 0.09300 (42%)
Completed in 0.18800 (5 reqs/sec) | Rendering: 0.06200 (32%) | DB: 0.09500 (50%)

Now switching to the 9.2 server :

Completed in 47.93700 (0 reqs/sec) | Rendering: 26.37400 (55%) | DB:
21.56300 (44%)
Completed in 37.14000 (0 reqs/sec) | Rendering: 16.21900 (43%) | DB:
20.90500 (56%)
Completed in 36.32800 (0 reqs/sec) | Rendering: 15.50000 (42%) | DB:
20.81200 (57%)

The question is, why just changing to a new Oracle instance just twice
fast as the production instance using simple sql or ruby-oci scripts,
Rails performance is more than 100 times slower!

Have you any debug hint for tracking down this issue.

Thanks

Mathieu

Mathieu Chappuis wrote:
> The question is, why just changing to a new Oracle instance just twice
> fast as the production instance using simple sql or ruby-oci scripts,
> Rails performance is more than 100 times slower!
> 
> Have you any debug hint for tracking down this issue.
Oracle has some wonky differences between versions in how it manages 
it''s data dictionary. The query responsible for column inflection 
(figuring out what attributes are in each table) worked fine in some 
versions of Oracle, but really slowly in others.

If you upgrade to the most recent version of rails (edge) you should 
find much faster performance.

Note that you can''t currently just upgrade to 1.1, because there was an
issue with that release (now fixed in edge, a new 1.1.1 gem should be 
coming soon-ish).