| << Back to previous view |
|
[QA-49] ORA-4031: High Allocation for "Oracle Text Commit new id" in Shared Pool. Created: 05/Nov/10 Updated: 05/Nov/10 |
|
| Status: | Closed |
| Project: | Questions & Answers |
| Fix Version/s: | None |
| Type: | Oracle - Database Tuning | Priority: | Major |
| Reporter: | ubTools Support | Assignee: | ubTools Support |
| Resolution: | Answered | Votes: | 0 |
| Product Version: | 10.2.0.4 |
| Operating System: | Solaris |
| Host Name: | . |
| Database Name: | . |
| Description |
|
The customer encountered ORA-4031 and trace file generated. SGA is an ASMM SGA. The application uses Oracle Text.
|
| Comments |
| Comment by 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. |
| Comment by ubTools Support [ 05/Nov/10 10:35 PM ] |
|
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. |
| Comment by 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:
|