Re: Why PUBLIC SYNONYM is bad ?

yong321_at_yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.0206160926.25dc8eea_at_posting.google.com>...

> Steve shows that synonyms, particularly public synonyms, are indeed
> bad in terms of CPU usage. But he said at the beginning "the best way
> to refute such scepticism [i.e. synonyms are bad] is with a
> reproducible test", as if his following test would show otherwise.

Note the difference between shared pool latch for "private synonyms" and "no synonyms" is much smaller for soft parses (the ones most likely to happen if your app is reasonably well designed). Public synonyms may indeed have an impact, but private ones may not. Steve also mentions a few caveats about the measurements and the load characteristics.

Personally, I've tried both in 8.0 and found no appreciable difference in the type of applications that I run, with the load that I have. So I use them: they are darn useful for security and ease of access to a schema by multiple users. Will gladly pay the extra CPU I may incur for the practicality of using them.

As in anything, testing is the most important criteria. Just because one site mentions a warning about public synonyms doesn't mean that EVERY database out there cannot use them. Each situation must be tested, rather than applying "blanket decisions".

Synonyms in Oracle have a purpose and a well defined advantage for managing object access in a database, which we may have to pay for in terms of additional CPU use. This is one case where I don't mind "wasting" CPU if I can afford it. It's desirable functionality, that makes management of access much easier. IMO, it's a desirable "waste".

Of course, if we are dealing with a 10**6 TPS benchmark system, we definitely do not want to use ANY synonyms. And we go for the max performance possible. I have the feeling that is not the case with the majority of databases out there.

Just my $0.02 anyways.
Cheers
Nuno Souto Received on Sun Jun 16 2002 - 19:06:37 CDT