【ORA-04031】诊断并解决ORA-04031错误

来源：互联网发布：梦幻超级土地公数据编辑：程序博客网时间：2024/05/22 07:05

In this Document

Purpose Troubleshooting Steps 1. Instance parameters related with the Shared Pool 2. Diagnosing error ORA-04031 3. Resolving error ORA-04031 4. ORA-04031 error and Large Pool 5. ORA-04031 and SHARED POOL FLUSHING 6. Advanced analysis to ORA-04031 error Community Discussion References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.7.4 and later
Information in this document applies to any platform.
***Checked for relevance on 24-Feb-2010***

PURPOSE

The purpose of this document is to provide an easy to use, step by step guide to resolving ORA-04031 errors.

In case of conflicting information between note and video presentation, information contained in the note takes precedence over information that may be recorded or shown on video.

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.

TROUBLESHOOTING STEPS

When any attempt to allocate a large piece of contiguous memory in the shared pool fails Oracle first flushes all objects that are not currently in use from the pool and the resulting free memory chunks are merged. If there is still not a single chunk large enough to satisfy the request the ORA-04031 error is returned. NOTE: These errors can occur on an ASM instance as well. The default SHARED_POOL_SIZE should be sufficient in most environments, but can be increased if you are experiencing ORA-04031 errors.

The message that you will get when this error appears is the following:

04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"

// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out of memory, either use the
// dbms_shared_pool package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// INIT.ORA parameters "shared_pool_reserved_size" and
// "shared_pool_size".
// If the large pool is out of memory, increase the INIT.ORA
// parameter "large_pool_size".

1. Instance parameters related with the Shared Pool

Before continuing, understanding the following instance parameters will be essential:

SHARED_POOL_SIZE - This parameter specifies the size of the shared pool in bytes and can accept a numerical values or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000" and "M" means "multiply by 1000000"
SHARED_POOL_RESERVED_SIZE - It specifies the shared pool space which is reserved for large contiguous requests for shared pool memory. This parameter along with the SHARED_POOL_RESERVED_MIN_ALLOC parameter, can be used to avoid the occurrence of this error from situations where shared pool fragmentation forces Oracle to search for and free
chunks of unused pool to satisfy the current request.

Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. Since the operating system memory may constraint the size of the shared pool, in general, you should set this parameter to 10% of the SHARED_POOL_SIZE parameter.
SHARED_POOL_RESERVED_MIN_ALLOC - The value of this parameter controls allocation of reserved memory. Memory allocation larger than this value can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool free lists. The default value is adequate for most systems. If you increase the value, then the Oracle server will allow fewer allocations from the reserved list and will request more memory from the shared pool list. This parameter is hidden in Oracle 8i and higer, but it can be found by executing the following SQL statement:

SELECT nam.ksppinm NAME,
val.ksppstvl VALUE
FROM x$ksppi nam,
x$ksppsv val
WHERE nam.indx = val.indx
AND nam.ksppinm LIKE '%shared%'
ORDER BY 1;

Note: This parameter was obsoleted with 8i. The parameter can still be modified via the underscore parameter _SHARED_POOL_RESERVED_MIN_ALLOC.

10g Note: In Oracle 10g a new feature called "Automatic Shared Memory Management" allows the DBA to reserve a pool of shared memory that is used to allocate the shared pool, the buffer cache, the java pool and the large pool.

In general, when the database needs to allocate a large object into the shared pool and cannot find contiguous space available, it will automatically increase the shared pool size using free space from other SGA structure.

Since the space allocation is automatically managed by Oracle, the probability of getting ora-4031 errors may be greatly reduced. Automatic Shared Memory Management is enabled when the parameter SGA_TARGET is greater than zero and the current setting can be obtained quering the V$SGA_DYNAMIC_COMPONENTS and V$SGA_RESIZE_OPS views.

It is not possible restrict the size of auto-tuned components in the SGA if using SGA_TARGET. If you want to exercise more control on shared pool size, please do not use SGA_TARGET parameter. Using explicit settings for the auto-tuned components is recommended as minimum sizes to keep auto-tuning from being too aggressive moving memory in the SGA.

Please refer to the 10g Administration Manual for further reference.

11g Note: In Oracle 11g a new feature called "Automatic Memory Mmanagement" allows the DBA to configure the database to manage shared memory areas as well as PGA memory.

In general, when the database needs to allocate a large object into the shared pool and cannot find contiguous space available, it will automatically increase the shared pool size using free space from other SGA structure and/or PGA. During times of heavy workload, PGA may automatically increase using free memory from the SGA structures.

Since the space allocation is automatically managed by Oracle, the probability of getting ora-4031 errors may be greatly reduced. Automatic Memory Management is enabled when the parameter MEMORY_TARGET is greater than zero and the current setting can be obtained quering the V$MEMORY_DYNAMIC_COMPONENTS and V$MEMORY_RESIZE_OPS views.

