ORA-4031 Common Analysis/Diagnostic Scripts

:	430473.1	类型:	SCRIPT
	上次修订日期:	22-JUL-2009	状态:	PUBLISHED

In this Document
  
  
  
  
  
  
     
  
  

---

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.6
Information in this document applies to any platform.

Purpose

This article is intended to standarize the use of scripts to diagnose and analyze an ORA-4031 error.   This article is intended for all Database Administrators and Oracle Support Analysts.

Software Requirements/Prerequisites

The scripts below can be run in SQL*Plus or iSQL*Plus.   Many of the scripts require DBA privileges in the databse.

Click [modified 7-22-2009] to download the scripts discussed in this article.

Click [modified 4-22-2009] to download the RAC versions of the scripts discussed in this article.

Configuring the Script

See the remarks with each script to determine if there are changes indicated for specific configuration / application environments.

NOTE:   Some of the scripts are provided above for RAC environments.   These will look at the GV$ views instead of the V$ views to give a complete RAC instance perspective on the analysis.   The non-RAC specific scripts are still useful in a RAC environment, but must be run on each node or a problem node.

Running the Script

For the scripts below, it is important to use an administrative database user account that is able to access DBA* and V$ tables.   Any scripts below accessing X$ "dictionary tables" should be used with caution.  

NOTE:  Running scripts against the dictionary frequently and/or during heavy business activity can cause performance impacts and in some cases cause additional ORA-4031 errors.   In worst case, scenarios, these scripts can cause apparent database hangs and database crashes.

Caution

This script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.

Script

ORA-4031 errors can be caused by a number of configuration/tuning issues, application code approaches, cursor usage techniques, or in some cases bugs. In the sections below, we will look at scripts helpful in investigating the first three potential problem areas.

The scripts used to investigate an ORA-4031 error can differ from Oracle release to release. At 10g, with Automatic Memory Management, there are additional scripts needed to investigate tuning options in the SGA.   The script files are provided as attachments to this note.   NOTE:  There can be formatting issues with these scripts when doing a cut and paste from this document.   Therefore, the scripts are attached to the document and can be downloaded for use.   This article will discuss the benefits/uses and provide a sample output for each script.

General Analysis of the Shared Pool and ORA-4031 Problems 

1.  Tuning:

Start with the alert log.   Are there indications of ORA-4031 errors in the alert log?   What was happening in the database around the time of the errors?   Are there patterns for when the errors occur during certain operations?   Where there trace files generated the errors?   Are they consistently failures on large memory requests?   small memory requests?

The following scripts will help in focusing on tuning opportunities.

SGAParameters.sql -- shows the SGA configuration information and parameters affecting the SGA.

SGAComponents.sql -- works on 10.2.x and higher.   Focuses analysis on the auto-tuned components in the SGA and shows activity moving memory around in the SGA.

NOTE:  Seeing constant increases and decreases in the Shared Pool over short periods of time can indicate that minimum settings for the SGA auto-tuned components are needed.   In some releases of 10g, auto-tuning can get over-aggressive in moving memory around in the SGA at times without minimum settings defined.

CursorEfficiency.sql -- shows the HWM information on cursor usage and cached cursor information.  This will help in determining if OPEN_CURSORS and SESSION_CACHED_CURSORS are over or under allocated.

SGAStat.sql -- shows sorted data from sgastat for the Shared Pool.   This can be changed to provide data on the other pools in the SGA by changing the

       where pool='shared pool'

to

       where pool=''

Note:  This query will work on 8i/9i/9.2.x/10g.   You can gather the data over time and look for trends in memory growth over time.

Trendsgastats.sql -- provides trend data through statistics gathered by default with the Automatic Workload Repository (AWR) in a comma delimited file for loading into a spreadsheet program.

NOTE:  This query works with AWR and 10.2.x.

PoolAdvice.sql -- provides estimated parse time savings at different Shared Pool sizes.

ReservedAnalysis.sql -- shows the cummulative statistics on memory failures in the Shared Pool and the Reserved Area.


2.  Library Cache Analysis

LibCacheOverview.sql  -- shows general overview information about the Shared Pool and the Library Cache usage. 

