Steve Adams is a smart guy.

Public Synonyms are not entirely evil. They are something you can avoid if you want to. They are not, in my opinion, something to be totally avoided. I use them but I do not overuse them.

I do most all of my work with packages -- end users never see the tables at all. There are relatively few "exposed" packages when compared to the total number of packages + tables and so on. So, there are a moderate number of synonyms in use.

You have to consider that thats the way the database works as well. We have dbms_output (no one calls sys.dbms_output), we have dbms_alert, dbms_pipe, utl_file, .... and so on. We have all_objects (not sys.all_objects) and so on. The database itself makes use of these public synonyms.

In the end, its a matter of sizing and testing. I for one cannot fully reproduce some of his points. The CPU parse point for example. I tried:

scott@ORA806.WORLD> declare
2 type rc is ref cursor;
3 x rc;
4 begin
5 for i in 1 .. 500 loop
6 open x for select * from tkyte.t;
7 close x;
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.99
scott@ORA806.WORLD>
scott@ORA806.WORLD> declare
2 type rc is ref cursor;
3 x rc;
4 begin
5 for i in 1 .. 500 loop
6 open x for select * from t_pub_syn;
7 close x;
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.60
scott@ORA806.WORLD>
scott@ORA806.WORLD> declare
2 type rc is ref cursor;
3 x rc;
4 begin
5 for i in 1 .. 500 loop
6 open x for select * from t_priv_syn;
7 close x;
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.58
scott@ORA806.WORLD>


tkprof shows me:


SELECT *
FROM
TKYTE.T


call count cpu elapsed disk query
------- ------ -------- ---------- ---------- ----------
Parse 500 0.85 0.86 0 0
Execute 500 0.15 0.13 0 0
Fetch 0 0.00 0.00 0 0


SELECT *
FROM
T_PUB_SYN


call count cpu elapsed disk query
------- ------ -------- ---------- ---------- ----------
Parse 500 0.89 0.88 0 0
Execute 500 0.10 0.08 0 0
Fetch 0 0.00 0.00 0 0

SELECT *
FROM
T_PRIV_SYN


call count cpu elapsed disk query
------- ------ -------- ---------- ---------- ----------
Parse 500 1.01 1.00 0 0
Execute 500 0.11 0.09 0 0
Fetch 0 0.00 0.00 0 0


So, the public synonym did not take 4x the CPU of the fully qualified or the private synonym -- they were all pretty much "equivalent" in this case. I would need to see his test case to fully understand how the 1:2:4 ratio came about.


So, I would take into consideration what Steve says -- however, I would also look at how my system is performing. I run a system that gets about 1,000-2,000 different named users every day. We use public synonyms for lots of different top level packges (but not on other stuff). It works well. Since each of our packages are in different schemas, a simple "alter session set current_schema" would not work for us (we have about 100 application schemas -- we don't know which one of these you want to use up front).