Re: all_synonyms: performance issues

Thanks, I had completely forgotten about that little trick in tkprof. Unfortunately, the query shows up even with sys=no, so that tells me that the app is doing it (unless some how SYS calls can *still* slip in there....). And the stinking vendor has no clue. Great.

On 8/23/06, Powell, Mark D <mark.powell_at_eds.com> wrote:
>
> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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_at_eds.com> 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_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
> >
> >
>
>
> --
> Charles Schultz
>

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l