NOTE:  The query will work on 9i/9.2.x/10g.  You can change to code to reflect that you are using SGA_TARGET if applicable (looking for pool sizes in hidden parameters).   THIS IS NOT intenteded to provide a script to find a "best" size for the Shared Pool.   You should look at the V$SHARED_POOL_ADVICE view for indicators of a better size for the Shared Pool.

With 10g, a number of fixed memory areas were moved from the SGA into the Shared Pool.   V$SGASTAT was enhanced to provide more detailed data on what memory allocations exist in the Shared Pool.  

PinnedCode.sql -- investigates which objects are pinned into the Library Cache?   NOTE:   These queries will work on 9i/9.2.x/10g.  The data provides more detailed information on what objects are pinned and how much memory is needed for them.

LCObjectsStats.sql -- reports statistics on pinned and non-pinned code.

3.  Fragmentation Issues

Fragmentation issues can be difficult to diagnose and often fall into the application tuning arena.  These queries can help in identifying suggested areas of improvements, but ultimately tuning application code is outside the scope of Oracle Support.

HardParses.sql -- provides data on code in the Library Cache that was executed only once.   Hard parses are expensive inside the Library Cache.   It is not possible to eliminate all hard parses, but it is important to watch the 'per second' numbers for hard parses in your hourly AWR / Statspack reports.   Periods of very high (in the hundreds or even thousands per second) should be investigated.

NOTE:  In environments which tends to have a lot of code that is executed only once where tuning opportunities in the application are unlikely, be sure to investigate settings for OPEN_CURSORS and SESSION_CACHED_CURSORS.   Having these values too high will cause these code pieces to stay around in the Shared Pool longer than needed.

PinCandidates.sql -- lists code that may be pinning candidates.   You can change the query to look for code with large 'sharable_mem' and/or a high value for 'executions'.   Larger code pieces that are executed over and over may still get flushed out of the Library Cache over time.   Pinning the code will keep the code in memory for future connections that need the object.

SQLCursInfo.sql -- reports data in the Library Cache on objects that are not shared and provides reasons for sharing problems.   For example, setting optimizer related parameters at the session level can cause multiple versions of a code piece.  The latest version (7/29/08) allows you to specify the level of reporting by indicating the percentage of problems to focus on.  Example, .8 would look at the top 20% of problem code based on number of copies of the code in the Library Cache.   The lower the percentage used the smaller the bigger the result set reported.

NOTE:   There is a bug on 9.2, 10.1, and 10.2 where you can get a PL/SQL length overflow error with this PL/SQL code.

DECLARE
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS

In those cases, use the non-PL/SQL version of the non-shared cursor sql code instead,  SQLCursorInfo<version>.sql.

TrendsLC.sql -- provides trend data through statistics gathered by default with the Automatic Workload Repository (AWR) in a comma delimited file for loading into a spreadsheet program.   This provides data on Reloads and on Invalidations.

NOTE; This query works with AWR and 10.2.x and higher.

SQLStats.sql -- a starting point to investigate statistics about code in the Library Cache.

NOTE:  If 'Versions HWM' is not showing an large number, then further research into copies (versions) may not be necessary.

SQLVersions.sql -- helps to investigate SQL with multiple copies (versions) in the Library Cache.  These versions are to be expected in many cases, but excessive copies (versions) can indicate a problem.   Version_count > 5 is just a starting point.  In cases with excessive versions of code, it would be appropriate to increase the condition in the query to focus on the problem code and ignore cases where a handful of versions of the code are expected.   You can use the SQLStats.sql code to find a better starting point for your query.

NOTE:  Objects in the Library Cache will have Parent objects and Child objects associated with those parents.   The copies (child objects) will be created for a number of reasons and is not an automatic indication there are problems in the Library Cache.

The second script investigates variances between V$SQLAREA and V$SQL_SHARED_CURSOR with respect to "copies"/versions of the code in the Library Cache.   There have been bugs on some Oracle releases where an excessive variance pointed to problems with child cursors.

Literals.sql -- find code using literals that will be executed fewer times (not shared).   Investigating different settings for CURSOR_SHARING may help to tune the Library Cache to handle the literal values more efficiently.  Modified Jul 15 2008

Fragmentation analysis can impact performance in the Shared Pool.   It is important to run analysis scripts as found in Diagnosing and Resolving Error ORA-04031 with caution.

Script Output

Sample output for each of the queries above is shown.

SGAParameters.sql (need to run appropriate code for release level)

Lists regular and hidden parameters that indicate how the SGA is configured.

