About
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 :
- Data Dictionary Cache
- Controle Structure (locks, …)
Articles Related
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)
Management
Flush
ALTER SYSTEM FLUSH SHARED_POOL;
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.
package
You can manage it with the apps/search/search.jsp package.
To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script.
@?/rdbms/admin/dbmspool.sql
The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs. These scripts are not run by as part of standard database creation.