Shared Pool

Shared Pool

The Shared Pool environment contains both fixed and variable structures. The fixed

structures remain relatively the same size, whereas the variable structures grow and shrink

based on user and program requirements. The actual sizing for the fixed and variable

structures is based on an initialization parameter and the work of an Oracle internal

algorithm.

Benefits of Using the Shared Pool

Proper use and sizing of the shared pool can reduce resource consumption in at least four ways:

1-If the SQL statement is in the shared pool, parse overhead is avoided, resulting in reduced CPU resources on the system and elapsed time for the end user.

2-Latching resource usage is significantly reduced, resulting in greater scalability.

3-Shared pool memory requirements are reduced, because all applications use the same pool of SQL statements and dictionary resources.

4-I/O is reduced, because dictionary elements that are in the shared pool do not require disk access

There are two parts of Shared Pool

1-Library Cache

2-Data Dictionary Cache

1- Library cache

                The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code.The Library Cache is managed by an LRU algorithm.It contains -

               

Shared SQL: The Shared SQL stores and shares the execution plan and parse tree for

SQL statements run against the database. The second time that an identical SQL

statement is run, it is able to take advantage of the parse information available in the

shared SQL to expedite its execution. To ensure that SQL statements use a shared SQL

area whenever possible, the text, schema, and bind variables must be exactly the same.

Shared PL/SQL: The Shared PL/SQL area stores and shares the most recently

executed PL/SQL statements. Parsed and compiled program units and procedures

(functions, packages, and triggers) are stored in this area.

2- Data Dictionary Cache

The Data Dictionary Cache is also referred to as the dictionary cache or row cache.

Information about the database (user account data, data file names, segment names, extent

locations, table descriptions, and user privileges) is stored in the data dictionary tables.

When this information is needed by the server, the data dictionary tables are read, and the

data that is returned is stored in the Data Dictionary Cache.