REM  This script is useful to Oracle Support as it focuses on the principle
REM  parameters associated with the configuration of the SGA

Setting                                                                        MBytes
------------------------------------------------------ -----------
16K Cache: 0                                                                           0
2K Cache: 0                                                                             0
32K Cache: 0                                                                           0
4K Cache: 0                                                                             0
8K Cache: 0                                                                             0
Buffer Cache: 0                                                                         0   < ideal to set minimum
Java Pool Size: 0                                                                       0
Keep Cache: 0                                                                          0
Large Pool Size: 0                                                                     0
Log Buffer: 7024640                                                                 7
Recycle Cache: 0                                                                      0
SGA Max: 734003200                                                         700
SGA Target: 612368384                                                      584   < if using auto-tuning
Shared Pool Size: 0                                                                   0   < ideal to set a minimum
Streams Pool Size: 0                                                                  0

15 rows selected.

Setting
-----------------------------------------------------------------
DB Files: 200
Open Cursors: 300   < larger values require more memory for the Library Cache
Processes: 150
Session Cached Cursors: 25
Sessions: 170

Setting
------------------------------
Cache Advice: ON
Compatible: 10.2.0.1.0
Cursor Sharing: EXACT
Query Rewrite: TRUE
Statistics Level: TYPICAL   < necessary for internal analysis to auto-tune

Resource                         Current             HWM               Setting
------------------------- ---------------- ---------------- ---------------
processes                                           25                     26                   150
sessions                                              29                     30                   170
enqueue_locks                                    21                     28                 2300
enqueue_resources                             21                      21                   968
ges_procs                                            0                         0                       0
dml_locks                                             0                        8                     748
max_shared_servers                              2                        2      UNLIMITED

14 rows selected.

Double underscore parameters below are the "current" auto-tuned size of the component.

Parameter                                      Session Value                  Instance Value
----------------------------------- ------------------------- -------------------------
_4031_dump_bitvec                      67194879                      67194879
_4031_max_dumps                               3600                              3600
_PX_use_large_pool                         FALSE                           FALSE
__db_cache_size                         503316480                    503316480
_shared_pool_reserved_pct                           5                                    5
event                                       10235 trace name context 10235 trace name context
                                                        forever, level 65536          forever, level 65536

SGAComponents.sql (need to run appropriate code for release level)

REM  Important information for investigating the efficiency with auto-tuning
REM  Most problems with auto-tuning will show up in sections 3 and 4 of this report
REM 
REM  In this sample, the last couple of sessions are not listed.   These show high and low
REM  memory usage over time and a breakdown of SGA information from V$SGAINFO
REM 
REM  Trends to be watching for:
REM  Are there no rows of data showing auto-tuning is making memory changes?
REM  Are there periods of time where one component grows and grows and then it shrinks and shrinks
REM  as the original component grows and grows?   This points to "fighting" for memory
REM  Are there repeated rows with STATUS='ERROR'
REM
REM  These cases indicate that SGA_TARGET is too small
REM
REM  Caveat:  Memory moves go through the default Buffer Cache.   So a move required in the
REM  Streams Pool may take memory from 1) the Shared Pool, and then 2) to the Buffer Cache, and
REM  then 3) to the Streams Pool.   Therefore, excess memory within SGA_TARGET that is not
REM  needed within components in the SGA will collect in the default Buffer Cache.   In other words,
REM  as the SGA stabilizes, the default Buffer Cache can be oversized.


COMPONENT             Current Size         Min Size         Max Size Granule Size
----------------- ---------------- ---------------- ---------------- ---------
shared pool            285,212,672      276,824,064                0 8,388,608
large pool               8,388,608        8,388,608                0 8,388,608
java pool                8,388,608        8,388,608                0 8,388,608
streams pool                     0                0                0 8,388,608
DEFAULT buffer cache   754,974,720      754,974,720                0 8,388,608
KEEP buffer cache                0                0                0 8,388,608
RECYCLE buffer cache             0                0                0 8,388,608
DEFAULT 2K buffer cache          0                0                0 8,388,608
DEFAULT 4K buffer cache          0                0                0 8,388,608
DEFAULT 8K buffer cache          0                0                0 8,388,608
DEFAULT 16K buffer cache         0                0                0 8,388,608
DEFAULT 32K buffer cache         0                0                0 8,388,608
ASM Buffer Cache                 0                0                0 8,388,608 
                  ----------------
