Implications of PL/SQL Object Location in Oracle

We generally recommend storing the PL/SQL objects on the server side, for many of the obvious reasons. The server is usually much more powerful and objects are reused much more often (especially when pinned into the shared pool). The security methods employed are also more straightforward. Sending the PL/SQL to be processed on the client side can be dependent on the power of the client and can lessen the number of roundtrips from client to server. But, when written correctly, the calls may be limited back to the server (see the next section for an example).

There is certainly a continuing debate on this one, but with the evolving thin client, the server is probably the only place to store the PL/SQL.

Figure 1 diagrams how PL/SQL is executed when stored on the server side. Some additional reasons for storing code on the server are listed here:

• Performance is improved because the code is already compiled code (p-code).
• You can pin objects in the Oracle SGA.
• It enables transaction-level security at the database level.
• You have less redundant code and fewer version control issues.
• You can query the source code online because it is stored in the data dictionary.
• Performing impact analysis is easier since the code is stored in the data dictionary.
• It uses less memory because only one copy of the code is in memory.
• If packages are used, then the entire package is loaded upon initially being referenced.

Figure 1. Executing an object on the server side

TIP

Where to store the PL/SQL code is an ongoing debate. Generally, the server side is the preferred place to store the code, and it may become the only choice as thin clients become more prevalent.