A Trace of Memory

Let's say you're cruising along with your database running just fine, with lots of PL/SQL and SQL statements happily zipping by, and then it strikes: ORA-04031: unable to allocate n bytes of shared memory. I've seen this error only in shared server mode, which caps a shared server's UGA memory; in dedicated server mode, Oracle simply grabs more virtual memory from the operating system.

Even just to start a PL/SQL program, Oracle must load all of its bytecode into memory. To see how much space an object actually occupies in the shared pool, you can run the built-in procedure DBMS_SHARED_POOL.SIZES, which lists all objects over a given size. Here is an example:^[6]

^[6] If you're wondering why the columns of data do not line up properly with their headings, it's probably because of the severe limitations of DBMS_OUTPUT. If you don't like it, write your own (grab the query from V$SQLAREA after running the package).

SQL> SET SERVEROUTPUT ON SIZE 1000000SQL> EXEC DBMS_SHARED_POOL.SIZES(minsize => 100) SIZE(K) KEPT NAME------- ------ ---------------------------------------------------------------371 SYS.STANDARD (PACKAGE)166 SYS./5ee89977_NamespaceRDBMS (JAVA CLASS)101 YES SYS.java/math/BigInteger (JAVA CLASS) PL/SQL procedure successfully completed.

This output shows that the package STANDARD occupies 371K of shared memory.^[7] This is necessary, but not sufficient, information; you must also know the size of the shared pool and how much of the shared pool is filled by "re-creatable" objects—that is, objects that can be aged out of memory and loaded again later when needed. As a developer, you also need to know if your applications contain a large amount of unshared code that logically could be shared.

^[7] There is a bug in most older versions of DBMS_SHARED_POOL.SIZES that results in the amount's being over-reported by about 2.3%. Oracle's package erroneously computes kilobytes by dividing bytes by 1000 instead of by 1024.

There are several ways to correct an ORA-04031 condition. A competent DBA (hey, don't look at me) will know how to tune the shared pool by adjusting parameters such as these:

SHARED_POOL_SIZE

Total amount of memory set aside for the shared pool.

SHARED_POOL_RESERVED_SIZE

Amount of memory in the shared pool that Oracle will keep contiguous.

LARGE_POOL_SIZE

Optional region of memory, separate from the shared pool, that holds UGA for shared server connections. This prevents the UGA from competing for use of the shared pool.

SESSION_CACHED_CURSORS

Number of entries in the session cursor cache.

CURSOR_SPACE_FOR_TIME

If set to true, Oracle will deallocate a cursor from the library cache only if all applications have closed any cursors associated with it. This value is normally true.

If you're the application developer, though, you can do at least two things on your own:

· Modify code to ensure that the maximum number of SQL statements get shared.

· Petition the DBA to force certain PL/SQL programs and/or cursors to be kept in memory.

You can force the shared pool to hold a particular program in memory with the DBMS_SHARED_POOL.KEEP procedure.^[8]

^[8] In the fine print, Oracle says that it may obsolete the feature when it comes up with better memory management algorithms.

For example, a DBA could run this:

BEGIN DBMS_SHARED_POOL.KEEP('SYS.STANDARD');END;

This would force Oracle to keep the package SYS.STANDARD in memory.

Oracle provides a variety of dictionary views that may help you decide what to keep in memory. While most authors recommend pinning the STANDARD package in the shared pool, respected internals expert Steve Adams recommends keeping "everything that moves." This became less important from 8.1.6 onwards, when Oracle introduced a feature that eliminates latch contention when the shared pool gets fragmented. For more of Mr. Adams' sage counsel about Oracle internals, visit http://www.ixora.com.au/.

The KEEP tactic could be appropriate for large programs that go unused for a stretch of time because, on reload, they may force out many smaller objects. KEEP works for packages, procedures, functions, triggers, sequences, and, with a little extra effort on the part of the programmer, cursors.

Oracle follows a different memory-sharing tactic for PL/SQL programs that are compiled into C using the native execution feature. These get linked into shared-library files whose common code will load into memory only once, and they allocate memory from the operating system. Other programs called by the server, such as Java stored procedures and external (C) procedures, also use shared memory.

Another memory error you may encounter in shared server mode is ORA-06500: PL/SQL: storage error. Increasing the LARGE_POOL_SIZE will usually make this go away.

20.4 The Processing of Server-Side PL/SQL

To restateand amplify some of the main points covered so far, I'd like to enumerate the steps Oracle takes when you use a program such as SQL*Plus to submit PL/SQL to the server.