sum                  1,056,964,608

COMPONENT                Type            Mode            Timestamp
------------------------ --------------- --------------- --------------------
shared pool              GROW            IMMEDIATE       08/12/2007 11:42:32
large pool               STATIC
java pool                STATIC
streams pool             STATIC
DEFAULT buffer cache     SHRINK          IMMEDIATE       08/12/2007 11:42:32
KEEP buffer cache        STATIC
RECYCLE buffer cache     STATIC
DEFAULT 2K buffer cache  STATIC
DEFAULT 4K buffer cache  STATIC
DEFAULT 8K buffer cache  STATIC
DEFAULT 16K buffer cache STATIC
DEFAULT 32K buffer cache STATIC
ASM Buffer Cache         STATIC

COMPONENT           . . .  Final       Status   Changed At
------------------         ----------- -------- -------------------
shared pool                276,824,064 COMPLETE 08/12/2007 11:40:24
large pool                   8,388,608 COMPLETE 08/12/2007 11:40:24
DEFAULT buffer cache       763,363,328 COMPLETE 08/12/2007 11:40:24
java pool                    8,388,608 COMPLETE 08/12/2007 11:40:24
streams pool                         0 COMPLETE 08/12/2007 11:40:24
DEFAULT buffer cache       763,363,328 COMPLETE 08/12/2007 11:40:24
KEEP buffer cache                    0 COMPLETE 08/12/2007 11:40:24
RECYCLE buffer cache                 0 COMPLETE 08/12/2007 11:40:24
.
.
.
ASM Buffer Cache                     0 COMPLETE 08/12/2007 11:40:24
DEFAULT buffer cache       754,974,720 COMPLETE 08/12/2007 11:42:32
shared pool                285,212,672 COMPLETE 08/12/2007 11:42:32

CursorEfficiency.sql

                                                 Max
REM Best used to investigate the efficiency of open_cursors
REM
REM session_cache_cursors can show 100% usage even though the parameter
REM is undersized.   This parameter is not necessary in many environments.
REM
REM The goal with open_cursors is to get the usage percentage up close to 100%


Init Parameter                           Limit   Usage
---------------------------------------- ------- ---------
session_cached_cursors                        25      100%
open_cursors                                 300       19%

SGAStat.sql

Excellent query to gather trend data prior to 10g.   For 10g, see Trendsgastats.sql.

REM  This data is for a point-in-time.  It is necessary to gather this data at intervals to
REM  build trend data for where memory is growing and stabilizing

free memory                    23,273,108 
sql area                       12,810,916 
library cache   
                7,691,788 
ASH buffers                     4,194,304 
KCB Table Scan Buffer           3,981,204 
KSFD SGA 
I/O b                  3,977,128 
row cache                       3,741,868 
KQR M SO                        3,621,804 
CCursor 
                        3,308,648 
. 
. 
. 
                         ---------------- 
Tot                           114,520,848

Trendsgastats.sql

You can load the text data into a spreadsheet program and build graphs for those entries that showed the most growth over time.  Not all memory allocations are tracked over time in the AWR, but any information listed that has stabilized across time can be ignored.   Focus instead on those areas in the Shared Pool that are growing and shrinking over time.

REM   If using AWR, this report is more efficient.   You run it once and it will show trend data
REM  for the previous day (hourly from midnight to midnight).

"ASH buffers"             4194304 4194304 4194304 4194304 4194304 4194304 4194304 ...
"PL/SQL MPCODE" 3618268 3618268 3618268 3618268 3618268 3618268 3618268 ...
"Heap0: KGL"            1386948 1386948 1386948 1386948 1392216 1392216 1392216 ...
"KQR M PO"             3148028 3148028 3148028 3148028 3148540 3148540 3148540 ...
"KTI-UNDO"             1235304 1235304 1235304 1235304 1235304 1235304 1235304 ...
"PCursor"                    2524836 2524836 2528000 2528000 2533268 2533268 2533268 ...
.
.
.

PoolAdvice.sql 

REM  The same data you will see in the AWR for the Shared Pool Advisor.
REM  There is a point of diminishing returns here.   The Shared Pool can work more efficiently
REM  at different settings.   However, the estimation data here can be influenced if the database
REM activity changes dramatically from time to time.

