Last updated on August 27th, 2016 at 05:04 pm
Shared pool is important memory structure in Oracle database system global area(SGA)
-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
- 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; |