RE: Who and What is using the shared pool.

From: Philip West <P.West_at_g-icap.com>
Date: Thu, 24 Aug 2000 11:19:14 +0100
Message-Id: <10599.115448@fatcity.com>

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_01C00DB7.2663CD68
Content-Type: text/plain;

charset="iso-8859-1"

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.

MEMORY FRAGMENTATION

The primary problem that occurs is that free memory in the shared pool becomes
fragmented into small pieces over time. Any attempt to allocate a large piece
of memory in the shared pool will cause large amount of objects in the library
cache to be flushed out and may result in an ORA-04031 out of shared memory error.

DIAGNOSIS OF FRAGMENTATION
ORA-04031 ERROR

One way to diagnose that this is happening is to look for ORA-04031 errors being returned from applications. When an attempt is made to allocate a large
contiguous piece of shared memory, and not enough contiguous memory can be created in the shared pool, the database will signal this error.

Before this error is signalled, all objects in the shared pool that are not currently in use will be flushed from the shared pool, and their memory will

be
freed and merged. This error only occurs when there is still not a large enough contiguous piece of free memory after this happens. There may be very
large amounts of total free memory in the shared pool, but just not enough contiguous memory.

ii) INIT.ORA PARAMETER

An init.ora parameter can be set so that whenever an ORA-04031 error is signalled a dump will occur into a trace file. By looking for these trace files, the DBA can determine that these errors are occurring. This is useful
when applications do not always report errors signalled by oracle, or if users
do not report the errors to the DBAs. The parameter is the following:

event = "4031 trace name errorstack"

If you are using 7.0.16 or higher you can use the following:

event = "4031 trace name errorstack level 4"

This will cause a dump of the Oracle state objects to occur when this error is
signalled. By looking in the dump for 'load=X' and then looking up a few lines
for 'name=' you can often tell whether an object was being loaded into the shared pool when this error occurred. If an object was being loaded then it

is
likely that this load is the cause of the problem and the object should be 'kept' in the shared pool. The object being loaded is the object printed after
the 'name='. Do not use the 'level 4' option in versions before 7.0.16 because
a bug existed that often caused the system to crash with this option enabled

due to a latch level violation.

Prior to version 7.3, there were a handful of cases where the RDBMS or PL/SQL
would attempt to allocate large pieces of contiguous memory. Most of this has
been fixed for 7.3. This problem was especially acute when running MTS, when

the UGA would be located in the SGA. This should also be fixed in 7.3 and using
MTS for a high OLTP scenario is recommended. As a result of all these changes,
the ORA-04031 error should be virtually eliminated. If an ORA-04031 error is

signalled, quite likely the shared pool is over 90% utilized and the alternative is to increase the shared pool. The only known situation is PL/SQL
packages (like STANDARD) where the package contains a very large number (over
400) procedure/function definitions. This still needs to be in contiguous memory and may request memory chunks as large as 15K. Packages like this should
be the only ones that should be kept.

iii) X$KSMLRU

There is a fixed table called x$ksmlru that tracks allocations in the shared pool that cause other objects in the shared pool to be aged out. This
fixed table can be used to identify what is causing the large allocation.

The columns of this fixed table are the following:

KSMLRCOM - allocation comment that describes the type of allocation.

If this comment is something like 'MPCODE' or 'PLSQL%' then there is a large

PL/SQL object being loaded into the shared pool. This PL/SQL object will need
to be 'kept' in the shared pool.

If this comment is 'kgltbtab' then the allocation is for a dependency table in
the library cache. This is only a problem when several hundred users are logged
on using distinct user ids. The solution in this case is to use fully qualified
names for all table references.

If you are running MTS and the comment is something like 'Fixed UGA' then the
problem is that the init.ora parameter 'open_cursors' is set too high.

KSMLRSIZ - amount of contiguous memory being allocated. Values over around 5K
start to be a problem, values over 10K are a serious problem, and values over
20K are very serious problems. Anything less then 5K should not be a problem.

KSMLRNUM - number of objects that were flushed from the shared pool in order

allocate the memory.

In release 7.1.3 or later, the following columns also exist:

