Re: Synonym Performance

billmil_at_my-deja.com wrote:
>
> How much overhead do you incur by using a public synonym to access a table
> outside your schema? E.g., for user LINUSER, instead of this statement:
> SELECT * FROM LIN.TOPICS; using: SELECT * FROM TOPICS; (where TOPICS is a
> public synonym which points to LIN.TOPICS;) I would guess the public synonym
> incurs minimal overhead, but perhaps "built for speed" applications might
> avoid them due to the drag on the system. Any thoughts? thanks bill milbratz
> chicago il usa
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

See Sybrand's post, but also remember that you also use slightly more memory for each public synonym for each user connected. (Some negative dependencies need to be stored)..

alter session set current_schema = XXX

is an easy way to avoid having synonyms as well as not needing to prefix your SQL's with XXX.tablename.

If you adopt this approach, ensure that the 'alter session' is the first thing you do when the user connects - some earlier releases of Oracle had problems if you issued it after other SQL's had been run.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse