Hi, after some experience with Rails on MySQL databases I gave it a try on one of our larger Oracle database. I was facing a problem when Rails tried to detect the columns for a model/table. The appropriate statement ran about 1 min which led to a timeout. Our Oracle guru told me to analyze the SYSTEM schema. After that hadn''t helped, he said the only remaining chance is to use a hint in the statement. Other tools like TOAD would do it by themself anyway... I made the change in oci_adapter.rb and Rails was responsive again. If anybody else has this problem or if anybody wants to include the change into the next ActiveRecord version, here is what I''ve changed: in activerecord-1.13.2/lib/active_record/connection_adapters/oci_adapter.rb at line 341 (in the #columns method): - select column_name, data_type, data_default, nullable, + select /*+ RULE */ column_name, data_type, data_default, nullable, Best regards and thanks for the great framework, Andreas Gungl
Andreas Gungl wrote:> in activerecord-1.13.2/lib/active_record/connection_adapters/oci_adapter.rb > at line 341 (in the #columns method): > - select column_name, data_type, data_default, nullable, > + select /*+ RULE */ column_name, data_type, data_default, nullable,You''ll have to excuse my ignorance, having had no deep exposure to Oracle: what does that actually do? -- Alex
That /*+ RULE */ is a hint to the optimizer that tells Oracle to
actually bypass the optimizer.
Try this too.. This statement will work:
table_cols = %Q{
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 (select table_name from #{scope}_catalog
where table_name = #{table}) cat,
(select * from #{scope}_synonyms where
synonym_name = #{table}) syn,
(select * from 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")}) }
Works for us here. I had an initial fix but it failed for synonyms.
Then I tried EXACTLY what you tried and it didn''t work for various
permissions.
-----Original Message-----
From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Andreas Gungl
Sent: Tuesday, February 07, 2006 9:53 AM
To: Rails@lists.rubyonrails.org
Subject: [Rails] OCI adapter slowdown on dictionary access
Hi,
after some experience with Rails on MySQL databases I gave it a try on
one
of our larger Oracle database.
I was facing a problem when Rails tried to detect the columns for a
model/table. The appropriate statement ran about 1 min which led to a
timeout.
Our Oracle guru told me to analyze the SYSTEM schema. After that hadn''t
helped, he said the only remaining chance is to use a hint in the
statement. Other tools like TOAD would do it by themself anyway...
I made the change in oci_adapter.rb and Rails was responsive again. If
anybody else has this problem or if anybody wants to include the change
into the next ActiveRecord version, here is what I''ve changed:
in
activerecord-1.13.2/lib/active_record/connection_adapters/oci_adapter.rb
at line 341 (in the #columns method):
- select column_name, data_type, data_default, nullable,
+ select /*+ RULE */ column_name, data_type, data_default, nullable,
Best regards and thanks for the great framework,
Andreas Gungl
_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails
> That /*+ RULE */ is a hint to the optimizer that tells Oracle to > actually bypass the optimizer. > > > > Try this too.. This statement will work: > > table_cols = %Q{ > 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 (select table_name from #{scope}_catalog > where table_name = #{table}) cat, > (select * from #{scope}_synonyms where > synonym_name = #{table}) syn, > (select * from 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")}) } > > > Works for us here. I had an initial fix but it failed for synonyms. > Then I tried EXACTLY what you tried and it didn''t work for various > permissions.What permission problem do you have? Our database is using some schemas with one account who can access all schema objects (used in a J2EE environment BTW). I don''t have a problem and actually the original solution would work for me if it wasn''t so slow. Hm, I wonder what your statement is intended to do. Anyway, I get this error (which is quite natural if you look at the first subselect): OCIError: ORA-00904: "CAT"."OWNER": invalid identifier: 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 (select table_name from all_catalog where table_name = ''WHACCOUNTTYPE'') cat, (select * from all_synonyms where synonym_name = ''WHACCOUNTTYPE'') syn, (select * from all_tab_columns) col where cat.table_name = ''WHACCOUNTTYPE'' 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, cat.owner) and cat.owner = ''SYSP'' and syn.owner (+)= cat.owner Regards, Andreas
Hogan, Brian P.
2006-Feb-07 17:39 UTC
[Rails] Re: OCI adapter slowdown on dictionary access
When hints are used, you may run into this:
ERROR:
ORA-03212: Temporary Segment cannot be created in locally-managed
tablespace
It''s all about your DB setup.
As for the error you''re getting, I sent an older version! My mistake!!
Thanks for pointing that out!!!
Should be
table_cols = %Q{
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 (select owner, table_name from
#{scope}_catalog where table_name = #{table}) cat,
(select * from #{scope}_synonyms where
synonym_name = #{table}) syn,
(select * from 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")}) }
The reason this works is that Oracle will run the subqueries first, and
then run the where cluases on the contents of the subqueries. The
original query actually has 42 steps and the inner most step is a full
table scan on the users table.)
The above query "prefetches" only the rows needed (and could be tuned
more to just include the columns needed).
So far it''s working for us. Our setup is a specific user accessing
their own objects via tables or synonyms.
-Brian Hogan
-----Original Message-----
From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Andreas Gungl
Sent: Tuesday, February 07, 2006 11:09 AM
To: Rails@lists.rubyonrails.org
Subject: [Rails] Re: OCI adapter slowdown on dictionary access
> That /*+ RULE */ is a hint to the optimizer that tells Oracle to
> actually bypass the optimizer.
>
>
>
> Try this too.. This statement will work:
>
> table_cols = %Q{
> 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 (select table_name from #{scope}_catalog
> where table_name = #{table}) cat,
> (select * from #{scope}_synonyms where
> synonym_name = #{table}) syn,
> (select * from 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")}) }
>
>
> Works for us here. I had an initial fix but it failed for synonyms.
> Then I tried EXACTLY what you tried and it didn''t work for various
> permissions.
What permission problem do you have? Our database is using some schemas
with one
account who can access all schema objects (used in a J2EE environment
BTW). I don''t
have a problem and actually the original solution would work for me if
it wasn''t so slow.
Hm, I wonder what your statement is intended to do. Anyway, I get this
error (which
is quite natural if you look at the first subselect):
OCIError: ORA-00904: "CAT"."OWNER": invalid identifier:
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 (select table_name from all_catalog where table_name
= ''WHACCOUNTTYPE'') cat,
(select * from all_synonyms where synonym_name
''WHACCOUNTTYPE'') syn,
(select * from all_tab_columns) col
where cat.table_name = ''WHACCOUNTTYPE''
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, cat.owner)
and cat.owner = ''SYSP''
and syn.owner (+)= cat.owner
Regards,
Andreas
_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails
Hogan, Brian P. wrote:> When hints are used, you may run into this: > > ERROR: > ORA-03212: Temporary Segment cannot be created in locally-managed > tablespace > > It''s all about your DB setup. > > As for the error you''re getting, I sent an older version! My mistake!! > Thanks for pointing that out!!! > > Should be > > table_cols = %Q{ > 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 (select owner, table_name from > #{scope}_catalog where table_name = #{table}) cat, > (select * from #{scope}_synonyms where > synonym_name = #{table}) syn, > (select * from 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")}) } > > > The reason this works is that Oracle will run the subqueries first, and > then run the where cluases on the contents of the subqueries. The > original query actually has 42 steps and the inner most step is a full > table scan on the users table.) > > The above query "prefetches" only the rows needed (and could be tuned > more to just include the columns needed). > > So far it''s working for us. Our setup is a specific user accessing > their own objects via tables or synonyms. > > -Brian HoganHere''s a diff between the ActiveRecord 1.13.2 (in RoR 1.0.0) and the above code, to make it easier to see what''s changed. Regards, Blair -- Blair Zajac, Ph.D. CTO, OrcaWare Technologies <blair@orcaware.com> Subversion training, consulting and support http://www.orcaware.com/svn/ -------------- next part -------------- --- /var/lib/gems/1.8/gems/activerecord-1.13.2/lib/active_record/connection_adapters/oci_adapter.rb 2005-12-13 12:00:43.000000000 -0800 +++ lib/active_record/connection_adapters/oci_adapter.rb 2006-02-07 11:28:59.394856000 -0800 @@ -343,7 +343,9 @@ ''VARCHAR2'', data_length, null) as length, decode(data_type, ''NUMBER'', data_scale, null) as scale - from #{scope}_catalog cat, #{scope}_synonyms syn, all_tab_columns col + from (select owner, table_name from #{scope}_catalog where table_name = #{table}) cat, + (select * from #{scope}_synonyms where synonym_name = #{table}) syn, + (select * from 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)