Re: Corporate Wide Public Synonyms ++ multiple schemas

"Richard Foote" <richard.foote_at_bigpond.nospam.com> wrote in message news:I%YOd.155917$K7.78680_at_news-server.bigpond.net.au...
> <linuxgeek_00_at_yahoo.com> wrote in message
> news:1108068838.968042.219230_at_f14g2000cwb.googlegroups.com...
>> My company wants to implement a corportate wide public synonym model
>> for our oracle 9i database. Meaning that every table in every schema
>> would have a public synonym created against it. Personally I think
>> this is a bad idea. However I need to develop a convincing arguement
>> to change this policy that has been suggested by our "DBA's".
>>
>> One issue is that I'm aware of is that all table names must be unique
>> across all schemas. Please let me know other reasons why it is a bad
>> idea to create public synonyms for every table in every schema.
>>
>
> At a site I work, they previously had a couple of thousand or so users
> accessing everything through public synonyms. Latch contention was a
> significant performance bottleneck.
>
> By using setting the appropriate current_schema via a logon trigger, the
> associated latch contention issues pretty well vanished.
>
> Avoid public synonyms if practical.
>
> Cheers
>
> Richard
>

been following bits and pieces of this thread, and checking some references regarding the seemingly geometric growth of latches when over-using public synonyms

a database trigger to alter the schema ia all well and good -- but that assumes all application objects are in the same schema doesn't it? or is the rest of the strategy to have private synonyms in the 'main' schema that would than allow schema-independent references to other objects (or was this covered in part of the thread that i missed?)

++ mcs Received on Fri Feb 11 2005 - 06:58:14 CST