rsim/ruby-plsql — Issue #20: Performance issue initialising in 11g database
Author: nbrett
Created: 2011-11-29 22:31:57+00:00
State: open

We have two databases in use and often have to run stored procedures in each. This means we change connections frequently.
Since upgrading to 11g, we have noticed a delay (2-3 seconds) in switching connections. This traces to the SQL (in procedure.rb, line 15, in the 'find' method:

SELECT o.owner, o.object_name
FROM all_synonyms s, all_objects o
WHERE s.owner IN (:owner, 'PUBLIC')
AND s.synonym_name = :synonym_name
AND o.owner = s.table_owner
AND o.object_name = s.table_name
AND o.object_type IN ('PROCEDURE','FUNCTION')
ORDER BY DECODE(s.owner, 'PUBLIC', 1, 0)

which (in our case at least) can be made to perform better by changing to:

SELECT o.owner, o.object_name
FROM all_synonyms s inner join all_objects o
on o.object_name = s.table_name
WHERE s.owner IN (:owner, 'PUBLIC')
AND s.synonym_name = :synonym_name
AND o.owner = s.table_owner
AND o.object_type IN ('PROCEDURE','FUNCTION')
ORDER BY DECODE(s.owner, 'PUBLIC', 1, 0)

(the change is to use the 'inner join' syntax, which 11g seems to like better).

We are using version 0.3.1 of ruby_plsql. I haven't checked later versions