ActiveRecord: Oracle metadata loading slow on large database

All,

While attempting to use ActiveRecord against a large database, approx.
1700 tables and 4 million rows, I discovered an inefficiency in
oci_adapter.rb. The problem seems to be the SQL being generated to
lazy-load the columns metadata. “def columns(table_name, name = nil)”
method generates the following SQL:

select column_name, data_type, data_default, nullable,
decode(data_type, ‘NUMBER’, data_precision,
‘VARCHAR2’, data_length,
null) as length,
decode(data_type, ‘NUMBER’, data_scale, null) as scale
from user_catalog cat, user_synonyms syn, all_tab_columns col
where cat.table_name = ‘#{NAME_OF_TABLE}’
and syn.synonym_name (+)= cat.table_name
and col.table_name = nvl(syn.table_name, cat.table_name)
and col.owner = nvl(syn.table_owner, user)

At first glance, the query looks okay. Oracle should apply the WHERE
clause on the FROM tables and then apply the DECODE on the results.
Unfortunately, that’s not the case. The decode is making the query very
expensive. In my case, 10g running on Sun V880 server, with decode takes
35 seconds, without decode takes less than a second.

I patched the columns method to do the “decode” in Ruby.

    def columns(table_name, name = nil) #:nodoc:
      table_name = table_name.to_s.upcase
      owner = table_name.include?('.') ? 

“’#{table_name.split(’.’).first}’” : “user”
table = “’#{table_name.split(’.’).last}’”
scope = (owner == “user” ? “user” : “all”)

      table_cols = %Q{
        select column_name, data_type, data_default, nullable,
               data_precision, data_length, data_scale
          from #{scope}_catalog cat, #{scope}_synonyms syn, 

all_tab_columns col
where cat.table_name = #{table}
and syn.synonym_name (+)= cat.table_name
and col.table_name = nvl(syn.table_name, cat.table_name)
and col.owner = nvl(syn.table_owner, #{(scope == “all” ?
“cat.owner” : “user”)}) }

      if scope == "all"
        table_cols << %Q{
           and cat.owner = #{owner}
           and syn.owner (+)= cat.owner }
      end

      select_all(table_cols, name).map do |row|
    length =row['data_length'] unless row['data_precision']
        row['data_default'].sub!(/^'(.*)'\s*$/, '\1') if 

row[‘data_default’]
OCIColumn.new(
oci_downcase(row[‘column_name’]),
row[‘data_default’],
row[‘data_type’],
length,
row[‘scale’],
row[‘nullable’] == ‘Y’
)
end
end

Also, I didn’t understand why only VARCHAR2 lengths were being picked
from the data_length column. What about VARCHAR, CHAR, CLOB, etc.? I
changed the length value to apply to all types of columns that don’t
specify precision.

Am I missing something? Let me know what you think about this patch and
whether or not it should be proposed.

Thanks,

  • Zak

On 3/23/06, Zak M. [email protected] wrote:

All,

While attempting to use ActiveRecord against a large database, approx.
1700 tables and 4 million rows, I discovered an inefficiency in
oci_adapter.rb. The problem seems to be the SQL being generated to
lazy-load the columns metadata. “def columns(table_name, name = nil)”
method generates the following SQL:

This has been fixed in Edge Rails, and will be in version 1.1. Much,
much faster now.
Here’s the patch that was selected:
http://dev.rubyonrails.org/ticket/3897

Wilson B. wrote:

This has been fixed in Edge Rails, and will be in version 1.1. Much,
much faster now.
Here’s the patch that was selected:
http://dev.rubyonrails.org/ticket/3897

Just evaluated it. There seems to be a problem with the new
oracle_adapter.rb, it doesn’t seem to work with synonyms.

Synonyms allow you to abstract physical names, and point to different
objects without changing dependent objects. I am not a big fan of this
Oracle feature because it can cause naming conflicts and invalid
associations, but I know many database administratos like to use it. Our
schema is full of public synonyms.

Here’s a sample scenario:

  • Schema INVENTORY has Table PRODUCTS
  • Public Synonym PRODUCTS = INVENTORY.PRODUCTS
  • Schema ECOMMERCE wants to use PRODUCTS by name as public synonym
  • In future, PRODUCTS moves to CATALOG schema. Simply update synonym
    PRODUCTS = CATALOG.PRODUCTS
  • No change to models

If you try this with 1.1RC, you would get:
OCIError: ORA-04043: object products does not exist
from
d:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.2.4008/lib/active_
record/connection_adapters/oracle_adapter.rb:535:in object_info' from (eval):3:in object_info’
from
d:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.2.4008/lib/active_
record/connection_adapters/oracle_adapter.rb:359:in columns' from d:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.2.4008/lib/active_ record/base.rb:733:in columns’
from
d:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.2.4008/lib/active_
record/base.rb:741:in columns_hash' from d:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.2.4008/lib/active_ record/base.rb:1678:in define_read_methods’
from
d:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.2.4008/lib/active_
record/base.rb:1630:in `method_missing’
from (irb):4

You can do set_table_name(‘INVENTORY.PRODUCTS’) to make the object_info
and describe() work. However, this workaround renders public synonyms
useless. We can call it an supported feature at this point and move on
with 1.1.

  • Zak

Check your object permissions… this seems to work fine on my
databases
but I’m not using public synonyms… just private ones. Also, what
Oracle
version are you using? (Apparently, that matters!)

I take that back… the latest one doesn’t work for me either Appears
that
the code is not even looking at the synonyms tables anymore. (at least,
as
far as I can tell.)

Brian H. wrote:

I take that back… the latest one doesn’t work for me either Appears
that
the code is not even looking at the synonyms tables anymore. (at least,
as
far as I can tell.)

Yep. And the permissions are fine.

We are using Oracle 10g Release 2 for Solaris/SPARC.

Zak:
Might want to report this as a support item!

Brian H. wrote:

I take that back… the latest one doesn’t work for me either Appears
that the code is not even looking at the synonyms tables anymore. (at
least, as far as I can tell.)

It actually does still support synonyms.

The previous slow performance on loading the column metadata was caused
by significant change to Oracle’s data dictionary views between various
Oracle versions. Queries that worked really well for some revs didn’t
work so well for others.

The implemented approach uses the OCI describe functionality to
determine the underlying table info, which handles synonyms. And then a
much simpler select is used to grab the info on that table.

The issue you seem to be running into appears to be limited to PUBLIC
synonyms only. Just confirmed in my own environment that while other
synonyms are fine, PUBLIC ones do fail.

Looking into why, but you may be able to use synonyms created in the
schema Rails is using as a workaround.

Michael, thanks for the note and for updated the ticket.

All, please refer to [http://dev.rubyonrails.org/ticket/4390] .

Michael Schoen wrote:

Brian H. wrote:
Looking into why, but you may be able to use synonyms created in the
schema Rails is using as a workaround.

Patch submitted, requires ruby-oci8 rev 0.1.14.

http://dev.rubyonrails.org/ticket/4390

Great work, Michael! Thanks.

That’s a much better approach… thanks so much for the effort.

-bph