Shared Pool decoded and Shared pool flush in Oracle database

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.

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
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

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;

1. 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 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;

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