Shared Pool decoded and Shared pool flush



Last updated on August 27th, 2016 at 05:04 pm

Shared pool is important memory structure in Oracle database system global area(SGA)

shared pool

 Shared Pool Decoded

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

In case of RAC,it has to be executed on all the nodes

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;


Leave a Reply