Slow query, can it be replaced?

[matthewtusker]

oracle-enhanced/lib/active_record/connection_adapters/oracle_enhanced/connection.rb

Lines 37 to 56 in d5b3daf

	SELECT owner, table_name, 'TABLE' name_type
	FROM all_tables
	WHERE owner = :table_owner
	AND table_name = :table_name
	UNION ALL
	SELECT owner, view_name table_name, 'VIEW' name_type
	FROM all_views
	WHERE owner = :table_owner
	AND view_name = :table_name
	UNION ALL
	SELECT table_owner, table_name, 'SYNONYM' name_type
	FROM all_synonyms
	WHERE owner = :table_owner
	AND synonym_name = :table_name
	UNION ALL
	SELECT table_owner, table_name, 'SYNONYM' name_type
	FROM all_synonyms
	WHERE owner = 'PUBLIC'
	AND synonym_name = :real_name
	SQL

The above query is hitting us hard in Production. I'm seeing ~2s responses for this query. Our dbas have suggested that it could be replaced with the following:

SELECT owner,object_name 
FROM all_objects 
WHERE owner=:table_owner 
  AND object_name=:table_name 
  AND object_type in ('TABLE','VIEW','SYNONYM');

I don't know enough about the inner workings of Oracle to know whether this is a useful improvement and whether it would work over all the supported versions of Oracle for this Gem. Can someone with a bit more knowledge provide me with some insight here?

[matthewtusker]

Also, we've tried to turn on schema caching and producing a cache file to avoid calling this query at all, but it's still being called. Is schema caching still available/working?

EDIT: It appears that schema caching only affects gathering table descriptions at boot time, so relevant to this discussion.

[matthewtusker]

I have been told that there's a DBMS.NAME_RESOLVE procedure which should be able to return the required information.