In this post, we will see shared pool architecture and how
it works internally since Oracle 7 through Oracle 11gR2.
Defining the Shared Pool
Shared pool is the most important component in System Global
Area (SGA) which is the RAM of database and it is the second largest in SGA
memory area. It contains several key
performance related memory areas. If the
size of the shared pool is sized either less or more, then the entire database
performance will suffer.
Purpose of Shared Pool
Many users run SQL or PL/SQL statements and it goes through
3 phases.
a. Parse – Translate and optimize the query
b. Execute – Lay down the execution plan and run
the query
c. Fetch – Pull back data from oracle objects based
on the execution plan.
When SQL queries are executed, the shared pool caches the executable
versions of SQL and PL/SQL statements.
So, when users/applications execute same SQL or PL/SQL code, it does
multiple executions without doing hard parse which significantly results in reduction
in CPU, memory and latches.
What does Shared
Pool Contains
Shared pool contains following substructures
1. Fixed area/Permanent area
·
This is allocated during instance startup
·
Contains structures such as
i.
Processes
- using PROCESSES paramenter
ii.
Sessions - using SESSIONS parameter
iii.
Segmented Arrays - used to store of
objects. It may grow dynamically.
2. Variable area
·
It is handled by oracle’s internal algorithm
·
Contains
i.
Library cache
ii.
Data dictionary cache ,etc
Shared pool acts like a repository for storing the SQL and
Pl/sql code which was executed successfully such that if we receive similar
statements, it can reduce the speed of the parsing. Much of the shared pool usage is to support
the execution of the shared SQL and Pl/SQL packages, but in order to build the
cursor or compile PL/Sql package, we need to about all the database objects
(like tables, procedures, indexes, etc) referenced by the pl/sql package and
their optimizer statistics. All these information are stored in the shared pool
independent of the cursors/program unit.
Metadata are stored independently and hence it is easy to
build the cursors. Few executions using
shared server, parallel query, RMAN used large memory allocations in the shared
pool.
Fixed area is permanent during instance startup after setup
by DBA and not much to deal with these areas.
Now, we will the dynamic memory allocation areas
Data Dictionary Cache
The data dictionary cache contains information like table
definitions, referential integrities, index informations, column definitions,
users, passwords, privileges, etc. This
is like the buffer cache except it stores dictionary information instead of
user information. Since the dictionary informations are buffered, when parsing
SQL cursors or during the compilation of PL/SQL program, it will be quick as
these are in RAM. It is also known as
ROW CACHE.
V$ROWCACHE will give details on the hit ratio for Data
dictionary. The tuning of data dictionary cache is done by changing
shared_pool_size parameter.
select sum(gets) "Gets", sum(getmisses) "Get Misses", (1-(sum(getmisses)/sum(gets))) * 100
"Hit Ratio" from
v$rowcache;
The value of the Hit Ratio should be over 90%
Library Cache
The primary responsibility of Library Cache is to
collect, parse and execute the SQL statements that are going against the
database. It contains the following.
a.
SQL/cursors
– Executable representation of SQL statements that may be used repeatedly by
many sessions.
b.
PL/SQL
– Executable representation of PL/SQL packages, procedures, functions thaty may
be used repeatedly by many sessions.
c.
Objects
of various types required to parse and execute a SQL statement which
includes tables, indexes, types, methods, etc.
Library cache will maintain the relation between tables
and SQL statements (specifically child cursors) and if any change is done to
tables, then oracle will know which cursors needs to invalidate and which one
to keep in the cache.
We need to understand that Oracle will not keep all
objects like synonyms, tables, indexes, etc in the cache. It keeps only the
objects which are recently referenced.
Statistics about the library cache activity is available
in V$LIBRARYCACHE.
If you want to know the objects in cache, we have to use
V$DB_OBJECT_CACHE.
Result Cache
This cache holds the result sets and the query fragments.
When a user queries again, the results will fetched from this cache and the
response will be quick.
Internal Working
of Shared Pool
As we have seen from above topics, we now know that
shared pool is a collection of objects like tables, cursors, views, procedures,
functions or packages (Pl/SQL package).
Size of single object will be small but when you have a
PL/SQL package, the size would grow in MB as it includes many objects and its
attributes. Example: A package body will have many procedures which in turn
will occupy more space when we run a simple SQL statement.
Each shared pool object is not a single allocation unit
(AU) and it is partitioned into independent memory allocations called “HEAPS”. Number of Heaps for the object varies depends
upon the type of objects. Eg: for a SQL cursor, there will be 2 heaps a)
Smaller Heap for library cache metadata and b) Larger Heap for executable
representation of the cursor.
Each HEAP is comprised of 1 or more chunks of memory of
Standard Allocation Units (AU) and these reduce the problem of memory
fragmentation.
When memory is allocated for objects, the memory is not
allocated in contiguous fashion. i.e., the chunks will not be contiguous as you
can see from the figure.
But the memory (free lists – Pointers to memory chunks) must
be contiguous.
Eg. Consider, we need 5KB size of Heap memory and each chunk
is 4K and we need 1K to complete memory allocation, so the remaining memory is allocated
to the heap from another memory chunk and hence the memory chunks are not
contiguous.
When the chunks are not contiguous and if they work this
way, it will avoid fragmentation.
Generally, the chunk sizes are of 1k and 4K creating more
uniform memory allocations. So, when same objects are allocated and aged out,
same size of chunks are allocated and aged out. By doing so, we can avoid
memory fragmentation and memory usage effectively.
LARGE POOL
RMAN, parallel query and shared servers uses allocation of
more than 5KB and they need large allocation of memory. Hence the use other
memory pool called LARGE POOL.
RESERVED POOL
Sometimes SQL, PL/SQL packages, if they are over 5KB, they
might require larger contiguous chunks of memory. Default settings of reserved pool is 4,400
bytes
If there is not enough free space in the shared pool, then
oracle must search for free enough memory to satisfy the request. Oracle might even have to age out the old
objects to satisfy it. In such cases,
oracle will hold the latch resource for a period until it tries to find the
memory. Till then, it will cause a minor disruption to the other requests
(concurrent request) at memory allocation.
So, what oracle does is, it internally configures a small
space called RESERVERED POOL so that when
we have operations involving PL/SQL and trigger compilations or to load
any java objects, this will be used.
When the operation is over, the memory freed will be returned back to
reserved pool.
When ORA-4031
error occurs
ORA-4031 occurs in any of the memory pools in the SGA when
oracle cannot find a memory chunk large enough to satisfy the internal
allocation request on behalf of users operation.
1.
A user executes a query.
2.
An object needs to be allocated in shared pool.
3.
The memory allocated for chunks to be contiguous
and this will be allocated to the objects.
4.
If it is small memory size, then the Heap
manager scans through freelists and if it is small, it will allocated.
5.
If the requested amount of contiguous memory is
not available for chunks, then Heap Manager iterates through the shared pool’s
LRU list and it attempts to create a contiguous chunk of requested size (this
is done by aging out the LRU objects).
6.
But, ORA-4031 error occurs even if the
large/free space created by the heap manager is NOT CONTIGUOUS
In case if ASMM/AMM is enabled, an additional granule of
memory is requested if sufficient contiguous memory can’t be found.
References :
http://narashimreddy.wordpress.com/2009/10/30/internals-of-shared-pool/
http://orainternals.wordpress.com/2009/08/06/ora-4031-and-shared-pool-duration/
http://orainternals.wordpress.com/2009/08/06/ora-4031-and-shared-pool-duration/
No comments:
Post a Comment