Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: all_synonyms: performance issues

RE: all_synonyms: performance issues

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Wed, 23 Aug 2006 11:39:26 -0400
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF41050839D591@usahm236.amer.corp.eds.com>


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.  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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 


--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 23 2006 - 10:39:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US