KSMLRHON - the name of the object being loaded into the shared pool if the object is a PL/SQL object or a cursor.

KSMLROHV - hash value of object being loaded

KSMLRSES - SADDR of the session that loaded the object.

The advantage of X$KSMLRU is that it allows you to identify problems with fragmentation that are effecting performance, but that are not bad enough to

be
causing ORA-04031 errors to be signalled. If a lot of objects are being periodically flushed from the shared pool then this will cause response time

problems and will likely cause library cache latch contention problems when the
objects are reloaded into the shared pool. With version 7.2, the library cache
latch contention should be significantly reduced with the breaking up of the

library cache pin latch into a configurable set of symmetric library cache latches.

One unusual thing about the x$ksmlru fixed table is that the contents of the

fixed table are erased whenever someone selects from the fixed table. This is
done since the fixed table stores only the largest allocations that have occurred. The values are reset after being selected so that subsequent large
allocations can be noted even if they were not quite as large as others that

occurred previously. Because of this resetting, the output of selecting from
this table should be carefully noted since it cannot be reselected if it is forgotten. Also you should take care that there are not multiple people on one
database that select from this table because only one of them will select the
real data.

To monitor this fixed table just run the following:

select * from x$ksmlru where ksmlrsiz > 5000;

iv) MTS

Oracle users using SQL*Net V2 can connect to the database using dedicated servers, or multiple clients can use a pool of shared (or MTS) servers. The biggest memory implication of this mode is that the session memory (also known
as the UGA) for every session needs to be accessible to every MTS server. This
implies that the logical UGA comes out of the physical SGA (or the shared pool) instead of the PGA (process memory).

In versions prior to 7.3, there were a few components in the UGA that would request large contiguous chunks of memory, contributing to fragmentation of the shared pool if using MTS. If the system had been up for a while, users would have failures when attempting to connect or executing sql. Starting with
7.3, all these allocations have been segmented such that the average size of

memory chunks allocated to the UGA should be about 5K.

B) CORRECTION OF FRAGMENTATION i) KEEPING OBJECTS

The primary source of problems is large PL/SQL objects. The means of correcting
these errors is to 'keep' large PL/SQL object in the shared pool at startup time. This will load the objects into the shared pool and will make sure that
the objects are never aged out of the shared pool. If the objects are never aged out then there will not be a problem with trying to load them and not having enough memory.

Objects are 'kept' in the shared pool using the dbms_shared_pool package that
is defined in the dbmspool.sql file. For example:

execute dbms_shared_pool.keep('SYS.STANDARD');

All large packages that are shipped should be 'kept' if the customer uses PL/SQL. This includes 'STANDARD', 'DBMS_STANDARD', and 'DIUTIL'. With 7.3, the
only package left in this list is 'STANDARD'.

All large customer packages should also be marked 'kept'.

To mark all packages in the system 'kept' execute the following:

declare
  own varchar2(100);
  nam varchar2(100);
  cursor pkgs is
   select owner, object_name

     from dba_objects 
     where  object_type = 'PACKAGE';

begin
  open pkgs;
  loop
    fetch pkgs into own, nam;
    exit when pkgs%notfound;
    dbms_shared_pool.keep(own || '.' || nam, 'P');   end loop;
end;

The dbms_shared_pool package was introduced in 7.0 and has evolved over the versions. Until 7.1.5, 'keep' could only be used for packages. Starting with

7.1.6, this was extended to standalone procedures, cursors as well as triggers.
For detailed usage instructions, see the dbmspool.sql file. So, prior to this
version, if you have large procedures or large anonymous blocks, then these will need to be put into packages and marked kept. With 7.3, most packages do
not need to be kept any longer since PL/SQL no longer requires large amounts

of
contiguous memory to load packages/procedures in memory.

You can determine what large stored objects are in the shared pool by selecting
from the v$db_object_cache fixed view. This will also tell you which objects
have been marked kept. This can be done with the following query:

select * from v$db_object_cache where sharable_mem > 10000;

Note that this query will not catch PL/SQ: objects that are only rarely used

and therefore the PL/SQL object is not currently loaded in the shared pool.

To determine what large PL/SQL objects are currently loaded in the shared pool
and are not marked 'kept' and therefore may cause a problem, execute the following:

    select name, sharable_mem
     from v$db_object_cache
    where sharable_mem > 10000

     and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION' 
          or type = 'PROCEDURE') 
     and kept = 'NO';

Another approach to the above is to use the dbms_shared_pool.sizes procedure.
To use this in SQLDBA:

set serveroutput on;
execute dbms_shared_pool.sizes(10);

This should show you the names of all the objects in the shared pool that take
more that 10K of memory as well as if they are marked kept or not. For SQL statements, if there are multiple versions of a query (usually a bug if the count is more than 3), they will also be indicated in parenthesis. Use the following query to check for problems:

select sql_text, loaded_versions, version_count, sharable_mem from v$sqlarea where loaded_versions > 3 order by sharable_mem;

In Oracle7.3 onwards the best candidates for keeping can be seen by querying

the table X$KSMSP to see if there are any chunks in the shared-pool that have
the KSMCHSIZ larger than 5K and KSMCHCOM like '%PL/SQL%'. If so then one can

identify the object name and owner of this chunk using the following SQL:

select distinct
decode(kglobtyp,0,'CURSOR',7,'PROCEDURE',8,'FUNCTION',9,'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
  open anon;
  loop
    fetch anon into addr, hash;
    exit when anon%notfound;
    dbms_shared_pool.keep(addr || ',' || to_char(hash), 'C');   end loop;
end;

v) REDUCING USAGE

Another way to reducing fragmentation is to reduce consumption. This is of special importance when using MTS, when every user's session memory is in the
shared pool and the impact is multiplied by the total concurrent users.

Insert, update, delete and anonymous blocks complete the execution in one round
trip. All the memory that is allocated on the server for the execute comes from
the PGA and is freed before the call returns to the user. But in the case of

selects, memory required to execute the statement - which could be large if a
sort was involved - is not freed until the end-of-fetch is reached or the query
is cancelled. In these situations using the OCI features to do an exact fetch
and cancel helps free memory back to the pool.

If the application logic has been embedded into server side PL/SQL, a large number of cursors may be getting cached on the server for every user. Though

this results in reduced latch contention and faster response, it does use more
memory in the UGA. Setting the close_cached_open_cursors init.ora to TRUE closes the PL/SQL cached cursors on the server, freeing the memory.

2) COMMON FALLACIES There are a number of common fallacies about the shared pool that are often stated as fact.

FREE MEMORY

One fallacy is that the amount of 'free memory' reported in v$sgastat needs to
be kept high. This is incorrect. The free memory reported in this table is

not
like the free memory reported by operating system statistics. Since the shared
pool acts as a cache, nothing will ever be aged out of the shared pool until

all
the free memory has been used up. This is entirely normal.

Free memory is more properly thought of as 'wasted memory'. You would rather
see this value be low than very high. In fact, a high value of free memory is
sometimes a symptom that a lot of objects have been aged out of the shared pool
and therefore the system is experiencing fragmentation problems.

B) FLUSH SHARED POOL Some people think that frequently executing 'alter system flush shared_pool'

improves the performance of the system and decreases the amount of fragmentation. This is incorrect. Executing this statement causes a big spike
in performance and does nothing to improve fragmentation.

The only time when it might be useful to run this statement is between shifts of
users so that the objects that are relevant to the last shift of users can be
flushed out before the next shift of users starts to use the system. This is
almost never needed though.

3) SIZING OF SHARED POOL One very difficult judgement that needs to be make in Oracle7 is to determine
the proper size of the shared pool. The following provides some guidelines for
this. It should be emphasized that these are just guidelines, there are no hard and fast rules here and experimentation will be needed to determine a good
value.

The shared pool size is highly application dependent. To determine the shared
pool size that will be needed for a production system it is generally necessary
to first develop the application and run it on a test system and take some measurements. The test system should be run with a very large value for the

shared pool size to make the measurements meaningful.

OBJECTS STORED IN THE DATABASE

The amount of shared pool that needs to be allocated for objects that are stored in the database like packages and views is easy to measure. You can just measure their size directly with the following statement:

