Subject: Library Cache and Shared Pool Tuning
Author: jloiza
Last Revision Date: 05 February 1997
This document discusses some of the common problems that occur with the
library cache/shared pool in version 7 and describes how to diagnose and
correct the problems.
With Release 7.2, library cache latch contention has been reduced by the
breaking up of the latch into multiple symmetric latches. With both
Release 7.2 and 7.3, significant changes have been made to reduce usage of
shared memory as well as per-user (UGA) memory. Also, memory is not being
allocated in large contiguous chunks anymore - resulting in much better
shared-pool utilization and reducing fragmentation.
1) MEMORY FRAGMENTATION
The primary problem that occurs is that free memory in the shared pool
becomes fragmented into small pieces over time. Any attempt to allocate
a large piece of memory in the shared pool will cause large amount of
objects in the library cache to be flushed out and may result in an
ORA-4031 out of shared memory error.
A) DIAGNOSIS OF FRAGMENTATION
i) ORA-4031 ERROR
One way to diagnose that this is happening is to look for ORA-4031 errors
being returned from applications. When an attempt is made to allocate a
large contiguous piece of shared memory, and not enough contiguous memory can
be created in the shared pool, the database will signal this error.
Before this error is signalled, all objects in the shared pool that are not
currently in use will be flushed from the shared pool, and their memory will
be freed and merged. This error only occurs when there is still not
a large enough contiguous piece of free memory after this happens. There may
be very large amounts of total free memory in the shared pool, but just not
enough contiguous memory.
ii) INIT.ORA PARAMETER
An init.ora parameter can be set so that whenever an ORA-4031 error is
signalled a dump will occur into a trace file. By looking for these trace
files, the DBA can determine that these errors are occurring. This is useful
when applications do not always report errors signalled by oracle, or if
users do not report the errors to the DBAs. The parameter is the following:
event = "4031 trace name errorstack"
If you are using 7.0.16 or higher you can use the following:
event = "4031 trace name errorstack level 4"
This will cause a dump of the oracle state objects to occur when this
error is signalled. By looking in the dump for 'load=X' and then looking
up a few lines for 'name=' you can often tell whether an object was being
loaded into the shared pool when this error occurred. If an object was
being loaded then it is likely that this load is the cause of the problem
and the object should be 'kept' in the shared pool. The object being loaded
is the object printed after the 'name='. Do not use the 'level 4' option
in versions before 7.0.16 because a bug existed that often caused the
system to crash with this option enabled due to a latch level violation.
Prior to version 7.3, there were a handful of cases where the RDBMS or PL/SQL
would attempt to allocate large pieces of contiguous memory. Most of this has
been fixed for 7.3. This problem was especially acute when running MTS, when
the UGA would be located in the SGA. This should also be fixed in 7.3 and
using MTS for a high OLTP scenario is recommended. As a result of all these
changes, the 4031 error should be virtually eliminated. If a 4031 error is
signalled, quite likely the shared pool is over 90% utilized and the
alternative is to increase the shared pool. The only known situation is PL/SQL
packages (like STANDARD) where the package contains a very large number (over
400) procedure/function definitions. This still needs to be in contiguous
memory and may request memory chunks as large as 15K. Packages like this
should be the only ones that should be kept.
iii) X$KSMLRU
There is a fixed table called x$ksmlru that tracks allocations in the
shared pool that cause other objects in the shared pool to be aged out.
This fixed table can be used to identify what is causing the large
allocation. The columns of this fixed table are the following:
KSMLRCOM - allocation comment that describes the type of allocation.
If this comment is something like 'MPCODE' or 'PLSQL%' then there is a
large pl/sql object being loaded into the shared pool. This plsql object
will need to be 'kept' in the shared pool.
If this comment is 'kgltbtab' then the allocation is for a dependency table
in the library cache. This is only a problem when several hundred users
are logged on using distinct user ids. The solution in this case is to
use fully qualified names for all table references. This problem will not
occur in 7.1.3 or later.
If you are running MTS and the comment is something like 'Fixed UGA' then
the problem is that the init.ora parameter 'open_cursors' is set too high.
This problem should not occur in 7.3 or later.
KSMLRSIZ - amount of contiguous memory being allocated. Values over around
5K start to be a problem, values over 10K are a serious problem, and values
over 20K are very serious problems. Anything less then 5K should not be
a problem.
KSMLRNUM - number of objects that were flushed from the shared pool in order
allocate the memory.
In release 7.1.3 or later, the following columns also exist:
KSMLRHON - the name of the object being loaded into the shared pool if the
object is a pl/sql object or a cursor.
KSMLROHV - hash value of object being loaded
KSMLRSES - SADDR of the session that loaded the object.
The advantage of X$KSMLRU is that it allows you to identify problems with
fragmentation that are effecting performance, but that are not bad enough
to be causing ORA-4031 errors to be signalled. If a lot of objects are
being periodically flushed from the shared pool then this will cause
response time problems and will likely cause library cache latch contention
problems when the objects are reloaded into the shared pool. With version
7.2, the library cache latch contention should be significantly reduced
with the breaking up of the library cache pin latch into a configurable set
of symmetric library cache latches.
One unusual thing about the x$ksmlru fixed table is that the contents of
the fixed table are erased whenever someone selects from the fixed table.
This is done since the fixed table stores only the largest allocations that
have occurred. The values are reset after being selected so that subsequent
large allocations can be noted even if they were not quite as large as others
that occurred previously. Because of this resetting, the output of selecting
from this table should be carefully noted since it cannot be reselected if
it is forgotten. Also you should take care that there are not multiple
people on one database that select from this table because only one of them
will select the real data.
To monitor this fixed table just run the following:
select * from x$ksmlru where ksmlrsiz > 5000;
iv) MTS
Oracle users using SQL*Net V2 can connect to the database using dedicated
servers, or multiple clients can use a pool of shared (or MTS) servers. The
biggest memory implication of this mode is that the session memory (also known
as the UGA) for every session needs to be accessible to every MTS server. This
implies that the logical UGA comes out of the physical SGA (or the shared
pool) instead of the PGA (process memory).
In versions prior to 7.3, there were a few components in the UGA that would
request large contiguous chunks of memory, contributing to fragmentation of
the shared pool if using MTS. If the system had been up for a while, users
would have failures when attempting to connect or executing sql. Starting with
7.3, all these allocations have been segmented such that the average size of
memory chunks allocated to the UGA should be about 5K.
B) CORRECTION OF FRAGMENTATION
i) KEEPING OBJECTS
The primary source of problems is large pl/sql objects. The means of
correcting these errors is to 'keep' large pl/sql object in the shared pool at
startup time. This will load the objects into the shared pool and will make
sure that the objects are never aged out of the shared pool. If the objects
are never aged out then there will not be a problem with trying to load them
and not having enough memory.
Objects are 'kept' in the shared pool using the dbms_shared_pool package
that is defined in the dbmspool.sql file. For example:
execute dbms_shared_pool.keep('SYS.STANDARD');
All large packages that are shipped should be 'kept' if the customer uses
pl/sql. This includes 'STANDARD', 'DBMS_STANDARD', and 'DIUTIL'. With 7.3, the
only package left in this list is 'STANDARD'.
All large customer packages should also be marked 'kept'.
To mark all packages in the system 'kept' execute the following:
declare
own varchar2(100);
nam varchar2(100);
cursor pkgs is
select owner, object_name
from dba_objects
where object_type = 'PACKAGE';
begin
open pkgs;
loop
fetch pkgs into own, nam;
exit when pkgs%notfound;
dbms_shared_pool.keep(own || '.' || nam, 'P');
end loop;
end;
The dbms_shared_pool package was introduced in 7.0 and has been evolved over
the versions. Until 7.1.5, 'keep' could only be used for packages. Starting
with 7.1.6, this was extended to standalone procedures, cursors as well as
triggers. For detailed usage instructions, see the dbmspool.sql file. So,
prior to this version, if the customer has large procedures or large anonymous
blocks, then these will need to be put into packages and marked kept. With
7.3, most packages do not need to be kept any longer since PL/SQL no longer
requires large amounts of contiguous memory to load packages/procedures in
memory.
You can determine what large stored objects are in the shared pool by
selecting from the v$db_object_cache fixed view. This will also tell you
which objects have been marked kept. This can be done with the following
query:
select * from v$db_object_cache where sharable_mem > 10000;
Note that this query will not catch plsql objects that are only rarely
used and therefore the plsql object is not currently loaded in the
shared pool.
To determine what large pl/sql objects are currently loaded in the shared
pool and are not marked 'kept' and therefore may cause a problem, execute the
following:
select name, sharable_mem
from v$db_object_cache
where sharable_mem > 10000
and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
or type = 'PROCEDURE')
and kept = 'NO';
Another approach to the above is to use the dbms_shared_pool.sizes procedure.
To use this in SQLDBA:
set serveroutput on;
execute dbms_shared_pool.sizes(10);
This should show you the names of all the objects in the shared pool that take
more that 10K of memory as well as if they are marked kept or not. For SQL
statements, if there are multiple versions of a query (usually a bug if the
count is more than 3), they will also be indicated in parenthesis. Use the
following query to check for problems:
select sql_text, loaded_versions, version_count, sharable_mem
from v$sqlarea where loaded_versions > 3
order by sharable_mem;
If you are 'keeping' certain PL/SQL objects today and migrate to 7.3, run the
script again for every object in the list and check the contents of fixed
table x$ksmsp to see if there are any chunks in the shared-pool that have the
KSMCHSIZ larger than 5K and KSMCHCOM like '%PL/SQL%'. If you do, then keep
the package/procedure/trigger in the list of objects to be kept, else you can
eliminate it - it does not need to be 'kept' anymore.
ii) USE BIND VARIABLES
Another thing that can be done to reduce the amount of fragmentation is to
reduce or eliminate the number of sql statements in the shared pool that
are duplicates of each other except for a constant that is embedded in the
statement. The statements should be replaced with one statement that uses
a bind variable instead of a constant.
For example:
select * from emp where empno=1;
select * from emp where empno=2;
select * from emp where empno=3;
Should all be replaced with:
select * from emp where empno=:1;
You can identify statements that potentially fall into this class with a
query like the following:
select substr(sql_text, 1, 30) sql, count(*) copies
from v$sqlarea
group by substr(sql_text, 1, 30)
having count(*) > 3;
iii) MAX BIND SIZE
It is possible for a sql statement to not be shared because the max bind
variable lengths of the bind variables in the statement do not match. This
is automatically taken care of for precompiler programs and forms programs,
but could be a problem for programs that directly use OCI. The bind call
in OCI takes two arguments, one is the max length of the value, and the
other is a pointer to the actual length. If the current length is always
passed in as the max length instead of the max possible length for the
variable, then this could cause the sql statement not to be shared.
To identify statements that might potentially have this problem execute
the following statement:
select sql_text, version_count from v$sqlarea where version_count > 5;
Starting with 7.1.6 this should no longer be an issue as the server can
graduate bind buffers even when the user's max bind lengths are jumping up or
down and continue to share cursors that are built for larger buffer lengths
and flush the smaller sql compilation from the shared pool.
iv) ELIMINATING LARGE ANONYMOUS PLSQL
Large anonymous plsql blocks should be turned into small anonymous plsql
blocks that call packaged functions. The packages should be 'kept' in
memory. For version earlier that 7.3, this includes anonymous plsql blocks
that are used for trigger definitions. With 7.3, triggers are compiled and
stored to disk like standalone procedures and should be treated as such. Large
anonymous blocks can be identified with the following query:
select sql_text from v$sqlarea
where command_type=47 -- command type for anonymous block
and length(sql_text) > 500;
Note that this query will not catch plsql blocks that are only rarely used and
therefore the plsql block is not currently loaded in the shared pool.
Another option that can be used when an anonymous block cannot be turned into
a package is to mark the anonymous block with some string so that it can be
identified in v$sqlarea and marked 'kept'.
For example, instead of using
declare x number; begin x := 5; end;;
you can use:
declare /* KEEP_ME */ x number; begin x := 5; end;
You can then use the following procedure to select these statements out of
the shared pool and mark them 'kept' using the dbms_shared_pool.keep
package.
declare
/* DONT_KEEP_ME */
addr varchar2(10);
hash number;
cursor anon is
select address, hash_value
from v$sqlarea
where command_type = 47 -- command type for anonymous block
and sql_text like '% KEEP_ME %'
and sql_text not like '%DONT_KEEP_ME%';
begin
open anon;
loop
fetch anon into addr, hash;
exit when anon%notfound;
dbms_shared_pool.keep(addr || ',' || to_char(hash), 'C');
end loop;
end;
v) REDUCING USAGE
Another way to reducing fragmentation is to reduce consumption. This is of
special importance when using MTS, when every user's session memory is in the
shared pool and the impact is multiplied by the total concurrent users.
Insert, update, delete and anonymous blocks complete the execution in one
round trip. All the memory that is allocated on the server for the execute
comes from the PGA and is freed before the call returns to the user. But in
the case of selects, memory required to execute the statement - which could be
large if a sort was involved - is not freed until the end-of-fetch is reached
or the query is cancelled. In these situations using the OCI features to do an
exact fetch and cancel helps free memory back to the pool.
If the application logic has been embedded into server side PL/SQL, a large
number of cursors may be getting cached on the server for every user. Though
this results in reduced latch contention and faster response, it does use more
memory in the UGA. Setting the close_cached_open_cursors init.ora to TRUE
closes the PL/SQL cached cursors on the server, freeing the memory.
*************************************************************************
2) LIBRARY CACHE LATCH CONTENTION
For versions prior to 7.2, another big problem that can occur in oracle7 on
multiprocessors that have a large number of CPUs is contention for the library
cache latches. With 7.2 and 7.3, this should no longer be an issue.
A) DIAGNOSIS
i) V$LATCH
Selecting from v$latch will show you which latches have the worst hit rates
and more importantly which latches are causing a lot of sleeps. If one of the
library cache latches is causing the most number of sleeps then you may have
a problem. One thing to watch out for here is that this information is
accumulated since the database starts, and so it may not show problems that
are intermittent in nature.
ii) V$SESSION_WAIT
By selecting from v$session_wait during a slowdown period you can usually
determine very accurately whether you have a problem with latching and which
latch is causing the problem. If you see a large number (more then 3 or 4)
of processes waiting for the library cache or library cache pin latch, then
there may be a problem. Run the following query to determine this:
select count(*) number_of_waiters
from v$session_wait w, v$latch l
where w.wait_time = 0
and w.event = 'latch free'
and w.p2 = l.latch#
and l.name like 'library%';
It is also very useful to just select from v$session_wait to determine what
else is causing a slowdown:
select * from v$session_wait
where event != 'client message'
and event not like '%NET%'
and wait_time = 0
and sid > 5;
B) CORRECTION
i) FRAGMENTATION
The primary cause of library cache latch contention is fragmentation of
the shared pool. This can be diagnosed and addressed as described in the
fragmentation section of this document. If you are running on a system
with just one or a very small number of CPUs and you have a problem with
library cache latch contention, then fragmentation is almost certainly
the source of the problem.
ii) INCREASE SHARING
By increasing the amount of sharing that occurs on the system you can
decrease the amount of missing and loading that occurs in the library cache
and therefore the load on the library cache latch. This is done by
identifying statements that are not being shared as described in the
fragmentation section above.
To determine the percentage of sql statement parse calls that find a
cursor to share you can execute the following:
select gethitratio from v$librarycache where namespace = 'SQL AREA';
This value should be in the high nineties.
iii) REDUCE PARSING
Another way to decrease the load on the library cache latch is to reduce
the number of parse calls that are coming into the system. Even if the
statement being parsed is found in the shared pool and shared, the load
of a parse call is high because the user must be authenticated to run the
sql statement, and all name translations must be done for the sql
statement. Reducing the amount of parsing is often as simple as setting
'HOLD_CURSOR=TRUE' for the precompilers. To identify the sql statements
that are receiving a lot of parse calls execute the following:
select sql_text, parse_calls, executions from v$sqlarea
where parse_calls > 100 and executions < 2*parse_calls;
To identify the total amount of parsing going on in the system execute
the following:
select name, value from v$sysstat where name = 'parse count';
If this value increases at a rate greater than about 10 per second
then this may be a problem.
iv) CURSOR_SPACE_FOR_TIME
Setting the init.ora parameter cursor_space_for_time to TRUE can reduce
the load on the library cache latch somewhat. However, setting this
parameter may add a lot of memory utilization, so before setting it to
true make sure that there is a lot of free memory on the system and that
the number of hard page faults per minute is very low or zero.
v) SESSION_CACHED_CURSORS
In version 7.1 there is an init.ora parameter called session_cached_cursors
that can be set that will help in situations where a user repeatedly parses
the same statements. This can occur in many applications including
FORMS based application if users often switch between forms. Every time
a user switches to a new form all the sql statements opened for the old form
will be closed. The session_cached_cursors parameter will cause closed
cursors to be cached within the session so that a subsequent call to parse
the statement will bypass the parse phase. This is similar to HOLD_CURSORS
in the precompilers. One thing to be careful about is that if this parameter
is set to a high value, the amount of fragmentation in the shared pool may
be increased. Another thing to note is that if the value of this parameter is
less than the cursors that are closed before the first one is reopened, you
will never see the benefit of the cache since the first cursor would have been
aged out already.
vi) CLOSE_CACHED_OPEN_CURSORS
Every cursor that is held open incurs a small memory cost on the server. If
PL/SQL is being used, there are SQL cursors that are probably being held open
on the server. This parameter is set to FALSE by default. If the library cache
latch is a bottleneck, ensure that the parameter is set to its default and not
TRUE. However, this will use more (UGA) memory per user.
vii) USING FULLY QUALIFIED TABLE NAMES
It can help to reduce the load on the library cache latch somewhat to use
fully qualified names for tables in sql statements. That is, instead of
saying 'select * from emp', say 'select * from scott.emp'. This is especially
helpful for sql statements that are parsed very frequently. If all users
log onto the database using the same userid then this may be of little or
no use.
viii) FORMS 4
SQL*forms version 4 generates less dynamic sql by making better use of
bind variables. Therefore it less loading to occur in the shared pool.
You might consider switching to this new version sooner than you
otherwise would have because of this.
*************************************************************************
3) COMMON FALLACIES
There are a number of common fallacies about the shared pool that are often
stated as fact.
A) FREE MEMORY
One fallacy is that the amount of 'free memory' reported in v$sgastat needs
to be kept high. This is incorrect. The free memory reported in this table
is not like the free memory reported by operating system statistics. Since
the shared pool acts as a cache, nothing will ever be aged out of the shared
pool until all the free memory has been used up. This is entirely normal.
Free memory is more properly thought of as 'wasted memory'. You would rather
see this value be low than very high. In fact, a high value of free
memory is sometimes a symptom that a lot of objects have been aged out of
the shared pool and therefore the system is experiencing fragmentation
problems.
B) FLUSH SHARED POOL
Some people think that frequently executing 'alter system flush shared_pool'
improves the performance of the system and decreases the amount of
fragmentation. This is incorrect. Executing this statement causes a big
spike in performance and does nothing to improve fragmentation. In fact
it can make things worse because this statement will cause even objects
that are marked as 'kept' to be flushed from the shared pool.
The only time when it might be useful to run this statement is between
shifts of users so that the objects that are relevant to the last shift
of users can be flushed out before the next shift of users starts to use
the system. This is almost never needed though.
*************************************************************************
4) SIZING OF SHARED POOL
One very difficult judgement that needs to be make in oracle7 is to determine
the proper size of the shared pool. The following provides some guidelines
for this. It should be emphasized that these are just guidelines, there are
no hard and fast rules here and experimentation will be needed to determine
a good value.
The shared pool size is highly application dependent. To
determine the shared pool size that will be needed for a production
system it is generally necessary to first develop the application and run
it on a test system and take some measurements. The test system should be
run with a very large value for the shared pool size to make the
measurements meaningful.
A) OBJECTS STORED IN THE DATABASE
The amount of shared pool that needs to be allocated for objects that are
stored in the database like packages and views is easy to measure. You can
just measure their size directly with the following statement:
select sum(sharable_mem) from v$db_object_cache;
This is especially effective because all large pl/sql object should be 'kept'
in the shared pool at all times.
B) SQL
The amount of memory needed to store sql statements in the shared pool is
more difficult to measure because of the needs of dynamic sql. If an
application has no dynamic sql then the amount of memory can simply
be measured after the application has run for a while by just selecting
it out of the shared pool as follows:
select sum(sharable_mem) from v$sqlarea;
If the application has a moderate or large amount of dynamic sql like
most applications do, then a certain amount of memory will be needed for
the shared sql plus more for the dynamic sql and more so that the dynamic
sql does not age the shared sql out of the shared pool.
The amount of memory for the shared sql can be approximated by the following:
select sum(sharable_mem) from v$sqlarea where executions > 5;
The remaining memory in v$sqlarea is for dynamic sql. Some shared pool will
need to be budgeted for this also, but there are few rules here.
C) PER-USER PER-CURSOR MEMORY
You will need to allow around 250 bytes of memory in the shared pool per
concurrent user for each open cursor that the user has whether the cursor
is shared or not. During the peak usage time of the production system, you
can measure this as follows:
select sum(250 * users_opening) from v$sqlarea;
In a test system you can measure it by selecting the number of open cursors
for a test user and multiplying by the total number of users:
select 250 * value bytes_per_user
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'opened cursors current'
and s.sid = 23; -- replace 23 with session id of user being measured
The per-user per-cursor memory is one of the classes of memory that shows
up as 'library cache' in v$sgastat.
D) MTS
If you are using multi-threaded server, then you will need to allow enough
memory for all the shared server users to put their session memory in the
shared pool. This can be measured for one user with the following query:
select value sess_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session uga memory'
and s.sid = 23; -- replace 23 with session id of user being measured
a more conservative value to use is the maximum session memory that was
ever allocated by the user:
select value sess_max_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session uga memory max'
and s.sid = 23; -- replace 23 with session id of user being measured
To select this value for all the currently logged on users the following query
can be used:
select sum(value) all_sess_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session uga memory max';
E) OVERHEAD
You will need to add a minimum of 30% overhead to the values calculated
above to allow for unexpected and unmeasured usage of the shared
pool.
*************************************************************************
5) FINAL COMMENTS
The most important point that needs to be understood by everyone using
oracle7 and plsql (prior to release 7.3) is that all large plsql objects
must be made into packages and those packages must be kept in the shared
pool. This point cannot be over emphasized. Many customers, especially
those running a lot of users, have had terrible performance problems that
were completely cleared up by doing this.
APPENDIX I: Reserved Shared Pool
=================================
1. RESERVED SPACE FROM THE SHARED POOL
======================================
On busy systems, the RDBMS may have difficulty finding a contiguous
piece of memory to satisfy a large request for memory. Because
the RDBMS will search for and free currently unused memory, the search
for this large piece of memory may disrupt the behavior of the share
pool, leading to more fragmentation and poor performance.
RDBMS 7.1.5 allows DBAs to reserve memory within the shared pool to
satisfy these large allocations during RDBMS operations such as pl/sql
compilation and trigger compilation. Smaller objects will not
fragment the reserved list, helping to ensure the reserved list will
have large contiguous chunks of memory. Once the memory allocated
from the reserved list is freed, it returns to the reserved list.
The size of the reserved list, as well as the minimum size of the
objects that can be allocated from the reserved list are controlled
via init.ora parameters: shared_pool_reserved_size and
shared_pool_reserved_min_alloc.
1.1 shared_pool_reserved_size
------------------------------
The init.ora parameter shared_pool_reserved_size controls the amount of
shared_pool_size reserved for large allocations. In order to
create a reserved list, shared_pool_reserved_size must be greater than
shared_pool_reserved_min_alloc.
units : bytes
default: 0 (no reserved list)
minimum: > shared_pool_reserved_min_alloc
maximum: 1/2 shared_pool_size
1.2 shared_pool_reserved_min_alloc
-----------------------------------
The init.ora parameter shared_pool_reserved_min_alloc controls
allocation for the reserved memory. Only allocations larger than
shared_pool_reserved_min_alloc are allowed to allocate space from the
reserved list if a chunk of memory of sufficient size is not found on
the shared pool's free lists.
units : bytes
default: 5000
minimum: 5000
maximum: < shared_pool_reserved_size
The default value for shared_pool_reserved_min_alloc should be
adequate for almost all systems.
2. CONTROLLING SPACE RECLAMATION OF THE SHARED POOL
====================================================
RDBMS 7.1.5 also provides a new procedure, aborted_request_threshold,
in package dbms_shared_pool, which allows users to set the limit
on the size of allocations allowed to flush the shared pool if the
free lists cannot satisfy the request size.
Before the RDBMS signals the ORA-4031 error, it incrementally flushes
unused objects from the shared pool until there is sufficient memory
to satisfy the allocation request. In most cases, incrementally
flushing objects from the shared pool frees enough memory for the
allocation to complete succesfully. If the RDBMS signals an ORA-4031
error, it has flushed all objects currently not in use on the system
without finding a large enough piece of contiguous memory.
On a busy system, the larger the space allocation, the more likely
the RDBMS will signal the ORA-4031 error. Flushing all objects,
however, will impact other users on the system, possibly causing
a degradation in performance.
The aborted_request_threshold procedure allows the DBA to localize the
impact the ORA-4031 error to the process that couldn't allocate memory.
The procedure takes a numeric value between 5000 and 2147483647,
representing the size, in bytes, of the threshold.
3. NEW FIXED VIEW V$SHARED_POOL_RESERVED
=========================================
RDBMS 7.1.5 has a new fixed view to help tune the reserved pool and
space within the shared pool. The name of the new fixed view is
V$SHARED_POOL_RESERVED and has the following columns:
Name Null? Type
------------------------------- -------- --------------
FREE_SPACE NUMBER
AVG_FREE_SIZE NUMBER
FREE_COUNT NUMBER
MAX_FREE_SIZE NUMBER
USED_SPACE NUMBER
AVG_USED_SIZE NUMBER
USED_COUNT NUMBER
MAX_USED_SIZE NUMBER
REQUESTS NUMBER
REQUEST_MISSES NUMBER
LAST_MISS_SIZE NUMBER
MAX_MISS_SIZE NUMBER
REQUEST_FAILURES NUMBER
LAST_FAILURE_SIZE NUMBER
ABORTED_REQUEST_THRESHOLD NUMBER
ABORTED_REQUESTS NUMBER
LAST_ABORTED_SIZE NUMBER
These columns of V$SHARED_POOL_RESERVED are only valid if the parameter
shared_pool_reserved_size is set to a valid value.
FREE_SPACE is the total amount of free space on the reserved list.
AVG_FREE_SIZE is the average size of the free memory on the reserved
list.
FREE_COUNT is the number of free pieces of memory on the reserved
list.
MAX_FREE_SIZE is the size of the largest free piece of memory on the
reserved list.
USED_SPACE is the total amount of used memory on the reserved list.
AVG_USED_SIZE is the average size of the of the used memory on the
reserved list.
USED_COUNT is the number of used pieces of memory on the reserved
list.
MAX_USED_SIZE is the size of the largest used piece of memory on the
reserved list.
REQUESTS is the number of times that the reserved list was searched
for a free piece of memory.
REQUEST_MISSES is the number of times the reserved list didn't have
a free piece of memory to satisfy the request, and
proceeded to start flushing objects from the LRU list.
LAST_MISS_SIZE is the request size of the last REQUEST_MISS.
MAX_MISS_SIZE is the request size of the largest REQUEST_MISS.
The next set of columns contain values which are valid even if
shared_pool_reserved_size is not set.
REQUEST_FAILURES is the number of times that no memory was found to
satisfy a request (e.g., number of times ORA-4031
occurred)
LAST_FAILURE_SIZE is the request size of the last failed request
(e.g., the request size of last ORA-4031).
ABORTED_REQUEST_THRESHOLD is the minimum size of a request which
will signal an ORA-4031 error without
flushing objects. See the procedure
aborted_request_threshold described above.
LAST_ABORTED_SIZE is the last size of the request which returned an
ORA-4031 error without flushing objects from the
LRU list.
4. TUNING HINTS BASED ON V$SHARED_POOL_RESERVED
================================================
Information in V$SHARED_POOL_RESERVED can help to set values for
shared_pool_reserved_size and even shared_pool_size. This section
assumes the DBA has performed all other shared pool
tuning on his system.
4.1 Initial Value for shared_pool_reserved_size
------------------------------------------------
The DBA should make shared_pool_reserved_size 10% of the
shared_pool_size. For most systems, this value should be sufficient,
if the DBA has already spent time tuning the shared pool.
4.2 Initial Value for shared_pool_reserved_min_alloc
-----------------------------------------------------
In most cases, the default value for this parameter is adequate. If
the DBA increases this value, then the RDBMS will allow fewer
allocations from the reserved list and will request more memory from
the shared pool list.
4.4 Tuning shared_pool_reserved_size
-------------------------------------
Ideally, shared_pool_reserved_size should be made large enough to
satisfy any request scanning for memory on the reserved list without
flushing objects from the shared pool. The amount of operating system
memory, however, may constrain the size of the SGA, and therefore the
size of the shared pool such that this is not a feasible goal.
If the DBA has a system with ample free memory to increase his SGA,
the goal is to have:
REQUEST_MISS = 0
If the DBA is constrained for OS memory, his goal is:
REQUEST_FAILURES = 0 or not increasing
LAST_FAILURE_SIZE > shared_pool_reserved_min_alloc
AVG_FREE_SIZE > shared_pool_reserved_min_alloc
If neither of these goals are met, increase shared_pool_reserved_size;
the DBA also needs to increase shared_pool_size by the same amount,
since the reserved list is taken from the shared pool.
4.5 shared_pool_reserved_size too low
--------------------------------------
The reserved pool is too small when:
REQUEST_FAILURES > 0 (and increasing)
and at least one of the following is true:
LAST_FAILURE_SIZE > shared_pool_reserved_min_alloc
MAX_FREE_SIZE < shared_pool_reserved_min_alloc
FREE_MEMORY < shared_pool_reserved_min_alloc
The DBA has two options, depending on his SGA size constraints:
o Increase shared_pool_reserved_size and shared_pool_size, accordingly
o Increase shared_pool_reserved_min_alloc (but may need to increase
shared_pool_size)
The first option will increase the amount of memory available on the
reserved list without impacting users not allocating memory from the
reserved list. The second options reduces the number of allocations
allowed to use memory from the reserved list; doing so, however, will
increase normal shared pool perhaps impacting other users on the system.
4.6 shared_pool_reserved_size too high
---------------------------------------
It is possible that too much memory has been allocated to the
reserved list. If:
REQUEST_MISS = 0 or not increasing
FREE_MEMORY = > 50% of shared_pool_reserved_size minimum
The DBA has two options:
o Decrease shared_pool_reserved_size
o Decrease shared_pool_reserved_min_alloc (if not the default
value)
4.7 shared_pool_size too small
-------------------------------
The new fixed table can also indicate when shared_pool_size is too
small. If:
REQUEST_FAILURES > 0 and increasing
LAST_FAILURE_SIZE < shared_pool_reserved_min_alloc
Then the DBA has two options if he has enabled the reserved list:
o Decrease shared_pool_reserved_size
o Decrease shared_pool_reserved_min_alloc (if set larger than the default)
Otherwise, the DBA the could:
o Increase shared_pool_size
APPENDIX 2: Procedure free_unused_memory
=========================================
This text is also in the specification for this procedure in dbmsutil.sql.
It is part of package dbms_session.
Procedure free_unused_memory --
Procedure for users to reclaim unused memory after performing operations
requiring large amounts of memory (where large is >100K). Note that
this procedure should only be used in cases where memory is at a
premium.
Examples operations using lots of memory are:
o large sorts where entire sort_area_size is used and
sort_area_size is hundreds of KB
o compiling large PL/SQL packages, procedures, or functions
o storing hundreds of KB of data within PL/SQL indexed tables
One can monitor user memory by tracking the statistics
"session uga memory" and "session pga memory" in the
v$sesstat/v$statname fixed views. Monitoring these statistics will
also show how much memory this procedure has freed.
The behavior of this procedure depends upon the configuration of the
server operating on behalf of the client:
o dedicated server - returns unused PGA memory to the OS
o MTS server - returns unused session memory to the shared_pool
In order to free memory using this procedure, the memory must
not be in use.
Once an operation allocates memory, only the same type of operation can
reuse the allocated memory. For example, once memory is allocated
for sort, even if the sort is complete and the memory is no longer
in use, only another sort can reuse the sort-allocated memory. For
both sort and compilation, after the operation is complete, the memory
is no longer in use and the user can invoke this procedure to free the
unused memory.
An indexed table implicitly allocates memory to store values assigned
to the indexed table's elements. Thus, the more elements in an indexed
table, the more memory the RDBMS allocates to the indexed table. As
long as there are elements within the indexed table, the memory
associated with an indexed table is in use.
The scope of indexed tables determines how long their memory is in use.
Indexed tables declared globally are indexed tables declared in packages
or package bodies. They allocate memory from session memory. For an
indexed table declared globally, the memory will remain in use
for the lifetime of a user's login (lifetime of a user's session),
and is freed after the user disconnects from ORACLE.
Indexed tables declared locally are indexed tables declared within
functions, procedures, or anonymous blocks. These indexed tables
allocate memory from PGA memory. For an indexed table declared
locally, the memory will remain in use for as long as the user is still
executing the procedure, function, or anonymous block in which the
indexed table is declared. After the procedure, function, or anonymous
block is finished executing, the memory is then available for other
locally declared indexed tables to use (i.e., the memory is no longer
in use).
Assigning an uninitialized, "empty," indexed table to an existing index
table is a method to explicitly re-initialize the indexed table and the
memory associated with the indexed table. After this operation,
the memory associated with the indexed table will no longer be in use,
making it available to be freed by calling this procedure. This method
is particularly useful on indexed tables declared globally which can grow
during the lifetime of a user's session, as long as the user no
longer needs the contents of the indexed table.
The memory rules associated with an indexed table's scope still apply;
this method and this procedure, however, allow users to
intervene and to explictly free the memory associated with an
indexed table.
The PL/SQL fragment below illustrates the method and the use
of procedure free_unused_user_memory.
create package foobar
type number_idx_tbl is table of number indexed by binary_integer;
store1_table number_idx_tbl; -- PL/SQL indexed table
store2_table number_idx_tbl; -- PL/SQL indexed table
store3_table number_idx_tbl; -- PL/SQL indexed table
...
end; -- end of foobar
declare
...
empty_table number_idx_tbl; -- uninitialized ("empty") version
begin
for i in 1..1000000 loop
store1_table(i) := i; -- load data
end loop;
...
store1_table := empty_table; -- "truncate" the indexed table
...
-
dbms_session.free_unused_user_memory; -- give memory back to system
store1_table(1) := 100; -- index tables still declared;
store2_table(2) := 200; -- but truncated.
...
end;
Performance Implication:
This routine should be used infrequently and judiciously.