Re: referencing objects

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:954575805.19620.0.nnrp-11.9e984b29_at_news.demon.co.uk...
>
> I can think of a pair of special circumstances
> that might give you figures like this, but
> I am very doubtful about a 1:2:4 ratio in
> the general case.
>
> Which version of Oracle, and how did
> you test it ?
>
> Having just run a test that did 4,000 hard
> parses (i.e. new statements) of a simple
> SQL query against one table, my timings
> for parse, execute and fetch came to:
> Qualified table 13.34 sec
> Private syn 12.99 sec !!
> Public syn 14.34 sec
>
> However, the variation in run-time of the
> tests was around 0.3 seconds anyway,
> so whilst there is a clear difference in
> timings (over 4,000 calls) , it would be
> pretty easy to lose it.
>
>
> Further note that using a public synonym
> requires one extra recursive call to syn$,
> and two extra calls to obj$, whereas a
> private synonym requires one extra to syn$
> and one extra to obj$ - the amount of extra
> recursive SQL needed is not significant.
>
> Finally, any application that manages to
> hard parse this many statements at this
> right is probably in serious trouble in
> all sorts of other areas anyway and I would
> tend to discount the extra quarter millisecond
> per call.

You seem to have missed the point that the structures required to make public synonyms work cannot be 'kept' in the library cache, and that there is therefore a tendency for public-synonym-dependent applications to be doing lots more re-parsing than would be 'desirable'. That is precisely the point, of course.

Regards
HJR
>
> On the plus side of your argument, though,
> the synonym solution does result in more
> data in the data dictionary - either for
> private synonyms or as 'non-existent'
> objects for the negative dependencies
> needed to avoid errors on public synonym
> parsing.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Howard J. Rogers wrote in message <38e4b897_at_news.iprimus.com.au>...
> >
> >For a statement that refers to a single table only, the CPU usage during
> the
> >parse phase is approximately 1:2:4 for a fully-qualified reference, a
> >private synonym and a public synonym respectively. For complex SQL
> >statements, the cost of synonym usage is even greater. The synonym based
> >solutions involve potentially large library cache dependency tables, and
> are
> >sensitive to the aging out of the library cache objects representing the
> >table name in the users' own schemas. These library cache objects cannot
be
> >"kept". In an instance with high parse rates, the use of synonyms
increases
> >CPU usage dramatically, and often causes contention on the latches that
> >control access to the library cache, dictionary cache and shared pool.
> >
>
>
>
>
>
Received on Sat Apr 01 2000 - 04:45:32 CST