|
Chapter 25 Tuning PL/SQL Applications |
|
Before your code can be executed (and perhaps run too slowly), it must be loaded into the System Global Area (SGA) of the Oracle instance (described in more detail in Chapter 23, Managing Code in the Database ). There are two elements to PL/SQL code in shared memory: the code segment and the data segment. This loading process can benefit from its own special tuning effort.
Before you can execute a stored package module or reference a stored package object, the compiled code for that package must be loaded into the SGA. Clearly, if the package is already present in shared memory, your code executes more quickly. An important element of tuning an application which is heavily dependent on stored packages (especially large ones) is to optimize package access so that the most often-used packages are always present when needed.
The default method for maintaining packages in the SGA (or "shared pool") is to let the RDBMS manage the code using its least-recently-used algorithm. The first time you reference a package, the compiled code is loaded into the shared pool. It is then available to anyone with EXECUTE authority on that package. It remains in the shared pool until the memory is needed by other memory-based resources and that package has not been used most recently. At that point, the package is flushed from the shared pool. The next time an object in the package is needed, the whole package has to be loaded once again into memory.
The larger your shared pool, the more likely it is that your programs will be resident in memory the next time they are needed. Yet if you make your shared pool too large, you will be wasting memory. You should monitor your shared buffer pool to make sure it is retaining all the parsed SQL cursors and PL/SQL code segments which are commonly referenced in your application. If you find that too much swapping is occurring, increase the size of the shared buffer pool (as physical memory permits) by adjusting the SHARED_POOL_SIZE parameter in your INIT.ORA file.
You can display all the objects currently in the shared pool that are larger than a specified size (in the example, 25KB) with the following statement (make sure you have SET SERVEROUTPUT ON in SQL*Plus before you make this call):
SQL> exec dbms_shared_pool.sizes (25);
To increase your ability to tune application performance, Oracle supplies the DBMS_SHARED_POOL package to pin a package in the shared pool. When a package is pinned, the RDBMS does not apply its least-recently-used algorithm to that package. The package remains in memory for as long as the database instance is available (or until you explicitly "unpin" the package, as described below).
At this time, only packages can be pinned in the shared pool; this fact provides added incentive to define your procedures and functions inside packages, rather than as standalone modules.
You will only want to pin code when absolutely necessary, since you can end up setting aside too much memory for code, resulting in a degradation in performance of other aspects of the application. In fact, Oracle Corporation warns that the KEEP and UNKEEP procedures may not be supported in future releases, since it might provide an "automatic mechanism" which replaces these procedures.
The usual candidates for pinning are particularly large programs. Prior to Oracle 7.3, Oracle requires contiguous memory in the SGA to store the code, so if sufficient space is not available at a given point of execution, Oracle will either raise an error or start swapping out other programs to make room. Neither scenario is optimal.
Usually you will pin programs right after the database is started up, so that all the critical elements of your application are in place for all users. Here is an example of the pinning of the order entry package (owned by the appowner schema):
DBMS_SHARED_POOL.KEEP ('appowner.ordentry');
Here is the code you would execute to unpin the same package from shared memory:
DBMS_SHARED_POOL.UNKEEP ('appowner.ordentry');
Keep the following factors in mind when working with the DBMS_SHARED_POOL package:
When you call the KEEP procedure, the package is "queued" for pinning in the SGA. However, the KEEP procedure does not immediately load the package into the shared pool -- that happens when the package is first referenced, either to execute a module or to use one of the declared objects, such as a global variable or a cursor.
Oracle recommends that you pin all your packages in the shared pool soon after instance startup, when the SGA is still relatively unfragmented. That way it can set aside contiguous blocks of memory for large packages.
If you pin a package which is not owned by SYS (DBMS_OUTPUT, for example, is owned by SYS), you must grant EXECUTE on the package to PUBLIC before you execute the KEEP procedure. Once you have pinned the package, you can revoke the PUBLIC access.
The SQL DDL command, ALTER SYSTEM FLUSH SHARED_POOL, does not affect pinned packages. You must either explicitly UNKEEP a package or restart the database to release the package from the shared pool.
You might consider pinning the following packages in the shared pool to improve performance:
Package which implements the core elements of the PL/SQL language.
Package of standard database-level modules, such as RAISE_APPLICATION_ERROR.
Package of low-level database utilities which are used to analyze schemas and objects.
Package containing a utility to describe the structure of a stored module.
Package which allows programmers to display output to the screen.
In addition, if you make frequent use of any other Oracle-provided "SYS" packages such as DBMS_LOCK or DBMS_PIPE, pinning those objects could improve performance as well.
You are probably getting the idea that the more you pin into the shared pool, the better off you are. Certainly that is true, at least as true as the statement: "If all your data is stashed in memory, your applications will run much faster." Memory is always quicker than disk access. The problem is making sure you have enough memory.
The more you pin into the shared pool, the less space is left in the SGA for other memory-based resources, such as data dictionary latches, application data, and shared SQL. Since a pinned object is never aged out of the SGA using a least-recently-used algorithm, other elements in the SGA are instead pushed out of the way.
You can use SQL to generate a script to KEEP packages in the SGA. You can use the following SQL statement to access the v$db_object_cache to generate a KEEP for each package currently in the shared pool:
SELECT 'EXECUTE DBMS_SHARED_POOL.KEEP('''||name||''');' FROM v$db_object_cache WHERE type='PACKAGE';
You can also generate a KEEP statement for every package currently stored in the database with these other SQL statements:
SELECT DISTINCT 'EXECUTE DBMS_SHARED_POOL.KEEP('''||name||''');' FROM user_source WHERE type='PACKAGE'; SELECT DISTINCT 'EXECUTE DBMS_SHARED_POOL.KEEP('''||object_name||''');'
FROM user_objects
WHERE object_type='PACKAGE';
When a database object is first referenced in a PL/SQL program, the PL/SQL engine checks the ACCESS$ table (owned by SYS) to see if the executor of the program has authority on that database object. The structure of this table is shown here:
SQL> desc access$ Name Null? Type ------------------------------- -------- ---- D_OBJ# NOT NULL NUMBER ORDER# NOT NULL NUMBER COLUMNS RAW(32) TYPES NOT NULL NUMBER
The PL/SQL engine searches through this table on the D_OBJ# column, so if you create a nonunique index on the D_OBJ# column, you may in some cases reduce significantly the amount of time needed to perform this security check.
Design your code (preferably, most of it inside packages) so that you only load into memory the code you need at any given moment in time. To accomplish this objective, you should create more smaller packages, each of which is tightly focused on implementing functionality in a given area. The alternative, which too many of us employ without giving it much thought, is to create a few large packages that group together lots of different elements of functionality.
The problem with this approach is that if I need to execute one function in that 32K package, the entire package still must be loaded up into memory. Suppose that my application then touches another element of the package, such as a constant or perhaps a different function in a different functional area. The least-recently-used algorithm will then ensure that all the memory for that package continues to be set aside, perhaps crowding out other smaller programs. The result can be excessive swapping of code.
As you build your programs and design your package interfaces, be on the lookout for an opportunity to break up a single package into two or even more distinct packages with minimal interdependencies.
Prior to PL/SQL8, any data declared in a package simply stayed around until the end of the session, whether or not it was needed any more by the application. This is an important feature of PL/SQL packages (persistent, global data), but it limits scalability since such memory grows linearly with the number of users.
To help applications better manage memory usage, PL/SQL8 provides the pragma SERIALLY_REUSABLE, which lets you mark some packages as "serially reusable." You can so mark a package if its state is needed only for the duration of a call to the server (for example, an OCI call to the server, a PL/SQL client-to-server, or server-to-server RPC).
The global memory for such packages is not kept in the memory area per user, but instead in a small SGA pool. At the end of the call to the server, this memory is returned to the pool for reuse. Before reuse, the package global variables are initialized to NULL or to the default values provided.
The pool is kept in SGA memory so that the work area of a package can be reused across users who have requests for the same package. In this scheme, the maximum number of work areas needed for a package is only as many as there are concurrent users of the package, which is typically much fewer than the total number of logged on users. The use of "serially reusable" packages does increase the shared-pool requirements slightly, but this is more than offset by the decrease in the per-user memory requirements. Further, Oracle ages out work areas not in use when it needs to reclaim shared pool memory.
The following example shows how global variables in a "serially reusable" package behave across call boundaries:
CREATE OR REPLACE PACKAGE sr_pkg IS PRAGMA SERIALLY_REUSABLE; num NUMBER := 0; PROCEDURE print_pkg; PROCEDURE init_pkg (n NUMBER); END sr_pkg; / CREATE OR REPLACE PACKAGE BODY sr_pkg IS -- the body is required to have the pragma since the -- specification of this package has the pragma PRAGMA SERIALLY_REUSABLE; -- Print package state PROCEDURE print_pkg is BEGIN DBMS_OUTPUT.PUT_LINE ('num: ' || sr_pkg.num); END; -- Initialize package state PROCEDURE init_pkg(n NUMBER) IS BEGIN sr_pkg.num := n; END; END sr_pkg; /
Now I will exercise this package. First, I enable output from SQL*Plus:
SQLPLUS> set serveroutput on;
Next, I initialize the package with a value of 4 and then display package contents -- all within a single PL/SQL block:
SQLPLUS> begin -- initialize and print the package SR_PKG.init_pkg(4); -- Print it in the same call to the server. -- We should see the new values. SR_PKG.print_pkg; end; / Statement processed. num: 4
And we see that initial value of 4. If I had not placed the call to sr_pkg.print_pkg inside the same PL/SQL block, however, that package variable would lose its setting, as you can see in the following steps:
SQLPLUS> begin -- We should see that the package state is reset to the -- initial (default) values. SR_PKG.print_pkg; end; / Statement processed. num: 0
Use this feature with care! Many of the packages I have constructed over the years absolutely rely on the persistent data feature.
|
|
|
|
| 25.1 Analyzing Program Performance |
|
25.3 Tuning Access to Your Data |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.