RE: Who and What is using the shared pool.

Hi Lisa/Phil,
 Please search Metalink for db_object_cache. You will find a number of responses related to this 'puzzle. This 'executions' colum from this view was to be dropped as of 7.3.3. Oracle is apparantly still working on it. At least they now stopped putting info into this column with Oracle 8... may be with Oracle 9.x it will go away completely... Sorry for the bad news.... - Kirti
> -----Original Message-----
> From: Philip West [SMTP:P.West_at_g-icap.com]
> Sent: Thursday, August 24, 2000 11:52 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Who and What is using the shared pool.
>
> Lisa,
>
> I am baffled. I do not have access to 8.x at the moment. Is there anyone
> out there who can help Lisa?
>
>
> 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: 24 August 2000 16:52
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Who and What is using the shared pool.
>
>
> Hi Phil,
>
> 8.1.5 on HP/UX 11.0. Check it out below. I figured I would have to
> look at executions to determine if something that is constantly being
> reloaded really needs to be pinned. Seems to me if it's being executed a
> lot, and reloaded a lot, it should be pinned - and if it's being reloaded
> a lot but not executed more than once per load, it's not worth the effort.
>
>
> Thanks in advance for any comments.
> Lisa
>
> SQL> select startup_time from v$instance;
>
> STARTUP_T
> ---------
> 02-AUG-00
>
> 1 select owner || '.' || name OBJECT
> 2 , type
> 3 , to_char(sharable_mem/1024,'9,999.9') "SPACE(K)"
> 4 , loads
> 5 , executions execs
> 6 , kept
> 7 from v$db_object_cache
> 8 where type in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE')
> 9 and owner not in ('SYS')
> 10* order by loads desc
> SQL> /
>
> OBJECT TYPE SPACE(K)
> LOADS EXECS KEP
> ---------------------------------------- --------------- --------
> --------- --------- ---
> QODE_LOAD.F_VALIDATE_ISBN FUNCTION 1.9
> 31 0 NO
> QODE_BC.PKG_GN_SPECIAL_OFFERS PACKAGE BODY 12.3
> 29 0 NO
> QODE_APP.CLEANUP_PARTNERNAME_STG2 PROCEDURE 2.8
> 25 0 NO
> QODE_APP.PKG_GENERATE_KEYWORD PACKAGE BODY 3.1
> 21 0 NO
> QODE_APP.PKG_UTIL PACKAGE 28.0
> 17 0 NO
> QODE_APP.PKG_CLEANUP PACKAGE BODY 9.3
> 17 0 NO
> QODE_BC.PKG_GN_SPECIAL_OFFERS PACKAGE 11.3
> 16 0 NO
> QODE_BC.CATHIERARCHY PACKAGE 1.7
> 15 0 NO
> QODE_BC.RPT_PARTNER_EMAIL PACKAGE 1.7
> 15 0 NO
> QODE_BC.PARTNER1 PACKAGE 1.7
> 15 0 NO
> QODE_APP.P_GUESTHOME PROCEDURE 73.7
> 15 0 NO
> QODE_USER.WEBDBTEST012345678912 PROCEDURE 1.7
> 15 0 NO
> QODE_BC.PARTRELATIONSHIP PACKAGE 1.2
> 15 0 NO
> QODE_BC.PARTNER_RELATIONSHIP1 PACKAGE 1.7
> 15 0 NO
> QODE_BC.CATHIERARCHY2 PACKAGE 1.7
> 15 0 NO
> QODE_APP.P_BUYQODER PROCEDURE 38.8
> 15 0 NO
> QODE_LOAD.PKG_LOAD PACKAGE BODY 1.4
> 15 0 NO
> QODE_APP.P_LOGIN PROCEDURE 75.2
> 15 0 NO
> QODE_BC.PARTNER9 PACKAGE 1.6
> 15 0 NO
> QODE_APP.PKG_SPECIALOFFERS_OBJ PACKAGE 35.6
> 14 0 NO
> QODE_APP.P_SIGN_UP_ERROR_VALIDATE PROCEDURE 17.6
> 13 0 NO
> QODE_BC.PARTNER9 PACKAGE BODY 1.4
> 13 0 NO
> QODE_LOAD.PKG_PRODUCT_CATEGORY PACKAGE BODY 2.6
> 13 0 NO
> QODE_APP.PKG_LIST_MAINT PACKAGE 21.8
> 13 0 NO
> QODE_BC.CATEGORY PACKAGE BODY 1.7
> 13 0 NO
>
> -----Original Message-----
> From: Philip West [mailto:P.West_at_g-icap.com]
> Sent: Thursday, August 24, 2000 7:27 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Who and What is using the shared pool.
>
>
> If your database has been up and running for a while I am
> VERY suprised that executions are 0 across the board. This is the way
> that I would always check for pinning candidates. Maybe someone else on
> the list could shed light. WHat versions are you running.
>
> Phil West - Impex IT ltd
> Unix Sys Admin and Oracle Financials DBA Services
>
> all opinions are worth [at most] what you paid for them
>
> -----Original Message-----
> From: Koivu, Lisa [mailto:lkoivu_at_qode.com]
> Sent: 23 August 2000 21:58
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Who and What is using the shared pool.
>
>
>
> Thanks Philip for sending this to the list.
>
> However now I see that executions in
> v$db_object_cache is 0 everywhere. Is there an easy way to join to
> v$sqlarea to v$db_object_cache? I guess I was hoping to see if any
> heavily-used objects were being reloaded frequently and are candidates for
> pinning.
>
> Anyone?? Thanks
> Lisa
> Ft. Lauderdale, FL, USA
> *O*fficially *C*rabby and *P*eeved
>
> -----Original Message-----
> From: Philip West [ <mailto:P.West_at_g-icap.com>]
> 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.
>
> 1) MEMORY FRAGMENTATION
>
> The primary problem that occurs is that free memory
> in the shared pool
> becomes
> fragmented into small pieces over time. Any attempt
> to allocate a large
> piece
> of memory in the shared pool will cause large amount
> of objects in the
> library
> cache to be flushed out and may result in an
> ORA-04031 out of shared memory
> error.
>
> A) DIAGNOSIS OF FRAGMENTATION
>
> i) ORA-04031 ERROR
>
> One way to diagnose that this is happening is to
> look for ORA-04031 errors
> being returned from applications. When an attempt
> is made to allocate a
> large
> contiguous piece of shared memory, and not enough
> contiguous memory can be
> created in the shared pool, the database will signal
> this error.
>
> Before this error is signalled, all objects in the
> shared pool that are not
> currently in use will be flushed from the shared
> pool, and their memory will
> be
> freed and merged. This error only occurs when there
> is still not a large
> enough contiguous piece of free memory after this
> happens. There may be
> very
> large amounts of total free memory in the shared
> pool, but just not enough
> contiguous memory.
>
> ii) INIT.ORA PARAMETER
>
> An init.ora parameter can be set so that whenever an
> ORA-04031 error is
> signalled a dump will occur into a trace file. By
> looking for these trace
> files, the DBA can determine that these errors are
> occurring. This is
> useful
> when applications do not always report errors
> signalled by oracle, or if
> users
> do not report the errors to the DBAs. The parameter
> is the following:
>
> event = "4031 trace name errorstack"
>
> If you are using 7.0.16 or higher you can use the
> following:
>
> event = "4031 trace name errorstack level 4"
>
> This will cause a dump of the Oracle state objects
> to occur when this error
> is
> signalled. By looking in the dump for 'load=X' and
> then looking up a few
> lines
> for 'name=' you can often tell whether an object was
> being loaded into the
> shared pool when this error occurred. If an object
> was being loaded then it
> is
> likely that this load is the cause of the problem
> and the object should be
> 'kept' in the shared pool. The object being loaded
> is the object printed
> after
> the 'name='. Do not use the 'level 4' option in
> versions before 7.0.16
> because
> a bug existed that often caused the system to crash
> with this option enabled
>
> due to a latch level violation.
>
> Prior to version 7.3, there were a handful of cases
> where the RDBMS or
> PL/SQL
> would attempt to allocate large pieces of contiguous
> memory. Most of this
> has
> been fixed for 7.3. This problem was especially
> acute when running MTS, when
>
> the UGA would be located in the SGA. This should
> also be fixed in 7.3 and
> using
> MTS for a high OLTP scenario is recommended. As a
> result of all these
> changes,
> the ORA-04031 error should be virtually eliminated.
> If an ORA-04031 error is
> signalled, quite likely the shared pool is over 90%
> utilized and the
> alternative is to increase the shared pool. The only
> known situation is
> PL/SQL
> packages (like STANDARD) where the package contains
> a very large number
> (over
> 400) procedure/function definitions. This still
> needs to be in contiguous
> memory and may request memory chunks as large as
> 15K. Packages like this
> should
> be the only ones that should be kept.
>
> iii) X$KSMLRU
>
> There is a fixed table called x$ksmlru that
> tracks allocations in the
> shared pool that cause other objects in the shared
> pool to be aged out.
> This
> fixed table can be used to identify what is
> causing the large allocation.
>
> The columns of this fixed table are the following:
>
> KSMLRCOM - allocation comment that describes the
> type of allocation.
>
> If this comment is something like 'MPCODE' or
> 'PLSQL%' then there is a large
>
> PL/SQL object being loaded into the shared pool.
> This PL/SQL object will
> need
> to be 'kept' in the shared pool.
>
> If this comment is 'kgltbtab' then the allocation is
> for a dependency table
> in
> the library cache. This is only a problem when
> several hundred users are
> logged
> on using distinct user ids. The solution in this
> case is to use fully
> qualified
> names for all table references.
>
> If you are running MTS and the comment is something
> like 'Fixed UGA' then
> the
> problem is that the init.ora parameter
> 'open_cursors' is set too high.
>
> KSMLRSIZ - amount of contiguous memory being
> allocated. Values over around
> 5K
> start to be a problem, values over 10K are a serious
> problem, and values
> over
> 20K are very serious problems. Anything less then
> 5K should not be a
> problem.
>
> KSMLRNUM - number of objects that were flushed from
> the shared pool in order
> allocate the memory.
>
> In release 7.1.3 or later, the following columns
> also exist:
>
> KSMLRHON - the name of the object being loaded into
> the shared pool if the
> object is a PL/SQL object or a cursor.
>
> KSMLROHV - hash value of object being loaded
>
> KSMLRSES - SADDR of the session that loaded the
> object.
>
> The advantage of X$KSMLRU is that it allows you to
> identify problems with
> fragmentation that are effecting performance, but
> that are not bad enough to
> be
> causing ORA-04031 errors to be signalled. If a lot
> of objects are being
> periodically flushed from the shared pool then this
> will cause response time
>
> problems and will likely cause library cache latch
> contention problems when
> the
> objects are reloaded into the shared pool. With
> version 7.2, the library
> cache
> latch contention should be significantly reduced
> with the breaking up of the
>
> library cache pin latch into a configurable set of
> symmetric library cache
> latches.
>
> One unusual thing about the x$ksmlru fixed table is
> that the contents of the
>
> fixed table are erased whenever someone selects from
> the fixed table. This
> is
> done since the fixed table stores only the largest
> allocations that have
> occurred. The values are reset after being selected
> so that subsequent
> large
> allocations can be noted even if they were not quite
> as large as others that
>
> occurred previously. Because of this resetting, the
> output of selecting
> from
> this table should be carefully noted since it cannot
> be reselected if it is
> forgotten. Also you should take care that there are
> not multiple people on
> one
> database that select from this table because only
> one of them will select
> the
> real data.
>
> To monitor this fixed table just run the following:
>
> select * from x$ksmlru where ksmlrsiz > 5000;
>
> iv) MTS
>
> Oracle users using SQL*Net V2 can connect to the
> database using dedicated
> servers, or multiple clients can use a pool of
> shared (or MTS) servers. The
> biggest memory implication of this mode is that the
> session memory (also
> known
> as the UGA) for every session needs to be accessible
> to every MTS server.
> This
> implies that the logical UGA comes out of the
> physical SGA (or the shared
> pool) instead of the PGA (process memory).
>
> In versions prior to 7.3, there were a few
> components in the UGA that would
> request large contiguous chunks of memory,
> contributing to fragmentation of
> the shared pool if using MTS. If the system had been
> up for a while, users
> would have failures when attempting to connect or
> executing sql. Starting
> with
> 7.3, all these allocations have been segmented such
> that the average size of
> memory chunks allocated to the UGA should be about
> 5K.
>
> B) CORRECTION OF FRAGMENTATION
>
> i) KEEPING OBJECTS
>
> The primary source of problems is large PL/SQL
> objects. The means of
> correcting
> these errors is to 'keep' large PL/SQL object in the
> shared pool at startup
> time. This will load the objects into the shared
> pool and will make sure
> that
> the objects are never aged out of the shared pool.
> If the objects are never
> aged out then there will not be a problem with
> trying to load them and not
> having enough memory.
>
> Objects are 'kept' in the shared pool using the
> dbms_shared_pool package
> that
> is defined in the dbmspool.sql file. For example:
>
> execute dbms_shared_pool.keep('SYS.STANDARD');
>
> All large packages that are shipped should be 'kept'
> if the customer uses
> PL/SQL. This includes 'STANDARD', 'DBMS_STANDARD',
> and 'DIUTIL'. With 7.3,
> the
> only package left in this list is 'STANDARD'.
>
> All large customer packages should also be marked
> 'kept'.
>
> To mark all packages in the system 'kept' execute
> the following:
>
> declare
> own varchar2(100);
> nam varchar2(100);
> cursor pkgs is
> select owner, object_name
> from dba_objects
> where object_type = 'PACKAGE';
> begin
> open pkgs;
> loop
> fetch pkgs into own, nam;
> exit when pkgs%notfound;
> dbms_shared_pool.keep(own || '.' || nam, 'P');
> end loop;
> end;
>
> The dbms_shared_pool package was introduced in 7.0
> and has evolved over the
> versions. Until 7.1.5, 'keep' could only be used for
> packages. Starting with
>
> 7.1.6, this was extended to standalone procedures,
> cursors as well as
> triggers.
> For detailed usage instructions, see the
> dbmspool.sql file. So, prior to
> this
> version, if you have large procedures or large
> anonymous blocks, then these
> will need to be put into packages and marked kept.
> With 7.3, most packages
> do
> not need to be kept any longer since PL/SQL no
> longer requires large amounts
> of
> contiguous memory to load packages/procedures in
> memory.
>
> You can determine what large stored objects are in
> the shared pool by
> selecting
> from the v$db_object_cache fixed view. This will
> also tell you which
> objects
> have been marked kept. This can be done with the
> following query:
>
> select * from v$db_object_cache where
> sharable_mem > 10000;
>
> Note that this query will not catch PL/SQ: objects
> that are only rarely used
>
> and therefore the PL/SQL object is not currently
> loaded in the shared pool.
>
> To determine what large PL/SQL objects are currently
> loaded in the shared
> pool
> and are not marked 'kept' and therefore may cause a
> problem, execute the
> following:
>
> select name, sharable_mem
> from v$db_object_cache
> where sharable_mem > 10000
> and (type = 'PACKAGE' or type = 'PACKAGE BODY'
> or type = 'FUNCTION'
> or type = 'PROCEDURE')
> and kept = 'NO';
>
> Another approach to the above is to use the
> dbms_shared_pool.sizes
> procedure.
> To use this in SQLDBA:
>
> set serveroutput on;
> execute dbms_shared_pool.sizes(10);
>
> This should show you the names of all the objects in
> the shared pool that
> take
> more that 10K of memory as well as if they are
> marked kept or not. For SQL
> statements, if there are multiple versions of a
> query (usually a bug if the
> count is more than 3), they will also be indicated
> in parenthesis. Use the
> following query to check for problems:
>
> select sql_text, loaded_versions, version_count,
> sharable_mem
> from v$sqlarea where loaded_versions > 3
> order by sharable_mem;
>
> In Oracle7.3 onwards the best candidates for keeping
> can be seen by querying
>
> the table X$KSMSP to see if there are any chunks in
> the shared-pool that
> have
> the KSMCHSIZ larger than 5K and KSMCHCOM like
> '%PL/SQL%'. If so then one can
>
> identify the object name and owner of this chunk
> using the following SQL:
>
> select distinct
>
> decode(kglobtyp,0,'CURSOR',7,'PROCEDURE',8,'FUNCTION',9,'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.
>
> A) FREE MEMORY
>
> One fallacy is that the amount of 'free memory'
> reported in v$sgastat needs
> to
> be kept high. This is incorrect. The free memory
> reported in this table is
> not
> like the free memory reported by operating system
> statistics. Since the
> shared
> pool acts as a cache, nothing will ever be aged out
> of the shared pool until
> all
> the free memory has been used up. This is entirely
> normal.
>
> Free memory is more properly thought of as 'wasted
> memory'. You would
> rather
> see this value be low than very high. In fact, a
> high value of free memory
> is
> sometimes a symptom that a lot of objects have been
> aged out of the shared
> pool
> and therefore the system is experiencing
> fragmentation problems.
>
> B) FLUSH SHARED POOL
>
> Some people think that frequently executing 'alter
> system flush shared_pool'
> improves the performance of the system and decreases
> the amount of
> fragmentation. This is incorrect. Executing this
> statement causes a big
> spike
> in performance and does nothing to improve
> fragmentation.
>
> The only time when it might be useful to run this
> statement is between
> shifts of
> users so that the objects that are relevant to the
> last shift of users can
> be
> flushed out before the next shift of users starts to
> use the system. This
> is
> almost never needed though.
>
>
> *************************************************************************
>
> 3) SIZING OF SHARED POOL
>
> One very difficult judgement that needs to be make
> in Oracle7 is to
> determine
> the proper size of the shared pool. The following
> provides some guidelines
> for
> this. It should be emphasized that these are just
> guidelines, there are no
> hard and fast rules here and experimentation will be
> needed to determine a
> good
> value.
>
> The shared pool size is highly application
> dependent. To determine the
> shared
> pool size that will be needed for a production
> system it is generally
> necessary
> to first develop the application and run it on a
> test system and take some
> measurements. The test system should be run with a
> very large value for the
>
> shared pool size to make the measurements
> meaningful.
>
> A) OBJECTS STORED IN THE DATABASE
>
> The amount of shared pool that needs to be allocated
> for objects that are
> stored in the database like packages and views is
> easy to measure. You can
> just measure their size directly with the following
> statement:
>
> select sum(sharable_mem) from v$db_object_cache;
>
> This is especially effective because all large
> pl/sql object should be
> 'kept'
> in the shared pool at all times.
>
> B) SQL
>
> The amount of memory needed to store SQL statements
> in the shared pool is
> more
> difficult to measure because of the needs of dynamic
> SQL. If an application
>
> has no dynamic SQL then the amount of memory can
> simply be measured after
> the
> application has run for a while by just selecting it
> out of the shared pool
> as
> follows:
>
> select sum(sharable_mem) from v$sqlarea;
>
> If the application has a moderate or large amount of
> dynamic SQL like most
> applications do, then a certain amount of memory
> will be needed for the
> shared
> SQL, plus more for the dynamic SQL, and more so that
> the dynamic SQL does
> not
> age the shared SQL out of the shared pool.
>
> The amount of memory for the shared SQL can be
> approximated by the
> following:
>
> select sum(sharable_mem) from v$sqlarea where
> executions > 5;
>
> The remaining memory in v$sqlarea is for dynamic SQL
> Some shared pool will
> need to be budgeted for this also, but there are few
> rules here.
>
> C) PER-USER PER-CURSOR MEMORY
>
> You will need to allow around 250 bytes of memory in
> the shared pool per
> concurrent user for each open cursor that the user
> has whether the cursor is
>
> shared or not. During the peak usage time of the
> production system, you can
>
> measure this as follows:
>
> select sum(250 * users_opening) from v$sqlarea;
>
> In a test system you can measure it by selecting the
> number of open cursors
> for a test user and multiplying by the total number
> of users:
>
> select 250 * value bytes_per_user
> from v$sesstat s, v$statname n
> where s.statistic# = n.statistic#
> and n.name = 'opened cursors current'
> and s.sid = 23; -- replace 23 with session
> id of user being
> measured
>
> The per-user per-cursor memory is one of the classes
> of memory that shows up
> as
> 'library cache' in v$sgastat.
>
> D) MTS
>
> If you are using multi-threaded server, then you
> will need to allow enough
> memory for all the shared server users to put their
> session memory in the
> shared pool. This can be measured for one user with
> the following query:
>
> select value sess_mem
> from v$sesstat s, v$statname n
> where s.statistic# = n.statistic#
> and n.name = 'session uga memory'
> and s.sid = 23; -- replace 23 with session
> id of user being
> measured
>
> A more conservative value to use is the maximum
> session memory that was
> ever allocated by the user:
>
> select value sess_max_mem
> from v$sesstat s, v$statname n
> where s.statistic# = n.statistic#
> and n.name = 'session uga memory max'
> and s.sid = 23; -- replace 23 with session
> id of user being
> measured
>
> To select this value for all the currently logged on
> users the following
> query
> can be used:
>
> select sum(value) all_sess_mem
> from v$sesstat s, v$statname n
> where s.statistic# = n.statistic#
> and n.name = 'session uga memory max';
>
> E) OVERHEAD
>
> You will need to add a minimum of 30% overhead to
> the values calculated
> above
> to allow for unexpected and unmeasured usage of the
> shared pool.
>
>
> *************************************************************************
>
> 4) FINAL COMMENTS
>
> The most important point that needs to be understood
> by everyone using
> Oracle7
> and PL/SQL (prior to release 7.3) is that all large
> PL/SQL objects must be
> made
> into packages and those packages must be kept in the
> shared pool. This point
>
> cannot be over emphasized. Many customers,
> especially those running a lot
> of
> users, have had terrible performance problems that
> were completely cleared
> up
> by doing this.
>
>
> APPENDIX I: Reserved Shared Pool
> =================================
>
> 1. RESERVED SPACE FROM THE SHARED POOL
> ======================================
> On busy systems, the RDBMS may have difficulty
> finding a contiguous piece of
>
> memory to satisfy a large request for memory.
> Because the RDBMS will search
>
> for and free currently unused memory, the search for
> this large piece of
> memory
> may disrupt the behavior of the share pool, leading
> to more fragmentation
> and
> poor performance.
>
> RDBMS 7.1.5 allows DBAs to reserve memory within the
> shared pool to satisfy
> these large allocations during RDBMS operations such
> as PL/SQL compilation
> and
> trigger compilation. Smaller objects will not
> fragment the reserved list,
> helping to ensure the reserved list will have large
> contiguous chunks of
> memory.
> Once the memory allocated from the reserved list is
> freed, it returns to the
>
> reserved list.
>
> The size of the reserved list, as well as the
> minimum size of the objects
> that
> can be allocated from the reserved list are
> controlled via init.ora
> parameters:
> shared_pool_reserved_size and
> shared_pool_reserved_min_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;
>
> Copyright (c) 1995,1999 Oracle Corporation. All
> Rights Reserved. Legal
> Notices and Terms of Use.
>
>
> Phil West - Impex IT ltd
> Unix Sys Admin and Oracle Financials DBA Services
>
> all opinions are worth [at most] what you paid for
Received on Thu Aug 24 2000 - 12:57:03 CDT