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

My customer has an application which using a lots of “temporary” table. They create so many tables but only operate once.And they are using automatic SGA management,all this cause large memory usage in shared pool, especially the “obj stat memo”.
Recently they encountered  the error 4031 , there are still 1G space available in shared pool, so i believe the root cause is too many obj stat fragment .
Verified the issue in the log file as noted below:
LOG FILE
-----------------------
Filename  =gpnms4_j000_22234.trc.log
See the following error:

***  2010-05-22 21:47:37.388
ORA-12012: error on auto execute of job 2
ORA-04031:  unable to allocate 4064 bytes of shared memory ("shared pool","select  SYSDATE+(30 / (24*60)...","sga heap(4,0)","kglsim heap")
***  2010-05-22 21:47:37.410
ORA-00604: error occurred at recursive SQL  level 1
ORA-04031: unable to allocate 4064 bytes of shared memory  ("shared pool","select sysdate+(64)/(24*60) ...","sga heap(6,0)","kglsim  heap")
ORA-12012: error on auto execute of job 2
ORA-04031:  unable to allocate 4064 bytes of shared memory ("shared pool","select  SYSDATE+(30 / (24*60)...","sga heap(4,0)","kglsim heap")
4031 specified trace as noted below:

TRACE FILE gpnms4_j000_22234.trc

------------
*** SERVICE  NAME:(SYS$USERS) 2010-05-22 21:47:37.240
*** SESSION ID:(121.20137)  2010-05-22 21:47:37.240
*** 2010-05-22 21:47:37.240
=================================
Begin  4031 Diagnostic Information
=================================
The  following information assists Oracle in diagnosing
causes of  ORA-4031 errors.  This trace may be disabled
by setting the init.ora  _4031_dump_bitvec = 0
=====================================
Allocation  Request Summary Informaton
=====================================
Current  information setting:  04014fff
SGA Heap Dump Interval=3600 seconds
Dump Interval=300 seconds
Last Dump Time=05/22/2010 21:47:37
Dump Count=1
Allocation request for:      kglsim heap
Heap:  380048e48, size: 4064
******************************************************
HEAP  DUMP heap name="sga heap(4,0)"  desc=380048e48
extent sz=0xfe0  alt=216 het=32767 rec=9 flg=-125 opc=2
parent=0 owner=0 nex=0  xsz=0x1000000
latch set 4 of 6
durations enabled for this heap
reserved granules for root 0 (granule size 16777216)
====================
Process  State Object
====================
----------------------------------------
SO:  5761a3750, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle  pid=37, calls cur/top: 56e79bdc8/572cd9160, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info)  last post received: 1359 0 4
last post  received-location: kslpsr
last process to post me:  57f156848 1 6
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by  me: 57f156848 1 6
(latch info) wait_event=0 bits=60
holding     (efd=19) 5731bf3a0 Child shared pool sim alloc level=6 child#=12
Location from where latch is held: kglsim_chk_heaplist: alloc:
Context saved from call: 0
state=busy, wlstate=free
holding    (efd=19) 5711d3de0 Child library cache level=5 child#=15
Location from where latch is held: kglobpn: child:: latch
Context saved from call: 22
state=busy, wlstate=free
Process Group: DEFAULT, pseudo proc: 57416ad08
O/S info:  user: oracle, term: UNKNOWN, ospid: 22234
OSD pid info: Unix  process pid: 22234, image: oracle@SHUDBa1 (J000)

SO:  5761d48b0, type: 4, owner: 5761a3750, flag: INIT/-/-/0x00
(session)  sid: 121 trans: 0, creator: 5761a3750, flag: (40000041) USR/-  BSY/-/-/-/-/-
DID: 0001-0025-0060BC7E, short-term DID:  0000-0000-00000000
txn branch: 0
oct: 0,  prv: 0, sql: 0, psql: 0, user: 93/WUBIAO
O/S info: user: oracle,  term: UNKNOWN, ospid: 22234, machine: SHUDBa1
program:  oracle@SHUDBa1 (J000)
last wait for 'SGA: allocation forcing  component growth' blocking sess=0x0 seq=2 wait_time=10349 seconds since  wait started=0
=0, =0, =0
Dumping Session Wait History
for 'SGA: allocation forcing component growth' count=1  wait_time=10349
=0, =0, =0
for 'db file sequential  read' count=1 wait_time=89
file#=7, block#=6f767, blocks=1
temporary object counter: 0

----- Call Stack Trace -----
ksm_4031_dump  <- ksmasg <- kghnospc <- kghalo <- kglsim_chk_heaplist
<- kglsim_upd_newhp <- 3076 <- kglUpdateSimulator <-  kglobpn <- kglpim
<- qcdlgtd <- qcsfplob <-  qcsprfro <- qcsprfro_tree <- qcsprfro_tree
<-  qcspafq <- qcspqbDescendents <- qcspqb <- kkmdrv <- opiSem
<- opiprs <- kksParseChildCursor <- rpiswu2 <-  kksLoadChild <- kxsGetRuntimeLock
<- kksfbc <-  kkspsc0 <- kksParseCursor <- opiosq0 <- opiodr
<- rpidrus <- skgmstack <- rpidru <- rpiswu2 <- rpidrv
<- rpisplu <- rpispl <- kkjfnd <- kkjex1e  <- kkjsexe
<- kkjrdp <- opirip <- opidrv  <- sou2o <- opimai_real
<- main

