Shared SQL Area

A shared SQL area contains the parse tree and execution plan for every given SQL statement being run in the instance. Oracle conserves memory by using just one shared SQL area for SQL statements run multiple times. This often happens when many users run the same application or when one user runs the same SQL statement repeatedly (including the same bind variables), using slightly different values for some of the where clauses.

Oracle allocates additional memory from the shared pool to the shared SQL area whenever a new SQL statement is parsed and stores that SQL statement in the shared SQL area. The size of the memory that gets allocated depends on the size and the complexity of the statement in question. If the entire shared pool has already been allocated to parsed SQL statements, Oracle can deallocate statements from the pool by using a modified Least Recently Used (LRU) algorithm repeatedly until there is enough free space for the new statement's shared SQL area. If Oracle deallocates a shared SQL area, the SQL statement associated with that shared SQL area must then be reparsed and reassigned to the shared SQL area the next time it is executed.

Shared PL/SQL Area

The shared PL/SQL area stores and shares the most recently executed PL/SQL statements. Any parsed and compiled program units as well as procedures, functions, packages, and triggers can be stored in this area. Any program unit not frequently used is eventually aged out using the LRU algorithm.

Data Dictionary Cache

The data dictionary cache (also referred to as the dictionary cache or row cache) is a collection of the most recently used definitions in the database. The information stored in the data dictionary cache comes directly from the data dictionary views and their underlying tables. Information stored in this area includes information about database files, tables, indexes, users and their privileges, and other database objects.

During the parse phase of any statement, the server processes look at the data dictionary for information that might be needed to resolve object names and to validate a user's privileges to access any given object. The caching of this information into volatile memory improves response time for queries.

Large Pool (Optional)

The database administrator can configure an optional memory area in the SGA called the large pool to provide large chunks of memory allocations for I/O server processes, for processes connected directly to real application clusters, and for backup and restoration operations. It can also be used to relieve some of the burden placed on the shared pool. The large pool does not user the LRU algorithm to age information out.

Java Pool

Another optional memory area that the database administrator can configure is the Java Pool. The Java Pool is necessary when you are installing, compiling, and using Java in the database. The default setting for Java Pool is 20MB, which should be adequate unless you are using either shared server architecture, or have Java-intensive applications. If either of these is the case, you might want to expand the Java Pool to up to 100MB. Java is stored much the same way as PL/SQL in the database tables.

Next, we will look at yet another memory region of the Oracle instance, the Program Global Area.