.
.
.
600       .8                             8,429,359
675       .9                             8,688,205
750        1                             8,934,125
825       1.1                           9,012,899
.
.
.

ReservedAnalysis.sql

This script will assist in analysis over time to find evidence of memory chunk stress in the Shared Pool.
It is safe to run these queries as often as you like.  Large memory misses in the Shared Pool will be attemped in the Reserved Area, but repeated failures in the Reserved Area can cause an ORA-4031 error.

What should you look for? 

Request Pool Misses = 0 can mean the Reserved Area is too big.

Request Pool Misses always increasing but "4031's" not increasing can mean the Reserved Area is too small.  In this case flushes in the Shared Pool satisfied the memory needs and an ORA-4031 was not actually reported to the user.

Request Pool Misses and "4031's" always increasing can mean the Reserved Area is too small and flushes in the Shared Pool are not helping (likely got an ORA-04031).

Failed Size small can indicate fragmentation issues in the Shared Pool.

                                Failed            Reserved             Reserved          Reserved
4031s?                        Size         Free Space                    Max                  Avg
----------- ---------------- ------------------ ---------------- ----------------
              1                    540            5,307,832            212,888            196,586


                                                                          Reserved
               Reserved                      Total                      Pool                      Size of 
                     Used                 Requests                  Misses                 Last Miss
-------------------- -------------------- ------------------- -------------------
                   14,368                             2                            0                           0

LibCacheOverview.sql (need appropriate code for release level)

Provides overview information on the Shared Pool and the Library Cache.


REM  Review pointers/hints on reading the data throughout the report
REM

                       SGA/Shared Pool Breakdown 
                                                 
 *** If database started recently, this data is not as useful *** 
                                                 
Database Started:  Oct/20/2008 08:18:41                 
Instance Name/No:     sneezy-1                          
                                                        
Breakdown of SGA           510.85M                      
   Shared Pool Size          : 264M (52%)  Reserved 12.79M (5%)
   Large Pool                       : 4M (1%)               
   Java Pool                        : 12M (2%)              
   Buffer Cache                     : 220M (43%)            
   Streams Pool                     : 4M (1%)               
   Other Areas in SGA               : 6.85M (1%)            
                                                            
 *** High level breakdown of memory ***                     
                                                            
     sharable                      :  78.31M                
     persistent                    :  74.33M                
     runtime                       :  70.64M                
                                                            
SQL Memory Usage (total)                     : 223.27M (85%)
                                                            
 *** No guidelines on SQL in Library Cache, but if ***      
 *** pinning a lot of code--may need larger Shared Pool *** 
                                              
# of SQL statements                : 3512     
# of pinned SQL statements         : 3        
# of programmatic constructs       : 23531    
# of pinned programmatic construct : 1795     
                                              
Efficiency Analysis:                          
 *** High versions (100s) could be bug ***    
                                              
  Max Child Cursors Found                              : 9     
  Programmatic construct memory size (Kept)            : 42.07M
  Pinned SQL statements memory size (active sessions)  : .21M 
                                                           
 *** LC at 50% or 60% of Shared Pool not uncommon ***      
                                                           
  Estimated Total Library Cache Memory Usage  : 117M (44%) 
  Other Shared Pool Memory                    : 147M       
  Shared Pool Free Memory Chunks              : 35.2M (13%)
                                                         
 ****Ideal percentages for 1 time executions is 20% or lower****      
                                                             
  # of objects executed only 1 time           : 1627 (46%)   
  Memory for 1 time executions:               : 129.89M (58%)
                                                     
  ***If these chunks are growing, SGA_TARGET may be too low***
                                                      
  Current KGH: NO ACCESS Allocations:  0M (0%)        

 ***0 misses is ideal, but if growing value points to memory issues***
                                                 
  # Of Misses for memory                      : 0
  Size of last miss                           : 0
  # Of Misses for Reserved Area               : 3
  Size of last miss Reserved Area             : 16384 
                                                                      


PinnedCode.sql

Lists objects that are pinned into the Library Cache currently. Also provides total memory associated with pinned objects.

REM  Lists the code pieces / objects that are pinned in memory.
REM  The report also sums up the memory currently pinned at the end of
REM  this report. 


