Re: referencing objects

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.

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.

>