select sum(sharable_mem) from v$db_object_cache;

This is especially effective because all large pl/sql object should be 'kept'
in the shared pool at all times.

B) SQL The amount of memory needed to store SQL statements in the shared pool is more
difficult to measure because of the needs of dynamic SQL. If an application

has no dynamic SQL then the amount of memory can simply be measured after the
application has run for a while by just selecting it out of the shared pool as
follows:

select sum(sharable_mem) from v$sqlarea;

If the application has a moderate or large amount of dynamic SQL like most applications do, then a certain amount of memory will be needed for the shared
SQL, plus more for the dynamic SQL, and more so that the dynamic SQL does not
age the shared SQL out of the shared pool.

The amount of memory for the shared SQL can be approximated by the following:

select sum(sharable_mem) from v$sqlarea where executions > 5;

The remaining memory in v$sqlarea is for dynamic SQL Some shared pool will need to be budgeted for this also, but there are few rules here.

C) PER-USER PER-CURSOR MEMORY You will need to allow around 250 bytes of memory in the shared pool per concurrent user for each open cursor that the user has whether the cursor is

shared or not. During the peak usage time of the production system, you can

measure this as follows:

select sum(250 * users_opening) from v$sqlarea;

In a test system you can measure it by selecting the number of open cursors for a test user and multiplying by the total number of users:

select 250 * value bytes_per_user
from v$sesstat s, v$statname n

   where s.statistic# = n.statistic# 
    and  n.name = 'opened cursors current' 
    and  s.sid  =  23;    -- replace 23 with session id of user being

measured

The per-user per-cursor memory is one of the classes of memory that shows up

as
'library cache' in v$sgastat.

D) MTS If you are using multi-threaded server, then you will need to allow enough memory for all the shared server users to put their session memory in the shared pool. This can be measured for one user with the following query:

select value sess_mem
from v$sesstat s, v$statname n

   where s.statistic# = n.statistic# 
    and  n.name = 'session uga memory' 
    and  s.sid  =  23;    -- replace 23 with session id of user being

measured

A more conservative value to use is the maximum session memory that was ever allocated by the user:

select value sess_max_mem
from v$sesstat s, v$statname n

   where s.statistic# = n.statistic# 
    and  n.name = 'session uga memory max' 
    and  s.sid  =  23;    -- replace 23 with session id of user being

measured

To select this value for all the currently logged on users the following query
can be used:

  select sum(value) all_sess_mem
    from v$sesstat s, v$statname n
   where s.statistic# = n.statistic#
    and n.name = 'session uga memory max';

E) OVERHEAD You will need to add a minimum of 30% overhead to the values calculated above
to allow for unexpected and unmeasured usage of the shared pool.

4) FINAL COMMENTS The most important point that needs to be understood by everyone using Oracle7
and PL/SQL (prior to release 7.3) is that all large PL/SQL objects must be made
into packages and those packages must be kept in the shared pool. This point

cannot be over emphasized. Many customers, especially those running a lot of
users, have had terrible performance problems that were completely cleared up
by doing this.

                  APPENDIX I:  Reserved Shared Pool 
                  =================================

RESERVED SPACE FROM THE SHARED POOL
On busy systems, the RDBMS may have difficulty finding a contiguous piece of

memory to satisfy a large request for memory. Because the RDBMS will search

for and free currently unused memory, the search for this large piece of memory
may disrupt the behavior of the share pool, leading to more fragmentation and
poor performance.

RDBMS 7.1.5 allows DBAs to reserve memory within the shared pool to satisfy these large allocations during RDBMS operations such as PL/SQL compilation and
trigger compilation. Smaller objects will not fragment the reserved list, helping to ensure the reserved list will have large contiguous chunks of memory.
Once the memory allocated from the reserved list is freed, it returns to the

reserved list.

The size of the reserved list, as well as the minimum size of the objects that
can be allocated from the reserved list are controlled via init.ora parameters:
shared_pool_reserved_size and shared_pool_reserved_min_alloc.

1.1 shared_pool_reserved_size

