Shared Pool in Oracle and Shared pool flush in Oracle database

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 a 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 codes.
• 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

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 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 activate these changes

Let’s find out some Useful Queries for Shared Pool

How to check free space in the 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. The shared pool size adjusts 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
sql> startup

Hope you like this article on Shared pool in Oracle, how to flush the shared pool, and How to Flush a Single SQL Statement from the Shared Pool. Please do provide the feedback

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