Oraclecan share the source and compiled versions of SQL statements and anonymous blocks even if they are submitted from different sessions by different users. For that to happen, though, certain conditions must be true. There are five major guidelines for getting Oracle to share SQL statements; the first three also apply to anonymous PL/SQL blocks.
1. The letter case andspacing conventions of the source code must match exactly.
2. External references must resolve to the same underlying object in order for the program to be shared.
3. Data values must be supplied via bind variables rather than literal strings (or the CURSOR_SHARING system setting must have an appropriate value).
Oracle caches SQL statements separately from any PL/SQL programs that contain them, and the sharing rules are slightly different. For SQL statements, the two additional rules are:
4. All database parameters influencing the SQL optimizer must match. For example, the invoking sessions must be using the same "optimizer goal" (ALL_ROWS versus FIRST_ROWS).
5. The invoking sessions must be using the same language (National Language Support, or NLS) environment.
Although I'm not going to talk more about these last two rules, I would like to explore the impact of the first three rules on your PL/SQL programs.
Rule #1, matching letter case and spacing, is a well-known condition to sharing statements. Despite the fact that PL/SQL is normally a case-independent language, the block:
BEGIN NULL; END;does not match:
begin null; end;nor does it match:
BEGIN NULL; END;This is a painful fact of life, and it also applies to SQL statements. However, if all your anonymous blocks are short and all your "real programs" are in stored code such as packages, there is much less chance of inadvertently disabling code sharing. The tip here is:
Centralize your SQL and PL/SQL code in stored programs. Anonymous blocks should be as short as possible, generally consisting of a single call to a stored program.
In addition, an extension of this tip applies to SQL:
To maximize the sharing of SQL statements, put SQL into programs. Then call these programs rather than write the SQL you need in each block.
I've always felt that trying to force statement sharing by adopting strict formatting conventions for SQL statements was just too impractical; it's much easier to put the SQL into a callable program.
Moving on, Rule #2 says that external references (to tables, procedures, etc.) must resolve to the same underlying object. Say that Scott and I are connected to Oracle, and we both run a block that goes like this:
BEGIN foo;END;Oracle's decision about whether to share the cached form of this anonymous block boils down to whether the name "foo" refers to the same underlying stored procedure. If Scott has a synonym foo that points to my copy of foo, then Oracle will share this anonymous block; if Scott and I own independent copies of foo, Oracle will not share this anonymous block. So even if the two copies of foo are line-by-line identical, Oracle caches these as different objects. Oracle also caches identical triggers on different tables as different objects. That leads to the following tip:
|
|
|
Avoid proliferating copies of tables and programs in different accounts unless you have a very good reason.
Furthermore:
To help Oracle economize memory, pull out code that is common to multiple programs (especially triggers) and incorporate it by call rather than by duplicating the code. Then, set up your system so that one database user owns the PL/SQL programs, and grant EXECUTE privilege to any other user who needs it. (The later section Section 20.6 covers the overall topic of execution privileges more thoroughly.)
There is an important exception to this tip, and it applies if you are running in a high concurrency environmentthat is, many users simultaneously executing the same PL/SQL program. Whenever these common bits of code are called, a "library cache latch" is needed to establish and then release a pin on the object. In high concurrency environments, this can lead to latch contention. In such cases, duplicating the code wherever it is needed is actually preferred, as doing so will avoid latching and improve performance.
Rule #3the one about bind variablesis important enough to have an entire section devoted to it.
