A shared pool is an important memory structure in Oracle database system global area(SGA)
What is Shared Pool in Oracle
- The shared pool in the 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 a 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 sessions 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 the 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
- The execution plan for the SQL or PL/SQL statements
- Data dictionary cache containing rows of data dictionary information
It consists of a 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 the 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 the unused pool to satisfy the current request.
SHARED_POOL_SIZE specifies (in bytes) the shared pool space allocated for shared pool memory.
This 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
How to check free space in shared pool in oracle/ Shared Pool Usage query
SELECT * FROM V$SGASTAT WHERE name = 'free memory' AND pool = 'shared pool';
Query to examine each namespace individually in the Library cache
SELECT namespace, pins, pinhits, reloads, invalidations FROM V$LIBRARYCACHE ORDER BY namespace;
How to check share pool size in Oracle
show parameter shared_pool_size; show parameter shared_pool_reserved_size;
How to do Shared_pool flush
We can flush the shared pool using the command
alter system flush shared_pool;
- It takes out all unusable SQL statements from it. The used ones are not flushed. The pinned ones are also not flushed
- shared_pool flush improves the performance if the 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 the case of Oracle RAC, it has to be executed on all the nodes
How to Flush a Single SQL Statement from the Shared Pool
We can use the below steps
Find the Address and Hash_value
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='&1';
The purge using the below syntax
exec DBMS_SHARED_POOL.PURGE ('ADDRESS,HASH_VALUE','C');
Or we can use the below script
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; / 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;
How to increase shared pool size in oracle
We need to restart the database to increase the shared pool size given in init.ora. Shared pool size adjust automatically from the given value in init.ora if the AMM(Automatic memory Management) is set
SQL>alter system set SHARED_POOL_SIZE = <new size> scope=spfile;
sql > shutdown immediate
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