| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Who and What is using the shared pool.
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C00DDA.31B1A532
Content-Type: text/plain;
charset="iso-8859-1"
Hi Phil,
8.1.5 on HP/UX 11.0. Check it out below. I figured I would have to look at executions to determine if something that is constantly being reloaded really needs to be pinned. Seems to me if it's being executed a lot, and reloaded a lot, it should be pinned - and if it's being reloaded a lot but not executed more than once per load, it's not worth the effort.
Thanks in advance for any comments.
Lisa
SQL> select startup_time from v$instance;
STARTUP_T
2 , type 3 , to_char(sharable_mem/1024,'9,999.9') "SPACE(K)" 4 , loads 5 , executions execs 6 , kept
OBJECT TYPE SPACE(K) LOADSEXECS KEP
---------------------------------------- --------------- -------- --------- --------- --- QODE_LOAD.F_VALIDATE_ISBN FUNCTION 1.9 31 0 NO QODE_BC.PKG_GN_SPECIAL_OFFERS PACKAGE BODY 12.3 29 0 NO QODE_APP.CLEANUP_PARTNERNAME_STG2 PROCEDURE 2.8 25 0 NO QODE_APP.PKG_GENERATE_KEYWORD PACKAGE BODY 3.1 21 0 NO QODE_APP.PKG_UTIL PACKAGE 28.0 17 0 NO QODE_APP.PKG_CLEANUP PACKAGE BODY 9.3 17 0 NO QODE_BC.PKG_GN_SPECIAL_OFFERS PACKAGE 11.3 16 0 NO QODE_BC.CATHIERARCHY PACKAGE 1.7 15 0 NO QODE_BC.RPT_PARTNER_EMAIL PACKAGE 1.7 15 0 NO QODE_BC.PARTNER1 PACKAGE 1.7 15 0 NO QODE_APP.P_GUESTHOME PROCEDURE 73.7 15 0 NO QODE_USER.WEBDBTEST012345678912 PROCEDURE 1.7 15 0 NO QODE_BC.PARTRELATIONSHIP PACKAGE 1.2 15 0 NO QODE_BC.PARTNER_RELATIONSHIP1 PACKAGE 1.7 15 0 NO QODE_BC.CATHIERARCHY2 PACKAGE 1.7 15 0 NO QODE_APP.P_BUYQODER PROCEDURE 38.8 15 0 NO QODE_LOAD.PKG_LOAD PACKAGE BODY 1.4 15 0 NO QODE_APP.P_LOGIN PROCEDURE 75.2 15 0 NO QODE_BC.PARTNER9 PACKAGE 1.6 15 0 NO QODE_APP.PKG_SPECIALOFFERS_OBJ PACKAGE 35.6 14 0 NO QODE_APP.P_SIGN_UP_ERROR_VALIDATE PROCEDURE 17.6 13 0 NO QODE_BC.PARTNER9 PACKAGE BODY 1.4 13 0 NO QODE_LOAD.PKG_PRODUCT_CATEGORY PACKAGE BODY 2.6 13 0 NO QODE_APP.PKG_LIST_MAINT PACKAGE 21.8 13 0 NO QODE_BC.CATEGORY PACKAGE BODY 1.7 130 NO
-----Original Message-----
From: Philip West [mailto:P.West_at_g-icap.com]
Sent: Thursday, August 24, 2000 7:27 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Who and What is using the shared pool.
If your database has been up and running for a while I am VERY suprised that executions are 0 across the board. This is the way that I would always check for pinning candidates. Maybe someone else on the list could shed light. WHat versions are you running.
Phil West - Impex IT ltd
Unix Sys Admin and Oracle Financials DBA Services
all opinions are worth [at most] what you paid for them
-----Original Message-----
From: Koivu, Lisa [mailto:lkoivu_at_qode.com]
Sent: 23 August 2000 21:58
To: Multiple recipients of list ORACLE-L
Subject: RE: Who and What is using the shared pool.
Thanks Philip for sending this to the list.
However now I see that executions in v$db_object_cache is 0 everywhere. Is there an easy way to join to v$sqlarea to v$db_object_cache? I guess I was hoping to see if any heavily-used objects were being reloaded frequently and are candidates for pinning.
Anyone?? Thanks
Lisa
Ft. Lauderdale, FL, USA
*O*fficially *C*rabby and *P*eeved
-----Original Message-----
From: Philip West [ mailto:P.West_at_g-icap.com <mailto:P.West_at_g-icap.com> ]
Sent: Monday, August 21, 2000 1:33 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Who and What is using the shared pool.
Always keep to the shallow end of the shared pool. There are things swimming around in the depths that you do not want to meet!
Or, the problem is most likely shared pool fragmentation rather than the sizing per se. Fragmentation is a 'feature'. This is a big subject, which I would recommend you read up on (good papers on metalink on this subject. You do not tell us your db version. Ways to improve the situation include reserving a portion of the pool for larger objects. pinning critical or frequently reloaded objects into the pool. and, yes, increasing the size of the pool if necessary.
Look at v$sgastat to see what is where in your sga.
Try these (from the $AD_TOP/sql directory if you run Applications)
select owner || '.' || name OBJECT
, type
, to_char(sharable_mem/1024,'9,999.9') "SPACE(K)"
, loads
, executions execs
, kept
from v$db_object_cache
where type in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE')
and owner not in ('SYS')
order by owner, name
/
select to_char(sum(sharable_mem)/1024,'9,999,999.9') "TOTAL SPACE (K)"
from v$db_object_cache
where type in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE')
and owner not in ('SYS')
/
also look here:
Bookmark Fixed font Go to End
Doc ID: Note:61623.1
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.
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';
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,'PACKAGE',
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
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.
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.
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
=================================
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
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
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
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
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
4.1 Initial Value for shared_pool_reserved_size
4.2 Initial Value for shared_pool_reserved_min_alloc
4.4 Tuning shared_pool_reserved_size
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
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
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
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
store1_table(1) := 100; -- index tables still declared; store2_table(2) := 200; -- but truncated....
Copyright (c) 1995,1999 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.
Phil West - Impex IT ltd
Unix Sys Admin and Oracle Financials DBA Services
all opinions are worth [at most] what you paid for them
When addressed to our clients any opinions or advice contained in this Internet e-mail are subject to the terms and conditions expressed in any applicable documentation or market practices governing the relationship between Garban Intercapital plc and its clients.
Any views expressed in this message are those of the individual sender except where they are stated to be the views of Garban Intercapital plc.
--
Author: Philip West
INET: P.West_at_g-icap.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
------_=_NextPart_001_01C00DDA.31B1A532
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<TITLE>RE: Who and What is using the shared pool.</TITLE>
<META content=3D"MSHTML 5.00.3103.1000" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D473444114-24082000>Hi=20
Phil, </SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D473444114-24082000></SPAN></FONT> </DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D473444114-24082000>8.1.5=20
on HP/UX 11.0. Check it out below. I figured I would have =
to look at=20
executions to determine if something that is constantly being reloaded =
really=20
needs to be pinned. Seems to me if it's being executed a lot, and =
reloaded=20
a lot, it should be pinned - and if it's being reloaded a lot but not =
executed=20
more than once per load, it's not worth the effort. =
</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D473444114-24082000></SPAN></FONT> </DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D473444114-24082000>Thanks=20
in advance for any comments. </SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D473444114-24082000>Lisa</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D473444114-24082000></SPAN></FONT> </DIV>
<DIV><FONT color=3D#0000ff face=3D"Courier New" size=3D2><SPAN=20
class=3D473444114-24082000>SQL> select startup_time from=20
v$instance;</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=3D#0000ff face=3D"Courier New" size=3D2><SPAN=20
class=3D473444114-24082000>STARTUP_T<BR>---------<BR>02-AUG-00</SPAN></F=
ONT></DIV>
<DIV><FONT color=3D#0000ff><SPAN=20
class=3D473444114-24082000></SPAN></FONT> </DIV>
<DIV><FONT color=3D#0000ff face=3D"Courier New" size=3D2><SPAN=20
class=3D473444114-24082000> 1 select owner || '.' || name=20
OBJECT<BR> =
2 ,=20
type<BR> 3 =
,=20
to_char(sharable_mem/1024,'9,999.9') "SPACE(K)"<BR> =20
4 , =
loads<BR> =20
5 , executions=20
execs<BR> 6 =
,=20
kept<BR> 7 from v$db_object_cache<BR> 8 where =
type in=20
('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE')<BR> =
9 =20
and owner not in ('SYS')<BR> 10* order by loads desc<BR>SQL>=20
/</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=3D#0000ff face=3D"Courier New" size=3D2><SPAN=20
class=3D473444114-24082000>OBJECT &nb=
sp; &nb=
sp; &nb=
sp; =20
TYPE =20
SPACE(K) LOADS EXECS=20
KEP<BR>---------------------------------------- --------------- =
--------=20
--------- ---------=20
---<BR>QODE_LOAD.F_VALIDATE_ISBN &nbs=
p; =20
FUNCTION &nbs=
p; =20
1.9 =20
31 0=20
NO<BR>QODE_BC.PKG_GN_SPECIAL_OFFERS &=
nbsp; =20
PACKAGE BODY =20
12.3 =20
29 0=20
NO<BR>QODE_APP.CLEANUP_PARTNERNAME_STG2 &nb=
sp; =20
PROCEDURE &nb=
sp;=20
2.8 =20
25 0=20
NO<BR>QODE_APP.PKG_GENERATE_KEYWORD &=
nbsp; =20
PACKAGE BODY =20
3.1 =20
21 0=20
NO<BR>QODE_APP.PKG_UTIL &=
nbsp; &=
nbsp; =20
PACKAGE  =
; =20
28.0 =20
17 0=20
NO<BR>QODE_APP.PKG_CLEANUP &nbs=
p; &nbs=
p;=20
PACKAGE BODY =20
9.3 =20
17 0=20
NO<BR>QODE_BC.PKG_GN_SPECIAL_OFFERS &=
nbsp; =20
PACKAGE  =
; =20
11.3 =20
16 0=20
NO<BR>QODE_BC.CATHIERARCHY &nbs=
p; &nbs=
p;=20
PACKAGE  =
; =20
1.7 =20
15 0=20
NO<BR>QODE_BC.RPT_PARTNER_EMAIL  =
; =20
PACKAGE  =
; =20
1.7 =20
15 0=20
NO<BR>QODE_BC.PARTNER1 &n=
bsp; &n=
bsp; =20
PACKAGE  =
; =20
1.7 =20
15 0=20
NO<BR>QODE_APP.P_GUESTHOME &nbs=
p; &nbs=
p;=20
PROCEDURE =20
73.7 =20
15 0=20
NO<BR>QODE_USER.WEBDBTEST012345678912  =
; =20
PROCEDURE &nb=
sp;=20
1.7 =20
15 0=20
NO<BR>QODE_BC.PARTRELATIONSHIP =
=20
PACKAGE  =
; =20
1.2 =20
15 0=20
NO<BR>QODE_BC.PARTNER_RELATIONSHIP1 &=
nbsp; =20
PACKAGE  =
; =20
1.7 =20
15 0=20
NO<BR>QODE_BC.CATHIERARCHY2 &nb=
sp; =20
PACKAGE  =
; =20
1.7 =20
15 0=20
NO<BR>QODE_APP.P_BUYQODER  =
;  =
; =20
PROCEDURE =20
38.8 =20
15 0=20
NO<BR>QODE_LOAD.PKG_LOAD =
=
=20
PACKAGE BODY =20
1.4 =20
15 0=20
NO<BR>QODE_APP.P_LOGIN &n=
bsp; &n=
bsp; =20
PROCEDURE =20
75.2 =20
15 0=20
NO<BR>QODE_BC.PARTNER9 &n=
bsp; &n=
bsp; =20
PACKAGE  =
; =20
1.6 =20
15 0=20
NO<BR>QODE_APP.PKG_SPECIALOFFERS_OBJ =
=20
PACKAGE  =
; =20
35.6 =20
14 0=20
NO<BR>QODE_APP.P_SIGN_UP_ERROR_VALIDATE &nb=
sp; =20
PROCEDURE =20
17.6 =20
13 0=20
NO<BR>QODE_BC.PARTNER9 &n=
bsp; &n=
bsp; =20
PACKAGE BODY =20
1.4 =20
13 0=20
NO<BR>QODE_LOAD.PKG_PRODUCT_CATEGORY =
=20
PACKAGE BODY =20
2.6 =20
13 0=20
NO<BR>QODE_APP.PKG_LIST_MAINT &=
nbsp; =20
PACKAGE  =
; =20
21.8 =20
13 0=20
NO<BR>QODE_BC.CATEGORY &n=
bsp; &n=
bsp; =20
PACKAGE BODY =20
1.7 =20
13 0 =
NO</SPAN></FONT></DIV>
<BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">
<DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B> Philip West=20
[mailto:P.West_at_g-icap.com]<BR><B>Sent:</B> Thursday, August 24, 2000 =
7:27=20
AM<BR><B>To:</B> Multiple recipients of list =
ORACLE-L<BR><B>Subject:</B> RE:=20
Who and What is using the shared pool.<BR><BR></DIV></FONT>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D781273408-24082000>If=20
your database has been up and running for a while I am VERY suprised =
that=20
executions are 0 across the board. This is the way that I would =
always=20
check for pinning candidates. Maybe someone else on the list =
could shed=20
light. WHat versions are you running.</SPAN></FONT></DIV>
<P><FONT face=3DArial>Phil West - Impex IT ltd</FONT> <BR><FONT=20
face=3D"Arial Narrow" size=3D1>Unix Sys Admin and Oracle Financials =
DBA
Services</FONT> </P>
<P><FONT face=3D"Arial Narrow" size=3D1>all opinions are worth [at =
most] what you=20
paid for them</FONT> </P>
<BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">
<DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B> Koivu, Lisa=20
[mailto:lkoivu_at_qode.com]<BR><B>Sent:</B> 23 August 2000 =
21:58<BR><B>To:</B>=20
Multiple recipients of list ORACLE-L<BR><B>Subject:</B> RE: Who and =
What is=20
using the shared pool.<BR><BR></DIV></FONT>
<P><FONT size=3D2>Thanks Philip for sending this to the list. =
</FONT></P>
<P><FONT size=3D2>However now I see that executions in =
v$db_object_cache is 0=20
everywhere. Is there an easy way to join to v$sqlarea to=20
v$db_object_cache? I guess I was hoping to see if any =
heavily-used=20
objects were being reloaded frequently and are candidates for=20
pinning.</FONT></P>
<P><FONT size=3D2>Anyone?? Thanks</FONT> <BR><FONT =
size=3D2>Lisa</FONT>=20
<BR><FONT size=3D2>Ft. Lauderdale, FL, USA</FONT> <BR><FONT=20
size=3D2>*O*fficially *C*rabby and *P*eeved</FONT> </P>
<P><FONT size=3D2>-----Original Message-----</FONT> <BR><FONT =
size=3D2>From:=20
Philip West [<A=20
=
href=3D"mailto:P.West_at_g-icap.com">mailto:P.West_at_g-icap.com</A>]</FONT>=20
<BR><FONT size=3D2>Sent: Monday, August 21, 2000 1:33 PM</FONT> =
<BR><FONT=20
size=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT=20
size=3D2>Subject: RE: Who and What is using the shared pool.</FONT> =
</P><BR>
<P><FONT size=3D2>Always keep to the shallow end of the shared =
pool. =20
There are things</FONT> <BR><FONT size=3D2>swimming around in the =
depths that=20
you do not want to meet!</FONT> </P>
<P><FONT size=3D2>Or, the problem is most likely shared pool=20
fragmentation rather than the</FONT> <BR><FONT size=3D2>sizing per =
se. =20
Fragmentation is a 'feature'. This is a big subject, =
which</FONT>=20
<BR><FONT size=3D2>I would recommend you read up on (good papers on =
metalink=20
on this subject.</FONT> <BR><FONT size=3D2>You do not tell us your =
db=20
version. Ways to improve the situation include</FONT> =
<BR><FONT=20
size=3D2>reserving a portion of the pool for larger objects. =
pinning=20
critical or</FONT> <BR><FONT size=3D2>frequently reloaded objects =
into the=20
pool. and, yes, increasing the size of</FONT> <BR><FONT =
size=3D2>the pool if=20
necessary.</FONT> </P>
<P><FONT size=3D2>Look at v$sgastat to see what is where in your =
sga.</FONT>=20
</P>
<P><FONT size=3D2>Try these (from the $AD_TOP/sql directory if you =
run=20
Applications)</FONT> </P>
<P><FONT size=3D2>select owner || '.' || name OBJECT</FONT> =
<BR><FONT=20
size=3D2> , type</FONT> =
<BR><FONT=20
size=3D2> ,=20
to_char(sharable_mem/1024,'9,999.9') "SPACE(K)"</FONT> <BR><FONT=20
size=3D2> , loads</FONT> =
<BR><FONT=20
size=3D2> , executions =
execs</FONT>=20
<BR><FONT size=3D2> , =
kept</FONT>=20
<BR><FONT size=3D2>from v$db_object_cache</FONT> <BR><FONT =
size=3D2>where type=20
in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE')</FONT> =
<BR><FONT=20
size=3D2> and owner not in ('SYS')</FONT> <BR><FONT =
size=3D2>order by=20
owner, name</FONT> <BR><FONT size=3D2>/</FONT> <BR><FONT =
size=3D2>select=20
to_char(sum(sharable_mem)/1024,'9,999,999.9') "TOTAL SPACE =
(K)"</FONT>=20
<BR><FONT size=3D2>from v$db_object_cache</FONT> <BR><FONT =
size=3D2>where type=20
in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE')</FONT> =
<BR><FONT=20
size=3D2> and owner not in ('SYS')</FONT> <BR><FONT =
size=3D2>/</FONT>=20
</P><BR>
<P><FONT size=3D2>also look here:</FONT> <BR><FONT size=3D2> =20
</FONT><BR><FONT size=3D2> </FONT><BR><FONT =
size=3D2> =20
Bookmark Fixed font Go to End </FONT></P>
<P><FONT size=3D2>Doc ID: Note:61623.1 </FONT><BR><FONT=20
size=3D2>Type: BULLETIN </FONT><BR><FONT =
size=3D2>Status: PUBLISHED=20
</FONT><BR><FONT size=3D2> Content Type: TEXT/PLAIN=20
</FONT><BR><FONT size=3D2>Creation Date: 12-SEP-1997 =
</FONT><BR><FONT=20
size=3D2>Last Revision Date: 23-MAY-2000 </FONT><BR><FONT=20
size=3D2>Language: USAENG </FONT><BR><FONT =
size=3D2> </FONT> </P>
<P><FONT size=3D2>PURPOSE</FONT> <BR><FONT size=3D2> This =
document=20
discusses some of the common issues associated with the</FONT> =
<BR><FONT=20
size=3D2>shared </FONT><BR><FONT size=3D2> pool in Oracle7 =
and describes=20
how to diagnose and respond to these issues.</FONT> </P>
<P><FONT size=3D2>RELATED DOCUMENTS</FONT> <BR><FONT =
size=3D2> =20
[NOTE:1012049.6] TUNING LIBRARY CACHE LATCH =
CONTENTION</FONT>=20
</P><BR>
<P><FONT size=3D2>With Release 7.2 and 7.3, changes have been made =
to reduce=20
usage of shared </FONT><BR><FONT size=3D2>memory as well as =
per-user (UGA)=20
memory. Also, memory is not being</FONT> <BR><FONT =
size=3D2>allocated=20
</FONT><BR><FONT size=3D2>in large contiguous chunks, resulting in =
better=20
shared-pool utilization and </FONT><BR><FONT size=3D2>reduction in=20
fragmentation.</FONT> </P>
<P><FONT size=3D2>1) MEMORY FRAGMENTATION</FONT> </P>
<P><FONT size=3D2>The primary problem that occurs is that free =
memory in the=20
shared pool</FONT> <BR><FONT size=3D2>becomes </FONT><BR><FONT=20
size=3D2>fragmented into small pieces over time. Any attempt =
to allocate=20
a large</FONT> <BR><FONT size=3D2>piece </FONT><BR><FONT =
size=3D2>of memory in=20
the shared pool will cause large amount of objects in the</FONT> =
<BR><FONT=20
size=3D2>library</FONT> <BR><FONT size=3D2>cache to be flushed out =
and may=20
result in an ORA-04031 out of shared memory </FONT><BR><FONT=20
size=3D2>error.</FONT> </P>
<P><FONT size=3D2>A) DIAGNOSIS OF FRAGMENTATION</FONT> </P>
<P><FONT size=3D2>i) ORA-04031 ERROR</FONT> </P>
<P><FONT size=3D2>One way to diagnose that this is happening is to =
look for=20
ORA-04031 errors</FONT> <BR><FONT size=3D2>being returned from=20
applications. When an attempt is made to allocate a</FONT> =
<BR><FONT=20
size=3D2>large </FONT><BR><FONT size=3D2>contiguous piece of shared =
memory, and=20
not enough contiguous memory can be </FONT><BR><FONT =
size=3D2>created in the=20
shared pool, the database will signal this error.</FONT> </P>
<P><FONT size=3D2>Before this error is signalled, all objects in =
the shared=20
pool that are not</FONT> <BR><FONT size=3D2>currently in use will =
be flushed=20
from the shared pool, and their memory will</FONT> <BR><FONT=20
size=3D2>be</FONT> <BR><FONT size=3D2>freed and merged. This =
error only=20
occurs when there is still not a large </FONT><BR><FONT =
size=3D2>enough=20
contiguous piece of free memory after this happens. There may =
be</FONT> <BR><FONT size=3D2>very </FONT><BR><FONT size=3D2>large =
amounts of=20
total free memory in the shared pool, but just not enough =
</FONT><BR><FONT=20
size=3D2>contiguous memory.</FONT> </P>
<P><FONT size=3D2>ii) INIT.ORA PARAMETER</FONT> </P>
<P><FONT size=3D2>An init.ora parameter can be set so that whenever =
an=20
ORA-04031 error is</FONT> <BR><FONT size=3D2>signalled a dump will =
occur into=20
a trace file. By looking for these trace </FONT><BR><FONT=20
size=3D2>files, the DBA can determine that these errors are =
occurring. =20
This is</FONT> <BR><FONT size=3D2>useful</FONT> <BR><FONT =
size=3D2>when=20
applications do not always report errors signalled by oracle, or =
if</FONT>=20
<BR><FONT size=3D2>users</FONT> <BR><FONT size=3D2>do not report =
the errors to=20
the DBAs. The parameter is the following:</FONT> </P>
<P><FONT size=3D2> event =3D "4031 trace name=20
errorstack"</FONT> </P>
<P><FONT size=3D2>If you are using 7.0.16 or higher you can use the =
following:</FONT> </P>
<P><FONT size=3D2> event =3D "4031 trace name =
errorstack level=20
4"</FONT> </P>
<P><FONT size=3D2>This will cause a dump of the Oracle state =
objects to occur=20
when this error</FONT> <BR><FONT size=3D2>is </FONT><BR><FONT=20
size=3D2>signalled. By looking in the dump for 'load=3DX' and =
then looking=20
up a few</FONT> <BR><FONT size=3D2>lines</FONT> <BR><FONT =
size=3D2>for 'name=3D'=20
you can often tell whether an object was being loaded into the=20
</FONT><BR><FONT size=3D2>shared pool when this error =
occurred. If an=20
object was being loaded then it</FONT> <BR><FONT size=3D2>is</FONT> =
<BR><FONT=20
size=3D2>likely that this load is the cause of the problem and the =
object=20
should be </FONT><BR><FONT size=3D2>'kept' in the shared =
pool. The=20
object being loaded is the object printed</FONT> <BR><FONT=20
size=3D2>after</FONT> <BR><FONT size=3D2>the 'name=3D'. Do not use =
the 'level 4'=20
option in versions before 7.0.16</FONT> <BR><FONT =
size=3D2>because</FONT>=20
<BR><FONT size=3D2>a bug existed that often caused the system to =
crash with=20
this option enabled</FONT> </P>
<P><FONT size=3D2>due to a latch level violation.</FONT> </P>
<P><FONT size=3D2>Prior to version 7.3, there were a handful of =
cases where=20
the RDBMS or</FONT> <BR><FONT size=3D2>PL/SQL</FONT> <BR><FONT =
size=3D2>would=20
attempt to allocate large pieces of contiguous memory. Most of =
this</FONT>=20
<BR><FONT size=3D2>has</FONT> <BR><FONT size=3D2>been fixed for =
7.3. This=20
problem was especially acute when running MTS, when</FONT> </P>
<P><FONT size=3D2>the UGA would be located in the SGA. This should =
also be=20
fixed in 7.3 and</FONT> <BR><FONT size=3D2>using</FONT> <BR><FONT =
size=3D2>MTS=20
for a high OLTP scenario is recommended. As a result of all =
these</FONT>=20
<BR><FONT size=3D2>changes, </FONT><BR><FONT size=3D2>the ORA-04031 =
error should=20
be virtually eliminated. If an ORA-04031 error is</FONT> <BR><FONT=20
size=3D2>signalled, quite likely the shared pool is over 90% =
utilized and the=20
</FONT><BR><FONT size=3D2>alternative is to increase the shared =
pool. The only=20
known situation is</FONT> <BR><FONT size=3D2>PL/SQL</FONT> =
<BR><FONT=20
size=3D2>packages (like STANDARD) where the package contains a very =
large=20
number</FONT> <BR><FONT size=3D2>(over</FONT> <BR><FONT =
size=3D2>400)=20
procedure/function definitions. This still needs to be in =
contiguous</FONT>=20
<BR><FONT size=3D2>memory and may request memory chunks as large as =
15K.=20
Packages like this</FONT> <BR><FONT size=3D2>should =
</FONT><BR><FONT size=3D2>be=20
the only ones that should be kept.</FONT> </P>
<P><FONT size=3D2>iii) X$KSMLRU</FONT> </P>
<P><FONT size=3D2>There is a fixed table called =
x$ksmlru=20
that tracks allocations in the </FONT><BR><FONT =
size=3D2>shared=20
pool that cause other objects in the shared pool to be =
aged=20
out.</FONT> <BR><FONT size=3D2>This</FONT> <BR><FONT size=3D2>fixed =
table can=20
be used to identify what is causing the large =
allocation.</FONT>=20
</P>
<P><FONT size=3D2>The columns of this fixed table are the =
following:</FONT>=20
</P>
<P><FONT size=3D2>KSMLRCOM - allocation comment that describes the =
type of=20
allocation.</FONT> </P>
<P><FONT size=3D2>If this comment is something like 'MPCODE' or =
'PLSQL%' then=20
there is a large</FONT> </P>
<P><FONT size=3D2>PL/SQL object being loaded into the shared =
pool. This=20
PL/SQL object will</FONT> <BR><FONT size=3D2>need </FONT><BR><FONT =
size=3D2>to=20
be 'kept' in the shared pool.</FONT> </P>
<P><FONT size=3D2>If this comment is 'kgltbtab' then the allocation =
is for a=20
dependency table</FONT> <BR><FONT size=3D2>in </FONT><BR><FONT =
size=3D2>the=20
library cache. This is only a problem when several hundred =
users=20
are</FONT> <BR><FONT size=3D2>logged</FONT> <BR><FONT size=3D2>on =
using distinct=20
user ids. The solution in this case is to use fully</FONT> =
<BR><FONT=20
size=3D2>qualified</FONT> <BR><FONT size=3D2>names for all table=20
references.</FONT> </P>
<P><FONT size=3D2>If you are running MTS and the comment is =
something like=20
'Fixed UGA' then</FONT> <BR><FONT size=3D2>the </FONT><BR><FONT =
size=3D2>problem=20
is that the init.ora parameter 'open_cursors' is set too =
high.</FONT> </P>
<P><FONT size=3D2>KSMLRSIZ - amount of contiguous memory being=20
allocated. Values over around</FONT> <BR><FONT size=3D2>5K=20
</FONT><BR><FONT size=3D2>start to be a problem, values over 10K =
are a serious=20
problem, and values</FONT> <BR><FONT size=3D2>over </FONT><BR><FONT =
size=3D2>20K=20
are very serious problems. Anything less then 5K should not =
be=20
a</FONT> <BR><FONT size=3D2>problem.</FONT> </P>
<P><FONT size=3D2>KSMLRNUM - number of objects that were flushed =
from the=20
shared pool in order</FONT> <BR><FONT size=3D2>allocate the =
memory.</FONT>=20
</P>
<P><FONT size=3D2>In release 7.1.3 or later, the following columns =
also=20
exist:</FONT> </P>
<P><FONT size=3D2>KSMLRHON - the name of the object being loaded =
into the=20
shared pool if the</FONT> <BR><FONT size=3D2>object is a PL/SQL =
object or a=20
cursor.</FONT> </P>
<P><FONT size=3D2>KSMLROHV - hash value of object being =
loaded</FONT> </P>
<P><FONT size=3D2>KSMLRSES - SADDR of the session that loaded the=20
object.</FONT> </P>
<P><FONT size=3D2>The advantage of X$KSMLRU is that it allows you =
to identify=20
problems with</FONT> <BR><FONT size=3D2>fragmentation that are =
effecting=20
performance, but that are not bad enough to</FONT> <BR><FONT=20
size=3D2>be</FONT> <BR><FONT size=3D2>causing ORA-04031 errors to =
be signalled.=20
If a lot of objects are being </FONT><BR><FONT =
size=3D2>periodically flushed=20
from the shared pool then this will cause response time</FONT> </P>
<P><FONT size=3D2>problems and will likely cause library cache =
latch=20
contention problems when</FONT> <BR><FONT size=3D2>the</FONT> =
<BR><FONT=20
size=3D2>objects are reloaded into the shared pool. With version =
7.2, the=20
library</FONT> <BR><FONT size=3D2>cache </FONT><BR><FONT =
size=3D2>latch=20
contention should be significantly reduced with the breaking up of=20
the</FONT> </P>
<P><FONT size=3D2>library cache pin latch into a configurable set =
of symmetric=20
library cache </FONT><BR><FONT size=3D2>latches.</FONT> </P>
<P><FONT size=3D2>One unusual thing about the x$ksmlru fixed table =
is that the=20
contents of the</FONT> </P>
<P><FONT size=3D2>fixed table are erased whenever someone selects =
from the=20
fixed table. This</FONT> <BR><FONT size=3D2>is </FONT><BR><FONT =
size=3D2>done=20
since the fixed table stores only the largest allocations that have =
</FONT><BR><FONT size=3D2>occurred. The values are reset =
after being=20
selected so that subsequent</FONT> <BR><FONT size=3D2>large =
</FONT><BR><FONT=20
size=3D2>allocations can be noted even if they were not quite as =
large as=20
others that</FONT> </P>
<P><FONT size=3D2>occurred previously. Because of this =
resetting, the=20
output of selecting</FONT> <BR><FONT size=3D2>from </FONT><BR><FONT =
size=3D2>this table should be carefully noted since it cannot be =
reselected if=20
it is </FONT><BR><FONT size=3D2>forgotten. Also you should =
take care=20
that there are not multiple people on</FONT> <BR><FONT =
size=3D2>one</FONT>=20
<BR><FONT size=3D2>database that select from this table because =
only one of=20
them will select</FONT> <BR><FONT size=3D2>the </FONT><BR><FONT =
size=3D2>real=20
data.</FONT> </P>
<P><FONT size=3D2>To monitor this fixed table just run the =
following:</FONT>=20
</P>
<P><FONT size=3D2> select * from x$ksmlru where =
ksmlrsiz=20
> 5000;</FONT> </P>
<P><FONT size=3D2>iv) MTS</FONT> </P>
<P><FONT size=3D2>Oracle users using SQL*Net V2 can connect to the =
database=20
using dedicated</FONT> <BR><FONT size=3D2>servers, or multiple =
clients can use=20
a pool of shared (or MTS) servers. The</FONT> <BR><FONT =
size=3D2>biggest=20
memory implication of this mode is that the session memory =
(also</FONT>=20
<BR><FONT size=3D2>known</FONT> <BR><FONT size=3D2>as the UGA) for =
every session=20
needs to be accessible to every MTS server.</FONT> <BR><FONT=20
size=3D2>This</FONT> <BR><FONT size=3D2>implies that the logical =
UGA comes out=20
of the physical SGA (or the shared</FONT> <BR><FONT size=3D2>pool) =
instead of=20
the PGA (process memory).</FONT> </P>
<P><FONT size=3D2>In versions prior to 7.3, there were a few =
components in the=20
UGA that would</FONT> <BR><FONT size=3D2>request large contiguous =
chunks of=20
memory, contributing to fragmentation of</FONT> <BR><FONT =
size=3D2>the shared=20
pool if using MTS. If the system had been up for a while, =
users</FONT>=20
<BR><FONT size=3D2>would have failures when attempting to connect =
or executing=20
sql. Starting</FONT> <BR><FONT size=3D2>with</FONT> <BR><FONT =
size=3D2>7.3, all=20
these allocations have been segmented such that the average size =
of</FONT>=20
<BR><FONT size=3D2>memory chunks allocated to the UGA should be =
about=20
5K.</FONT> </P>
<P><FONT size=3D2>B) CORRECTION OF FRAGMENTATION</FONT> </P>
<P><FONT size=3D2>i) KEEPING OBJECTS</FONT> </P>
<P><FONT size=3D2>The primary source of problems is large PL/SQL =
objects. The=20
means of</FONT> <BR><FONT size=3D2>correcting</FONT> <BR><FONT =
size=3D2>these=20
errors is to 'keep' large PL/SQL object in the shared pool at =
startup=20
</FONT><BR><FONT size=3D2>time. This will load the objects into the =
shared=20
pool and will make sure</FONT> <BR><FONT size=3D2>that =
</FONT><BR><FONT=20
size=3D2>the objects are never aged out of the shared pool. If the =
objects are=20
never </FONT><BR><FONT size=3D2>aged out then there will not be a =
problem with=20
trying to load them and not </FONT><BR><FONT size=3D2>having enough =
memory.</FONT> </P>
<P><FONT size=3D2>Objects are 'kept' in the shared pool using the=20
dbms_shared_pool package</FONT> <BR><FONT size=3D2>that =
</FONT><BR><FONT=20
size=3D2>is defined in the dbmspool.sql file. For =
example:</FONT> </P>
<P><FONT size=3D2> execute=20
dbms_shared_pool.keep('SYS.STANDARD');</FONT> </P>
<P><FONT size=3D2>All large packages that are shipped should be =
'kept' if the=20
customer uses</FONT> <BR><FONT size=3D2>PL/SQL. This includes =
'STANDARD',=20
'DBMS_STANDARD', and 'DIUTIL'. With 7.3,</FONT> <BR><FONT =
size=3D2>the</FONT>=20
<BR><FONT size=3D2>only package left in this list is =
'STANDARD'.</FONT> </P>
<P><FONT size=3D2>All large customer packages should also be marked =
'kept'.</FONT> </P>
<P><FONT size=3D2>To mark all packages in the system 'kept' execute =
the=20
following:</FONT> </P>
<P><FONT size=3D2>declare</FONT> <BR><FONT size=3D2> own=20
varchar2(100);</FONT> <BR><FONT size=3D2> nam =
varchar2(100);</FONT>=20
<BR><FONT size=3D2> cursor pkgs is</FONT> <BR><FONT =
size=3D2> =20
select owner, object_name</FONT> <BR><FONT =
size=3D2> =20
from dba_objects</FONT> <BR><FONT size=3D2> =
where object_type =3D 'PACKAGE';</FONT> <BR><FONT =
size=3D2>begin</FONT>=20
<BR><FONT size=3D2> open pkgs;</FONT> <BR><FONT =
size=3D2> =20
loop</FONT> <BR><FONT size=3D2> fetch pkgs into =
own,=20
nam;</FONT> <BR><FONT size=3D2> exit when=20
pkgs%notfound;</FONT> <BR><FONT size=3D2> =20
dbms_shared_pool.keep(own || '.' || nam, 'P');</FONT> <BR><FONT=20
size=3D2> end loop;</FONT> <BR><FONT size=3D2>end;</FONT> =
</P>
<P><FONT size=3D2>The dbms_shared_pool package was introduced in =
7.0 and has=20
evolved over the </FONT><BR><FONT size=3D2>versions. Until 7.1.5, =
'keep' could=20
only be used for packages. Starting with</FONT> </P>
<P><FONT size=3D2>7.1.6, this was extended to standalone =
procedures, cursors=20
as well as</FONT> <BR><FONT size=3D2>triggers.</FONT> <BR><FONT =
size=3D2>For=20
detailed usage instructions, see the dbmspool.sql file. So, prior =
to</FONT>=20
<BR><FONT size=3D2>this </FONT><BR><FONT size=3D2>version, if you =
have large=20
procedures or large anonymous blocks, then these </FONT><BR><FONT=20
size=3D2>will need to be put into packages and marked kept. With =
7.3, most=20
packages</FONT> <BR><FONT size=3D2>do </FONT><BR><FONT size=3D2>not =
need to be=20
kept any longer since PL/SQL no longer requires large =
amounts</FONT>=20
<BR><FONT size=3D2>of</FONT> <BR><FONT size=3D2>contiguous memory =
to load=20
packages/procedures in memory.</FONT> </P>
<P><FONT size=3D2>You can determine what large stored objects are =
in the=20
shared pool by</FONT> <BR><FONT size=3D2>selecting</FONT> <BR><FONT =
size=3D2>from the v$db_object_cache fixed view. This will =
also tell you=20
which</FONT> <BR><FONT size=3D2>objects </FONT><BR><FONT =
size=3D2>have been=20
marked kept. This can be done with the following =
query:</FONT> </P>
<P><FONT size=3D2> select * from =
v$db_object_cache where=20
sharable_mem > 10000;</FONT> </P>
<P><FONT size=3D2>Note that this query will not catch PL/SQ: =
objects that are=20
only rarely used</FONT> </P>
<P><FONT size=3D2>and therefore the PL/SQL object is not currently =
loaded in=20
the shared pool.</FONT> </P>
<P><FONT size=3D2>To determine what large PL/SQL objects are =
currently loaded=20
in the shared</FONT> <BR><FONT size=3D2>pool </FONT><BR><FONT =
size=3D2>and are=20
not marked 'kept' and therefore may cause a problem, execute =
the</FONT>=20
<BR><FONT size=3D2>following:</FONT> </P>
<P><FONT size=3D2> select name, =
sharable_mem</FONT>=20
<BR><FONT size=3D2> from =
v$db_object_cache</FONT>=20
<BR><FONT size=3D2> where sharable_mem > =
10000</FONT>=20
<BR><FONT size=3D2> and (type =3D 'PACKAGE' =
or type =3D=20
'PACKAGE BODY' or type =3D 'FUNCTION'</FONT> <BR><FONT=20
size=3D2> or =
type =3D=20
'PROCEDURE')</FONT> <BR><FONT size=3D2> and =
kept =3D=20
'NO';</FONT> </P>
<P><FONT size=3D2>Another approach to the above is to use the=20
dbms_shared_pool.sizes</FONT> <BR><FONT size=3D2>procedure.</FONT> =
<BR><FONT=20
size=3D2>To use this in SQLDBA:</FONT> </P>
<P><FONT size=3D2> set serveroutput on;</FONT> =
<BR><FONT=20
size=3D2> execute =
dbms_shared_pool.sizes(10);</FONT> </P>
<P><FONT size=3D2>This should show you the names of all the objects =
in the=20
shared pool that</FONT> <BR><FONT size=3D2>take</FONT> <BR><FONT =
size=3D2>more=20
that 10K of memory as well as if they are marked kept or not. For =
SQL</FONT>=20
<BR><FONT size=3D2>statements, if there are multiple versions of a =
query=20
(usually a bug if the</FONT> <BR><FONT size=3D2>count is more than =
3), they=20
will also be indicated in parenthesis. Use the</FONT> <BR><FONT=20
size=3D2>following query to check for problems:</FONT> </P>
<P><FONT size=3D2> select sql_text, loaded_versions, =
version_count,=20
sharable_mem</FONT> <BR><FONT size=3D2> from v$sqlarea =
where=20
loaded_versions > 3</FONT> <BR><FONT size=3D2> order =
by=20
sharable_mem;</FONT> </P>
<P><FONT size=3D2>In Oracle7.3 onwards the best candidates for =
keeping can be=20
seen by querying</FONT> </P>
<P><FONT size=3D2>the table X$KSMSP to see if there are any chunks =
in the=20
shared-pool that</FONT> <BR><FONT size=3D2>have </FONT><BR><FONT =
size=3D2>the=20
KSMCHSIZ larger than 5K and KSMCHCOM like '%PL/SQL%'. If so then =
one=20
can</FONT> </P>
<P><FONT size=3D2>identify the object name and owner of this chunk =
using the=20
following SQL:</FONT> </P>
<P><FONT size=3D2> select distinct</FONT> <BR><FONT =
size=3D2> =20
=
decode(kglobtyp,0,'CURSOR',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',</FONT=
>=20
<BR><FONT=20
=
size=3D2> &nb=
sp; =20
11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE</FONT> <BR><FONT=20
size=3D2>BODY','OTHER')</FONT> <BR><FONT size=3D2> ||' =
-=20
'||kglnaown||'.'||kglnaobj "Eligible PL/SQL objects"</FONT> =
<BR><FONT=20
size=3D2> from x$kglob</FONT> <BR><FONT size=3D2> where =
kglobhd4=20
in</FONT> <BR><FONT size=3D2> (select =
ksmchpar from=20
x$ksmsp</FONT> <BR><FONT size=3D2> =
where=20
ksmchcom=3D'PL/SQL MPCODE' and ksmchsiz>5120)</FONT> </P>
<P><FONT size=3D2>If you are 'keeping' PL/SQL objects today and =
migrate to 7.3=20
or higher there</FONT> <BR><FONT size=3D2>is</FONT> <BR><FONT =
size=3D2>no need=20
to re-assess the list of objects that you are keeping.</FONT> </P>
<P><FONT size=3D2>ii) USE BIND VARIABLES</FONT> </P>
<P><FONT size=3D2>One of the best things that can be done to reduce =
the amount=20
of</FONT> <BR><FONT size=3D2>fragmentation</FONT> <BR><FONT =
size=3D2>is to=20
reduce or eliminate the number of sql statements in the shared =
pool</FONT>=20
<BR><FONT size=3D2>that</FONT> <BR><FONT size=3D2>are duplicates of =
each other=20
except for a constant that is embedded in the</FONT> <BR><FONT=20
size=3D2>statement. The statements should be replaced with =
one statement=20
that uses</FONT> <BR><FONT size=3D2>a bind variable instead of a=20
constant.</FONT> </P>
<P><FONT size=3D2>For example:</FONT> </P>
<P><FONT size=3D2> select * from emp where empno=3D1;</FONT> =
<BR><FONT=20
size=3D2> select * from emp where empno=3D2;</FONT> <BR><FONT =
size=3D2> select * from emp where empno=3D3;</FONT> </P>
<P><FONT size=3D2>Should all be replaced with:</FONT> </P>
<P><FONT size=3D2> select * from emp where empno=3D:1;</FONT> =
</P>
<P><FONT size=3D2>You can identify statements that potentially fall =
into this=20
class with a</FONT> <BR><FONT size=3D2>query </FONT><BR><FONT =
size=3D2>like the=20
following:</FONT> </P>
<P><FONT size=3D2> select substr(sql_text, 1, 30) sql, =
count(*)=20
copies</FONT> <BR><FONT size=3D2> from =
v$sqlarea</FONT>=20
<BR><FONT size=3D2> group by substr(sql_text, 1, =
30)</FONT>=20
<BR><FONT size=3D2> having count(*) > 3;</FONT> </P>
<P><FONT size=3D2>iii) MAX BIND SIZE</FONT> </P>
<P><FONT size=3D2>It is possible for a SQL statement to not be =
shared because=20
the max bind </FONT><BR><FONT size=3D2>variable lengths of the bind =
variables=20
in the statement do not match. This</FONT> <BR><FONT =
size=3D2>is=20
</FONT><BR><FONT size=3D2>automatically taken care of for =
precompiler programs=20
and forms programs, but</FONT> </P>
<P><FONT size=3D2>could be a problem for programs that directly use =
OCI. =20
The bind call in OCI</FONT> </P>
<P><FONT size=3D2>takes two arguments, one is the max length of the =
value, and=20
the other is a </FONT><BR><FONT size=3D2>pointer to the actual =
length. =20
If the current length is always passed in as</FONT> <BR><FONT=20
size=3D2>the</FONT> <BR><FONT size=3D2>max length instead of the =
max possible=20
length for the variable, then this</FONT> <BR><FONT size=3D2>could=20
</FONT><BR><FONT size=3D2>cause the SQL statement not to be =
shared.</FONT>=20
</P>
<P><FONT size=3D2>To identify statements that might potentially =
have this=20
problem execute the </FONT><BR><FONT size=3D2>following =
statement:</FONT> </P>
<P><FONT size=3D2> select sql_text, version_count =
from=20
v$sqlarea where version_count > 5;</FONT> </P>
<P><FONT size=3D2>Starting with 7.1.6 this should no longer be an =
issue as the=20
server can </FONT><BR><FONT size=3D2>graduate bind buffers even =
when the=20
user's max bind lengths are jumping up</FONT> <BR><FONT =
size=3D2>or</FONT>=20
<BR><FONT size=3D2>down and continue to share cursors that are =
built for=20
larger buffer lengths</FONT> <BR><FONT size=3D2>and flush the =
smaller sql=20
compilation from the shared pool.</FONT> </P>
<P><FONT size=3D2>iv) ELIMINATING LARGE ANONYMOUS PL/SQL</FONT> =
</P>
<P><FONT size=3D2>Large anonymous PL/SQL blocks should be turned =
into small=20
anonymous PL/SQL</FONT> <BR><FONT size=3D2>blocks that call =
packaged=20
functions. The packages should be 'kept' in</FONT> <BR><FONT=20
size=3D2>memory. </FONT><BR><FONT size=3D2>For version earlier that =
7.3, this=20
includes anonymous PL/SQL blocks that are</FONT> </P>
<P><FONT size=3D2>used for trigger definitions. With 7.3, triggers =
are=20
compiled and stored to </FONT><BR><FONT size=3D2>disk like =
standalone=20
procedures and should be treated as such. Large</FONT> <BR><FONT=20
size=3D2>anonymous </FONT><BR><FONT size=3D2>blocks can be =
identified with the=20
following query:</FONT> </P>
<P><FONT size=3D2> select sql_text from v$sqlarea</FONT> =
<BR><FONT=20
size=3D2> where=20
command_type=3D47 -- command =
type for=20
anonymous block</FONT> <BR><FONT size=3D2> and=20
length(sql_text) > 500;</FONT> </P>
<P><FONT size=3D2>Note that this query will not catch PL/SQL blocks =
that are=20
only rarely used</FONT> <BR><FONT size=3D2>and</FONT> <BR><FONT=20
size=3D2>therefore the PL/SQL block is not currently loaded in the =
shared=20
pool.</FONT> </P>
<P><FONT size=3D2>Another option that can be used when an anonymous =
block=20
cannot be turned</FONT> <BR><FONT size=3D2>into</FONT> <BR><FONT =
size=3D2>a=20
package is to mark the anonymous block with some string so that it =
can=20
be</FONT> <BR><FONT size=3D2>identified in v$sqlarea and marked =
'kept'.</FONT>=20
</P>
<P><FONT size=3D2>For example, instead of using:</FONT> <BR><FONT=20
size=3D2> declare x number; begin x :=3D 5; end;;</FONT> </P>
<P><FONT size=3D2>you can use:</FONT> <BR><FONT size=3D2> =
declare /*=20
KEEP_ME */ x number; begin x :=3D 5; end;</FONT> </P>
<P><FONT size=3D2>You can then use the following procedure to =
select these=20
statements out of</FONT> <BR><FONT size=3D2>the </FONT><BR><FONT =
size=3D2>shared=20
pool and mark them 'kept' using the dbms_shared_pool.keep =
package.</FONT>=20
</P>
<P><FONT size=3D2>declare</FONT> <BR><FONT size=3D2> /* =
DONT_KEEP_ME=20
*/</FONT> <BR><FONT size=3D2> addr varchar2(10);</FONT> =
<BR><FONT=20
size=3D2> hash number;</FONT> <BR><FONT size=3D2> =
cursor anon=20
is</FONT> <BR><FONT size=3D2> select address, =
hash_value</FONT>=20
<BR><FONT size=3D2> from v$sqlarea</FONT> =
<BR><FONT=20
size=3D2> where command_type =3D=20
=
47 &nbs=
p; =20
-- command type for anonymous</FONT> <BR><FONT =
size=3D2>block</FONT> <BR><FONT=20
size=3D2> and sql_text like '% =
KEEP_ME %'</FONT>=20
<BR><FONT size=3D2> and sql_text not =
like=20
'%DONT_KEEP_ME%';</FONT> <BR><FONT size=3D2>begin</FONT> <BR><FONT=20
size=3D2> open anon;</FONT> <BR><FONT size=3D2> =
loop</FONT>=20
<BR><FONT size=3D2> fetch anon into addr, =
hash;</FONT>=20
<BR><FONT size=3D2> exit when =
anon%notfound;</FONT>=20
<BR><FONT size=3D2> dbms_shared_pool.keep(addr || =
',' ||=20
to_char(hash), 'C');</FONT> <BR><FONT size=3D2> end =
loop;</FONT>=20
<BR><FONT size=3D2>end;</FONT> </P>
<P><FONT size=3D2>v) REDUCING USAGE</FONT> </P>
<P><FONT size=3D2>Another way to reducing fragmentation is to =
reduce=20
consumption. This is of</FONT> <BR><FONT size=3D2>special =
importance when=20
using MTS, when every user's session memory is in</FONT> <BR><FONT=20
size=3D2>the</FONT> <BR><FONT size=3D2>shared pool and the impact =
is multiplied=20
by the total concurrent users.</FONT> </P>
<P><FONT size=3D2>Insert, update, delete and anonymous blocks =
complete the=20
execution in one</FONT> <BR><FONT size=3D2>round </FONT><BR><FONT =
size=3D2>trip.=20
All the memory that is allocated on the server for the execute =
comes</FONT>=20
<BR><FONT size=3D2>from</FONT> <BR><FONT size=3D2>the PGA and is =
freed before=20
the call returns to the user. But in the case of</FONT> </P>
<P><FONT size=3D2>selects, memory required to execute the statement =
- which=20
could be large if</FONT> <BR><FONT size=3D2>a </FONT><BR><FONT =
size=3D2>sort was=20
involved - is not freed until the end-of-fetch is reached or =
the</FONT>=20
<BR><FONT size=3D2>query </FONT><BR><FONT size=3D2>is cancelled. In =
these=20
situations using the OCI features to do an exact</FONT> <BR><FONT=20
size=3D2>fetch </FONT><BR><FONT size=3D2>and cancel helps free =
memory back to=20
the pool.</FONT> </P>
<P><FONT size=3D2>If the application logic has been embedded into =
server side=20
PL/SQL, a large</FONT> <BR><FONT size=3D2>number of cursors may be =
getting=20
cached on the server for every user. Though</FONT> <BR><FONT =
size=3D2>this=20
results in reduced latch contention and faster response, it does =
use</FONT>=20
<BR><FONT size=3D2>more</FONT> <BR><FONT size=3D2>memory in the =
UGA. Setting the=20
close_cached_open_cursors init.ora to TRUE</FONT> <BR><FONT =
size=3D2>closes=20
the PL/SQL cached cursors on the server, freeing the memory.</FONT> =
</P>
<P><FONT=20
=
size=3D2>***************************************************************=
**********</FONT>=20
</P>
<P><FONT size=3D2>2) COMMON FALLACIES</FONT> </P>
<P><FONT size=3D2>There are a number of common fallacies about the =
shared pool=20
that are often</FONT> <BR><FONT size=3D2>stated as fact.</FONT> =
</P>
<P><FONT size=3D2>A) FREE MEMORY</FONT> </P>
<P><FONT size=3D2>One fallacy is that the amount of 'free memory' =
reported in=20
v$sgastat needs</FONT> <BR><FONT size=3D2>to </FONT><BR><FONT =
size=3D2>be kept=20
high. This is incorrect. The free memory reported in =
this table=20
is</FONT> <BR><FONT size=3D2>not</FONT> <BR><FONT size=3D2>like the =
free memory=20
reported by operating system statistics. Since the</FONT> =
<BR><FONT=20
size=3D2>shared</FONT> <BR><FONT size=3D2>pool acts as a cache, =
nothing will=20
ever be aged out of the shared pool until</FONT> <BR><FONT =
size=3D2>all</FONT>=20
<BR><FONT size=3D2>the free memory has been used up. This is =
entirely=20
normal.</FONT> </P>
<P><FONT size=3D2>Free memory is more properly thought of as =
'wasted=20
memory'. You would</FONT> <BR><FONT size=3D2>rather</FONT> =
<BR><FONT=20
size=3D2>see this value be low than very high. In fact, a =
high value of=20
free memory</FONT> <BR><FONT size=3D2>is </FONT><BR><FONT =
size=3D2>sometimes a=20
symptom that a lot of objects have been aged out of the =
shared</FONT>=20
<BR><FONT size=3D2>pool</FONT> <BR><FONT size=3D2>and therefore the =
system is=20
experiencing fragmentation problems.</FONT> </P>
<P><FONT size=3D2>B) FLUSH SHARED POOL</FONT> </P>
<P><FONT size=3D2>Some people think that frequently executing =
'alter system=20
flush shared_pool'</FONT> <BR><FONT size=3D2>improves the =
performance of the=20
system and decreases the amount of </FONT><BR><FONT=20
size=3D2>fragmentation. This is incorrect. Executing =
this=20
statement causes a big</FONT> <BR><FONT size=3D2>spike</FONT> =
<BR><FONT=20
size=3D2>in performance and does nothing to improve =
fragmentation.</FONT> </P>
<P><FONT size=3D2>The only time when it might be useful to run this =
statement=20
is between</FONT> <BR><FONT size=3D2>shifts of</FONT> <BR><FONT =
size=3D2>users=20
so that the objects that are relevant to the last shift of users =
can</FONT>=20
<BR><FONT size=3D2>be </FONT><BR><FONT size=3D2>flushed out before =
the next=20
shift of users starts to use the system. This</FONT> =
<BR><FONT=20
size=3D2>is </FONT><BR><FONT size=3D2>almost never needed =
though.</FONT> </P>
<P><FONT=20
=
size=3D2>***************************************************************=
**********</FONT>=20
</P>
<P><FONT size=3D2>3) SIZING OF SHARED POOL</FONT> </P>
<P><FONT size=3D2>One very difficult judgement that needs to be =
make in=20
Oracle7 is to</FONT> <BR><FONT size=3D2>determine</FONT> <BR><FONT =
size=3D2>the=20
proper size of the shared pool. The following provides some=20
guidelines</FONT> <BR><FONT size=3D2>for</FONT> <BR><FONT =
size=3D2>this. =20
It should be emphasized that these are just guidelines, there are =
no=20
</FONT><BR><FONT size=3D2>hard and fast rules here and =
experimentation will be=20
needed to determine a</FONT> <BR><FONT size=3D2>good</FONT> =
<BR><FONT=20
size=3D2>value.</FONT> </P>
<P><FONT size=3D2>The shared pool size is highly application =
dependent. =20
To determine the</FONT> <BR><FONT size=3D2>shared </FONT><BR><FONT =
size=3D2>pool=20
size that will be needed for a production system it is =
generally</FONT>=20
<BR><FONT size=3D2>necessary</FONT> <BR><FONT size=3D2>to first =
develop the=20
application and run it on a test system and take some =
</FONT><BR><FONT=20
size=3D2>measurements. The test system should be run with a =
very large=20
value for the</FONT> </P>
<P><FONT size=3D2>shared pool size to make the measurements =
meaningful.</FONT>=20
</P>
<P><FONT size=3D2>A) OBJECTS STORED IN THE DATABASE</FONT> </P>
<P><FONT size=3D2>The amount of shared pool that needs to be =
allocated for=20
objects that are </FONT><BR><FONT size=3D2>stored in the database =
like=20
packages and views is easy to measure. You can =
</FONT><BR><FONT=20
size=3D2>just measure their size directly with the following =
statement:</FONT>=20
</P>
<P><FONT size=3D2> select sum(sharable_mem) from=20
v$db_object_cache;</FONT> </P>
<P><FONT size=3D2>This is especially effective because all large =
pl/sql object=20
should be</FONT> <BR><FONT size=3D2>'kept'</FONT> <BR><FONT =
size=3D2>in the=20
shared pool at all times.</FONT> </P>
<P><FONT size=3D2>B) SQL</FONT> </P>
<P><FONT size=3D2>The amount of memory needed to store SQL =
statements in the=20
shared pool is</FONT> <BR><FONT size=3D2>more </FONT><BR><FONT=20
size=3D2>difficult to measure because of the needs of dynamic =
SQL. If an=20
application</FONT> </P>
<P><FONT size=3D2>has no dynamic SQL then the amount of memory can =
simply be=20
measured after</FONT> <BR><FONT size=3D2>the </FONT><BR><FONT=20
size=3D2>application has run for a while by just selecting it out =
of the=20
shared pool</FONT> <BR><FONT size=3D2>as </FONT><BR><FONT=20
size=3D2>follows:</FONT> </P>
<P><FONT size=3D2> select sum(sharable_mem) from=20
v$sqlarea;</FONT> </P>
<P><FONT size=3D2>If the application has a moderate or large amount =
of dynamic=20
SQL like most </FONT><BR><FONT size=3D2>applications do, then a =
certain=20
amount of memory will be needed for the</FONT> <BR><FONT =
size=3D2>shared=20
</FONT><BR><FONT size=3D2>SQL, plus more for the dynamic SQL, and =
more so that=20
the dynamic SQL does</FONT> <BR><FONT size=3D2>not </FONT><BR><FONT =
size=3D2>age=20
the shared SQL out of the shared pool.</FONT> </P>
<P><FONT size=3D2>The amount of memory for the shared SQL can be =
approximated=20
by the</FONT> <BR><FONT size=3D2>following:</FONT> </P>
<P><FONT size=3D2> select sum(sharable_mem) from =
v$sqlarea=20
where executions > 5;</FONT> </P>
<P><FONT size=3D2>The remaining memory in v$sqlarea is for dynamic =
SQL =20
Some shared pool will</FONT> <BR><FONT size=3D2>need to be budgeted =
for this=20
also, but there are few rules here.</FONT> </P>
<P><FONT size=3D2>C) PER-USER PER-CURSOR MEMORY</FONT> </P>
<P><FONT size=3D2>You will need to allow around 250 bytes of memory =
in the=20
shared pool per</FONT> <BR><FONT size=3D2>concurrent user for each =
open cursor=20
that the user has whether the cursor is</FONT> </P>
<P><FONT size=3D2>shared or not. During the peak usage time =
of the=20
production system, you can</FONT> </P>
<P><FONT size=3D2>measure this as follows:</FONT> </P>
<P><FONT size=3D2> select sum(250 * =
users_opening) from=20
v$sqlarea;</FONT> </P>
<P><FONT size=3D2>In a test system you can measure it by selecting =
the number=20
of open cursors</FONT> <BR><FONT size=3D2>for a test user and multip=
lying by=20
the total number of users:</FONT> </P>
<P><FONT size=3D2> select 250 * value bytes_per_user</FONT> =
<BR><FONT=20
size=3D2> from v$sesstat s, v$statname n</FONT> =
<BR><FONT=20
size=3D2> where s.statistic# =3D n.statistic#</FONT> =
<BR><FONT=20
size=3D2> and n.name =3D 'opened cursors =
current'</FONT>=20
<BR><FONT size=3D2> and s.sid =
=3D =20
23; -- replace 23 with session id of user =
being</FONT>=20
<BR><FONT size=3D2>measured</FONT> </P>
<P><FONT size=3D2>The per-user per-cursor memory is one of the =
classes of=20
memory that shows up</FONT> <BR><FONT size=3D2>as</FONT> <BR><FONT=20
size=3D2>'library cache' in v$sgastat.</FONT> </P>
<P><FONT size=3D2>D) MTS</FONT> </P>
<P><FONT size=3D2>If you are using multi-threaded server, then you =
will need=20
to allow enough</FONT> <BR><FONT size=3D2>memory for all the shared =
server=20
users to put their session memory in the </FONT><BR><FONT =
size=3D2>shared=20
pool. This can be measured for one user with the following=20
query:</FONT> </P>
<P><FONT size=3D2> select value sess_mem</FONT> <BR><FONT=20
size=3D2> from v$sesstat s, v$statname n</FONT> =
<BR><FONT=20
size=3D2> where s.statistic# =3D n.statistic#</FONT> =
<BR><FONT=20
size=3D2> and n.name =3D 'session uga =
memory'</FONT>=20
<BR><FONT size=3D2> and s.sid =
=3D =20
23; -- replace 23 with session id of user =
being</FONT>=20
<BR><FONT size=3D2>measured</FONT> </P>
<P><FONT size=3D2>A more conservative value to use is the maximum =
session=20
memory that was</FONT> <BR><FONT size=3D2>ever allocated by the =
user:</FONT>=20
</P>
<P><FONT size=3D2> select value sess_max_mem</FONT> <BR><FONT =
size=3D2> from v$sesstat s, v$statname n</FONT> =
<BR><FONT=20
size=3D2> where s.statistic# =3D n.statistic#</FONT> =
<BR><FONT=20
size=3D2> and n.name =3D 'session uga =
memory max'</FONT>=20
<BR><FONT size=3D2> and s.sid =
=3D =20
23; -- replace 23 with session id of user =
being</FONT>=20
<BR><FONT size=3D2>measured</FONT> </P>
<P><FONT size=3D2>To select this value for all the currently logged =
on users=20
the following</FONT> <BR><FONT size=3D2>query</FONT> <BR><FONT =
size=3D2>can be=20
used:</FONT> </P>
<P><FONT size=3D2> select sum(value) all_sess_mem</FONT> =
<BR><FONT=20
size=3D2> from v$sesstat s, v$statname n</FONT> =
<BR><FONT=20
size=3D2> where s.statistic# =3D n.statistic#</FONT> =
<BR><FONT=20
size=3D2> and n.name =3D 'session uga =
memory=20
max';</FONT> </P>
<P><FONT size=3D2>E) OVERHEAD</FONT> </P>
<P><FONT size=3D2>You will need to add a minimum of 30% overhead to =
the values=20
calculated</FONT> <BR><FONT size=3D2>above </FONT><BR><FONT =
size=3D2>to allow=20
for unexpected and unmeasured usage of the shared pool.</FONT> </P>
<P><FONT=20
=
size=3D2>***************************************************************=
**********</FONT>=20
</P>
<P><FONT size=3D2>4) FINAL COMMENTS</FONT> </P>
<P><FONT size=3D2>The most important point that needs to be =
understood by=20
everyone using</FONT> <BR><FONT size=3D2>Oracle7 </FONT><BR><FONT =
size=3D2>and=20
PL/SQL (prior to release 7.3) is that all large PL/SQL objects must =
be</FONT> <BR><FONT size=3D2>made </FONT><BR><FONT size=3D2>into =
packages and=20
those packages must be kept in the shared pool. This point</FONT> =
</P>
<P><FONT size=3D2>cannot be over emphasized. Many customers, =
especially=20
those running a lot</FONT> <BR><FONT size=3D2>of </FONT><BR><FONT=20
size=3D2>users, have had terrible performance problems that were =
completely=20
cleared</FONT> <BR><FONT size=3D2>up </FONT><BR><FONT size=3D2>by =
doing=20
this.</FONT> </P><BR>
<P><FONT=20
size=3D2>  =
; =20
APPENDIX I: Reserved Shared Pool</FONT> <BR><FONT=20
=
size=3D2> &nb=
sp; =20
=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT> </P>
<P><FONT size=3D2>1. RESERVED SPACE FROM THE SHARED POOL</FONT> =
<BR><FONT=20
=
size=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT> <BR><FONT =
size=3D2>On=20
busy systems, the RDBMS may have difficulty finding a contiguous =
piece=20
of</FONT> </P>
<P><FONT size=3D2>memory to satisfy a large request for =
memory. Because=20
the RDBMS will search</FONT> </P>
<P><FONT size=3D2>for and free currently unused memory, the search =
for this=20
large piece of</FONT> <BR><FONT size=3D2>memory </FONT><BR><FONT =
size=3D2>may=20
disrupt the behavior of the share pool, leading to more =
fragmentation</FONT>=20
<BR><FONT size=3D2>and </FONT><BR><FONT size=3D2>poor =
performance.</FONT> </P>
<P><FONT size=3D2>RDBMS 7.1.5 allows DBAs to reserve memory within =
the shared=20
pool to satisfy </FONT><BR><FONT size=3D2>these large allocations =
during RDBMS=20
operations such as PL/SQL compilation</FONT> <BR><FONT size=3D2>and =
</FONT><BR><FONT size=3D2>trigger compilation. Smaller =
objects will not=20
fragment the reserved list, </FONT><BR><FONT size=3D2>helping to =
ensure the=20
reserved list will have large contiguous chunks of</FONT> <BR><FONT =
size=3D2>memory.</FONT> <BR><FONT size=3D2>Once the memory =
allocated from the=20
reserved list is freed, it returns to the</FONT> </P>
<P><FONT size=3D2>reserved list.</FONT> </P>
<P><FONT size=3D2>The size of the reserved list, as well as the =
minimum size=20
of the objects</FONT> <BR><FONT size=3D2>that </FONT><BR><FONT =
size=3D2>can be=20
allocated from the reserved list are controlled via init.ora</FONT> =
<BR><FONT size=3D2>parameters:</FONT> <BR><FONT=20
size=3D2>shared_pool_reserved_size and =
shared_pool_reserved_min_alloc.</FONT>=20
</P>
<P><FONT size=3D2>1.1 shared_pool_reserved_size</FONT> =
<BR><FONT=20
size=3D2>------------------------------</FONT> <BR><FONT =
size=3D2>The init.ora=20
parameter shared_pool_reserved_size controls the amount of</FONT> =
<BR><FONT=20
size=3D2>shared_pool_size reserved for large allocations. In =
order to=20
create a</FONT> <BR><FONT size=3D2>reserved</FONT> <BR><FONT =
size=3D2>list,=20
shared_pool_reserved_size must be greater than </FONT><BR><FONT=20
size=3D2>shared_pool_reserved_min_alloc.</FONT> </P>
<P><FONT size=3D2> units : bytes</FONT> <BR><FONT =
size=3D2> default: 0 (no reserved list)</FONT> =
<BR><FONT=20
size=3D2> minimum: > =
shared_pool_reserved_min_alloc</FONT>=20
<BR><FONT size=3D2> maximum: 1/2 =
shared_pool_size</FONT> </P>
<P><FONT size=3D2>1.2 shared_pool_reserved_min_alloc</FONT> =
<BR><FONT=20
size=3D2>-----------------------------------</FONT> <BR><FONT =
size=3D2>The=20
init.ora parameter shared_pool_reserved_min_alloc controls =
allocation</FONT>=20
<BR><FONT size=3D2>for </FONT><BR><FONT size=3D2>the reserved =
memory. Only=20
allocations larger than </FONT><BR><FONT=20
size=3D2>shared_pool_reserved_min_alloc are allowed to allocate =
space from=20
the</FONT> <BR><FONT size=3D2>reserved </FONT><BR><FONT =
size=3D2>list if a chunk=20
of memory of sufficient size is not found on the shared</FONT> =
<BR><FONT=20
size=3D2>pool's </FONT><BR><FONT size=3D2>free lists.</FONT> </P>
<P><FONT size=3D2> units : bytes</FONT> <BR><FONT =
size=3D2> default: 5000</FONT> <BR><FONT =
size=3D2> =20
minimum: 5000</FONT> <BR><FONT size=3D2> maximum: =
<=20
shared_pool_reserved_size</FONT> </P>
<P><FONT size=3D2>The default value for =
shared_pool_reserved_min_alloc should=20
be adequate for </FONT><BR><FONT size=3D2>almost all =
systems.</FONT> </P>
<P><FONT size=3D2>2. CONTROLLING SPACE RECLAMATION OF THE =
SHARED=20
POOL</FONT> <BR><FONT=20
=
size=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D</FONT> <BR><FONT=20
size=3D2>RDBMS 7.1.5 also provides a new procedure, =
aborted_request_threshold,=20
in</FONT> <BR><FONT size=3D2>package</FONT> <BR><FONT =
size=3D2>dbms_shared_pool,=20
which allows users to set the limit on the size of</FONT> <BR><FONT =
size=3D2>allocations</FONT> <BR><FONT size=3D2>allowed to flush the =
shared pool=20
if the free lists cannot satisfy the</FONT> <BR><FONT =
size=3D2>request=20
</FONT><BR><FONT size=3D2>size.</FONT> </P>
<P><FONT size=3D2>Before the RDBMS signals the ORA-04031 error, it=20
incrementally flushes</FONT> <BR><FONT size=3D2>unused =
</FONT><BR><FONT=20
size=3D2>objects from the shared pool until there is sufficient =
memory to=20
satisfy the</FONT> </P>
<P><FONT size=3D2>allocation request. In most cases, =
incrementally=20
flushing objects from the </FONT><BR><FONT size=3D2>shared pool =
frees enough=20
memory for the allocation to complete succesfully.</FONT> <BR><FONT =
size=3D2>If </FONT><BR><FONT size=3D2>the RDBMS signals an =
ORA-04031 error, it=20
has flushed all objects currently</FONT> <BR><FONT size=3D2>not=20
</FONT><BR><FONT size=3D2>in use on the system without finding a =
large enough=20
piece of contiguous</FONT> <BR><FONT size=3D2>memory.</FONT> </P>
<P><FONT size=3D2>On a busy system, the larger the space =
allocation, the more=20
likely the RDBMS</FONT> </P>
<P><FONT size=3D2>will signal the ORA-04031 error. Flushing =
all objects,=20
however, will impact</FONT> </P>
<P><FONT size=3D2>other users on the system, possibly causing a =
degradation in=20
performance.</FONT> </P>
<P><FONT size=3D2>The aborted_request_threshold procedure allows =
the DBA to=20
localize the</FONT> <BR><FONT size=3D2>impact </FONT><BR><FONT =
size=3D2>the=20
ORA-04031 error to the process that couldn't allocate memory. =
The</FONT>=20
<BR><FONT size=3D2>procedure</FONT> <BR><FONT size=3D2>takes a =
numeric value=20
between 5000 and 2147483647, representing the size, in</FONT> </P>
<P><FONT size=3D2>bytes, of the threshold.</FONT> </P>
<P><FONT size=3D2>3. NEW FIXED VIEW =
V$SHARED_POOL_RESERVED</FONT>=20
<BR><FONT =
size=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT> =
<BR><FONT=20
size=3D2>RDBMS 7.1.5 has a new fixed view to help tune the reserved =
pool and=20
space</FONT> <BR><FONT size=3D2>within</FONT> <BR><FONT =
size=3D2>the shared=20
pool. The name of the new fixed view is =
V$SHARED_POOL_RESERVED</FONT>=20
<BR><FONT size=3D2>and </FONT><BR><FONT size=3D2>has the following=20
columns:</FONT> </P>
<P><FONT=20
=
size=3D2>Name  =
;  =
; =20
Null? Type</FONT> <BR><FONT=20
size=3D2>------------------------------- -------- =
--------------</FONT>=20
<BR><FONT=20
=
size=3D2>FREE_SPACE  =
;  =
; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>AVG_FREE_SIZE &n=
bsp; &n=
bsp; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>FREE_COUNT  =
;  =
; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>MAX_FREE_SIZE &n=
bsp; &n=
bsp; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>USED_SPACE  =
;  =
; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>AVG_USED_SIZE &n=
bsp; &n=
bsp; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>USED_COUNT  =
;  =
; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>MAX_USED_SIZE &n=
bsp; &n=
bsp; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>REQUESTS &=
nbsp; &=
nbsp; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>REQUEST_MISSES &=
nbsp; &=
nbsp; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>LAST_MISS_SIZE &=
nbsp; &=
nbsp; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>MAX_MISS_SIZE &n=
bsp; &n=
bsp; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>REQUEST_FAILURES  =
;  =
; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>LAST_FAILURE_SIZE &nbs=
p; &nbs=
p; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>ABORTED_REQUEST_THRESHOLD &n=
bsp; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>ABORTED_REQUESTS  =
;  =
; =20
NUMBER</FONT> <BR><FONT=20
=
size=3D2>LAST_ABORTED_SIZE &nbs=
p; &nbs=
p; =20
NUMBER</FONT> </P>
<P><FONT size=3D2>These columns of V$SHARED_POOL_RESERVED are only =
valid if=20
the parameter</FONT> <BR><FONT size=3D2>shared_pool_reserved_size =
is set to a=20
valid value.</FONT> </P>
<P><FONT size=3D2> FREE_SPACE is the total amount of free =
space on the=20
reserved list.</FONT> <BR><FONT size=3D2> </FONT><BR><FONT =
size=3D2> =20
AVG_FREE_SIZE is the average size of the free memory on the =
reserved=20
list.</FONT> </P>
<P><FONT size=3D2> FREE_COUNT is the number of free pieces of =
memory on=20
the reserved list.</FONT> </P>
<P><FONT size=3D2> MAX_FREE_SIZE is the size of the largest =
free piece=20
of memory on the</FONT> <BR><FONT size=3D2>reserved =
</FONT><BR><FONT=20
=
size=3D2> &nb=
sp; =20
list.</FONT> </P>
<P><FONT size=3D2> USED_SPACE is the total amount of used =
memory on the=20
reserved list.</FONT> </P>
<P><FONT size=3D2> AVG_USED_SIZE is the average size of the =
of the used=20
memory on the</FONT> <BR><FONT size=3D2>reserved </FONT><BR><FONT=20
=
size=3D2> &nb=
sp; =20
list.</FONT> </P>
<P><FONT size=3D2> USED_COUNT is the number of used pieces of =
memory on=20
the reserved list.</FONT> </P>
<P><FONT size=3D2> MAX_USED_SIZE is the size of the largest =
used piece=20
of memory on the</FONT> <BR><FONT size=3D2>reserved =
</FONT><BR><FONT=20
=
size=3D2> &nb=
sp; =20
list.</FONT> </P>
<P><FONT size=3D2> REQUESTS is the number of times that the =
reserved=20
list was searched for a </FONT><BR><FONT=20
=
size=3D2> =
free=20
piece of memory.</FONT> </P>
<P><FONT size=3D2> REQUEST_MISSES is the number of times the =
reserved=20
list didn't have a free</FONT> </P>
<P><FONT=20
=
size=3D2> &nb=
sp; =20
piece of memory to satisfy the request, and proceeded to</FONT> =
<BR><FONT=20
size=3D2>start</FONT> <BR><FONT=20
=
size=3D2> &nb=
sp; =20
flushing objects from the LRU list.</FONT> </P>
<P><FONT size=3D2> LAST_MISS_SIZE is the request size of the =
last=20
REQUEST_MISS.</FONT> </P>
<P><FONT size=3D2> MAX_MISS_SIZE is the request size of the =
largest=20
REQUEST_MISS.</FONT> </P>
<P><FONT size=3D2>The next set of columns contain values which are =
valid even=20
if</FONT> <BR><FONT size=3D2>shared_pool_reserved_size is not =
set.</FONT> </P>
<P><FONT size=3D2> REQUEST_FAILURES is the number of times =
that no=20
memory was found to</FONT> <BR><FONT size=3D2>satisfy a</FONT> =
<BR><FONT=20
=
size=3D2> &nb=
sp; =20
request (example: number of times ORA-04031 occurred)</FONT> </P>
<P><FONT size=3D2> LAST_FAILURE_SIZE is the request size of =
the last=20
failed request </FONT><BR><FONT=20
=
size=3D2> &nb=
sp; =20
(example: the request size of last ORA-04031).</FONT> </P>
<P><FONT size=3D2> ABORTED_REQUEST_THRESHOLD is the minimum =
size of a=20
request which will</FONT> <BR><FONT size=3D2>signal =
</FONT><BR><FONT=20
=
size=3D2> &nb=
sp; &nb=
sp; =20
an ORA-04031 error without flushing objects. See</FONT> </P>
<P><FONT=20
=
size=3D2> &nb=
sp; &nb=
sp; =20
the procedure aborted_request_threshold</FONT> <BR><FONT =
size=3D2>described=20
</FONT><BR><FONT=20
=
size=3D2> &nb=
sp; &nb=
sp; =20
above.</FONT> </P>
<P><FONT size=3D2> LAST_ABORTED_SIZE is the last size of the =
request=20
which returned an</FONT> <BR><FONT size=3D2>ORA-04031 =
</FONT><BR><FONT=20
=
size=3D2> &nb=
sp; =20
error without flushing objects from the LRU list.</FONT> </P><BR>
<P><FONT size=3D2>4. TUNING HINTS BASED ON =
V$SHARED_POOL_RESERVED</FONT>=20
<BR><FONT =
size=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D</FONT>=20
<BR><FONT size=3D2>Information in V$SHARED_POOL_RESERVED can help =
to set=20
values for</FONT> <BR><FONT size=3D2>shared_pool_reserved_size and =
even=20
shared_pool_size. This section assumes</FONT> <BR><FONT =
size=3D2>the=20
</FONT><BR><FONT size=3D2>DBA has performed all other shared pool =
tuning on=20
his system.</FONT> </P>
<P><FONT size=3D2>4.1 Initial Value for =
shared_pool_reserved_size</FONT>=20
<BR><FONT =
size=3D2>------------------------------------------------</FONT>=20
<BR><FONT size=3D2>The DBA should make shared_pool_reserved_size =
10% of the=20
shared_pool_size.</FONT> <BR><FONT size=3D2>For</FONT> <BR><FONT =
size=3D2>most=20
systems, this value should be sufficient, if the DBA has already=20
spent</FONT> <BR><FONT size=3D2>time</FONT> <BR><FONT =
size=3D2>tuning the shared=20
pool.</FONT> </P>
<P><FONT size=3D2>4.2 Initial Value for=20
shared_pool_reserved_min_alloc</FONT> <BR><FONT=20
=
size=3D2>-----------------------------------------------------</FONT>=20
<BR><FONT size=3D2>In most cases, the default value for this =
parameter is=20
adequate. If the DBA </FONT><BR><FONT size=3D2>increases this =
value, then the=20
RDBMS will allow fewer allocations from the </FONT><BR><FONT =
size=3D2>reserved=20
list and will request more memory from the shared pool list.</FONT> =
</P>
<P><FONT size=3D2>4.4 Tuning shared_pool_reserved_size</FONT> =
<BR><FONT=20
size=3D2>-------------------------------------</FONT> <BR><FONT=20
size=3D2>Ideally, shared_pool_reserved_size should be made large =
enough to=20
satisfy</FONT> <BR><FONT size=3D2>any </FONT><BR><FONT =
size=3D2>request scanning=20
for memory on the reserved list without flushing objects</FONT> =
<BR><FONT=20
size=3D2>from </FONT><BR><FONT size=3D2>the shared pool. The =
amount of=20
operating system memory, however, may</FONT> <BR><FONT=20
size=3D2>constrain</FONT> <BR><FONT size=3D2>the size of the SGA, =
and therefore=20
the size of the shared pool such that</FONT> <BR><FONT =
size=3D2>this=20
</FONT><BR><FONT size=3D2>is not a feasible goal.</FONT> </P>
<P><FONT size=3D2>If the DBA has a system with ample free memory to =
increase=20
his SGA, the goal</FONT> <BR><FONT size=3D2>is </FONT><BR><FONT =
size=3D2>to=20
have:</FONT> </P>
<P><FONT size=3D2> =
REQUEST_MISS =3D=20
0</FONT> </P>
<P><FONT size=3D2>If the DBA is constrained for OS memory, his goal =
is:</FONT>=20
</P>
<P><FONT size=3D2> REQUEST_FAILURES =3D 0 or not=20
increasing</FONT> <BR><FONT size=3D2> LAST_FAILURE_SIZE =
>=20
shared_pool_reserved_min_alloc</FONT> <BR><FONT =
size=3D2> =20
AVG_FREE_SIZE >=20
shared_pool_reserved_min_alloc</FONT> </P>
<P><FONT size=3D2>If neither of these goals are met, increase=20
shared_pool_reserved_size; the</FONT> <BR><FONT size=3D2>DBA =
</FONT><BR><FONT=20
size=3D2>also needs to increase shared_pool_size by the same =
amount, since=20
the</FONT> <BR><FONT size=3D2>reserved </FONT><BR><FONT =
size=3D2>list is taken=20
from the shared pool.</FONT> </P>
<P><FONT size=3D2>4.5 shared_pool_reserved_size too =
low</FONT> <BR><FONT=20
size=3D2>--------------------------------------</FONT> <BR><FONT =
size=3D2>The=20
reserved pool is too small when:</FONT> </P>
<P><FONT size=3D2> REQUEST_FAILURES > 0 (and=20
increasing)</FONT> </P>
<P><FONT size=3D2>and at least one of the following is true:</FONT> =
</P>
<P><FONT size=3D2> LAST_FAILURE_SIZE >=20
shared_pool_reserved_min_alloc</FONT> <BR><FONT =
size=3D2> =20
MAX_FREE_SIZE <=20
shared_pool_reserved_min_alloc</FONT> <BR><FONT =
size=3D2> =20
FREE_MEMORY <=20
shared_pool_reserved_min_alloc</FONT> </P>
<P><FONT size=3D2>The DBA has two options, depending on his SGA =
size=20
constraints:</FONT> </P>
<P><FONT size=3D2> o Increase =
shared_pool_reserved_size and=20
shared_pool_size, accordingly</FONT> <BR><FONT =
size=3D2> o =20
Increase shared_pool_reserved_min_alloc (but may need to =
increase</FONT>=20
<BR><FONT size=3D2> =
shared_pool_size)</FONT>=20
</P>
<P><FONT size=3D2>The first option will increase the amount of =
memory=20
available on the</FONT> <BR><FONT size=3D2>reserved =
</FONT><BR><FONT=20
size=3D2>list without impacting users not allocating memory from =
the reserved=20
list.</FONT> <BR><FONT size=3D2>The </FONT><BR><FONT =
size=3D2>second options=20
reduces the number of allocations allowed to use memory from</FONT> =
<BR><FONT size=3D2>the</FONT> <BR><FONT size=3D2>reserved list; =
doing so,=20
however, will increase normal shared pool perhaps </FONT><BR><FONT=20
size=3D2>impacting other users on the system.</FONT> </P>
<P><FONT size=3D2>4.6 shared_pool_reserved_size too =
high</FONT>=20
<BR><FONT size=3D2>---------------------------------------</FONT> =
<BR><FONT=20
size=3D2>It is possible that too much memory has been allocated to =
the=20
reserved list.</FONT> </P>
<P><FONT size=3D2>If:</FONT> </P>
<P><FONT size=3D2> =
REQUEST_MISS =3D 0=20
or not increasing</FONT> <BR><FONT size=3D2> =20
FREE_MEMORY =3D > 50% of=20
shared_pool_reserved_size minimum</FONT> </P>
<P><FONT size=3D2>The DBA has two options:</FONT> </P>
<P><FONT size=3D2> o Decrease=20
shared_pool_reserved_size</FONT> <BR><FONT size=3D2> =
o =20
Decrease shared_pool_reserved_min_alloc (if not the default</FONT> =
<BR><FONT=20
size=3D2> value)</FONT> </P>
<P><FONT size=3D2>4.7 shared_pool_size too small</FONT> =
<BR><FONT=20
size=3D2>-------------------------------</FONT> <BR><FONT =
size=3D2>The new fixed=20
table can also indicate when shared_pool_size is too small. =20
</FONT><BR><FONT size=3D2>If:</FONT> </P>
<P><FONT size=3D2> REQUEST_FAILURES > 0 =
and=20
increasing</FONT> <BR><FONT size=3D2> =
LAST_FAILURE_SIZE <=20
shared_pool_reserved_min_alloc</FONT> </P>
<P><FONT size=3D2>Then the DBA has two options if he has enabled =
the reserved=20
list:</FONT> </P>
<P><FONT size=3D2> o Decrease=20
shared_pool_reserved_size</FONT> <BR><FONT size=3D2> =
o =20
Decrease shared_pool_reserved_min_alloc (if set larger than =
the</FONT>=20
<BR><FONT size=3D2>default)</FONT> </P>
<P><FONT size=3D2>Otherwise, the DBA the could:</FONT> </P>
<P><FONT size=3D2> o Increase =
shared_pool_size</FONT>=20
</P><BR>
<P><FONT=20
=
size=3D2> &nb=
sp; =20
APPENDIX 2: Procedure free_unused_memory</FONT> <BR><FONT=20
=
size=3D2> &nb=
sp; =20
=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT> </P>
<P><FONT size=3D2>This text is also in the specification for this =
procedure in=20
dbmsutil.sql.</FONT> <BR><FONT size=3D2>It is part of package=20
dbms_session.</FONT> </P>
<P><FONT size=3D2>Procedure free_unused_memory --</FONT> </P>
<P><FONT size=3D2>Procedure for users to reclaim unused memory =
after=20
performing operations</FONT> <BR><FONT size=3D2>requiring large =
amounts of=20
memory (where large is >100K). Note that this =
</FONT><BR><FONT=20
size=3D2>procedure should only be used in cases where memory is at =
a=20
premium.</FONT> </P>
<P><FONT size=3D2>Examples operations using lots of memory =
are:</FONT> </P>
<P><FONT size=3D2> o large sorts where entire =
sort_area_size=20
is used and</FONT> <BR><FONT =
size=3D2> =20
sort_area_size is hundreds of KB</FONT> <BR><FONT =
size=3D2> =20
o compiling large PL/SQL packages, procedures, or =
functions</FONT>=20
<BR><FONT size=3D2> o storing hundreds of KB of =
data within=20
PL/SQL indexed tables</FONT> </P>
<P><FONT size=3D2>One can monitor user memory by tracking the =
statistics=20
"session uga memory"</FONT> <BR><FONT size=3D2>and</FONT> <BR><FONT =
size=3D2>"session pga memory" in the v$sesstat/v$statname fixed =
views. =20
Monitoring</FONT> <BR><FONT size=3D2>these </FONT><BR><FONT =
size=3D2>statistics=20
will also show how much memory this procedure has freed. The</FONT> =
<BR><FONT size=3D2>behavior</FONT> <BR><FONT size=3D2>of this =
procedure depends=20
upon the configuration of the server operating on </FONT><BR><FONT=20
size=3D2>behalf of the client:</FONT> </P>
<P><FONT size=3D2> o dedicated server - returns =
unused PGA=20
memory to the OS</FONT> <BR><FONT size=3D2> o MTS =
server - returns unused session =
memory=20
to the shared_pool</FONT> </P>
<P><FONT size=3D2>In order to free memory using this procedure, the =
memory=20
must not be in use.</FONT> </P>
<P><FONT size=3D2>Once an operation allocates memory, only the same =
type of=20
operation can</FONT> <BR><FONT size=3D2>reuse </FONT><BR><FONT =
size=3D2>the=20
allocated memory. For example, once memory is allocated for =
sort,=20
even</FONT> <BR><FONT size=3D2>if </FONT><BR><FONT size=3D2>the =
sort is complete=20
and the memory is no longer in use, only another sort</FONT> =
<BR><FONT=20
size=3D2>can </FONT><BR><FONT size=3D2>reuse the sort-allocated =
memory. =20
For both sort and compilation, after the </FONT><BR><FONT =
size=3D2>operation=20
is complete, the memory is no longer in use and the user can</FONT> =
<BR><FONT size=3D2>invoke </FONT><BR><FONT size=3D2>this procedure =
to free the=20
unused memory.</FONT> </P>
<P><FONT size=3D2>An indexed table implicitly allocates memory to =
store values=20
assigned to the</FONT> </P>
<P><FONT size=3D2>indexed table's elements. Thus, the more =
elements in=20
an indexed table, the</FONT> <BR><FONT size=3D2>more</FONT> =
<BR><FONT=20
size=3D2>memory the RDBMS allocates to the indexed table. As =
long as=20
there are</FONT> <BR><FONT size=3D2>elements </FONT><BR><FONT =
size=3D2>within=20
the indexed table, the memory associated with an indexed table is =
in</FONT>=20
<BR><FONT size=3D2>use.</FONT> </P>
<P><FONT size=3D2>The scope of indexed tables determines how long =
their memory=20
is in use.</FONT> <BR><FONT size=3D2>Indexed </FONT><BR><FONT =
size=3D2>tables=20
declared globally are indexed tables declared in packages or =
package=20
</FONT><BR><FONT size=3D2>bodies. They allocate memory from =
session=20
memory. For an indexed table </FONT><BR><FONT =
size=3D2>declared=20
globally, the memory will remain in use for the lifetime of =
a</FONT>=20
<BR><FONT size=3D2>user's </FONT><BR><FONT size=3D2>login (lifetime =
of a user's=20
session), and is freed after the user</FONT> <BR><FONT =
size=3D2>disconnects=20
</FONT><BR><FONT size=3D2>from ORACLE.</FONT> </P>
<P><FONT size=3D2>Indexed tables declared locally are indexed =
tables declared=20
within</FONT> <BR><FONT size=3D2>functions, </FONT><BR><FONT=20
size=3D2>procedures, or anonymous blocks. These indexed =
tables allocate=20
memory from</FONT> <BR><FONT size=3D2>PGA</FONT> <BR><FONT=20
size=3D2>memory. For an indexed table declared locally, the =
memory will=20
remain in</FONT> <BR><FONT size=3D2>use </FONT><BR><FONT =
size=3D2>for as long as=20
the user is still executing the procedure, function, or</FONT> =
<BR><FONT=20
size=3D2>anonymous</FONT> <BR><FONT size=3D2>block in which the =
indexed table is=20
declared. After the procedure,</FONT> <BR><FONT =
size=3D2>function,=20
or</FONT> <BR><FONT size=3D2>anonymous block is finished executing, =
the memory=20
is then available for</FONT> <BR><FONT size=3D2>other</FONT> =
<BR><FONT=20
size=3D2>locally declared indexed tables to use (i.e., the memory =
is no longer=20
in</FONT> <BR><FONT size=3D2>use).</FONT> </P>
<P><FONT size=3D2>Assigning an uninitialized, "empty," indexed =
table to an=20
existing index</FONT> <BR><FONT size=3D2>table is</FONT> <BR><FONT =
size=3D2>a=20
method to explicitly re-initialize the indexed table and the =
memory</FONT>=20
<BR><FONT size=3D2>associated</FONT> <BR><FONT size=3D2>with the =
indexed=20
table. After this operation, the memory associated =
with</FONT>=20
<BR><FONT size=3D2>the </FONT><BR><FONT size=3D2>indexed table will =
no longer be=20
in use, making it available to be freed by </FONT><BR><FONT =
size=3D2>calling=20
this procedure. This method is particularly useful on =
indexed</FONT>=20
<BR><FONT size=3D2>tables </FONT><BR><FONT size=3D2>declared =
globally which can=20
grow during the lifetime of a user's session, as</FONT> </P>
<P><FONT size=3D2>long as the user no longer needs the contents of =
the indexed=20
table.</FONT> </P>
<P><FONT size=3D2>The memory rules associated with an indexed =
table's scope=20
still apply; this </FONT><BR><FONT size=3D2>method and this =
procedure,=20
however, allow users to intervene and to</FONT> <BR><FONT =
size=3D2>explictly=20
</FONT><BR><FONT size=3D2>free the memory associated with an =
indexed=20
table.</FONT> </P>
<P><FONT size=3D2>The PL/SQL fragment below illustrates the method =
and the use=20
of procedure </FONT><BR><FONT =
size=3D2>free_unused_user_memory.</FONT> </P>
<P><FONT size=3D2> create package foobar</FONT> <BR><FONT=20
size=3D2> type number_idx_tbl is table of number =
indexed by=20
binary_integer;</FONT> <BR><FONT size=3D2> =
store1_table =20
number_idx_tbl; -- PL/SQL indexed =
table</FONT>=20
<BR><FONT size=3D2> store2_table =20
number_idx_tbl; -- PL/SQL indexed =
table</FONT>=20
<BR><FONT size=3D2> store3_table =20
number_idx_tbl; -- PL/SQL indexed =
table</FONT>=20
<BR><FONT size=3D2> ...</FONT> <BR><FONT=20
=
size=3D2> end;  =
; =20
-- end of foobar</FONT> <BR><FONT =
size=3D2> declare</FONT>=20
<BR><FONT size=3D2> ...</FONT> <BR><FONT =
size=3D2> =20
empty_table number_idx_tbl; =
-- =20
uninitialized ("empty") version</FONT> <BR><FONT =
size=3D2> begin</FONT>=20
<BR><FONT size=3D2> for i in 1..1000000 loop</FONT> =
<BR><FONT=20
size=3D2> store1_table(i) :=3D=20
i; =
-- load=20
data</FONT> <BR><FONT size=3D2> end loop;</FONT> =
<BR><FONT=20
size=3D2> ...</FONT> <BR><FONT size=3D2> =
store1_table :=3D=20
empty_table; -- "truncate" the =
indexed=20
table</FONT> <BR><FONT size=3D2> ...</FONT> <BR><FONT=20
Received on Thu Aug 24 2000 - 09:47:15 CDT
 |
 |