Shared pool is important memory structure in Oracle database system global area(SGA)
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.
it 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
It consists of Library cache and Dictionary cache primarily
-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
-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
It 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;
We can flush the shared pool using the command
alter system flush shared_pool;
It takes out all unusable SQL statement from it. 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
How to Flush a Single SQL Statement from the Shared Pool
SELECT inst_id, loaded_versions, invalidations, address, hash_value FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1; SELECT inst_id, child_number, plan_hash_value, executions, is_shareable FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2; BEGIN FOR i IN (SELECT address, hash_value FROM gv$sqlarea WHERE sql_id = '&&sql_id.') LOOP SYS.DBMS_SHARED_POOL.UNKEEP(name => i.address||','||i.hash_value, flag => 'C'); SYS.DBMS_SHARED_POOL.PURGE(name => i.address||','||i.hash_value, flag => 'C'); END LOOP; END; / PRO *** after flush *** SELECT inst_id, loaded_versions, invalidations, address, hash_value FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1; SELECT inst_id, child_number, plan_hash_value, executions, is_shareable FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;