Re: Corporate Wide Public Synonyms ++ multiple schemas

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 11 Feb 2005 10:45:08 -0500
Message-ID: <4O6dnbptZZ-VTpHfRVn-hw@comcast.com>

"Richard Foote" <richard.foote_at_bigpond.nospam.com> wrote in message news:kD2Pd.156725$K7.65363_at_news-server.bigpond.net.au...
> "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
> news:oJidnYFUfNJ3NpHfRVn-rQ_at_comcast.com...

>>
>> "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?)
>>
>

> Hi Mark,

> Yes, in my specific example, there was just the one production schema.

> However, it doesn't necessarily have to be the case. If there's something
> about the connecting session that might identify what application/schema
> within the database might be appropriate (such as the program, module,
> etc.), then a similar course of action could be adopted with a piece of
> case logic in the logon trigger.

> Cheers

> Richard

>

actually my point is that a single application uses multiple schemas -- which in my experience is typically the case. there are always at lease a few common tables, and there are often cross-schema references. in other words, not every table that the application accesses is always going to be in the target schema

++ mcs Received on Fri Feb 11 2005 - 09:45:08 CST