Public synonym performance

How to reference a different schema without hard coding it


Oracle resolves all names while parsing, and the query execution plan generated is the same whether or not those names were resolved via synonyms. So there can be no difference in query execution performance. There is a small difference in parsing, but it goes the other way. A reference via a public synonym requires additional library cache and dictionary cache lookups. However, the performance impact should not be noticeable.

What may be more significant is that public synonym usage clutters the library cache and dictionary cache with information needed to track the negative dependencies on non-existent objects. If JONES and KING both refer to SCOTT.EMP and SCOTT.DEPT via public synonyms, then cache entries are needed to represent the non-existent tables JONES.EMP, KING.EMP, JONES.DEPT and KING.DEPT, and all dependent SQL statements must have negative dependencies on all these objects. With say 1000 users, 200 tables, and 1000 SQL statements each accessing 2 tables on average, you would have 200,000 non-existent object records and 2,000,000 negative dependency records. This clutter can cause latch contention in the library cache, dictionary cache and shared pool.


http://www.techrepublic.com/article/choose-alternatives-for-public-and-private-synonyms/5693565

In many large applications, a common way of separating the end user from the schema owner is to use private synonyms for application schema objects. For example, user A and user B both have their own login accounts. Schema user X has two tables, T1 and T2, so the application designers set up private synonyms for both A and B that reference X.T1 and X.T2, like this:

connect X/X
 create table T1(…);
 create table T2(…);
 connect A/A
 create synonym T1 for X.T1;
 create synonym T2 for X.T2;
 connect B/B
 create synonym T1 for X.T1;
 create synonym T2 for X.T2;
With two schema objects and two users, you have only four private synonyms. When you have a large number of users and application objects, you'll probably see a slow degradation of database performance. The performance problem will mostly be during the parse phase.

Every time a user wants to query T1, the parser must query across a large set of synonyms and put each synonym in the library cache along with its dependency structure. This would even affect queries such as select * from dual. You should avoid synonyms and use alternatives that depend on your application design.

The best alternative is to always fully qualify a table with its schema name. This alternative is only effective if you can guarantee the schema name will not change between installations and that there's only one schema with the given name in the database. In most applications where the designers carefully hide the SQL from the end user, there is no real benefit to coding select * from T1 in the code when select * from X.T1 would work in your situation.

In cases where there may be multiple schemas but each end user only accesses one schema at a time, the ALTER SESSION SET CURRENT_SCHEMA command is better than a synonym. This command makes the default schema for unqualified tables go to a particular schema. For example:

connect A/A
alter session set current_schema = X;
select * from T1;

This final query will select values from X.T1 and doesn't require any synonyms, while still obeying granted privileges on database objects. Even when it isn't possible to update an application so it always issues the alter session after a connect, you can code a database LOGON trigger to automatically set the current schema for a user.
==========================
create or replace trigger logon_trg
   after logon on database
 begin
    if user in ('A','B') then
        execute immediate 'alter session set current_schema=X';
    end if;
 end;
=======================

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON SCHEMA
BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = LDBO';
EXCEPTION
  when others
    then null;
END;
/

============================

Following Alex's suggestion, here is a logon trigger that checks the role rather than a username:

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON DATABASE
declare
  has_role boolean;
BEGIN

    has_role := dbms_session.is_role_enabled('FOOBAR_ROLE');

    if (has_role) then
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
    end if;
exception
   when others
      then null; -- prevent a login failure due to an exception  
END logon_trg;
/



============================
When user A or B logs in, his default schema will set automatically to X. The query select * from T1 will choose X.T1 automatically without requiring a synonym lookup or lock in the library.

Even in cases where the end user is accessing multiple schemas, or the names of database objects change between the end user and the application, you can still use views, which users can share in a central account:

create view T1 as select * from X.T1;
All of these alternatives rely on privileges the user gets to access a specific table. However, this may be more access than an application needs to give. A user who gets SELECT on a table in another schema can view all the columns and rows in that table or view any utility that can execute generic SQL.

An alternative that restricts exactly what data and operations the user can perform against the data is to define PL/SQL functions, procedure, and packages to wrap up specific access to an object. The PL/SQL code runs with the owner's privilege, and unqualified names resolve according to the code owner's schema. If the code owner is also the schema object owner, this eliminates the need for synonyms or even hard-coded schema names.

create or replace function t1_get_count return integer
 as
    l_count integer;
 begin
    select count(*) into l_count from t1;
    return l_count;
 exception
    when others then return 0;
 end;
 /
If you use "invoker rights" with AUTHID CURRENT_USER, then unqualified tables will resolve in the current user's schema, and you'll have to use another way of resolving those names at runtime.