Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Nadya.

Asked: December 10, 2001 - 1:02 am UTC

Last updated: August 10, 2004 - 7:46 am UTC

Version: 8i

Viewed 1000+ times

You Asked

Tom, we read your answers about using public synonyms.
But I can't exactly understand is it recommended to use public synonyms, or their number should be limited.
The matter is: historically we use public synonyms for every object such as table, view, sequence. From your answers we have known they lead to non-existing objects and reduce performance. How much the using of public synonyms influences on performance,so must we reduce
the number of public synonyms or it's not very important thing.
Ones more question: One of our server procedures gives compilation error it's too long. From documentation we know, we must divide it.
But we want know what factors restrict the size of storage procedure?
Are they permanent or tuned?
Thanks.
Nadya





and Tom said...


You should limit your use of public synonyms.

When I write PLSQL code, I never use public synonyms in the code itself. I use schema names in the code.

I might create a single (or two or three) public synonyms for the TOP LEVEL procedure that calls the package -- the "entry point" into the system for web based systems, so users can use a URL like:

</code> http://host/pls/application_name

instead of 

  
http://host/pls/schema.pkg.procedure <code>

but all URLS after that would be in the form of the second one above.


Public synonyms -- they should be used purely for the convienence of the end user (limited number of entry points = limited number of synonyms).

In your code you can use:

o schema names
o alter session set current_scheme = <some name> if you don't want to use schema names.


If you have a procedure that gives you a compilation error due to size, it must be VERY large indeed (i shudder to think how large). I'm a guy who firmly believes a PROCEDURE should fit on a page -- a single screen (at the highest resolution I can get about 90 lines of code on a screen).

If it doesn't -- its too big to be called a procedure, you need to recode it in a more modular fashion.

Hopefully when you say "procedure" you really mean package -- you should only use packages and packages should have lots of little, modular, understandable procedures/functions in them.

The limit is based on the size of the generated pcode. they are permanent, burned into the database, you cannot set them.




Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Performance

Jim, December 10, 2001 - 6:56 pm UTC

Tom,
Totally agree with you on the one page limit
for procedures.

You have previously mentioned packages increase your namespace, what is "namespace" please ?

Also I seem to recall using a network monitoring program once that showed when referencing a public synonym in a client server app, that there was at least 3 network
trips to resolve the synonym, the first 2 times there
was an Oracle error code returned, the sniffer program
logged it but the error code did not cause the client application to halt.
I have this theory, the first time Oracle trys to resolve
the object (table, stored procedure etc)
second trip it looks for a private synonym,
third time it looks for a public synonym.

Do you know if I'm correct ot how I might test it
I don't have access to the network monitoring app anymore


Tom Kyte
December 10, 2001 - 8:09 pm UTC

The namespace thing I refer to is the namespace you have to create object names with.

Lets say you use procedures -- no packages. You can have one procedure "push" and one procedure "pop" in your schema. If someone else writes a "push" and a "pop" in your schema, it'll overwrite your push and pop.

Now, you put it into a packages "utils". You can put procedures push, pop, x, y, z in there (whatever). You have one NAME in the database, "utils". anyone can put another package in there -- say "foo" with procedures push, pop, x, y, z. You've increased your "namespace".

There would not be any increased roundtrips made by correctly programmed software with public synonyms. If you submit "select * from emp" to the database -- all of the work is done in that one trip. What you might have been seeing was a client server app that itself was trying to figure out what the heck "emp" was.

Thanks

Jim, December 10, 2001 - 8:45 pm UTC

Thanks Tom..
The client app was built using Powerbuilder,
perhaps thats all that needs to be said ;-)

private synonym vs alter session

Dilip, January 30, 2003 - 4:51 pm UTC

As mentioned fully qualifying or executing alter session set current_scheme is better than having public synonym.
a) Do we have the same performance hit if we use private synoymn's?
b) Will there be difference in preformance if using private synonym vs alter session set current_sch..?
Thanks


Tom Kyte
January 30, 2003 - 5:30 pm UTC

a) no, much less
b) no, i did not really observe any

The problem with public synonyms is they are sort of "anti objects". all kind of dependency hoops to jump through since they are not in your schema. with private synonyms -- there is a real object.

OK

Raju, August 10, 2004 - 1:13 am UTC


Dear Tom,
Is it not possible to create a synonym for a packaged function
or procedure?The problem is synonym gets created but when the
packaged function or procedure is accessed through that
synonym,Compiler throws some errors.
Could you please tell why this happens and how to correct it?
Please do reply.


Tom Kyte
August 10, 2004 - 7:46 am UTC

you can only create synonyms for top level schema objects -- things you see right after "create"

create table t
create package p
create function f


they can have synonyms -- but much like you cannot have a synonym for "a column" in a table - you cannot have a synonym for a function in a package.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.