The init.ora parameter shared_pool_reserved_size controls the amount of shared_pool_size reserved for large allocations. In order to create a reserved
list, shared_pool_reserved_size must be greater than shared_pool_reserved_min_alloc.

  units  :  bytes 
  default:  0  (no reserved list) 
  minimum:  > shared_pool_reserved_min_alloc 
  maximum:  1/2 shared_pool_size

1.2 shared_pool_reserved_min_alloc

The init.ora parameter shared_pool_reserved_min_alloc controls allocation for
the reserved memory. Only allocations larger than shared_pool_reserved_min_alloc are allowed to allocate space from the reserved
list if a chunk of memory of sufficient size is not found on the shared pool's
free lists.

  units  :  bytes 
  default:  5000 
  minimum:  5000 
  maximum:  < shared_pool_reserved_size

The default value for shared_pool_reserved_min_alloc should be adequate for almost all systems.

2. CONTROLLING SPACE RECLAMATION OF THE SHARED POOL

RDBMS 7.1.5 also provides a new procedure, aborted_request_threshold, in package
dbms_shared_pool, which allows users to set the limit on the size of allocations
allowed to flush the shared pool if the free lists cannot satisfy the request
size.

Before the RDBMS signals the ORA-04031 error, it incrementally flushes unused
objects from the shared pool until there is sufficient memory to satisfy the

allocation request. In most cases, incrementally flushing objects from the shared pool frees enough memory for the allocation to complete succesfully. If
the RDBMS signals an ORA-04031 error, it has flushed all objects currently not
in use on the system without finding a large enough piece of contiguous memory.

On a busy system, the larger the space allocation, the more likely the RDBMS

will signal the ORA-04031 error. Flushing all objects, however, will impact

other users on the system, possibly causing a degradation in performance.

The aborted_request_threshold procedure allows the DBA to localize the impact
the ORA-04031 error to the process that couldn't allocate memory. The procedure
takes a numeric value between 5000 and 2147483647, representing the size, in

bytes, of the threshold.

3. NEW FIXED VIEW V$SHARED_POOL_RESERVED

RDBMS 7.1.5 has a new fixed view to help tune the reserved pool and space within
the shared pool. The name of the new fixed view is V$SHARED_POOL_RESERVED and
has the following columns:

Name                            Null?    Type 
------------------------------- -------- -------------- 
FREE_SPACE                               NUMBER 
AVG_FREE_SIZE                            NUMBER 
FREE_COUNT                               NUMBER 
MAX_FREE_SIZE                            NUMBER 
USED_SPACE                               NUMBER 
AVG_USED_SIZE                            NUMBER 
USED_COUNT                               NUMBER 
MAX_USED_SIZE                            NUMBER 
REQUESTS                                 NUMBER 
REQUEST_MISSES                           NUMBER 
LAST_MISS_SIZE                           NUMBER 
MAX_MISS_SIZE                            NUMBER 
REQUEST_FAILURES                         NUMBER 
LAST_FAILURE_SIZE                        NUMBER 
ABORTED_REQUEST_THRESHOLD                NUMBER 
ABORTED_REQUESTS                         NUMBER 
LAST_ABORTED_SIZE                        NUMBER

These columns of V$SHARED_POOL_RESERVED are only valid if the parameter shared_pool_reserved_size is set to a valid value.

FREE_SPACE is the total amount of free space on the reserved list.

AVG_FREE_SIZE is the average size of the free memory on the reserved list.

FREE_COUNT is the number of free pieces of memory on the reserved list.

MAX_FREE_SIZE is the size of the largest free piece of memory on the reserved

                list.

USED_SPACE is the total amount of used memory on the reserved list.

AVG_USED_SIZE is the average size of the of the used memory on the reserved

                list.

USED_COUNT is the number of used pieces of memory on the reserved list.

MAX_USED_SIZE is the size of the largest used piece of memory on the reserved

                list. 

  REQUESTS is the number of times that the reserved list was searched for a 
           free piece of memory.

REQUEST_MISSES is the number of times the reserved list didn't have a free

                 piece of memory to satisfy the request, and proceeded to 
start 
                 flushing objects from the LRU list.

LAST_MISS_SIZE is the request size of the last REQUEST_MISS.

