search

kubo / ruby-oci8

Ruby-oci8 - Oracle interface for ruby
Other
169 stars 75 forks source link

Performance issue #76

Closed pakerimus closed 9 years ago

pakerimus commented 9 years ago

Hi everyone! I'm not sure if my problem is with this gem, or what... I'm experiencing performance issues. If I run a procedure from the rails console, it takes much more time to run than from sqldeveloper... In console 45 sec, in sqldev 5 sec.! Anyone had this problem? I'm using ruby 2.1.1, rails 4.1.4, ruby-oci8 2.1.8, oracle-enhanced 1.5.6, ruby-plsql 0.5.0, oracle instantclient 12.1 The database is oracle 11g 11.2.0.1.0 standard, on another server.

pakerimus commented 9 years ago

I checked on oracle support, and exists a bug regarding the instantclient version vs database version. So I downgraded the client to match the version of the database.... same result.

cjbj commented 9 years ago

This was cross posted to https://github.com/rsim/oracle-enhanced/issues/619

pakerimus commented 9 years ago

I must do a select of a bunch of rows. In sqldev or toad it takes 0,8 seconds to get my results (around 50 rows). But in rails, if I do this: results = ActiveRecord::Base.connection.exec_query("select ...") it takes 30 over seconds!

With the help of a DBA I traced my sessions (sqldev and rails console), and I got different execution plans. We tried using some hints, but we couldn't make it to work properly. I don't see differences between my sqldev and ruby connection...

PLEASE HELP!

pakerimus commented 9 years ago

Well, thanks to Kubo and his ocidump we could see that I had this in my initializer: ENV['NLS_COMP']='LINGUISTIC' ENV['NLS_SORT']='BINARY_AI' I had this in order to perform case-insensitive and accent-insensitive searches. My DBA explained me that this settings causes bad performance: Using Case Insensitive Searches and Setting NLS_SORT to Anything Other than BINARY (e.g. BINARY_CI) with NLS_COMP set to ANSI or LINGUISTIC Causes Full Table Scans Instead of Using Indexes (Doc ID 2008186.1) Symptoms Queries are performing more slowly following the introduction of case insensitive searches (in this case using NLS_SORT=BINARY_CI) in the database With NLS_SORT set to anything other than BINARY (here NLS_SORT=BINARY_CI), queries are perform full table scans and ignore index based searches. NLS_COMP is set to ANSI or LINGUISTIC This is expected behavior.

So, I removed those settings and worked perfectly.

For the functionality needed (case-and-accent-insensitive searches), I used Oracle Text, supported by oracle-enhanced gem.

Thanks, and I hope this is useful for someone else!