Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Excess synonyms?

    Our vendor is setting up synonyms for most of the objects in the database (tables, packages, sequences, views). Is this going to be a problem down the road, in terms of performance?

    In every case the synonym matches the name of the object:

    EMPLOYEES (syn.) = HR.EMPLOYEES

    -Chuck
    10g

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    well, since you can't create a synonym with the same name as the table in that schema, your synonyms are either public, or in other schemas. The reason for synonyms is because without them, either 1) all users need to connect as the table owner (bad), or 2) all code prefixes all objects with the owner name (becomes bad when the schema name changes - mult owners in one db; test env with diff name; product upgrade changes schema name). So, your vendor is not doing anything bad.

    Performance - there is slight overhead in that oracle needs to translate the synonym to the underlying object. This is very small, and wouldn't be a bottleneck. When you execute a sql statement, oracle is already parsing the statement, determining what the objects are (tables, views, functions...), preforming the necessary translations, verifying privs, verifying that the objects are valid, and so on. So, the one extra translation of the synonym isn't going to amount to much in the grand scheme of things.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is this going to be a problem down the road, in terms of performance?
    It depends; as always.
    This approach has at least two potential problems associated with it.
    1) It incurrs recursive SQL.
    2) It precludes a 2nd package from using the same object name.
    Typically this is down be (small?) 3rd party s/w vendors who design & code "assuming" theirs is the only application on the system.
    This approach simplifies s/w development for which their customers pay the price.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Synonyms are a standard approach to offer additional security, it avoids end users or application connecting as the owner of the various objects in the schema.

    However, unless they have a specific reason, they should be creating private synonyms rather than public ones.

    Viz:
    create synonym end_user.employees for hr.employees;

    Hth
    Bill - a small 3rd party application vendor whose customers can run multiple versions of my applications on the one instance
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    If our database objects are guaranteed to have unique names (and thus the resulting synonyms), is there then a preference for PRIVATE synonyms over PUBLIC?

    -cf

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Just found this relation to synonymn performance (public, private and none). From a purely performance perspective, it would appear that no synonyms are preferable to private synonyms, which in turn are preferable to public.

    http://www.ixora.com.au/newsletter/2001_05.htm

    There is plenty of other material on there wrt to synonyms as well.

    This quantifies the performance cost of synonyms as mentioned by shoblock earlier.

    It follows that as a very simple rule, more functionality used can mean more overhead.

    Back to the functional reasons for private synonyms, you can have multiple users with identically named synonyms pointing to subsets of data, reduced columns in different views etc completely transparent to the user, similar to what would be achieved with fine grained access control.

    It becomes particularly useful when a user demands to be let loose with Crystal Reports or some such, their synonyms point to restricted amounts of data/views of the data as necessary.

    Viz:

    Code:
    connect main_schema;
    create view detailed as 
       select column1,
                <some confidential data>
       from tablea
       where some_condition_appropriate_to_management;
    grant select on detailed to management;
    create view limited as
       select column1,
                <null> as confidential_data
       from tablea
       where some_condition_appropriate_mere_mortals;
    grant select on limited to mere_mortal;
    
    create synonym mere_mortal.tablea for main_schema.limited;
    create synonym management.tablea for main_schema.detailed;
    Both management and mere_mortals can be set free on Crystal/SqlPlus or whatever but with flexibility / security / confidentiality.

    If your vendors are going to create public synonyms for everything, I'm assuming they're going to be granting select, insert, update etc to public too. If not, and they're going to restrict grants to specific schemas, then why create the public synonyms to start with? I'm guessing it's laziness.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  7. #7
    Join Date
    Sep 2004
    Posts
    17
    We discussed that problem - public or private synonyms - in our application. The (main) advantage of private synonyms is, that many application owner can coexist in one instance. If you have public synonyms you must deside for what owner they are. And so we implemented private synonyms. Then our ct. created 5000 user in his production database. Along with 1200 objects that have to be granted we had 6.000.000 synonyms in the database - to much because they took 1,5 GB of system tablespace. Performance also decreased about 5 - 10%. So we implemented public synonyms.
    But in a way, that tha dba has the chance to decide if the installation should use private or public synonyms. In production environment mostly public synonyms are the better way (for our app), because only one owner and many user exists. In dev and test environment we can choose private synonyms to install more then one owner in one isntance.

    Alex

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    In this case, the contractor claimed they set up these PUBLIC SYNONYMS in case any of our objects migrated to a different schema. I also think that they are also there to save typing.

    -Chuck

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    An alternative that requires no synonyms at all is:

    alter session set current_schema=xxx;

    Do that when the users connect and they will automatically access the "xxx" schema whenever object names are not prefixed.

    Of course, if the users need to be able to access their own tables as well (at the same time), then this could be an issue.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by andrewst
    An alternative that requires no synonyms at all is:

    alter session set current_schema=xxx;

    Do that when the users connect and they will automatically access the "xxx" schema whenever object names are not prefixed.

    Of course, if the users need to be able to access their own tables as well (at the same time), then this could be an issue.
    Tony is very sharp, but it has been my observation that this is a good soultion for simple applications, but any complex applications I have seen use multiple schemas to hold different modules and they use grants and synonyms for the applications to run from a seperate user that has very few, if any application tables in the schema
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You are right (and I don't mean the bit about me being sharp!)

    In fact, the application I currently work on has its tables spread over several schemas, one for "core" tables and one per subsystem. So of course, it uses synonyms! Public synonyms, as it happens.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •