| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Ora-04031
This is from Oracle as well, a technical Document on Shared Pool
tuning on this error.
Regards,
Phil Lima
SCT Global Government Solutions
Subject:
TUNING THE SHARED POOL AND
RESOLVING ORA-4031
Type:
BULLETIN
Status:
PUBLISHED
Content Type:
TEXT/PLAIN
Creation Date:
12-SEP-1997
Last Revision Date:
23-MAY-2000
Language:
USAENG
PURPOSE
This document discusses
some of the common issues
associated with the shared
pool in Oracle7 and
describes how to diagnose and
respond to these issues.
RELATED DOCUMENTS
[NOTE:1012049.6] TUNING
LIBRARY CACHE LATCH
CONTENTION
With Release 7.2 and 7.3,
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,
resulting in better
shared-pool utilization and
reduction in 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-04031
out of shared memory
error.
A) DIAGNOSIS OF FRAGMENTATION
i) ORA-04031 ERROR
One way to diagnose that this
is happening is to look for
ORA-04031 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-04031 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 ORA-04031 error should be
virtually eliminated. If an
ORA-04031 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
PL/SQL 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.
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.
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-04031 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
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 you have 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 PL/SQ: objects that are
only rarely used
and therefore the PL/SQL
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;
In Oracle7.3 onwards the best
candidates for keeping can be
seen by querying
the 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 so then one can
identify the object name and
owner of this chunk using the
following SQL:
select distinct
decode(kglobtyp,0,'CURSOR',7,
'PROCEDURE',8,'FUNCTION',9,'P
ACKAGE',
11,'PACKAGE
BODY',12,'TRIGGER',13,'TYPE',
14,'TYPE BODY','OTHER')
||' - '||kglnaown||'.'
||kglnaobj "Eligible PL/SQL
objects"
from x$kglob
where kglobhd4 in
(select ksmchpar from
x$ksmsp
where ksmchcom='PL/SQL
MPCODE' and ksmchsiz>5120)
If you are 'keeping' PL/SQL
objects today and migrate to
7.3 or higher there is
no need to re-assess the list
of objects that you are
keeping.
ii) USE BIND VARIABLES
One of the best things 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 PL/SQL
Large anonymous PL/SQL blocks
should be turned into small
anonymous PL/SQL
blocks that call packaged
functions. The packages
should be 'kept' in memory.
For version earlier that 7.3,
this includes anonymous
PL/SQL 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 PL/SQL blocks that are
only rarely used and
therefore the PL/SQL 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) 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.
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.
*****************************
*****************************
***************
3) 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.
*****************************
*****************************
***************
4) FINAL COMMENTS
The most important point that
needs to be understood by
everyone using Oracle7
and PL/SQL (prior to release
7.3) is that all large PL/SQL
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_allo
c.
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_allo
c.
units : bytes
default: 0 (no reserved
list)
minimum: >
shared_pool_reserved_min_allo
c
maximum: 1/2
shared_pool_size
1.2
shared_pool_reserved_min_allo
c
-----------------------------
------
The init.ora parameter
shared_pool_reserved_min_allo
c controls allocation for
the reserved memory. Only
allocations larger than
shared_pool_reserved_min_allo
c 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_allo
c 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-04031 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-04031 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-04031
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-04031 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
(example: number of times
ORA-04031 occurred)
LAST_FAILURE_SIZE is the
request size of the last
failed request
(example:
the request size of last
ORA-04031).
ABORTED_REQUEST_THRESHOLD
is the minimum size of a
request which will signal
an ORA-04031 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-04031
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_allo
c
-----------------------------
------------------------
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_allo
c
AVG_FREE_SIZE >
shared_pool_reserved_min_allo
c
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_allo
c
MAX_FREE_SIZE <
shared_pool_reserved_min_allo
c
FREE_MEMORY <
shared_pool_reserved_min_allo
c
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_allo
c (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_allo
c (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_allo
c
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_allo
c (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
...
-
Received on Tue Jul 25 2000 - 14:21:56 CDT
 |
 |