Re: referencing objects

From: Howard J. Rogers <howardjr_at_www.com>
Date: 2000/04/01
Message-ID: <38e5d299@news.iprimus.com.au>#1/1

"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 - 00:00:00 CST