It is not possible restrict the size of auto-tuned components in the SGA if using MEMORY_TARGET. If you want to exercise more control on the components in the SGA or PGA do not use MEMORY_TARGET or SGA_TARGET. SGA_TARGET and explicit settings for the auto-tuned components are recommended as minimum sizes to keep auto-tuning from being too aggressive moving memory for the database workload.

Please refer to the 11g Administration Manual for further reference.

2. Diagnosing error ORA-04031

Note: most common ORA-4031 occurrences are related to the SHARED POOL SIZE, therefore the diagnostic steps provided in this article will mostly address issues related to the shared pool. For other areas like large_pool or java_pool where the memory allocation algorith is simpler, normally the error is caused by an undersized structure.

ORA-04031 error can be due to either an inadequeate sizing of the SHARED POOL size or due to heavy fragmentation leading the database to not finding large enough chuncks of memory. NOTE: There have been many reports where the default size for shared_pool_size on an ASM instance is too small. If you experience ORA-04031 error on your ASM instance, increase the shared_pool_size parameter to 50M. If the problem persists, then increase the parameter again in increments of 10M until you stop seeing the error.

Inadequate Sizing
The first thing is determining if the ORA-04031 error is a result of lack of contiguous space in the library cache by verifying the following from V$SHARED_POOL_RESERVED:
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC
To resolve this consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower the number of objects being cached into the shared pool reserved space and increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the available memory in the shared pool reserved space.

Note: a bug was discoverd where LAST_FAILURE_SIZE can be wrong in cases where multiple pools are used. The value in LAST_FAILURE_SIZE can be a sum of failure sizes across all pools. This is filed in unpublished Bug:3669074 and has been fixed as of 9.2.0.7, 10.1.0.4, and 10.2.x.
Fragmentation
If this is not the case, then you must determine if the ORA-04031 was a result of fragmentation in the library cache or in the shared pool reserved space by following this rule:
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC.
If this is the case, consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to allow the database putting more objects into the shared pool reserved space and then increase the SHARED_POOL_SIZE if the problem is not resolved.

Another consideration: pre-9i, changing OPTIMIZER_MAX_PERMUTATIONS to 2000 can reduce shared pool space pressure.

The above checks are still applicable with ORA-04031 in 10g and beyond with auto-tuning functionality. However, explicitly adjusting the setting for SHARED_POOL_SIZE only changes the auto-tuned "minimum" for the Shared Pool. It is a "best practice" to set an explicit setting but to ensure it is low enough to accommodate memory needs in other places if the auto-tuner has to shrink the Shared Pool to handle workload.

3. Resolving error ORA-04031

Oracle BUGs

Oracle recommends to apply the latest patchset available for your platform. Most of the ORA-4031 errors related to BUGs can be avoided by applying these patchsets. See Document 4031.1 with the latest reports of bugs and patches.

If you run out of memory while compiling a java code (within loadjava or deployejb), you should see an error:

A SQL exception occurred while compiling: : ORA-04031: unable to allocate bytes of shared memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal")

The solution is to shut down the database and set JAVA_POOL_SIZE to a larger value. The mention of "shared pool" in the error message is a misleading reference to running out of memory in the "Shared Global Area". It does not mean you should increase your SHARED_POOL_SIZE. Instead, you must increase your JAVA_POOL_SIZE, restart your server, and try again.

The cause of this problem has been identified in:
unpublished Bug:2736601 - CORRECT ORA-4031 MESSAGE TO FLAG JAVA POOL.
Small shared pool size

In many cases, a small shared pool can be the cause of the ORA-04031 error.

The following information will help you to adjust the size of the shared pool:
- Library Cache Hit Ratio
  The hit ratio helps to measure the usage of the shared pool based on how many times a SQL/PLSQL statement needed to be parsed instead of being reused. The following SQL statement help you to calculate the library cache hit ratio:
  SELECT SUM(pins) "EXECUTIONS",
  SUM(reloads) "CACHE MISSES WHILE EXECUTING"
  FROM v$librarycache;
  
  If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses by increasing the shared pool size.
- Shared Pool Size Calculation
  Scripts used to be available for calculating a "best" size for the Shared Pool. Problems arose over time due to changes to internal memory structures and, in some cases, those older scripts could account for certain memory areas twice presenting you with percentages greater than 100%. There are some adjusted scripts available at:
  Document 430473.1 - ORA-4031 Common Analysis/Diagnostic Scripts [Video]
  
  However, with the introduction of the memory advisors (with 9.2x) and auto-tuning (with 10g Release 2), these estimation scripts are not as useful. See also:
  Document 276103.1 - Performance Tuning Using Advisors and Manageability Features: AWR, ASH, ADDM and Sql Tuning Advisor
Shared Pool Fragmentation:

Every time a SQL or PL/SQL statement needs to be executed the parse representation is loaded in the library cache requiring a specific amount of free contiguous space. The first resource where the database scans is the free memory available in the shared pool. Once the free memory is exhausted, the database looks for reusing an already allocated piece not in use. If a chunk with the exact size is not available, the scan continues looking for space based on the following criteria:
- The chunk size is larger than the required size
- The space is contiguous
- The chunk is available (not in use)
Then that chunk is split and the remaining free space is added to the appropriate free space list. When the database is operating in this way for a certain period of time the shared pool structure will be fragmented.

When the shared pool is suffering fragmentation ORA-04031 errors (when the database cannot find a contiguous piece of free memory) may occur. Also as a concequence, the allocation of a piece of free space takes more time an the performance may be affected (the "chunk allocation" is protected by a single latch called "shared pool latch" which is held during the whole operation). However, ORA-4031 errors don't always affect the performance of the database.

If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a result of dynamic SQL fragmenting the shared pool. This can be caused by:
- Not sharing SQL
- Making unnecessary parse calls (soft)
- Setting SESSION_CACHED_CURSORS too high
- Not using bind variables
To reduce fragmentation you will need to address one or more of the causes described before. In general to reduce fragmentation you must analyze how the application is using the shared pool and maximize the use of sharable cursors.

Please refer to Document 62143.1, which describes these options in greater detail. This note contains as well further detail on how the shared pool works. The following views will help you to identify non-sharable versions of SQL/PLSQL text in the shared pool:
- V$SQLAREA View
  This view keeps information of every SQL statement and PL/SQL block executed in the database. The following SQL can show you statements with literal values or candidates to include bind variables:
  SELECT SUBSTR(sql_text,1,40) "SQL",
  COUNT(*),
  SUM(executions) "TotExecs"
  FROM v$sqlarea
  WHERE executions < 5
  GROUP BY SUBSTR(sql_text,1,40)
  HAVING count(*) > 30
  ORDER BY 2;
  
  Note: The number "30" in the having section of the statement can be adjusted as needed to get more detailed information.
- X$KSMLRU View
  
  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.
  
  If many 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.
  
  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 kept since it cannot be retrieved back after the query is issued.
  
  To monitor this fixed table just run the following:
  SELECT *
  FROM X$KSMLRU
  WHERE ksmlrsiz > 0;
  
  This view can only be queried by connected as the SYS.
- X$KSMSP View (Similar to Heapdump Information)
  
  Using this view you will be able to find out how the free space is currently allocated, which will be helpful to undrestand the level of fragmentation of the shared pool. As it was described before, the first place to find a chunck big enough for the cursor allocation is the free list. The following SQL shows the chunks available in the free list:
  SELECT '0 (<140)' bucket, ksmchcls, ksmchidx, 10*TRUNC(ksmchsiz/10) "From",
         COUNT(*) "Count", MAX(ksmchsiz) "Biggest",
         TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
  FROM x$ksmsp
  WHERE ksmchsiz<140
  AND ksmchcls='free'
  GROUP BY ksmchcls, ksmchidx, 10*TRUNC(ksmchsiz/10)
  UNION ALL
  SELECT '1 (140-267)' bucket, ksmchcls, ksmchidx,20*TRUNC(ksmchsiz/20),
         COUNT(*), MAX(ksmchsiz),
         TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
  FROM x$ksmsp
  WHERE ksmchsiz BETWEEN 140 AND 267
  AND ksmchcls='free'
  GROUP BY ksmchcls, ksmchidx, 20*TRUNC(ksmchsiz/20)
  UNION ALL
  SELECT '2 (268-523)' bucket, ksmchcls, ksmchidx, 50*TRUNC(ksmchsiz/50),
         COUNT(*), MAX(ksmchsiz),
         TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
  FROM x$ksmsp
  WHERE ksmchsiz BETWEEN 268 AND 523
  AND ksmchcls='free'
  GROUP BY ksmchcls, ksmchidx, 50*TRUNC(ksmchsiz/50)
  UNION ALL
  SELECT '3-5 (524-4107)' bucket, ksmchcls, ksmchidx, 500*TRUNC(ksmchsiz/500),
         COUNT(*), MAX(ksmchsiz) ,
         TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
  FROM x$ksmsp
  WHERE ksmchsiz BETWEEN 524 AND 4107
  AND ksmchcls='free'
  GROUP BY ksmchcls, ksmchidx, 500*TRUNC(ksmchsiz/500)
  UNION ALL
  SELECT '6+ (4108+)' bucket, ksmchcls, ksmchidx, 1000*TRUNC(ksmchsiz/1000),
         COUNT(*), MAX(ksmchsiz),
         TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
  FROM x$ksmsp
  WHERE ksmchsiz >= 4108
  AND ksmchcls='free'
  GROUP BY ksmchcls, ksmchidx, 1000*TRUNC(ksmchsiz/1000);
  我更习惯用的另一个脚本:    select
  decode(
  sign(ksmchsiz-812),
  -1,(ksmchsiz-16)/4,
  decode(
  sign(ksmchsiz-4012),
  -1,trunc((ksmchsiz+11924)/64),
  decode(
  sign(ksmchsiz-65548),
  -1,trunc(1/log(ksmchsiz-11,2))+238,
  254
  )
  )
  ) bucket,
  sum(ksmchsiz) "free_space",
  count(*) "free_chunks",
  trunc(avg(ksmchsiz)) "average_size",
  max(ksmchsiz) biggest
  from
  sys.x$ksmsp
  where
  inst_id=userenv('Instance') and
  ksmchcls='free'
  group by
  decode(
  sign(ksmchsiz-812),
  -1,(ksmchsiz-16)/4,
  decode(
  sign(ksmchsiz-4012),
  -1,trunc((ksmchsiz+11924)/64),
  decode(
  sign(ksmchsiz-65548),
  -1,trunc(1/log(ksmchsiz-11,2))+238,
  254
  )
  )
  )
  order by 4
  /
  使用PL/SQL Developer工具将输出结果图形化，可以很直观的看到不能大小free chunk的数量。按经验来说，当较小的chunk的值超过2000-5000时我们认为存在较多的碎片。
  Note: The information available in this view is the same that is generated as part of a HEAPDUMP level 2.
  
  Be aware of Bug:4715420 stating that selecting from X$KSMSP is asking one session to hold the shared pool latches in turn for a LONG period of time and should be avoided on live systems. Selecting from X$KSMSP on a production system is a very bad idea.
  
  If the result of the above query shows that must of the space available is on the top part of the list (meaning available only in very small chuncks). It is very likely that the error is due to a heavy fragmentation.
  
  You can also use this view as follows to review overall memory usage in the SGA:
  SQL> SELECT ksmchcls CLASS, COUNT(ksmchcls) NUM, SUM(ksmchsiz) SIZ,
  TO_CHAR(((SUM(ksmchsiz)/COUNT(ksmchcls)/1024)),'999,999.00')||'k' "AVG SIZE"
  FROM X$KSMSP GROUP BY ksmchcls;
  
  CLASS           NUM        SIZ AVG SIZE
  -------- ---------- ---------- ------------
  R-free           12    8059200      655.86k
  R-freea          24        960         .04k
  free            331  151736448      447.67k
  freeabl        4768    7514504        1.54k
  perm              2   30765848   15,022.39k
  recr           3577    3248864         .89k
  1. if free memory (SIZ) is low (less than 5mb or so) you may need to increase the shared_pool_size and shared_pool_reserved_size.
  2. if perm continually grows then it is possible you are seeing system memory leak.
  3. if freeabl and recr are always huge, this indicates that you have lots of cursor info stored that is not releasing.
  4. if free is huge but you are still getting 4031 errors, (you can correlate that with the reloads and invalids causing fragmentation).

4. ORA-04031 error and Large Pool

The Large pool is an optional memory area that can be configured to provide large memory allocations for one of the following operations:

session memory for the multi-threaded server and the Oracle XA interface.
The memory ( Buffers ) for Oracle backup and restore operations and for I/O server processes.
Parallel Execution messaging buffers.

The Large pool does not have a LRU list. It is different from reserved space in the shared pool, which uses the same LRU list as other memory allocated from the shared pool. Chunks of memory are never aged out of the large pool, memory has to be explicitly allocated and freed by each session. If there is no free memory left when a request is made then an ORA-4031 will be signalled similar to this:

ORA-04031: unable to allocate XXXX bytes of shared memory ("large pool","unknown object","session heap","frame")

Few things can be checked when this error occurs:

Check V$SGASTAT and see how much memory is used and free using the following SQL statement:
SELECT pool, name, bytes
FROM v$sgastat
WHERE pool = 'large pool';
You can also take a heapdump level 32 to dump the large pool heap and check free chunks sizes.

Memory is allocated from the large pool in chunks of LARGE_POOL_MIN_ALLOC bytes to help avoid fragmentation. Any request to allocate a chunk size less LARGE_POOL_MIN_ALLOC will be allocated with size of LARGE_POOL_MIN_ALLOC. In general you may see more memory usage when using Large Pool compared to Shared Pool.

Usually to resolve an ORA-4031 in the large pool the LARGE_POOL_SIZE size must be increased.

5. ORA-04031 and SHARED POOL FLUSHING

There are several techniques to increase cursor sharability so that shared pool fragmentation is reduce as well as likeability of ORA-4031 errors. The best way is by modifying the application to use bind variables. Another workaround when the application cannot be modified is using CURSOR_SHARING to a value different of EXACT (Be aware that this may cause changes in execution plan, so it is advisable to test the application first). When none of the above techniques can be used and fragmentation is considearble heavy in the system, flushing the shared pool might help alliviating the fragmentation. However some considerations must be taken into account:

Flushing the shared pool will cause that all the cursor that are not in use are removed from the library cache. Therefore just after the shared pool flusing is issued, most of the SQL and PL/SQL cursors will have to be hard parsed. This will increase the CPU usage of the system and will also increase the latch activity.
When applications don't use bind variables and have heavy possibilities of many users doing frequen similar operations (like in OLTP systems) it is common that soon after the flush is issued the fragmentation is back in place. So be advice that flushing the shared pool is not always the solution for a bad application.
For large shared pool flushing the shared pool may cause a halt of the system, specially when the instance is very active. It is recommended to flush the shared pool during off-peak hours.

我们常用alter system flush shared_pool;来flushing share pool，但是这条命令只能用来合并相连的chunk合并为较大的chunk，当遇到ORA-04031问题时往往可以临时解决此问题。但也有执行多次alter system flush shared_pool后仍然没有足够大的满足请求的chunk，ORA-04031问题没能立即消失。此时，我们可以增大share pool或重启。事后，确定问题的原因是sql bind问题还是share pool太小了，针对具体原因确定解决方案。

我们来看一个发生ORA-04031问题时，free chunk的分配情况，以及执行flush后free chunk的分配情况：

 select    decode(            sign(ksmchsiz-812),            -1,(ksmchsiz-16)/4,            decode(                    sign(ksmchsiz-4012),                    -1,trunc((ksmchsiz+11924)/64),                    decode(                            sign(ksmchsiz-65548),                            -1,trunc(1/log(ksmchsiz-11,2))+238,                            254                            )                    )            ) bucket,            sum(ksmchsiz) "free_space",            count(*) "free_chunks",            trunc(avg(ksmchsiz)) "average_size",            max(ksmchsiz) biggest from    sys.x$ksmsp where    inst_id=userenv('Instance') and    ksmchcls='free' group by decode(            sign(ksmchsiz-812),            -1,(ksmchsiz-16)/4,            decode(                    sign(ksmchsiz-4012),                    -1,trunc((ksmchsiz+11924)/64),                    decode(                            sign(ksmchsiz-65548),                            -1,trunc(1/log(ksmchsiz-11,2))+238,                            254                            )                    )            ) order by 4 /    BUCKET free_space free_chunks average_size    BIGGEST---------- ---------- ----------- ------------ ----------         6     102440        2561           40         40         8     443088        9231           48         48        10     131992        2357           56         56        12     216640        3385           64         64        14     136224        1892           72         72        16     166160        2077           80         80        18    1584352       18004           88         88        20     254976        2656           96         96        22     166296        1599          104        104        24     206416        1843          112        112        26     238680        1989          120        120        28     311552        2434          128        128        30     222360        1635          136        136        32     223632        1553          144        144        34     306280        2015          152        152        36     317120        1982          160        160        38     145824         868          168        168        40     380688        2163          176        176        42     335064        1821          184        184        44     283392        1476          192        192        46     373600        1868          200        200        48     465920        2240          208        208        50     254880        1180          216        216        52     213920         955          224        224        54     298816        1288          232        232        56     269280        1122          240        240        58     273544        1103          248        248        60     247808         968          256        256        62     215688         817          264        264        64     353872        1301          272        272        66     265720         949          280        280        68     356544        1238          288        288        70     266992         902          296        296        72     234080         770          304        304        74     503568        1614          312        312        76     338560        1058          320        320        78     394584        1203          328        328        80     273168         813          336        336        82     263504         766          344        344        84     352352        1001          352        352        86     212400         590          360        360        88     303968         826          368        368        90     285760         760          376        376        92     238080         620          384        384        94      23520          60          392        392        96     151200         378          400        400        98     235416         577          408        408       100     203008         488          416        416       102     312488         737          424        424       104     476496        1103          432        432       106     670560        1524          440        440       108     659008        1471          448        448       110     576384        1264          456        456       112     565152        1218          464        464       114     660800        1400          472        472       116     966720        2014          480        480       118     499224        1023          488        488       120     478640         965          496        496       122     437472         868          504        504       124     530944        1037          512        512       126    1878760        3613          520        520       128     398112         754          528        528       130      31624          59          536        536       132     355776         654          544        544       134     380328         689          552        552       136     833280        1488          560        560       138     303312         534          568        568       140     332928         578          576        576       142     269224         461          584        584       144     281200         475          592        592       146     376800         628          600        600       148     823840        1355          608        608       150     521752         847          616        616       152     647712        1038          624        624       154     518240         820          632        632       156     607360         949          640        640       158    1521504        2348          648        648       160     494624         754          656        656       162     655368         987          664        664       164     590016         878          672        672       166     666400         980          680        680       168     771936        1122          688        688       170     508080         730          696        696       172     594176         844          704        704       174     769672        1081          712        712       176     725760        1008          720        720       178     704704         968          728        728       180     415104         564          736        736       182     557256         749          744        744       184     693344         922          752        752       186     687800         905          760        760       188     645120         840          768        768       190     696072         897          776        776       192     537824         686          784        784       194     772992         976          792        792       196     628000         785          800        800       198     652864         808          808        808       199    5849384        6927          844        872       200    6049744        6667          907        936       201    6822456        7011          973       1000       202    9361768        9053         1034       1064       203    7563304        7006         1079       1104       206    2707472        2079         1302       1320       207    5959112        4385         1358       1384       208    6394536        4499         1421       1448       209    5745728        3879         1481       1512       210    6117920        3949         1549       1576       211    9559280        5921         1614       1640       212    6971632        4158         1676       1704       213    8506864        4889         1740       1768       214    7959808        4412         1804       1832       215    6958224        3725         1867       1896       216    7599072        3932         1932       1960       217    8266272        4142         1995       2024       218    9188400        4460         2060       2088       219   11862360        5583         2124       2152       220   12856464        5879         2186       2216       221    9823520        4377         2244       2272       222    6246328        2691         2321       2344       223    9803352        4120         2379       2408       224    8516144        3485         2443       2472       225    9929152        3958         2508       2536       226    8891552        3457         2572       2600       227    9517976        3608         2638       2664       228   10158200        3761         2700       2728       229   11908488        4310         2762       2792       230   10198784        3606         2828       2856       231   10608296        3668         2892       2920       232   10362480        3506         2955       2984       233    9756384        3231         3019       3048       234   10570720        3427         3084       3112       235   12667472        4022         3149       3176       236   13504864        4205         3211       3240       237   17516752        5344         3277       3304       238   14035808        4203         3339       3368       239   13363640        3925         3404       3432       240   13544360        3905         3468       3496       241   13994512        3962         3532       3560       242   15400536        4282         3596       3624       243   19071632        5209         3661       3688       244   21121568        5673         3723       3752       245   23530760        6209         3789       3816       246   32159328        8345         3853       3880       247   45519056       11617         3918       3944       248   86272160       21660         3983       4008       249  130881160       32363         4044       4104       250    2216504         536         4135       8112       251      24624           2        12312      12376

此时，在报错信息里看到因找不到连续的4120k的内存而报错，我们可以看到具有较严重的碎片。紧接着我们执行了多次flush命令后：

SQL> alter system flush shared_pool;System altered.SQL>  /System altered.SQL>  /System altered.SQL>  /System altered.select    decode(            sign(ksmchsiz-812),            -1,(ksmchsiz-16)/4,            decode(                    sign(ksmchsiz-4012),                    -1,trunc((ksmchsiz+11924)/64),                    decode(                            sign(ksmchsiz-65548),                            -1,trunc(1/log(ksmchsiz-11,2))+238,                            254                            )                    )            ) bucket,            sum(ksmchsiz) "free_space",            count(*) "free_chunks",            trunc(avg(ksmchsiz)) "average_size",            max(ksmchsiz) biggest from    sys.x$ksmsp where    inst_id=userenv('Instance') and    ksmchcls='free' group by decode(            sign(ksmchsiz-812),            -1,(ksmchsiz-16)/4,            decode(                    sign(ksmchsiz-4012),                    -1,trunc((ksmchsiz+11924)/64),                    decode(                            sign(ksmchsiz-65548),                            -1,trunc(1/log(ksmchsiz-11,2))+238,                            254                            )                    )            ) order by 4 /    BUCKET free_space free_chunks average_size    BIGGEST---------- ---------- ----------- ------------ ----------         6      71840        1796           40         40         8     412176        8587           48         48        10     109984        1964           56         56        12     204032        3188           64         64        14     121896        1693           72         72        16     150320        1879           80         80        18    1522136       17297           88         88        20     207648        2163           96         96        22     144976        1394          104        104        24     177520        1585          112        112        26     201960        1683          120        120        28     279040        2180          128        128        30     193256        1421          136        136        32     192816        1339          144        144        34     268128        1764          152        152        36     261920        1637          160        160        38     305592        1819          168        168        40     350768        1993          176        176        42     289248        1572          184        184        44     282816        1473          192        192        46     366800        1834          200        200        48     454896        2187          208        208        50     247536        1146          216        216        52     198688         887          224        224        54     290000        1250          232        232        56     264720        1103          240        240        58     273296        1102          248        248        60     247296         966          256        256        62     215688         817          264        264        64     353872        1301          272        272        66     266000         950          280        280        68     512064        1778          288        288        70     266992         902          296        296        72     234080         770          304        304        74     502944        1612          312        312        76     338560        1058          320        320        78     394584        1203          328        328        80     273168         813          336        336        82     263160         765          344        344        84     351296         998          352        352        86     212400         590          360        360        88     303968         826          368        368        90     285384         759          376        376        92     237696         619          384        384        94      84280         215          392        392        96     183600         459          400        400        98     236232         579          408        408       100     203008         488          416        416       102     312488         737          424        424       104     678672        1571          432        432       106     670120        1523          440        440       108     659008        1471          448        448       110     576384        1264          456        456       112     565616        1219          464        464       114     660800        1400          472        472       116     965760        2012          480        480       118     499712        1024          488        488       120     477152         962          496        496       122     437472         868          504        504       124     533504        1042          512        512       126    1877720        3611          520        520       128     398112         754          528        528       130     274968         513          536        536       132     367744         676          544        544       134     384744         697          552        552       136     973840        1739          560        560       138     581064        1023          568        568       140     563328         978          576        576       142     536696         919          584        584       144     544048         919          592        592       146     642600        1071          600        600       148     880992        1449          608        608       150     566104         919          616        616       152     693888        1112          624        624       154     558688         884          632        632       156     626560         979          640        640       158    1618056        2497          648        648       160     568752         867          656        656       162     654704         986          664        664       164     590016         878          672        672       166     666400         980          680        680       168     771936        1122          688        688       170     507384         729          696        696       172     595584         846          704        704       174     769672        1081          712        712       176     725760        1008          720        720       178     722904         993          728        728       180     423200         575          736        736       182     560976         754          744        744       184     700112         931          752        752       186     687040         904          760        760       188     645888         841          768        768       190     695296         896          776        776       192     537824         686          784        784       194     768240         970          792        792       196     628000         785          800        800       198     652864         808          808        808       199    5898128        6986          844        872       200    6049960        6667          907        936       201    6845720        7035          973       1000       202    9371080        9062         1034       1064       203    7913096        7331         1079       1128       204    1516792        1302         1164       1192       205    2907952        2378         1222       1256       206    4242464        3277         1294       1320       207    6171920        4544         1358       1384       208    6419848        4517         1421       1448       209    5756192        3886         1481       1512       210    6116448        3948         1549       1576       211    9557608        5920         1614       1640       212    6971560        4158         1676       1704       213    8508624        4890         1740       1768       214    7958000        4411         1804       1832       215    6961920        3727         1867       1896       216    7595216        3930         1932       1960       217    8272176        4145         1995       2024       218    9192536        4462         2060       2088       219   12016072        5655         2124       2152       220   12858664        5880         2186       2216       221    9819008        4375         2244       2272       222    8736504        3766         2319       2344       223    9805840        4121         2379       2408       224    8518576        3486         2443       2472       225    9931672        3959         2508       2536       226    8883856        3454         2572       2600       227    9515344        3607         2638       2664       228   10160840        3762         2700       2728       229   11900216        4307         2762       2792       230   10195944        3605         2828       2856       231   10608344        3668         2892       2920       232   10359456        3505         2955       2984       233    9753352        3230         3019       3048       234   10573792        3428         3084       3112       235   12664344        4021         3149       3176       236   13495264        4202         3211       3240       237   17513488        5343         3277       3304       238   14025784        4200         3339       3368       239   13367016        3926         3404       3432       240   13544376        3905         3468       3496       241   13983840        3959         3532       3560       242   15400552        4282         3596       3624       243   19071632        5209         3661       3688       244   21129048        5675         3723       3752       245   23500504        6201         3789       3816       246   32140056        8340         3853       3880       247   45479800       11607         3918       3944       248   86236400       21651         3983       4008       249  149325744       36865         4050       4104       250    7006400        1661         4218       8192       251     774448          68        11388      16384       252     380896          17        22405      32688       253      35416           1        35416      35416

可以看到，在多次执行flush命令后碎片稍有缓解，但是应用中ora-04031错误仍然存在。为了即使解决该问题，领导迅速把数据库重启了T.T，好吧那就来看看重启后的情况吧:

SQL>  select  2      decode(  3              sign(ksmchsiz-812),  4              -1,(ksmchsiz-16)/4,  5              decode(  6                      sign(ksmchsiz-4012),  7                      -1,trunc((ksmchsiz+11924)/64),  8                      decode(  9                              sign(ksmchsiz-65548), 10                              -1,trunc(1/log(ksmchsiz-11,2))+238, 11                              254 12                              ) 13                      ) 14              ) bucket, 15              sum(ksmchsiz) "free_space", 16              count(*) "free_chunks", 17              trunc(avg(ksmchsiz)) "average_size", 18              max(ksmchsiz) biggest 19   from 20      sys.x$ksmsp 21   where 22      inst_id=userenv('Instance') and 23      ksmchcls='free' 24   group by 25   decode( 26              sign(ksmchsiz-812), 27              -1,(ksmchsiz-16)/4, 28              decode( 29                      sign(ksmchsiz-4012), 30                      -1,trunc((ksmchsiz+11924)/64), 31                      decode( 32                              sign(ksmchsiz-65548), 33                              -1,trunc(1/log(ksmchsiz-11,2))+238, 34                              254 35                              ) 36                      ) 37              ) 38   order by 4 39   /    BUCKET free_space free_chunks average_size    BIGGEST---------- ---------- ----------- ------------ ----------         6        760          19           40         40         8        528          11           48         48        10        448           8           56         56        12        512           8           64         64        14        216           3           72         72        16        480           6           80         80        18        264           3           88         88        20        672           7           96         96        22       2288          22          104        104        24       2576          23          112        112        26        240           2          120        120    BUCKET free_space free_chunks average_size    BIGGEST---------- ---------- ----------- ------------ ----------        28        128           1          128        128        30        272           2          136        136        32        144           1          144        144        34        304           2          152        152        36        960           6          160        160        38        504           3          168        168        40        528           3          176        176        48      13104          63          208        208        52        448           2          224        224        54        232           1          232        232        56        960           4          240        240    BUCKET free_space free_chunks average_size    BIGGEST---------- ---------- ----------- ------------ ----------        58        496           2          248        248        60        768           3          256        256        62       1056           4          264        264        66        280           1          280        280        68        288           1          288        288        70        296           1          296        296        72        304           1          304        304        74       1560           5          312        312        76        960           3          320        320        78        328           1          328        328        80        672           2          336        336    BUCKET free_space free_chunks average_size    BIGGEST---------- ---------- ----------- ------------ ----------        82        344           1          344        344        84       1056           3          352        352        90       1128           3          376        376        92        384           1          384        384        94        392           1          392        392        96        800           2          400        400        98       3264           8          408        408       100        832           2          416        416       102        848           2          424        424       116        480           1          480        480       186        760           1          760        760    BUCKET free_space free_chunks average_size    BIGGEST---------- ---------- ----------- ------------ ----------       199       2552           3          850        872       200        920           1          920        920       201        952           1          952        952       205       1224           1         1224       1224       236       3240           1         3240       3240       250      13928           3         4642       4776       251       8576           1         8576       8576       254   53470904           8      6683863   11002592

数据库刚刚启动时，free chunk的数量都很少。随着时间free chunk的分配逐渐增多，但小的chunk的数据量很大时，也就是碎片产生了。如前面所述，小的chunk一般超过2000-5000时便认为碎片较为严重，这个2000-5000是一个经验值！

6. Advanced analysis to ORA-04031 error

If none of the techniques provided cannot resolve the occurence of ORA-04031 errors, additional tracing may be needed to get a snapshot of the shared pool when the problem is in place.

Modify the init.ora paramater to add the following events to get a trace file with additional information about the problem:

event = "4031 trace name errorstack level 3"
event = "4031 trace name HEAPDUMP level 2"

Note: These parameters will take not effect unless the instance is bounced.

Starting with 9.2.0.5, instead of requesting heapdump level 1,2, 3 or 32 you can use level those same levels plus (536870912). This will generate the 5 largest subheaps AND the 5 largest heap areas within each of those. If the problem is reproducible, the event can be set at session level using the following statement before the execution of the faulty SQL statement:

SQL> ALTER SESSION SET EVENTS '4031 trace name errorstack level 3';
SQL> ALTER SESSION SET EVENTS '4031 trace name heapdump level 536870914';

The trace file(s) should be sent to Oracle Support for troubleshooting.

Important Note: In Oracle 9.2.0.5 and higher releases a trace file is generated BY DEFAULT every time an ORA-4031 error occurs, and can be located in the USER_DUMP_DEST directory (or ADR with 11g). If your database version is one of these, you don't need to follow the steps described before to generate additional tracing.

此文章转载自MOS，并加入了一些自己的实验信息。

MOS上还有一篇分析解决ORA-04031的文章，这篇文章更加有深度和广度的分析了share pool的分配机制及产生的原因：ID 396940.1

ballontt
2013/12/24

---The End---

微博：weibo.com/ballontt

如需转载，请标明出处和链接，谢谢！

0 0