MAX_MISS_SIZE is the request size of the largest REQUEST_MISS.

The next set of columns contain values which are valid even if shared_pool_reserved_size is not set.

REQUEST_FAILURES is the number of times that no memory was found to satisfy a

                   request (example: number of times ORA-04031 occurred) 

  LAST_FAILURE_SIZE is the request size of the last failed request 
                    (example: the request size of last ORA-04031).

ABORTED_REQUEST_THRESHOLD is the minimum size of a request which will signal

                            an ORA-04031 error without flushing objects. See


                            the procedure aborted_request_threshold 
described 
                            above.

LAST_ABORTED_SIZE is the last size of the request which returned an ORA-04031

                    error without flushing objects from the LRU list.

4. TUNING HINTS BASED ON V$SHARED_POOL_RESERVED

Information in V$SHARED_POOL_RESERVED can help to set values for shared_pool_reserved_size and even shared_pool_size. This section assumes the
DBA has performed all other shared pool tuning on his system.

4.1 Initial Value for shared_pool_reserved_size

The DBA should make shared_pool_reserved_size 10% of the shared_pool_size. For
most systems, this value should be sufficient, if the DBA has already spent time
tuning the shared pool.

4.2 Initial Value for shared_pool_reserved_min_alloc

In most cases, the default value for this parameter is adequate. If the DBA increases this value, then the RDBMS will allow fewer allocations from the reserved list and will request more memory from the shared pool list.

4.4 Tuning shared_pool_reserved_size

Ideally, shared_pool_reserved_size should be made large enough to satisfy any
request scanning for memory on the reserved list without flushing objects from
the shared pool. The amount of operating system memory, however, may constrain
the size of the SGA, and therefore the size of the shared pool such that this
is not a feasible goal.

If the DBA has a system with ample free memory to increase his SGA, the goal

is
to have:

REQUEST_MISS = 0 If the DBA is constrained for OS memory, his goal is:

REQUEST_FAILURES = 0 or not increasing LAST_FAILURE_SIZE > shared_pool_reserved_min_alloc AVG_FREE_SIZE > shared_pool_reserved_min_alloc

If neither of these goals are met, increase shared_pool_reserved_size; the DBA
also needs to increase shared_pool_size by the same amount, since the reserved
list is taken from the shared pool.

4.5 shared_pool_reserved_size too low

The reserved pool is too small when:

REQUEST_FAILURES > 0 (and increasing)

and at least one of the following is true:

   LAST_FAILURE_SIZE > shared_pool_reserved_min_alloc 
   MAX_FREE_SIZE     < shared_pool_reserved_min_alloc 
   FREE_MEMORY       < shared_pool_reserved_min_alloc

The DBA has two options, depending on his SGA size constraints:

o Increase shared_pool_reserved_size and shared_pool_size, accordingly o Increase shared_pool_reserved_min_alloc (but may need to increase

shared_pool_size)

The first option will increase the amount of memory available on the reserved
list without impacting users not allocating memory from the reserved list. The
second options reduces the number of allocations allowed to use memory from the
reserved list; doing so, however, will increase normal shared pool perhaps impacting other users on the system.

4.6 shared_pool_reserved_size too high

It is possible that too much memory has been allocated to the reserved list.

If:

   REQUEST_MISS      = 0 or not increasing 
   FREE_MEMORY       = > 50% of shared_pool_reserved_size minimum

The DBA has two options:

o Decrease shared_pool_reserved_size o Decrease shared_pool_reserved_min_alloc (if not the default

value)

4.7 shared_pool_size too small

The new fixed table can also indicate when shared_pool_size is too small. If:

REQUEST_FAILURES > 0 and increasing LAST_FAILURE_SIZE < shared_pool_reserved_min_alloc

Then the DBA has two options if he has enabled the reserved list:

o Decrease shared_pool_reserved_size o Decrease shared_pool_reserved_min_alloc (if set larger than the default)

Otherwise, the DBA the could:

o Increase shared_pool_size

                  APPENDIX 2:  Procedure free_unused_memory 
                  =========================================

