Perhaps it is the internal query where Oracle checks to see who the table
belongs to in order to parse the query and check security. The presence of
:"SYS_B_1" type names leads me to believe you have cursor_sharing set to
similar. We have not had good luck when we tried to use that setting.
-- Mark D Powell --
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Charles Schultz
*Sent:* Wednesday, August 23, 2006 11:21 AM
*To:* oracle-l
*Subject:* all_synonyms: performance issues
Good day, list,
In light of Metalink note *377037.1* (*Selects Against ALL_SYNONYMS
Perform Badly on 10g Release 10.2*), we have been tracking down why we
have so many calls to all_synonyms in the first place. We see a lot of these
types of queries occuring:
SELECT table_name, table_owner FROM all_synonyms WHERE owner = :"SYS_B_0"
AND synonym_name = :"SYS_B_1"
Where does this come from? When I queried v$sql, I could not find any
parent cursors ( where parent.child_address = child.address and lower(
child.sql_text) like '%all_synonyms%'). We are also unable to find
explicit calls from the application, so we are operating under the
assumption that Oracle does this as part of a recursive call. A 10046 trace
will show this query and a preceding query (ie, select * from some_table),
but I do not quite understand how they are related.
Any help much appreciated,
--
Charles Schultz
