Oracle Database - Shared Pool

Card Puncher Data Processing


The Shared pool is a shared memory area where cursors and PL/SQL objects are stored.

The shared pool is a shared memory that store:

  • Cached data dictionary information and
  • Shared Cursors and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.

Portion of the SGA that contains shared memory constructs such as :

Oracle Database Sga

shared_pool_size in parallel execution

Parallel servers communicate among themselves and with the Query Coordinator by passing messages. The messages are passed via memory buffers that are allocated from the shared pool. When a parallel server is started it will allocate buffers in the shared pool so it can communicate, if there is not enough free space in the shared pool to allocate the buffers the parallel server will fail to start.

In order to size your shared pool appropriate you should use the following formulas to calculate the additional overhead parallel servers will put on the shared pool. If you are doing inter-node parallel operations

(((2 + (cpu_count X parallels_threads_per_cpu)) X 2) X (cpu_count X parallels_threads_per_cpu)) X
parallels_execution_message_size X # concurrent queries

or when you use cross instance parallel operation in a RAC environment.

(((2+ (cpu_count X 2)) X 4) X cpu_count X 2)) X parallel_execution_message_size X # concurrent queries

Note the results are returned in bytes.

Only the memory needed for the parallel_min_servers will be preallocated from the shard_pool at database startup. As additional parallel servers are needed, their memory buffers will be allocated “on the fly” from the shared pool. These rules apply irrespective of whether you use shared_pool_size directly, or sga_target (10g and higher) or memory_target (starting with 11g)




get advice

The view VSHARED_POOL_ADVICE gives advices. It oredicts the impact of different values of shared_pool_size on elapsed parse time saved.

This view is gathered when the statistics_level parameter is set on TYPICAL.


You can manage it with the apps/search/search.jsp package.

To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script.


The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs. These scripts are not run by as part of standard database creation.

Documentation / Reference

Recommended Pages
Data System Architecture
Concurrency - Latches (System Lock)

Latches are like semaphores. Latches are used to guarantee physical consistency of data, while locks are used to assure logical consistency of data. Latches are simple, low-level system lock (serialization...
Card Puncher Data Processing
Oracle Database - (Memory) Cache (Tables, )

Set the tablespace offline and online or flush the buffer cache or flush the shared pool Small tables are automatically cached according to this criteria : Table Caching Criteria Table Size Size...
Card Puncher Data Processing
Oracle Database - Automatic reoptimization / (statistics|cardinality) feedback

Statistics feedback also known as: cardinality feedback Automatic reoptimization is an adaptive feature. It improves plans for repeated queries that have cardinality misestimates. adaptive plans...
Card Puncher Data Processing
Oracle Database - DDL Locks (data dictionary lock)

A data dictionary (DDL) lock protects the definition of a schema object while an ongoing DDL operation (CREATE, ALTER, DROP) acts on or refers to the object. Only individual schema objects that are modified...
Oracle Database Sga
Oracle Database - Session (Library|Cursor) Cache

The library cache is a part of the shared pool Private Sql Area Also known as: Session cursor cache. V contains the execution plan information for each child cursor loaded in the library...
Oracle Database Sga
Oracle Database - System Global Area - SGA (Shared Memory)

The SGA (System Global Area) is an area of memory allocated when an Oracle Instance starts up. The SGA memory is shared by all sessions connected to Oracle Instance. See A group of shared memory structures...
Card Puncher Data Processing
Oracle Database - V$SQL_WORKAREA

V displays information work areas used by SQL cursors. The primary key for the view is WORKAREA_ADDRESS, the memory Address...
Card Puncher Data Processing
Oracle Database - V$SQL

V lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text entered (This parents must be seen in v). When a SQL statement ages out...
Card Puncher Data Processing
Oracle Database - parallel_execution_message_size parameter

The Parallel servers communicate among themselves and with the Query Coordinator by passing messages via memory buffers. If you execute a lot of large operations in parallel pa, it’s advisable to reduce...
Oracle Database Sql Processing
SQL Engine - SQL Parser

The parser in an SQL engine parse a Sql statement. It's the first stage of SQL processing. This stage involves separating the pieces of a SQL statement into a SQL Tree where each node is a SQL token...

Share this page:
Follow us:
Task Runner