| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Who and What is using the shared pool.
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
Subject: TUNING THE SHARED POOL AND RESOLVING ORA-4031
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 12-SEP-1997
Last Revision Date: 23-MAY-2000
Language: USAENG
PURPOSE
This document discusses some of the common issues associated with the
shared
pool in Oracle7 and describes how to diagnose and respond to these issues.
RELATED DOCUMENTS
[NOTE:1012049.6] TUNING LIBRARY CACHE LATCH CONTENTION
With Release 7.2 and 7.3, changes have been made to reduce usage of shared
memory as well as per-user (UGA) memory. Also, memory is not being
allocated
in large contiguous chunks, resulting in better shared-pool utilization and
reduction in fragmentation.
Before this error is signalled, all objects in the shared pool that are not
currently in use will be flushed from the shared pool, and their memory will
be
freed and merged. This error only occurs when there is still not a large
enough contiguous piece of free memory after this happens. There may be
very
large amounts of total free memory in the shared pool, but just not enough
contiguous memory.
ii) INIT.ORA PARAMETER
An init.ora parameter can be set so that whenever an ORA-04031 error is
signalled a dump will occur into a trace file. By looking for these trace
files, the DBA can determine that these errors are occurring. This is
useful
when applications do not always report errors signalled by oracle, or if
users
do not report the errors to the DBAs. The parameter is the following:
event = "4031 trace name errorstack"
If you are using 7.0.16 or higher you can use the following:
event = "4031 trace name errorstack level 4"
This will cause a dump of the Oracle state objects to occur when this error
is
signalled. By looking in the dump for 'load=X' and then looking up a few
lines
for 'name=' you can often tell whether an object was being loaded into the
shared pool when this error occurred. If an object was being loaded then it
is
likely that this load is the cause of the problem and the object should be
'kept' in the shared pool. The object being loaded is the object printed
after
the 'name='. Do not use the 'level 4' option in versions before 7.0.16
because
a bug existed that often caused the system to crash with this option enabled
due to a latch level violation.
Prior to version 7.3, there were a handful of cases where the RDBMS or
PL/SQL
would attempt to allocate large pieces of contiguous memory. Most of this
has
been fixed for 7.3. This problem was especially acute when running MTS, when
the UGA would be located in the SGA. This should also be fixed in 7.3 and
using
MTS for a high OLTP scenario is recommended. As a result of all these
changes,
the ORA-04031 error should be virtually eliminated. If an ORA-04031 error is
signalled, quite likely the shared pool is over 90% utilized and the
alternative is to increase the shared pool. The only known situation is
PL/SQL
packages (like STANDARD) where the package contains a very large number
(over
400) procedure/function definitions. This still needs to be in contiguous
memory and may request memory chunks as large as 15K. Packages like this
should
be the only ones that should be kept.
iii) X$KSMLRU
There is a fixed table called x$ksmlru that tracks allocations in the
shared pool that cause other objects in the shared pool to be aged out.
This
fixed table can be used to identify what is causing the large allocation.
The columns of this fixed table are the following:
KSMLRCOM - allocation comment that describes the type of allocation.
If this comment is something like 'MPCODE' or 'PLSQL%' then there is a large
PL/SQL object being loaded into the shared pool. This PL/SQL object will
need
to be 'kept' in the shared pool.
If this comment is 'kgltbtab' then the allocation is for a dependency table
in
the library cache. This is only a problem when several hundred users are
logged
on using distinct user ids. The solution in this case is to use fully
qualified
names for all table references.
If you are running MTS and the comment is something like 'Fixed UGA' then
the
problem is that the init.ora parameter 'open_cursors' is set too high.
KSMLRSIZ - amount of contiguous memory being allocated. Values over around
5K
start to be a problem, values over 10K are a serious problem, and values
over
20K are very serious problems. Anything less then 5K should not be a
problem.
KSMLRNUM - number of objects that were flushed from the shared pool in order allocate the memory.
In release 7.1.3 or later, the following columns also exist:
KSMLRHON - the name of the object being loaded into the shared pool if the object is a PL/SQL object or a cursor.
KSMLROHV - hash value of object being loaded
KSMLRSES - SADDR of the session that loaded the object.
The advantage of X$KSMLRU is that it allows you to identify problems with
fragmentation that are effecting performance, but that are not bad enough to
be
causing ORA-04031 errors to be signalled. If a lot of objects are being
periodically flushed from the shared pool then this will cause response time
problems and will likely cause library cache latch contention problems when
the
objects are reloaded into the shared pool. With version 7.2, the library
cache
latch contention should be significantly reduced with the breaking up of the
library cache pin latch into a configurable set of symmetric library cache latches.
One unusual thing about the x$ksmlru fixed table is that the contents of the
fixed table are erased whenever someone selects from the fixed table. This
is
done since the fixed table stores only the largest allocations that have
occurred. The values are reset after being selected so that subsequent
large
allocations can be noted even if they were not quite as large as others that
occurred previously. Because of this resetting, the output of selecting
from
this table should be carefully noted since it cannot be reselected if it is
forgotten. Also you should take care that there are not multiple people on
one
database that select from this table because only one of them will select
the
real data.
To monitor this fixed table just run the following:
select * from x$ksmlru where ksmlrsiz > 5000;
iv) MTS
Oracle users using SQL*Net V2 can connect to the database using dedicated
servers, or multiple clients can use a pool of shared (or MTS) servers. The
biggest memory implication of this mode is that the session memory (also
known
as the UGA) for every session needs to be accessible to every MTS server.
This
implies that the logical UGA comes out of the physical SGA (or the shared
pool) instead of the PGA (process memory).
In versions prior to 7.3, there were a few components in the UGA that would
request large contiguous chunks of memory, contributing to fragmentation of
the shared pool if using MTS. If the system had been up for a while, users
would have failures when attempting to connect or executing sql. Starting
with
7.3, all these allocations have been segmented such that the average size of
memory chunks allocated to the UGA should be about 5K.
B) CORRECTION OF FRAGMENTATION i) KEEPING OBJECTS
The primary source of problems is large PL/SQL objects. The means of
correcting
these errors is to 'keep' large PL/SQL object in the shared pool at startup
time. This will load the objects into the shared pool and will make sure
that
the objects are never aged out of the shared pool. If the objects are never
aged out then there will not be a problem with trying to load them and not
having enough memory.
Objects are 'kept' in the shared pool using the dbms_shared_pool package
that
is defined in the dbmspool.sql file. For example:
execute dbms_shared_pool.keep('SYS.STANDARD');
All large packages that are shipped should be 'kept' if the customer uses
PL/SQL. This includes 'STANDARD', 'DBMS_STANDARD', and 'DIUTIL'. With 7.3,
the
only package left in this list is 'STANDARD'.
All large customer packages should also be marked 'kept'.
To mark all packages in the system 'kept' execute the following:
declare
own varchar2(100);
nam varchar2(100);
cursor pkgs is
select owner, object_name
from dba_objects
where object_type = 'PACKAGE';
The dbms_shared_pool package was introduced in 7.0 and has evolved over the versions. Until 7.1.5, 'keep' could only be used for packages. Starting with
7.1.6, this was extended to standalone procedures, cursors as well as
triggers.
For detailed usage instructions, see the dbmspool.sql file. So, prior to
this
version, if you have large procedures or large anonymous blocks, then these
will need to be put into packages and marked kept. With 7.3, most packages
do
not need to be kept any longer since PL/SQL no longer requires large amounts
of
contiguous memory to load packages/procedures in memory.
You can determine what large stored objects are in the shared pool by
selecting
from the v$db_object_cache fixed view. This will also tell you which
objects
have been marked kept. This can be done with the following query:
select * from v$db_object_cache where sharable_mem > 10000;
Note that this query will not catch PL/SQ: objects that are only rarely used
and therefore the PL/SQL object is not currently loaded in the shared pool.
To determine what large PL/SQL objects are currently loaded in the shared
pool
and are not marked 'kept' and therefore may cause a problem, execute the
following:
select name, sharable_mem
from v$db_object_cache
where sharable_mem > 10000
and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
or type = 'PROCEDURE')
and kept = 'NO';
Another approach to the above is to use the dbms_shared_pool.sizes
procedure.
To use this in SQLDBA:
set serveroutput on;
execute dbms_shared_pool.sizes(10);
This should show you the names of all the objects in the shared pool that
take
more that 10K of memory as well as if they are marked kept or not. For SQL
statements, if there are multiple versions of a query (usually a bug if the
count is more than 3), they will also be indicated in parenthesis. Use the
following query to check for problems:
select sql_text, loaded_versions, version_count, sharable_mem from v$sqlarea where loaded_versions > 3 order by sharable_mem;
In Oracle7.3 onwards the best candidates for keeping can be seen by querying
the table X$KSMSP to see if there are any chunks in the shared-pool that
have
the KSMCHSIZ larger than 5K and KSMCHCOM like '%PL/SQL%'. If so then one can
identify the object name and owner of this chunk using the following SQL:
select distinct
decode(kglobtyp,0,'CURSOR',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',
11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY','OTHER')
||' - '||kglnaown||'.'||kglnaobj "Eligible PL/SQL objects"
from x$kglob
where kglobhd4 in
(select ksmchpar from x$ksmsp
where ksmchcom='PL/SQL MPCODE' and ksmchsiz>5120)
If you are 'keeping' PL/SQL objects today and migrate to 7.3 or higher there
is
no need to re-assess the list of objects that you are keeping.
ii) USE BIND VARIABLES
One of the best things that can be done to reduce the amount of
fragmentation
is to reduce or eliminate the number of sql statements in the shared pool
that
are duplicates of each other except for a constant that is embedded in the
statement. The statements should be replaced with one statement that uses
a bind variable instead of a constant.
For example:
select * from emp where empno=1; select * from emp where empno=2; select * from emp where empno=3;
Should all be replaced with:
select * from emp where empno=:1;
You can identify statements that potentially fall into this class with a
query
like the following:
select substr(sql_text, 1, 30) sql, count(*) copies
from v$sqlarea
group by substr(sql_text, 1, 30)
having count(*) > 3;
iii) MAX BIND SIZE
It is possible for a SQL statement to not be shared because the max bind
variable lengths of the bind variables in the statement do not match. This
is
automatically taken care of for precompiler programs and forms programs, but
could be a problem for programs that directly use OCI. The bind call in OCI
takes two arguments, one is the max length of the value, and the other is a
pointer to the actual length. If the current length is always passed in as
the
max length instead of the max possible length for the variable, then this
could
cause the SQL statement not to be shared.
To identify statements that might potentially have this problem execute the following statement:
select sql_text, version_count from v$sqlarea where version_count > 5;
Starting with 7.1.6 this should no longer be an issue as the server can
graduate bind buffers even when the user's max bind lengths are jumping up
or
down and continue to share cursors that are built for larger buffer lengths
and flush the smaller sql compilation from the shared pool.
iv) ELIMINATING LARGE ANONYMOUS PL/SQL
Large anonymous PL/SQL blocks should be turned into small anonymous PL/SQL
blocks that call packaged functions. The packages should be 'kept' in
memory.
For version earlier that 7.3, this includes anonymous PL/SQL blocks that are
used for trigger definitions. With 7.3, triggers are compiled and stored to
disk like standalone procedures and should be treated as such. Large
anonymous
blocks can be identified with the following query:
select sql_text from v$sqlarea
where command_type=47 -- command type for anonymous block
and length(sql_text) > 500;
Note that this query will not catch PL/SQL blocks that are only rarely used
and
therefore the PL/SQL block is not currently loaded in the shared pool.
Another option that can be used when an anonymous block cannot be turned
into
a package is to mark the anonymous block with some string so that it can be
identified in v$sqlarea and marked 'kept'.
For example, instead of using:
declare x number; begin x := 5; end;;
you can use:
declare /* KEEP_ME */ x number; begin x := 5; end;
You can then use the following procedure to select these statements out of
the
shared pool and mark them 'kept' using the dbms_shared_pool.keep package.
declare
/* DONT_KEEP_ME */
addr varchar2(10);
hash number;
cursor anon is
select address, hash_value
from v$sqlarea
where command_type = 47 -- command type for anonymous
block
and sql_text like '% KEEP_ME %'
and sql_text not like '%DONT_KEEP_ME%';
begin
v) REDUCING USAGE
Another way to reducing fragmentation is to reduce consumption. This is of
special importance when using MTS, when every user's session memory is in
the
shared pool and the impact is multiplied by the total concurrent users.
Insert, update, delete and anonymous blocks complete the execution in one
round
trip. All the memory that is allocated on the server for the execute comes
from
the PGA and is freed before the call returns to the user. But in the case of
selects, memory required to execute the statement - which could be large if
a
sort was involved - is not freed until the end-of-fetch is reached or the
query
is cancelled. In these situations using the OCI features to do an exact
fetch
and cancel helps free memory back to the pool.
If the application logic has been embedded into server side PL/SQL, a large
number of cursors may be getting cached on the server for every user. Though
this results in reduced latch contention and faster response, it does use
more
memory in the UGA. Setting the close_cached_open_cursors init.ora to TRUE
closes the PL/SQL cached cursors on the server, freeing the memory.
2) COMMON FALLACIES There are a number of common fallacies about the shared pool that are often stated as fact.
Free memory is more properly thought of as 'wasted memory'. You would
rather
see this value be low than very high. In fact, a high value of free memory
is
sometimes a symptom that a lot of objects have been aged out of the shared
pool
and therefore the system is experiencing fragmentation problems.
B) FLUSH SHARED POOL
Some people think that frequently executing 'alter system flush shared_pool'
improves the performance of the system and decreases the amount of
fragmentation. This is incorrect. Executing this statement causes a big
spike
in performance and does nothing to improve fragmentation.
The only time when it might be useful to run this statement is between
shifts of
users so that the objects that are relevant to the last shift of users can
be
flushed out before the next shift of users starts to use the system. This
is
almost never needed though.
3) SIZING OF SHARED POOL
One very difficult judgement that needs to be make in Oracle7 is to
determine
the proper size of the shared pool. The following provides some guidelines
for
this. It should be emphasized that these are just guidelines, there are no
hard and fast rules here and experimentation will be needed to determine a
good
value.
The shared pool size is highly application dependent. To determine the
shared
pool size that will be needed for a production system it is generally
necessary
to first develop the application and run it on a test system and take some
measurements. The test system should be run with a very large value for the
shared pool size to make the measurements meaningful.
select sum(sharable_mem) from v$db_object_cache;
This is especially effective because all large pl/sql object should be
'kept'
in the shared pool at all times.
B) SQL
The amount of memory needed to store SQL statements in the shared pool is
more
difficult to measure because of the needs of dynamic SQL. If an application
has no dynamic SQL then the amount of memory can simply be measured after
the
application has run for a while by just selecting it out of the shared pool
as
follows:
select sum(sharable_mem) from v$sqlarea;
If the application has a moderate or large amount of dynamic SQL like most
applications do, then a certain amount of memory will be needed for the
shared
SQL, plus more for the dynamic SQL, and more so that the dynamic SQL does
not
age the shared SQL out of the shared pool.
The amount of memory for the shared SQL can be approximated by the following:
select sum(sharable_mem) from v$sqlarea where executions > 5;
The remaining memory in v$sqlarea is for dynamic SQL Some shared pool will need to be budgeted for this also, but there are few rules here.
C) PER-USER PER-CURSOR MEMORY You will need to allow around 250 bytes of memory in the shared pool per concurrent user for each open cursor that the user has whether the cursor is
shared or not. During the peak usage time of the production system, you can
measure this as follows:
select sum(250 * users_opening) from v$sqlarea;
In a test system you can measure it by selecting the number of open cursors for a test user and multiplying by the total number of users:
select 250 * value bytes_per_user
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'opened cursors current'
and s.sid = 23; -- replace 23 with session id of user being
measured
The per-user per-cursor memory is one of the classes of memory that shows up
as
'library cache' in v$sgastat.
D) MTS If you are using multi-threaded server, then you will need to allow enough memory for all the shared server users to put their session memory in the shared pool. This can be measured for one user with the following query:
select value sess_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session uga memory'
and s.sid = 23; -- replace 23 with session id of user being
measured
A more conservative value to use is the maximum session memory that was ever allocated by the user:
select value sess_max_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session uga memory max'
and s.sid = 23; -- replace 23 with session id of user being
measured
To select this value for all the currently logged on users the following
query
can be used:
select sum(value) all_sess_mem
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'session uga memory max';
E) OVERHEAD
You will need to add a minimum of 30% overhead to the values calculated
above
to allow for unexpected and unmeasured usage of the shared pool.
4) FINAL COMMENTS
The most important point that needs to be understood by everyone using
Oracle7
and PL/SQL (prior to release 7.3) is that all large PL/SQL objects must be
made
into packages and those packages must be kept in the shared pool. This point
cannot be over emphasized. Many customers, especially those running a lot
of
users, have had terrible performance problems that were completely cleared
up
by doing this.
APPENDIX I: Reserved Shared Pool
=================================
memory to satisfy a large request for memory. Because the RDBMS will search
for and free currently unused memory, the search for this large piece of
memory
may disrupt the behavior of the share pool, leading to more fragmentation
Received on Mon Aug 21 2000 - 11:11:36 CDT
 |
 |