History | Log In     View a printable version of the current page.  
   HOME       BROWSE PROJECT       FIND ISSUES   
    QUICK SEARCH:  
Issue Details (XML | Word | Printable)

Key: QA-49
Type: Oracle - Database Tuning Oracle - Database Tuning
Status: Closed Closed
Resolution: Answered
Priority: Major Major
Assignee: ubTools Support
Reporter: ubTools Support
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Questions & Answers

ORA-4031: High Allocation for "Oracle Text Commit new id" in Shared Pool.

Created: 05/Nov/10 10:01 PM   Updated: 05/Nov/10 10:47 PM
Fix Version/s: None

Product Version: 10.2.0.4
Operating System: Solaris
Host Name: .
Database Name: .


 Description  « Hide
The customer encountered ORA-4031 and trace file generated. SGA is an ASMM SGA. The application uses Oracle Text.

 All   Comments   Change History      Sort Order: Ascending order - Click to sort in descending order
[ Permlink | « Hide ]
ubTools Support - 05/Nov/10 10:24 PM
Analysis of the Trace:

The Requested SUBPOOL:

.....
=================================
Begin 4031 Diagnostic Information
=================================
.....
HEAP DUMP heap name="sga heap(3,0)"  desc=380043660
 extent sz=0xfe0 alt=216 het=32767 rec=9 flg=-126 opc=0
 parent=0 owner=0 nex=0 xsz=0x1000000
 latch set 3 of 4
 durations enabled for this heap
 reserved granules for root 0 (granule size 16777216)
.....

The allocation was requested from sga heap(3,0), which is (SUBPOOL:3,DURATION:0).

All SUBPOOLS and Their DURATION Memories:

.....
HEAP DUMP heap name="sga heap(1,0)"  desc=380030610
Total heap size    =218102664
Total free space   =  1066928
Total reserved free space   =  8439520
Unpinned space     = 38812528  rcr=11971 trn=17906
Permanent space    =208595160
HEAP DUMP heap name="sga heap(1,1)"  desc=380031e68
Total heap size    = 67108512
Total free space   =  2912528
Total reserved free space   =  1382816
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(1,2)"  desc=3800336c0
Total heap size    =167771280
Total free space   = 92743480
Total reserved free space   =  3852856
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(1,3)"  desc=380034f18
Total heap size    =268434048
Total free space   = 74547592
Total reserved free space   = 13497472
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(2,0)"  desc=380039e38
Total heap size    =201325536
Total free space   =    17200
Total reserved free space   =  8435920
Unpinned space     = 26474112  rcr=7934 trn=8094
Permanent space    =192871456
HEAP DUMP heap name="sga heap(2,1)"  desc=38003b690
Total heap size    = 83885640
Total free space   = 48723768
Total reserved free space   =  1035792
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(2,2)"  desc=38003cee8
Total heap size    =369096816
Total free space   =258674312
Total reserved free space   = 16982464
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(2,3)"  desc=38003e740
Total heap size    =218102664
Total free space   = 17202608
Total reserved free space   = 10966696
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(3,0)"  desc=380043660
Total heap size    =184548408
Total free space   =    13008
Total reserved free space   =  5061928
Unpinned space     = 26943408  rcr=4930 trn=9425
Permanent space    =179472608
HEAP DUMP heap name="sga heap(3,1)"  desc=380044eb8
Total heap size    = 67108512
Total free space   = 27568352
Total reserved free space   =     4744
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(3,2)"  desc=380046710
Total heap size    =352319688
Total free space   =233302736
Total reserved free space   = 15981216
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(3,3)"  desc=380047f68
Total heap size    =385873944
Total free space   =143746536
Total reserved free space   = 19402616
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(4,0)"  desc=38004ce88
Total heap size    =184548408
Total free space   =     8616
Total reserved free space   =  7592328
Unpinned space     = 28725496  rcr=8459 trn=9864
Permanent space    =176946600
HEAP DUMP heap name="sga heap(4,1)"  desc=38004e6e0
Total heap size    = 83885640
Total free space   = 33356784
Total reserved free space   =  1189120
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(4,2)"  desc=38004ff38
Total heap size    =335542560
Total free space   =238988592
Total reserved free space   = 16293768
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(4,3)"  desc=380051790
Total heap size    =721416504
Total free space   =445595432
Total reserved free space   = 33743680
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
.....

All PERMANENT SPACES were allocated in DURATION 0. Although there are enough free spaces in the other DURATIONS of (3,1),(3,2),(3,3); free space can not be allocated from them.

.....
duration memory (duration 0) cannot take free memory from other durations within the same subpool.  
It can only get more memory by being given a new complete EXTENT (granule) from the granule management code.
.....

Ref: Oracle Bug 9911213: ORA-04031 AFTER APPLYING 10.2.0.4 PATCSHET

Since the lower limit of BUFFER CACHE was determined by DB_CAHCE_SIZE parameter; SHARED POOL could not grow by allocating a new EXTENT, then ORA-4031 appeared.

SUBPOOL Allocations:

.....
==============================
Memory Utilization of Subpool 1
================================
     Allocation Name          Size   
_________________________  __________
"free memory              "   215299680  
.....
"sql area                 "   151923248
.....
"Oracle Text Commit new id"   399237696
.....
"library cache            "    30711448
.....
==============================
Memory Utilization of Subpool 2
================================
     Allocation Name          Size   
_________________________  __________
"free memory              "   367295736
.....
"sql area                 "   160984248
.....
"Oracle Text Commit new id"   392833064
.....
"library cache            "    35069800
.....
==============================
Memory Utilization of Subpool 3
================================
     Allocation Name          Size   
_________________________  __________
"free memory              "   450731968  
.....
"sql area                 "   182415376
.....
"Oracle Text Commit new id"   417149240
.....
"library cache            "    39156336
.....
==============================
Memory Utilization of Subpool 4
================================
     Allocation Name          Size   
_________________________  __________
"free memory              "   781766288
.....
"sql area                 "   156513808
.....
"Oracle Text Commit new id"   410783408
.....
"library cache            "    31300664

The total size of Oracle Text Commit new id is 1.5GB (399237696+392833064+417149240+410783408). It's high.


[ Permlink | « Hide ]
ubTools Support - 05/Nov/10 10:35 PM - edited
Oracle Text Commit new id Allocation Trend:

An Excerpt from SGA Stat:

SQL> select a.instance_number,begin_interval_time, bytes from dba_hist_sgastat a, dba_hist_snapshot b
  2  where pool='shared pool' and
  3        a.snap_id=b.snap_id and
  4        a.instance_number=b.instance_number and
  5        name='Oracle Text Commit new id'
  6  order by begin_interval_time;

.....
              1 06/10/2010 01:00:07,750                                                      352864368
              1 06/10/2010 02:00:55,107                                                      353711568
.....
              1 12/10/2010 11:00:12,212                                                      448444792
              1 12/10/2010 12:00:27,412                                                      449299672
              1 12/10/2010 13:00:12,435                                                      450157752
              1 12/10/2010 14:00:19,294                                                      450179512
.....
              1 04/11/2010 14:31:10,604                                                     1622639416
              1 04/11/2010 14:40:18,341                                                     1623339552
              1 04/11/2010 14:50:28,971                                                     1623879936
              1 04/11/2010 15:00:40,721                                                     1623880712

722 rows selected.

SQL>

Oracle Text Commit new id had increased in small sizes.


[ Permlink | « Hide ]
ubTools Support - 05/Nov/10 10:43 PM
Summary:

Root Cause:

This problem is Oracle BUG:8593562 encountered in Oracle Text environment.

.....
It is incremented as the space is allocated, but not decremented as it is freed.
 It will reset when the instance is restarted.
.....
The bug is currently in work by Development and expected to be resolved in a future release.
.....

Ref: Growth of "Oracle Text Commit new id" memory with Sync on Commit Index [ID 872413.1]

Workaround:

  • Restart the INSTANCE.
Powered by Atlassian JIRA the Professional Issue Tracker. (Standard Edition, Version: 3.12.3-#302) - Bug/feature request - Atlassian news - Contact Administrators