Re: referencing objects

From: Howard J. Rogers <howardjr_at_www.com>
Date: Sat, 01 Apr 2000 10:42:34 GMT
Message-ID: <38e5d299@news.iprimus.com.au>

"Greg Stark" <greg-spare-1_at_mit.edu> wrote in message news:87bt3ufhd2.fsf_at_HSE-MTL-ppp43091.qc.sympatico.ca...
> "Howard J. Rogers" <howardjr_at_www.com> writes:
>
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:954353669.2363.1.pluto.d4ee154e_at_news.demon.nl...
> > >
> > > strongly disagree about that. It is extreemly BAD practice to do that.
> > > You are loosing transparency. Whenever you want to maintain a
development
> > > and production scheme, you can easily do that by using private and
public
> > > synonyms.
> > > When you hardcode the schema owner, you'r stuck forever, and you end
up
> > > setting up a second istance, which makes things only more difficult.
> >
> > And use of public synonyms is a performance dog, as I'm sure you'll be
> > aware.
> >
> > 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.
>
> Sorry, 4 x 0 is still 0. Or more accurately, 4 times a small constant is
still
> only going to be a small constant. For DSS systems parse times are
irrelevant,
> and OLTP applications should only be parsing their queries once and
executing
> them millions of times.

...and the point is (which you seem to have overlooked) that you cannot guarantee parse-once-execute-a-zillion-times with public synonyms, since, and I quote from my earlier post:

---

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".

---

On a busy oltp system, use of public synonyms makes a discernible and considerable difference to performance, for precisely the reasons that you elaborated -even if you then went on to ignore them!

Regards
HJR
>
> The cost of the parse time should be irrelevant either way. It's certainly
not
> worth sacrificing hard coding schema names into your code which will make
it
> impossible to develop without each developer having his or her own
instance to
> work on.
>
> --
> greg
Received on Sat Apr 01 2000 - 04:42:34 CST