Forum: Ruby on Rails ActiveRecord: Oracle metadata loading slow on large database

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
49c9c24cf7349be02b40f068db3a9f0e?d=identicon&s=25 Zak Mandhro (zakmandhro)
on 2006-03-23 16:27
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
25e11a00a89683f7e01e425a1a6e305c?d=identicon&s=25 Wilson Bilkovich (Guest)
on 2006-03-23 16:43
(Received via mailing list)
On 3/23/06, Zak Mandhro <mandhro@yahoo.com> 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
49c9c24cf7349be02b40f068db3a9f0e?d=identicon&s=25 Zak Mandhro (zakmandhro)
on 2006-03-23 19:58
Wilson Bilkovich 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
6ef8cb7cd7cd58077f0b57e4fa49a969?d=identicon&s=25 Brian Hogan (Guest)
on 2006-03-23 20:34
(Received via mailing list)
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!)
6ef8cb7cd7cd58077f0b57e4fa49a969?d=identicon&s=25 Brian Hogan (Guest)
on 2006-03-23 20:38
(Received via mailing list)
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.)
49c9c24cf7349be02b40f068db3a9f0e?d=identicon&s=25 Zak Mandhro (zakmandhro)
on 2006-03-23 20:46
Brian Hogan 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.
6ef8cb7cd7cd58077f0b57e4fa49a969?d=identicon&s=25 Brian Hogan (Guest)
on 2006-03-23 20:58
(Received via mailing list)
Zak:
Might want to report this as a support item!
E75cda3e915fa209386fae3de962cb6a?d=identicon&s=25 Michael Schoen (Guest)
on 2006-03-24 06:51
(Received via mailing list)
Brian Hogan 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.
49c9c24cf7349be02b40f068db3a9f0e?d=identicon&s=25 Zak Mandhro (zakmandhro)
on 2006-03-24 17:25
Michael, thanks for the note and for updated the ticket.

All, please refer to [http://dev.rubyonrails.org/ticket/4390] .
E75cda3e915fa209386fae3de962cb6a?d=identicon&s=25 Michael Schoen (Guest)
on 2006-03-25 21:54
(Received via mailing list)
Michael Schoen wrote:
> Brian Hogan 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
6ef8cb7cd7cd58077f0b57e4fa49a969?d=identicon&s=25 Brian Hogan (Guest)
on 2006-03-27 17:11
(Received via mailing list)
That's a much better approach.... thanks so much for the effort.

-bph
49c9c24cf7349be02b40f068db3a9f0e?d=identicon&s=25 Zak Mandhro (Guest)
on 2006-04-04 16:31
Great work, Michael! Thanks.
This topic is locked and can not be replied to.