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]
> *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