==============================
Memory  Utilization of Subpool 1
================================
Allocation Name          Size
_________________________   __________
"free memory              "   390197664
"obj stat  memo            "   403349328
"KGH: NO ACCESS           "     16695456

==============================
Memory Utilization  of Subpool 2
================================
Allocation Name           Size
_________________________  __________
"free  memory              "   319286368
"obj stat memo            "    380580336
"KGH: NO ACCESS           "    16728160

==============================
Memory  Utilization of Subpool 3
================================
Allocation Name          Size
_________________________   __________
"free memory              "   325262592
"obj stat  memo            "   351782568
"KGH: NO ACCESS           "     16728160

==============================
Memory Utilization  of Subpool 4
================================
Allocation Name           Size
_________________________  __________
"free  memory              "   556035112
"obj stat memo            "    551594016
"KGH: NO ACCESS           "    32394336

==============================
Memory  Utilization of Subpool 5
================================
Allocation Name          Size
_________________________   __________
"free memory              "   577033336
"obj stat  memo            "   432672408
"KGH: NO ACCESS           "     16736320

==============================
Memory Utilization  of Subpool 6
================================
Allocation Name           Size
_________________________  __________
"free  memory              "   333050648
"obj stat memo            "    406731024
"KGH: NO ACCESS           "    16719968

LIBRARY  CACHE STATISTICS:
namespace           gets hit ratio      pins hit  ratio    reloads   invalids
-------------- --------- ---------  --------- --------- ---------- ----------
CRSR           2214572558      0.355 3977649054     0.403   81251674   34891116
TABL             69900473     0.844 438948912     0.885   11952398          0
BODY              1126452     0.970  86610514     0.999      50956          0
TRGR            104735463     0.994 156443548     0.994     369130          0
INDX             23588405     0.667  47086339     0.801    1478850          0
CLST              2746262     0.997   4303977     0.994      15628          0
At first, i think this “obj stat memory”  subheap can be cleaned up by flush shared pool, but i ‘m wrong ; The component is permanent in shared pool.
SQL>  select  * from v$sgastat where name='obj stat memo';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  obj stat memo                 1454448

SQL> alter system flush shared_pool;

System altered.
SQL> select  * from v$sgastat where name='obj stat memo';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  obj stat memo                 1454448
After that I  read the metalink note “Bug 5573238  Shared pool memory use / ORA-4031 due to “obj stat memo” in one subpool”, oracle support advise to workaround this problem by setting STATISTICS_LEVEL=BASIC or “_object_statistics”=false. But STATISTICS_LEVEL=BASIC will disable many automatic features like ASMM, Automatic statistics gathering;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.
And i have opened a new tar , the oracle support advise as below:

Trace file shows indeed a high size for the ‘obj stat memo’ component but also some imbalance between the subpools.

Bug 5573238 is not an issue here as fixed as from 10.2.0.4 but the workaround can be used here as well. So, setting
“statistics_level”=basic or “_object_statistics”=false should also workaround this issue.

The imbalance of the subpools on the other hand might be caused by bug 6271590 which is fixed as from PSU patch
10.2.0.4.2.

I would suggest to apply the latest PSU patch available right now and that is 10.2.0.4.4.

For the ‘obj stat memo’ issue, you have to choose between further investigating the issue or using the workaround by
setting “statistics_level”=basic or “_object_statistics”=false.

If you want to further investigate the issue, we will need a more detailed heapdump of the ORA-4031.

To Do:
1. Apply PSU patch 10.2.0.4.4 (patch 9352164) on top of patchset 10.2.0.4

2. Regarding the ‘obj stat memo’ issue:

a) Workaround the issue by setting “statistics_level”=basic or “_object_statistics”=false
+
Bounce the instance

-OR-

b) Further investigate the issue:

Set following parameters in the init.ora file (SPFILE/PFILE):
SQL> alter system set max_dump_file_size = unlimited scope=spfile;
SQL> alter system set events ‘4031 trace name heapdump level 536870914’ scope=spfile;

Bounce the instance

Once the ORA-4031 reoccurs, provide alert+trace file

So imbalance between subpool can be fixed , and will reduce the likelihood of  4031 occurrence. But we  can never flush huge memory used by “obj stat memory”,i think it’s awful .


Posted

in

by

Tags:

Comments

4 responses to “ora-4031 and "obj stat memory" component in Shared Pool”

  1. admin Avatar
    admin

    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 Avatar
    admin

    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 Avatar

    这个问题后来经过网友的指出发现在最新的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 Avatar

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *