RE: all_synonyms: performance issues

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.
 
-- Mark D Powell --
 


________________________________

        From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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 <mark.powell@xxxxxxx> 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.
                 
                -- Mark D Powell --
                 
                 
________________________________

                From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:
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 




        -- 
        Charles Schultz

Other related posts: