search

rsim / oracle-enhanced

Oracle enhaced adapter for ActiveRecord
MIT License
547 stars 308 forks source link

Performance issue #619

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

How many rows? Sounds like a prefetch or array size difference between sqldev and ruby-oci8

pakerimus commented 9 years ago

I've traced my sessions, and turns out I get different execution plans... do you need me to post them?

pakerimus commented 9 years ago

The prefetch_rows in sqldev is 50, in oracle-enhanced wasn't set, so it was the default, 100. I've changed this value in config/database.yml, reconnected and executed again, with same result... Also tried changing de cursor_sharing setting, to "exact" and to "similar", without changes...

pakerimus commented 9 years ago

The procedure processes 322 rows, and inserts 153, in a single insert statement.

pakerimus commented 9 years ago

Chris, thanks for your help! Let me explain a little more...

My application calls a single procedure that works all the magic within the database, and at the end it calls another procedure that inserts a bunch of rows (153) using a single insert from a select statement. By commenting out this final procedure I was able to confirm that it was the one with the performance issue. So I (with the help of a experienced dba) traced my session, executing this procedure from sqldev and from ruby (using ruby-plsql gem). We tried a few things: changing the instantclient, adding hints (rule, ordered, index), changing the value of cursor_sharing parameter, and even tried changing the optimizer features, but didn't help (the difference was about 3 seconds... out of 45, wasn't a real gain).

But my question is: why do I get different execution plans?

Could it be that I'm using OCI in ruby and JDBC in sqldev? If so, is there a way to use JDBC with MRI?

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!

nathanvda commented 9 years ago

OMG I had exactly the same problem here, legacy tables that use strings as primary key (bwaaaarrgghhh) were querying extremely slow, while using sql developer or pure ruby-oci8 in irb where a lot faster.

At least now I know why, switching NLS_SORT back to default BINARY made my queries fast again. Now I can get back to my client and check for the best way to fix it (I would prefer to replace or extend the legacy tables, I really loathe strings as primary key :smile: ).

A shame Oracle does not have an ILIKE operator like postgresql ...

Thanks again for this valuable tip!

pakerimus commented 9 years ago

Hi nathanvda! For case-and-accent-insensitive queries I used Oracle Text. The oracle-enhanced gem supports it, you can use "contains". Before that, I tried with function-based indexes and indexed virtual-columns (I found out that they are the same actually) But I don't know if such options will help when working with the PK...