Owner        Name                   Type                 Memory Used 
------------ ---------------------- -------------------- -------------------- SYS         COL$                   TABLE                               1,984 
 SYS         CCOL$                  TABLE                               1,877 
. 
. 
.  
 SYS         select trunc           CURSOR                              1,453 
               (sysdate,'HH24')
               +1/24 from dual 
 SYS         SELECT COUNT           CURSOR                              1,453 
             (TARGET_NAME) 
             FROM MGMT_TARGETS 
. 
. 
.
  
Total Pinned Memory 
------------------- 
             49,268  


NOTE: When pinning code in the Shared Pool, it 
is important to know how much memory is needed 
to accommodate the pinned code. Your Shared Pool, 
should be increased by the size of the pinned code 
so that sufficient memory is available to handle 
normal operations in the Shared Pool as well as 
pinned code. 



 

LCObjectsStats.sql

REM  Another way to summarize the memory used in the Shared Pool

KEPT  Type                               MEMORY
----- -------------------- --------------------
NO    CURSOR                         27,300,516
NO    PACKAGE BODY                    2,748,534
NO    PACKAGE                         2,436,437
NO    TABLE                           1,117,134
NO    JAVA CLASS                        927,961
NO    TYPE                              768,688
NO    VIEW                              342,337
NO    INDEX                             320,467

.
.
.
YES   TYPE                           10,972,564
YES   JAVA CLASS                      9,652,635
YES   PACKAGE BODY                    8,172,962
YES   PACKAGE                         7,389,916
YES   TRIGGER                           748,359
.
.
.


HardParses.sql


REM  Focuses on the non-shared code pieces in the Shared Pool.
REM  The Shared Pool is designed for shared memory areas.  Too many
REM  objects/code pieces that are loaded and only executed once, are
REM  "wasting" memory and a high setting for OPEN_CURSORS in this scenario
REM  would slow down our tendency to age these out of memory.

Inst       Hash SQL
---- ---------- ----------------------------------------
   1 3791216038 DELETE FROM xs$session_appns
   1 1863381713 DELETE FROM xs$session_hws
   1 2675026937 DELETE FROM xs$session_roles
   1 3661099134 DELETE FROM xs$sessions
   1 1297772850 SELECT 1 FROM obj$ WHERE name LIKE
                'XS$SESSIONS'

   1 1149513840 SELECT
                mgmt_metric_value_obj.new(target_guid,nu
                ll,null,

                mgmt_namevalue_array(mgmt_namevalue_obj.
                new('GROUP_TARG
                ET_GUID',GROUP_TARGET_GUID),mgmt_nameval
                ue_obj.new('MEMBER_TARGET_TYPE',MEMBER_T
.
.
.


PinCandidates.sql

REM Finding "best" candidates for pinning is outside the scope of Oracle Support. This better
REM fits the services of our Consulting organization.
REM
REM This report is intended to help in identifying what code pieces/objects are loaded in the
REM Shared Pool. Generally, large memory objects that are executed many times are
REM shared more efficiently if pinned.


Owner                  Name         Type           Memory Used
----- --------------------- ------------ ---------------------
SYS             DBMS_OUTPUT      PACKAGE                13,091
SYS   DBMS_APPLICATION_INFO      PACKAGE                12,369
SYS             DBMS_OUTPUT PACKAGE BODY                 6,219

Code Loaded                       Memory Footprint Invalidations   Loads  Executions
------------------------------------ ------------ -------------  -------- --------
SELECT INSTANTIABLE, supertype_owner,     207,455            0         1        189
supertype_name, LOCAL_ATTRIBUTES FROM
all_

.
.
.

Code Loaded                     Memory Footprin t Invalidations       Loads
-------------------------------- ---------------- ------------- ------------
select   LOW_OPTIMAL_SIZE,                 12,845             0            1
HIGH_OPTIMAL_SIZE,
OPTIMAL_EXECUT

UPDATE MGMT_TARGETS SET                    20,864             0            1
LAST_LOAD_TIME=:B2 WHERE TARGET_GUID =
:B1 AND (LAST_LOA

INSERT INTO WRH$_ACTIVE_SESSION_HISTORY     48,328             0            1
( snap_id, dbid, instance_number, sample

.
.
.


SQLCursInfo.sql / SQLCursorInfo.sql in most platforms ( for PL/SQL Bug)

REM If you see indicators of high "versions" or "copies" of objects/
REM code pieces in the Shared Pool (looking at LibCacheOverview output),
REM this is a natural second report to investigate why code is
REM non-shared.
REM
REM Focus on the end of the report first for a summarized listing of
REM the reasons code is not shared.   A search in MySupport/Metalink
REM using the "reason" can sometimes point to a known bug.


.
.
.

select value$ from sys.props$ where name = :1
---------- Not Shared Because of ---------------
No Reasons Indicated
-------------

select value$ from sys.props$ where name = :1
---------- Not Shared Because of ---------------
SQL Type Not Matching Child Cursor Information
Not Shared for 1 reasons
-------------


################## Statistics ##################


########## Rows indicating non-shared code: 66
########## Rows not showing a reason for non-shared code:  16
########## Percent no reason indicated:  24


############# Breakdown of Reasons #############


SQL_TYPE_MISMATCH   3
BIND_MISMATCH   16
EDITION_MISMATCH   12
BIND_UACS_DIFF   18
ROLL_INVALID_MISMATCH   28


TrendsLC.sql


REM  Like the V$SGASTAT information from the AWR statistics,
REM  this report will show hourly data on Library Cache statistics
REM 
REM  The goal is to have the hourly percentage at 10 or lower
REM  

"TABLE/PROCEDURE"                      27         27         27 ...
"CLUSTER"   
"TRIGGER"                               2          2          2 ...
"SQL AREA"                              9          9          9 ...
"INDEX"                                42         42         42 ...
"BODY"                                  2          2          2 ...  

SQLStats.sql

This query reports HWM information about SQL in the Library Cache.  The 'Versions HWM' is a good  starting point to investigate statistics about code in the Library Cache   Updated on 5-21-2008


REM  A breakdown of High Water Mark information for the Shared Pool

====================================
HWM Information:
----- Max Invalidations:                     1
----- Max Versions Loaded:                   4
----- Versions HWM:                          4
----- Largest Memory object:           408,084
====================================

PL/SQL procedure successfully completed. 


SQLVersions.sql

There are versions of this code based on Oracle release level.

Use this code to investigate SQL with multiple copies (versions) in the Library Cache.  Excessive copies (versions) can indicate a problem and should be where you focus your analysis.. Version_count > 5 is just a starting point. In cases with excessive versions of code, it would be appropriate to increase the condition in the query to focus on the problem code and ignore cases where a handful of versions of the code are expected. You can use the SQLStats.sql code to find a better starting point for this query in your environment.

REM  Another way to investigate the "versions" or "copies" of code pieces / objects
REM
REM  The second part of this report is only important in some known bug problems where
REM  V$SQLAREA and V$SQL_SHARED_CURSOR get out of sync in tracking
REM  data in the Library Cache.   In those cases, the "variance" shown in the last column can
REM  grow very high

SQL ID          SQL                            VERSION_COUNT
--------------- ------------------------------ -------------

062savj8zgzut   UPDATE sys.wri$_adv_parameters            22 
                SET datatype = :1,value = :2,
                flags = :3, description = :4
                WHERE task_id = :5 AND name =
                :6                                   

.
.
.

SQL ID        Object in Memory                                        Variance
------------- ------------------------------------------------------- --------
52umjphjycvka select location_name, user#, user_context,                     0
              context_size, presentation,  version, status,
              any_context, context_type, qosflags, payload_callback,
              timeout, reg_id, reg_time, ntfn_grouping_class,
              ntfn_grouping_value,  ntfn_grouping_type,
              ntfn_grouping_start_time, ntfn_grouping_repeat_count
              from reg$ where subscription_name = :1 and  namespace =
              :2  order by location_name, user#, presentation,
              version

47a50dvdgnxc2 update sys.job$ set failures=0, this_date=null,                1
              flag=:1, last_date=:2,  next_date = greatest(:3,
              sysdate),  total=total+(sysdate-nvl(this_date,sysdate))
              where job=:4

2xgubd6ayhyb1 select max(procedure#) from procedureplsql$ where                                     -2
              obj#=:1

38pn2vmg711x1 SELECT INST_SCHEMA, INST_NAME, INST_MODE, INST_SUB_ID          1
              FROM WK$INST_LIST WHERE INST_ID = :B1

...


References

- Diagnosing and Resolving Error ORA-04031
- Troubleshooting and Diagnosing ORA-4031 Error