ora-4031 and "obj stat memory" component in Shared Pool

Comments

1. admin says

   2010/08/17 at 23:08

   Ora-04031 With Continuous Growth Of “obj stat memo” in V$SGASTAT

   Applies to:

   Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.3
   This problem can occur on any platform.
   Symptoms

   ORA-4031 errors are observed in database

   Fri Nov 16 23:53:01 2007
   Errors in file /b01/admin/SMTPGC3/bdump/testdb10_cjq0_11406.trc:
   ORA-00604: error occurred at recursive SQL level 3
   ORA-04031: unable to allocate 4108 bytes of shared memory (“shared pool”,”select
   name,password,datats#…”,”Typecheck”,”kgghteInit”)

   Looking at trace file testdb10_cjq0_11406.trc

   Memory Utilization of Subpool 1
   ================================
   Allocation Name Size
   _________________________ __________
   “free memory ” 88845736
   “miscellaneous ” 0
   “Undo Meta Data ” 240

   >>> output truncated

   “obj stat memo ” 112909088 <--107 Mb "object level " 12355104 Second trace file reports "obj stat memo " 121212308 <--115 Mb "object level " 13263264 Around 107 Mb of memory is being used by obj stat memo and is continuously increasing. >>
   Cause

   From the above we see that at time of failure obj stat memo occupied 107mb of memory.

   The symptoms hint that the issue is caused by ‘unpublished’ Bug 5573238 According to this bug

   Shared pool memory use / ORA-4031 due to “obj stat memo” in one subpool of the SGA. This fix spreads the allocations across subpool rather than keeping them in one subpool and batches the allocations to help reduce the effect of fragmentation from allocation of many small chunks.There is no actual leak issue in this bug, but systemswith very large numbers of segments may see problems related to the fragmentation.

   So according to this bug fix, if we have multiple pools and we see one pool having lot of “obj stat memo” component with very less free memory (or fragmented subpool), then you need to apply this fix. Along with this, the bug batches the allocation to prevent fragmentation. Following query can be used to check the number of subpools.

   SQL> select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and nam.ksppinm like ‘%kghdsidx%’ order by 1 ;

   NAME VALUE
   —————————— ——————–
   _kghdsidx_count 1
   In this case only 1 subpool is present.

   Along with this bug says that if you have too many objects in your database like a highly partitioned database then the memory consumption is high.
   Solution

   To resolve this problem , you have following options

   1)Apply the Patch 5573238 This will take care of fragmentation only and will not reduce the memory consumption in shared pool.
   Along with this you will have to size your shared pool correctly.

   OR

   2)Disable the object statistics using

   Set STATISTICS_LEVEL=BASIC
   or
   Set “_object_statistics”=false

   Please note that setting STATISTICS_LEVEL=BASIC will disable many automatic features like ASMM, Automatic statistics gathering, etc.

   If you set “_object_statistics”=false, then as _object_statistics control the population of segment stats in the v$segstat andv$segment_statistics views. So setting this to false will result in no data populating these v$
   views.
   Both the parameters will require a database restart

   回复
2. admin says

   2010/08/17 at 23:10

   Hdr: 5573238 10.2.0.2 RDBMS 10.2.0.2 BUFFER CACHE PRODID-5 PORTID-46 ORA-4031
   Abstract: SHARED_POOL FRAGMENATION CAUSED BY SEGMENT STATISTICS STRUCTURES
   PROBLEM:
   ——–
   The problem we have seen once and think we might be seeing again is when
   there are very large numbers of segments because of partitioned tables and
   partitioned indexes. In both cases we are looking at hundreds of thousands
   of segments. 500k+. In both cases we are seeing ORA-4031’s. I had one
   good heap dump that showed that the object statistics structures were 250
   bytes, allocated directly out of the shared_pool and were permanent. It used
   kghx to allocate but 250 was greater than the max element size that kghx
   supported so it routed the alloc to the parent heap I believe.

   DIAGNOSTIC ANALYSIS:
   ——————–

   WORKAROUND:
   ———–
   Set

   _object_statistics = false

   RELATED BUGS:
   ————-

   REPRODUCIBILITY:
   —————-

   TEST CASE:
   ———-

   STACK TRACE:
   ————

   SUPPORTING INFORMATION:
   ———————–
   Check out the code in kghxrg_retain

   /* If the type is freeable and the chunk is small then we will allocate
   * chunks from the heap of type permanent in batches. This avoids
   * fragmentation caused by lots of small allocations in a heap.
   *
   * Additionally, don’t batch heap descriptors and free lists where the
   * is not the top sga heap since the top SGA heap is the only heap with
   * LRU lists.
   */
   #define KGHXBATCH 250 /* allocations smaller than this are batched
   */
   if (( bit(chunkflags, KGHACMASK) != KGHACFREE
   || xfr->kghxfsiz > KGHXBATCH
   || bit(xfrflg, KGHXFHEAP))
   && (heap == kgsmtsga(kgsmgsga(gp)) || KGHISNEW(heap)))
   {
   if (!bit(xfrflg, KGHXFNORC))

   As I remember the object_statistics struct was something like 256 bytes.

   The 4031 does not happen on allocation of these structs. What I noticed from
   the heap dump was that they got allocated everywhere and fragmented the
   shared_pool dramatically. After they were all allocated though it still
   possibly took awhile longer to start getting 4031’s.

   Can think of a number of possible fixes here but you guys will have a much
   better idea of which ones are practical.
   1) Shrink the size of the stats struct dramatically. 256 seems high. This
   would allow it to be batched.

   2) Scan the dictionary at start up and preallocate some number of entries
   based on the number of segments that exist at that time. Would work best
   with option 1 above.

   3) Just make sure that the structs are batched (change the constant in kghx).
   Not very desirable but certainly easy.

   4) Do not keep segment stats for all segments. Particularly for all segments
   of partitioned objects. Make user specify that they want segment stats for
   partitioned objects explicitly?

   5) Make segment stats freeable. Write them to disk and allow them to be
   flushed. Works best with options 1-3 maybe.

   24 HOUR CONTACT INFORMATION FOR P1 BUGS:
   —————————————-

   DIAL-IN INFORMATION:
   ——————–

   IMPACT DATE:
   ————
   REDISCOVERY INFORMATION:
   many allocations labeled “obj stat memory” from the same pool and ora-431 as ar
   esult
   WORKAROUND:
   None
   RELEASE NOTES:
   ]]memory problem caused by object statistics.

   回复
3. maclean says

   2011/08/25 at 22:32

   这个问题后来经过网友的指出发现在最新的10.2.0.5中存在一种workaround的方法，即设置隐藏 _disable_objstat_del_broadcast=false来绕过问题

   Bug 9737897 V$SGASTAT shows “obj stat memo” increases continuously
   This note gives a brief overview of bug 9737897.
   The content was last updated on: 04-MAR-2011
   Click here for details of each of the sections below.
   Affects:

   Product (Component) Oracle Server (Rdbms)
   Range of versions believed to be affected Versions BELOW 11.1
   Versions confirmed as being affected

   10.2.0.4
   10.2.0.3

   Platforms affected Generic (all / most platforms affected)

   Fixed:

   This issue is fixed in

   (None Specified)

   Symptoms:

   Related To:

   Leak (Memory Leak / Growth)
   Shared Pool Affected

   V$SGASTAT
   _OBJECT_STATISTICS

   Description

   An SGA memory leak is possible for “obj stat memo” and “obj htab chun”
   memory if a segment is dropped and recreated many times.

   Rediscovery Information:
   If all of following conditions are met, probably you hit this problem
   – version
   obj stat memo : shared pool obj stat memo 202811472

   at 20110719 10:20:02 —>
   obj stat memo : shared pool obj stat memo 376349112

   3) select distinct fts_objd, fts_objn
   from x$ksolsfts x
   where not exists (select 1 from obj$ o
   where o.dataobj#=x.fts_objd and o.obj#=x.fts_objn)

   returns many rows

   setting “_disable_objstat_del_broadcast=false” solves the problem:

   obj stat del channel: 1110
   obj stat memo : shared pool obj stat memo 192888

   From Bug 9737897 :
   ” …
   On 10.2.0.5, bug:8493119 fix is included and ‘obj stat del channel’ is not
   used unless _disable_objstat_del_broadcast = FALSE is set. This means, on
   10.2.0.5 ‘obj stat memo’ monotonically increases by create/drop without any
   special conditions.
   …
   And there is a note for 10.2.0.5
   bug:8493119 fix is included in 10.2.0.5 and ‘obj stat del channel’ is not
   used without _disable_objstat_del_broadcast = FALSE
   Since ‘obj stat del channel’ is not used, object statistics element is left
   in SGA even we drop segment.”
   …
   ”

   Problem is reproducible on 10.2.0.5 HP-UX Itanium (customer DB)

   WORKAROUND:
   ———–
   set “_disable_objstat_del_broadcast=false” but this is not acceptable by
   customer.

   RELATED BUGS:
   ————-
   – Bug 9737897 – SIZE OF “OBJ STAT MEMO” INCREASES CONTINUOUSLY.
   – Note 9737897.8 – Bug 9737897 – V$SGASTAT shows “obj stat memo” increases
   continuously
   From this note a condition to hit this bug is “version < 10.2.0.5" but
   customer is on 10.2.0.5

   – Bug 8493119 – request to enable the patch for bug 6320124 by default in
   10.2.0.5

   REPRODUCIBILITY:
   —————-
   problem is reproducible only on customer DB (also on a customer UAT
   environment)
   Hdr: 12859499 10.2.0.5 RDBMS 10.2.0.5 BUFFER CACHE PRODID-5 PORTID-226 ORA-4031
   Abstract: ORA-4031 DUE TO HIGH ALLOCATION FOR SGA AREA OBJ STAT MEMO

   —-
   this is for SAP message 544832-2011 Siemens Financial Services

   PROBLEM:
   ——–
   After upgrading database to 10.2.0.5 a lot of cases with ORA-4031 has
   happend. This was not visible during the usage of 10.2.0.4 before.

   DIAGNOSTIC ANALYSIS:
   ——————–
   The contents of 4031 diagnostic shows a high allocation for "obj stat memo".
   There are 4 subpools used and we have around 300MB memory allocated each for
   that
   area until it crashes. That area has continuos grown since database start.
   It is the largest allocation of all the areas.

   shared_pool_size is set and limited to 3170893824 bytes.

   From that observvations we were pointed to bug 9737897.
   Testing the workaround given
   _disable_objstat_del_broadcast=false

   prevent to hit ORA-4031 again since some weeks now.
   So this looks that we have still an issue with orphaned object statistics of
   dropped objects in 10.2.0.5.
   Possible duplicate bug12800336

   Customer provided tracefiles, lsinventory output, parameter settings

   WORKAROUND:
   ———–
   _disable_objstat_del_broadcast=false

   RELATED BUGS:
   ————-
   9737897, 12800336

   REPRODUCIBILITY:
   —————-
   on Linux with 10.2.0.5
   on HP-UX with 10.2.0.5

   回复
4. Maclean Liu says

   2011/12/18 at 22:52

   1. we are hitting bug 9737897 ? or we are suspecting hitting bug 9737897?

   Bug 9737897 is only known bug that cause increases continuously on “obj stat memo”.

   2. is it hitting bug 9737897 or MOS note 9737897.8.

   yes

   3. if yes, What DB version will hitting this bug? Customer is currently using 10.2.0.5 on HP-IA 11.31

   10.2.0.3, 10.2.0.4, 10.2.0.5

   4. if yes, What kind of OS Platform will hit this bug? HPUX/Solaris/AIX ? Because customer also has 10.2.0.5 DB on diffierence Platform .

   It is reported only with AIX platform but this is not mean that it can not hit another platform.

   5. if yes, Any patch/workaround for this bug?
   For 10.2.0.5, we have no patch.

   The workaround is :

   – set one of following init parameters

   _disable_objstat_del_broadcast=false

   OR

   _object_statistics = false

   Based on the above information, we recommend to apply the workaround to see if the DB hang and “obj stat memo” issues are resolved.

   Customer would have more detail on bug 9737897.

   1) Under which condition or situation, we will hit the bug 9737897 ?
   Based on MOS 9737897.8, bug may be triggered by freqently drop/create segment, However, customer claim they did not frequently drop/create segment but only frequently truncate the table. Is there other operation/condition will also trigger this bug?

   2) Since customer has other 10.2 database on difference platform. They are highly concern on this bug will be affected .
   Is it a generic platform bug for all 10.2.0.3 / 10.2.0.4 / 10.2.0.5 ?

   3) Customer request to file oneoff patch fix for 10.2.0.5 in HP-IA platform.

   4) Customer would like to know any impact of suggested workaround parameters and what is side effect after set:

   “_object_statistics” = false
   “ _disable_objstat_del_broadcast” = false

   Also, can customer set both “_object_statistics” and “ _disable_objstat_del_broadcast” at the same time?

   1. The mentioned bug is also triggered with truncate segment/object.

   2. As we stated that till now it is reported only on AIX platform and produced internally by DEV team on HP-UX Itanium platform. But it could be reported for any other platform. It is not a specific platform bug.

   3. Let me explain how this bug is produced:

   When a segment is dropped/truncated marks the segment as temporary sends an object stats deletion message to remove the object statistics for the object being dropped.
   At this stage, the object statistics have been deleted but the buffer cache has not been flushed of buffers for the object being dropped. Therefore, if buffers get “aged” out of the cache in between the object stats being deleted and the calls to flush the buffers for the object being dropped, a new object stats element will be generated to record the physical write stat.
   This will lead to the situation where we have an object stat element for an object about to be dropped, which will not get removed until the instance is restarted. At this stage the “obj stat memo” will keep increased till the next restart.
   On 10.2.0.5, bug:8493119 fix is included and ‘obj stat del channel’ (object stats deletion message) is not used unless _disable_objstat_del_broadcast = FALSE is set. This means, on 10.2.0.5 ‘obj stat memo’ monotonically increases drop/truncate without any special conditions.

   I checked internally the possibility of have a backport on 10.2.0.5. Since the object stats deletion messages are disabled by default in 10.2.0.5, the internal bug document state that the backport on 10.2.0.5 is feasible only if the issue produced when the object stats deletion messages are enabled by using _disable_objstat_del_broadcast=false. If it is produced, a separate bug should be filled to diagnose the issue and get its fix.

   4. _disable_objstat_del_broadcast=false :
   As we explain above, it enables sending an object stats deletion message to remove the object statistics for the object being dropped/truncated.

   _object_statistics=false
   control the population of segment stats in the v$segstat andv$segment_statistics views. So setting this to false will result in no data populating these v$ views.

   Based on the above information we strongly recommend to set _disable_objstat_del_broadcast=false and monitor the database by running periodically the following queries to see if the issue is reproduced with frequently object truncation operations.
   – conn / as sysdba
   – select ch.mesgcnt_ksrchdl
   from x$ksrchdl ch, x$ksrcdes cd, x$ksrcctx cc
   where cd.indx = cc.indx
   and cc.addr = ch.ctxp_ksrchdl
   and cd.name_ksrcdes = ‘obj stat del channel’;

   – select * from v$sgastat where name in (‘obj stat memo’);

   – select distinct fts_objd, fts_objn from x$ksolsfts x
   where not exists (select 1 from obj$ o
   where o.dataobj#=x.fts_objd and o.obj#=x.fts_objn);

   Except performance impact. Customer would like to know if there is other side effect after set this parameter _disable_objstat_del_broadcast=false in 10.2.0.5 DB.

   In addition, as mentioned before, 10.2.0.5, by default which disabled objstst del broadcash feature. Therefore, which is suggested to use _disable_objstat_del_broadcast=false. However customer they have other 10.2.0.3 or 10.2.0.4 DB, should we use _disable_objstat_del_broadcast=false or _object_statistics = false or both for 10.2.0.3 / 10.2.0.4?

   Finally, customer is asking for any backport one-off patch available for bug 9737897 for 10.2.0.3 / 10.2.0.4/ 10.2.0.5 (if yes, pls specify patch number and platform).
   Is AIX 10.2.0.4.3 PSU include this bug fix?

   – There are no side effects reported before as a result of setting such parameter. Simply, cause this feature is enabled by default on 10.2.0.3 and 10.2.0.4 and they are not encountering any problems or side effects.

   – As we stated above, on 10.2.0.3 and 10.2.0.4 objstst del broadcast feature is enabled by default, so _disable_objstat_del_broadcast parameter itself is not existing at all on these DB versions.

   The only workaround on 10.2.0.3 and 10.2.0.4 is to set _object_statistics=false.

   – We have only one patch 9737897 for AIX platform on the top of PSU 10.2.0.4.3.

   10.2.0.4.3 PSU does not include the fix of this bug. We have a patch on the top of 10.2.0.4.3 PSU for AIX platform.

   Customer is now trying to simulate the issue and they have setup 3 SQL to periodcallly monitor SGA status.
   Would you please help to give a brief explanation on these 3 SQL result?
   For example, What is the meaning if number of SQL reture is keep increasing during simulation test?

   SQL 1)
   – select ch.mesgcnt_ksrchdl
   from x$ksrchdl ch, x$ksrcdes cd, x$ksrcctx cc
   where cd.indx = cc.indx
   and cc.addr = ch.ctxp_ksrchdl
   and cd.name_ksrcdes = ‘obj stat del channel’;

   SQL 2)
   – select * from v$sgastat where name in (‘obj stat memo’);

   SQL 3)
   – select distinct fts_objd, fts_objn from x$ksolsfts x
   where not exists (select 1 from obj$ o
   where o.dataobj#=x.fts_objd and o.obj#=x.fts_objn);

   1. SQl 1: get the number of “obj stat del channel” channel message processed. The issue is reproduced If it shows small value and do not increasing.

   2. SQL 2: get the size of ‘obj stat memo’ sub-heap. The issue is reproduced If it shows large value and keep increasing.

   3. SQL 3: get the number of count non-existent (dropped/truncated) objects that still having object stats. The issue is reproduced If it shows large value and keep increasing.

   The most important SQLs are SQL 2,3 outputs. We recommend to run them periodically. Especially, before and after truncating operations.

   回复