Last updated on February 23rd, 2019 at 07:43 am
Shared pool is important memory structure in Oracle database system global area(SGA)
Shared Pool in Oracle Decoded
Shared pool in Oracle database is the place where Oracle caches the SQL , PL/SQL and dictionary data.
Whenever we parse a query, the parsed representation is cached there and it is shared also.Oracle check for the query first in the shared pool before parsing an entire query, if it finds the parse copy cached, it does not perform that work again and this is significant saving for the CPU and time.
Similarly PL/SQL code that you run is cached in the shared pool, so the next time you run it, Oracle doesn’t have to read it in from disk again. PL/SQL code is not only cached here, it is shared here as well. So if we have multiple session executing the same PL/SQL program, it uses the same copy cached in the pool
Oracle also stores the system parameters in the shared pool.
The data dictionary cache (cached information about database objects) is stored here.
Shared Pool manages memory on an LRU basis, similar to buffer cache, which is perfect for caching and reusing data.
In short, it has these areas
-Text of the SQL or PL/SQL statement
-Parsed form of the SQL or PL/SQL statement
-Execution plan for the SQL or PL/SQL statements
-Data dictionary cache containing rows of data
dictionary information
It consists of Library cache and Dictionary cache primarily
Library Cache
-shared SQL area
-private SQL area
-PL/SQL procedures and package:Executable representation of PL/SQL packages ,procedures and functions that may be used repeatedly by many sessions.It also holds stored trigger code.
-control structures : lock and library cache handles
Dictionary Cache
-names of all tables and views in the database
-names and datatypes of columns in database tables
-privileges of all Oracle users
-Oracle Database uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs
Shared pool size
Shared pool is defined by the shared_pool_size and shared_pool_reserved_size parameters in the Oracle database. If we are using Automatic Memory management like SGA_TARGET or MEMORY_TARGET, then we dont need to specify these parameters.Oracle automatically sets the memory size for the shared pool depending on the requirement.
If we are not using Automatic Memory management , we can use the below two parameter to control the size of the shared pool in oracle database
SHARED_POOL_RESERVED_SIZE specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory.
You can use this parameter to avoid performance degradation in the shared pool in situations where pool fragmentation forces Oracle to search for and free chunks of unused pool to satisfy the current request.
SHARED_POOL_SIZE specifies (in bytes) the shared pool space that is allocated for shared pool memory.
These parameter can be altered using alter system set command
alter system set shared_pool_size=2G scope=spfile;
We will need to bounce the oracle database to modified
Useful Queries for Shared Pool
1. View the Amount of Free Memory in the Shared Pool
SELECT * FROM V$SGASTAT WHERE name = 'free memory' AND pool = 'shared pool';
2.shows a query of this view to examine each namespace individually.
SELECT namespace, pins, pinhits, reloads, invalidations FROM V$LIBRARYCACHE ORDER BY namespace;
- Checking the size of the shared pool
show parameter shared_pool_size; show parameter shared_pool_reserved_size;
Shared_pool flush
We can flush the shared pool using the command
alter system flush shared_pool;
It takes out all unusable SQL statement from the shared pool. The used ones are not flushed. The pinned one are also not flushed
shared_pool flush improves the performance if application issues a large amount of non-reusable SQL statements. The library cache becomes floored with non-reusable SQL and there will be significant slowdowns as Oracle futilely parses incoming SQL looking for a pre-parsed matching statement.
This operation shared_pool flush can be done online
In case of Oracle RAC,it has to be executed on all the nodes
Related Articles
how to find the semaphore for the Database instance
Why pinning objects into the shared pool?
How to turn on the SQL trace, 10046 event in Oracle database and trcsess, tkprof utility
Top Useful scripts for Oracle Database for Monitoring purpose