all_synonyms: performance issues
- From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Aug 2006 10:20:55 -0500
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
Other related posts:
