ORA-4031 Common Analysis/Diagnostic Scripts [Video] (文档 ID 430473.1)

In this Document

Purpose

Requirements

Configuring

Instructions

Sample Code

General Analysis of the Shared Pool and ORA-4031 Problems

Sample Output

Community discussion

References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

PURPOSE

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

Note:
If you would like to explore this topic further, please join the Community discussion ' Diagnosing and Resolving ORA-4031 errors' where you can ask questions, get help from others, and share your experiences with this specific article.

REQUIREMENTS

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

Click here [modified 4-12-2013] to download the scripts discussed in this article.

 Video - Standardized Diagnostics for ORA-4031 (15:00) 

CONFIGURING

See the remarks with each script to determine if there are changes indicated for specific configuration / application environments.   Cursor Analysis can be done using a streamlined approach in Note 438755.1

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.

INSTRUCTIONS

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 sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

SAMPLE CODE

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.

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 Note:146599.1Diagnosing and Resolving Error ORA-04031 with caution.

SAMPLE 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. 

"Reserved Pool Misses" and "Shared Pool Misses" 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.

NOTE:  You can remark out the last query of the report on v$sql_shared_memory when using this report to monitor Shared Pool workload.  This last query is most useful
when do diagnostics on ongoing ORA-4031 errors.

Shared
   Pool                 Failed            Reserved             Reserved          Reserved     
   Miss                    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

ALLOC_CL AVERAGE     MAXIMUM
------------- --------------- -------------
freeabl                       64.73            4,184
recr                      4,131.71            4,152
free                      1,916.80            4,056

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 

.
.
.

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 

...

 

 

COMMUNITY DISCUSSION

Community discussion For further discussion or questions about topics in this article, please visit 'Diagnosing and Resolving ORA-4031 errors'.

REFERENCES

NOTE:1355030.1 - How To Troubleshoot ORA-4031's and Shared Pool Issues With Procwatcher
NOTE:146599.1 - Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools [Video]
NOTE:396940.1 - Troubleshooting and Diagnosing ORA-4031 Error [Video]
NOTE:459694.1 - Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes
NOTE:438755.1 - High SQL Version Counts - Script to determine reason(s)
NOTE:778.1 - Troubleshooting Video Issues in MOS

附件

• Various Scripts for analysis(44.63 KB)
• Standardized Diagnostics for ORA-4031(1.94 MB)

相关内容

产品

• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Generic issues-rollback redo logs db creation

关键字

4031; DYNAMIC ADV TROUBLESHOOT; FRAGMENTATION; MEMORY USAGE; PERFORMANCE; SHARED POOL; SHOW SGA

错误

ORA-4031