Rails - Mar 2006 - 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

-- 
Posted via http://www.ruby-forum.com/.

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

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

-- 
Posted via http://www.ruby-forum.com/.

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!)

On 3/23/06, Zak Mandhro <mandhro@yahoo.com> wrote:
>
> 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
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060323/1049940b/attachment-0001.html

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.)

On 3/23/06, Brian Hogan <bphogan@gmail.com> wrote:
>
> 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!)
>
>
> On 3/23/06, Zak Mandhro <mandhro@yahoo.com> wrote:
> >
> > 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
> >
> > --
> > Posted via http://www.ruby-forum.com/.
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060323/c8748ddd/attachment.html

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.

-- 
Posted via http://www.ruby-forum.com/.

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


On 3/23/06, Zak Mandhro <mandhro@yahoo.com> wrote:
>
> 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.
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060323/80a65a5d/attachment.html

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.

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

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

-- 
Posted via http://www.ruby-forum.com/.

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

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

-bph

On 3/25/06, Michael Schoen <schoenm@earthlink.net>
wrote:
>
> 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
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060327/98ffcfda/attachment.html

Great work, Michael! Thanks.

-- 
Posted via http://www.ruby-forum.com/.