Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: all_synonyms: performance issues

From: Powell, Mark D <>
Date: Wed, 23 Aug 2006 12:19:01 -0400
Message-ID: <>

The query should show in the raw trace. You can test my theory by running tkprof with sys=no to exclude internal queries from the tkprof output.  

As far as details of why Oracle runs this I do not know of any reference other than the general information given in the Performance manual where Oracle discusses the steps followed in parsing SQL.  

[] On Behalf Of Charles Schultz

	Sent: Wednesday, August 23, 2006 11:49 AM
	To: Powell, Mark D
	Cc: oracle-l
	Subject: Re: all_synonyms: performance issues
	We are experimenting with cursor_sharing - it is a double-edge
sword. Lots of new bugs quickly came to the surface. But that is OT. =)
	I kinda figured it was an internal query, but where do I find
more information on it?

        On 8/23/06, Powell, Mark D <> wrote:

                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: [mailto: <> ] 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 

	Charles Schultz 

Received on Wed Aug 23 2006 - 11:19:01 CDT

Original text of this message