This text is also in the specification for this procedure in dbmsutil.sql. It is part of package dbms_session.

Procedure free_unused_memory --

Procedure for users to reclaim unused memory after performing operations requiring large amounts of memory (where large is >100K). Note that this procedure should only be used in cases where memory is at a premium.

Examples operations using lots of memory are:

o large sorts where entire sort_area_size is used and

sort_area_size is hundreds of KB
o compiling large PL/SQL packages, procedures, or functions o storing hundreds of KB of data within PL/SQL indexed tables

One can monitor user memory by tracking the statistics "session uga memory" and
"session pga memory" in the v$sesstat/v$statname fixed views. Monitoring these
statistics will also show how much memory this procedure has freed. The behavior
of this procedure depends upon the configuration of the server operating on behalf of the client:

o dedicated server - returns unused PGA memory to the OS o MTS server - returns unused session memory to the shared_pool

In order to free memory using this procedure, the memory must not be in use.

Once an operation allocates memory, only the same type of operation can reuse
the allocated memory. For example, once memory is allocated for sort, even if
the sort is complete and the memory is no longer in use, only another sort can
reuse the sort-allocated memory. For both sort and compilation, after the operation is complete, the memory is no longer in use and the user can invoke
this procedure to free the unused memory.

An indexed table implicitly allocates memory to store values assigned to the

indexed table's elements. Thus, the more elements in an indexed table, the more
memory the RDBMS allocates to the indexed table. As long as there are elements
within the indexed table, the memory associated with an indexed table is in use.

The scope of indexed tables determines how long their memory is in use. Indexed
tables declared globally are indexed tables declared in packages or package bodies. They allocate memory from session memory. For an indexed table declared globally, the memory will remain in use for the lifetime of a user's
login (lifetime of a user's session), and is freed after the user disconnects
from ORACLE.

Indexed tables declared locally are indexed tables declared within functions,
procedures, or anonymous blocks. These indexed tables allocate memory from PGA
memory. For an indexed table declared locally, the memory will remain in use
for as long as the user is still executing the procedure, function, or anonymous
block in which the indexed table is declared. After the procedure, function, or
anonymous block is finished executing, the memory is then available for other
locally declared indexed tables to use (i.e., the memory is no longer in use).

Assigning an uninitialized, "empty," indexed table to an existing index table is
a method to explicitly re-initialize the indexed table and the memory associated
with the indexed table. After this operation, the memory associated with the
indexed table will no longer be in use, making it available to be freed by calling this procedure. This method is particularly useful on indexed tables
declared globally which can grow during the lifetime of a user's session, as

long as the user no longer needs the contents of the indexed table.

The memory rules associated with an indexed table's scope still apply; this method and this procedure, however, allow users to intervene and to explictly
free the memory associated with an indexed table.

The PL/SQL fragment below illustrates the method and the use of procedure free_unused_user_memory.

create package foobar
type number_idx_tbl is table of number indexed by binary_integer;

   store1_table  number_idx_tbl;     --  PL/SQL indexed table 
   store2_table  number_idx_tbl;     --  PL/SQL indexed table 
   store3_table  number_idx_tbl;     --  PL/SQL indexed table 
   ... 
 end;            --  end of foobar

declare
   ...
   empty_table number_idx_tbl; -- uninitialized ("empty") version begin
   for i in 1..1000000 loop
     store1_table(i) := i; -- load data    end loop;
   ...
   store1_table := empty_table; -- "truncate" the indexed table    ...
   -
   dbms_session.free_unused_user_memory; -- give memory back to system

   store1_table(1) := 100;           --  index tables still declared; 
   store2_table(2) := 200;           --  but truncated.

...
end;

Phil West - Impex IT ltd
Unix Sys Admin and Oracle Financials DBA Services

all opinions are worth [at most] what you paid for them

***
The information in this Internet e-mail is confidential and may be legally privileged. It is intended solely for the addressee. Access to this Internet e-mail by anyone else is unauthorised and any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.

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_01C00DB7.2663CD68
Content-Type: text/html;
	charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>RE: Who and What is using the shared pool.</TITLE>

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

Received on Thu Aug 24 2000 - 05:19:14 CDT