when I click on the "followup" button and send you an email (the one you just got), you should be seeing the WHOLE thing -- if not, please send me the email you just recieved and I'll see why not....
Anyway, I've run his test in 9iR2 and find (as measured by v$mystat):
Hard Parse Performance
NO synonyms private synonyms public synonyms
CPU 168 165 171
row cache 25,920 27,840 29,818
shared pool 27,162 29,124 31,240
library cache 21,287 27,048 33,213<b>
TOTAL LATCHES 127,054 142,659 161,192</b>
Soft Parse Performance
NO synonyms private synonyms public synonyms
CPU 81 83 83
row cache 20,160 20,160 20,218
shared pool 7,786 7,787, 8,013
library cache 15,453 15,454 15,852<b>
TOTAL LATCHES 82,676 83,232 85,708</b>
So, I do not see the egregious results he does on the soft parse, however, I zero in on the total latches personally -- as latched = locks = scalability killers.
During the hard parse -- it is clear, no synonyms use 20-25% less latches, during the soft parse, about 5% less.
But my major gripe with synonyms has never been purely performance based. When used JUDICIOUSLY (in small quantities), they can be good. Hey -- I use them on asktom -- that procedure F you see in my urls? that's a public synonym -- but it is the ONLY one, everything else is a qualifitied schema call.
My gripe is the namespace pollution, the confusion, the human errors it lends itself towards -- not so much the performance -- although that is a consideration.
Here is my test script -- should run in at last 817 and up:
spool test
set echo on
define NITERS=&1
@connect /
drop table run_stats;
create table run_stats
( runid varchar2(20), what varchar2(10), name varchar2(80), value int );
REM pause
create or replace view run_stats_report
as
select runid, what, name, diff, sum(diff) over(partition by what) tot
from (
select b.runid, b.what, b.name, b.value-a.value diff
from run_stats a, run_stats b
where a.name = b.name
and a.what = b.what
and a.runid = 'before'
and b.runid <> 'before'
and b.value-a.value <> 0
)
order by what, diff
/
REM pause
create or replace procedure save_stats( p_str in varchar2 )
as
begin
if (p_str = 'before')
then
delete from run_stats;
end if;
insert into run_stats (runid, what, name, value)
select p_str, stats.*
from ( select 'STAT', a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH', name, gets
from v$latch ) stats;
end;
/
REM pause
grant execute on save_stats to public;
drop user tbl_owner cascade;
begin
for x in ( select synonym_name from dba_synonyms where synonym_name like 'SYNTEST_T%' )
loop
execute immediate 'drop public synonym ' || x.synonym_name;
end loop;
end;
/
drop user u1 cascade;
drop user u2 cascade;
REM pause
grant dba to tbl_owner identified by tbl_owner;
alter user tbl_owner default tablespace assm;
grant dba to u1 identified by u1;
grant dba to u2 identified by u2;
REM pause
@connect tbl_owner/tbl_owner
begin
for i in 1 .. &NITERS
loop
execute immediate 'create table syntest_t' || i || ' ( x number )';
execute immediate 'create public synonym syntest_t' || i || ' for syntest_t' || i;
end loop;
end;
/
REM pause
@connect u1/u1
begin
for i in 1 .. &NITERS
loop
execute immediate 'create synonym syntest_t' || i || ' for tbl_owner.syntest_t' || i;
end loop;
end;
/
REM pause
alter system flush shared_pool;
begin
for i in 1 .. &NITERS
loop
execute immediate 'insert into syntest_t' || i || ' values (1)';
end loop;
end;
/
REM pause
exec ops$tkyte.save_stats( 'before' );
begin
for i in 1 .. &NITERS
loop
execute immediate 'insert into syntest_t' || i || ' values (0)';
end loop;
end;
/
exec ops$tkyte.save_stats( 'after hard privsyn' );
select * from ops$tkyte.run_stats_report;
REM pause
exec ops$tkyte.save_stats( 'before' );
begin
for i in 1 .. &NITERS
loop
execute immediate 'insert into syntest_t' || i || ' values (0)';
end loop;
end;
/
exec ops$tkyte.save_stats( 'after soft privsyn' );
select * from ops$tkyte.run_stats_report;
REM pause
@connect u2/u2
alter system flush shared_pool;
begin
for i in 1 .. &NITERS
loop
execute immediate 'insert into syntest_t' || i || ' values (1)';
end loop;
end;
/
REM pause
exec ops$tkyte.save_stats( 'before' );
begin
for i in 1 .. &NITERS
loop
execute immediate 'insert into syntest_t' || i || ' values (0)';
end loop;
end;
/
exec ops$tkyte.save_stats( 'after hard pubsyn' );
select * from ops$tkyte.run_stats_report;
REM pause
exec ops$tkyte.save_stats( 'before' );
begin
for i in 1 .. &NITERS
loop
execute immediate 'insert into syntest_t' || i || ' values (0)';
end loop;
end;
/
exec ops$tkyte.save_stats( 'after soft pubsyn' );
select * from ops$tkyte.run_stats_report;
REM pause
@connect /
alter system flush shared_pool;
begin
for i in 1 .. &NITERS
loop
execute immediate 'insert into tbl_owner.syntest_t' || i || ' values (1)';
end loop;
end;
/
REM pause
exec ops$tkyte.save_stats( 'before' );
begin
for i in 1 .. &NITERS
loop
execute immediate 'insert into tbl_owner.syntest_t' || i || ' values (0)';
end loop;
end;
/
exec ops$tkyte.save_stats( 'after hard NOsyn' );
select * from ops$tkyte.run_stats_report;
REM pause
exec ops$tkyte.save_stats( 'before' );
begin
for i in 1 .. &NITERS
loop
execute immediate 'insert into tbl_owner.syntest_t' || i || ' values (0)';
end loop;
end;
/
exec ops$tkyte.save_stats( 'after soft NOsyn' );
select * from ops$tkyte.run_stats_report;
spool off