ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [729], [10992], [SPACE LEAK] Example

The customers got  this error every alternative days on Version  9.2.0.7. They did increase the shared pool from 450MB to 704MB. Let’s see the alert.log and the last generated trace file.

SQL> l
1  select  nam.ksppinm NAME,
2  val.KSPPSTVL VALUE
3  from x$ksppi nam,
4  x$ksppsv val
5  where nam.indx = val.indx
6  and  nam.ksppinm like '%shared%'
7* order by 1
SQL> /

NAME                                                              VALUE
----------------------------------------------------------------  ----------
_all_shared_dblinks
_shared_pool_reserved_min_alloc                                   4400
_shared_pool_reserved_pct                                         5
hi_shared_memory_address                                          0
max_shared_servers                                                20
shared_memory_address                                             0
shared_pool_reserved_size                                         31876710
shared_pool_size                                                  738197504
shared_server_sessions                                            0
shared_servers                                                    0

10 rows selected.

SQL>  select FREE_SPACE,LAST_FAILURE_SIZE,REQUEST_FAILURES,LAST_MISS_SIZE  from v$shared_pool_reserved;

FREE_SPACE LAST_FAILURE_SIZE  REQUEST_FAILURES LAST_MISS_SIZE
---------- -----------------  ---------------- --------------
19018368               456               725              0

1 row selected.

Alert log
~~~~~~~~~~
Thu May 28 19:05:11 2009
Errors in file  /u01/app/oracle/admin/preg062/udump/preg062_ora_17314.trc:
ORA-00600:  internal error code, arguments: [729], [10992], [space leak], [], [],  [], [], []

Trace File
~~~~~~~~~~~
Dump file  /u01/app/oracle/admin/preg062/udump/preg062_ora_17314.trc
Oracle9i  Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the  Partitioning, OLAP and Oracle Data Mining options
JServer Release  9.2.0.7.0 - Production
ORACLE_HOME =  /u01/app/oracle/product/920preg062
System name:	SunOS
Node name:	 iccscorp
Release:	5.9
Version:	Generic_122300-22
Machine:	sun4u
Instance  name: preg062

Error
-----
ORA-00600: internal error code,  arguments: [729], [10992], [space leak], [], [], [], [], []

Current  SQL
-----------
None

Call Stack
----------
ksedmp  kgeriv kgesiv ksesic2 ksmuhe ksmugf ksuxds ksudel opilof opiodr ttcpip  opitsk opiino opiodr opidrv sou2o main start

Session info
------------
SO:  411536570, type: 4, owner: 40e583e08, flag: INIT/-/-/0x00
(session) trans: 0, creator: 40e583e08, flag: (41) USR/- BSY/-/-/DEL/-/-
DID: 0001-00F9-00000F5B, short-term DID:  0000-0000-00000000
txn branch: 0
oct:  0, prv: 0, sql: 417fbbf18, psql: 416fa9840, user: 31/MATRIXTWO
O/S info: user: matrixadmin, term: , ospid: 17281, machine: iccscorp
program: mql@iccscorp (TNS V1-V3)
last wait for  'SQL*Net message from client' blocking sess=0x0 seq=3208 wait_time=836
driver id=54435000, #bytes=1, =0

ORA-04031  details
~~~~~~~~~~~~~
Begin 4031 Diagnostic Information

Allocation  Request
-------------------
Allocation request for: kkslpkp -  literal info.
Heap: 3d6fb45f0, size: 4200

Call stack
-----------
ksm_4031_dump   ksmasg  kghnospc  kghalp  kghsupmm  kghssgai  kkslpkp  kkslpgo  kkepsl   kkecdn  kkotap  kkoiqb  kkooqb  kkoqbc  apakkoqb
apaqbd  apadrv   opitca  kkssbt  kksfbc  kkspfda  kpodny  kpoal8  opiodr  ttcpip  opitsk   opiino  opiodr  opidrv  sou2o  main

Session Info
-------------
SO:  411536570, type: 4, owner: 40e583e08, flag: INIT/-/-/0x00
(session) trans: 0, creator: 40e583e08, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-00F9-00000F5B, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 0, prv: 0, sql: 4311e4e30,  psql: 4311e4e30, user: 31/MATRIXTWO
O/S info: user: matrixadmin,  term: , ospid: 17281, machine: iccscorp
program:  mql@iccscorp (TNS V1-V3)
application name: mql@iccscorp (TNS  V1-V3), hash value=0
last wait for 'SQL*Net message from client'  blocking sess=0x0 seq=3196 wait_time=1975
driver  id=54435000, #bytes=1, =0

Number of Subpools and allocations
----------------------------------
===============================
Memory  Utilization of Subpool 1
===============================
Allocation Name          Size
_________________________   __________
"free memory              "    25065216
"miscellaneous             "    14914048

===============================
Memory  Utilization of Subpool 2
===============================
Allocation Name          Size
_________________________   __________
"free memory              "     9306608
"miscellaneous             "    19358000

===============================
Memory  Utilization of Subpool 3
===============================
Allocation Name          Size
_________________________   __________
"free memory              "    25209192
"miscellaneous             "    10192440

===============================
Memory  Utilization of Subpool 4
===============================
Allocation Name          Size
_________________________   __________
"free memory              "    15005800
"miscellaneous             "    11097176

LIBRARY CACHE STATISTICS:
namespace            gets hit ratio      pins hit ratio    reloads   invalids
--------------  --------- --------- --------- --------- ---------- ----------
CRSR            400143894     0.951 1821611655     0.969   10619950      63892
TABL/PRCD/TYPE  230543353     0.996 255666572     0.934    7504796          0

Connection  Mode & Relevant parameters
--------------------------------------
sga_max_size       = 3159332528
shared_pool_size =  738197504
db_cache_size       =  956301312
cursor_sharing      = SIMILAR
pga_aggregate_target  = 2097152000

It seems the ORA-04031 is the main issue, which triggered the ORA-00600 [729] error, after the session got abnormally terminated or killed.

Memory request failed on “shared pool” while trying to allocate 4200 bytes even though you have 9 to 25 mb of free space in 4 subpools.

I have reviewed the alert, trace and RDA report and following are my findings.

# Shared_pool_size is 738197504 and 4 subpools are used.
# Memory request failed for 4200 bytes.
# None of the components in subpools are showing any abnormal growth.

Suggestion
—————-
Issue is not exactly matching with any known bugs. Modifying the memory related parameters will help to avoid these errors.

1) Reduce the number of subpools to 2 from 4, by setting “_kghdsidx_count”=2 and restart the database. This will also help to reduce the shared pool fragmentation. Refer Note 396940.1

SQL> alter system set “_kghdsidx_count”=2 scope=spfile;

2) I have checked the memory request failure which is showing the size of 4200 bytes plus.
Set the _shared_pool_reserved_min_alloc=4000 which will help to allocate memory in reserved area, if the request is greater than 4000 bytes.

alter system set “_shared_pool_reserved_min_alloc”=4000 scope=spfile;

3) Set the shared_pool_reserved_size to 10 to 15 % of the shared pool size, by setting _shared_pool_reserved_pct parameter.

SQL> alter system set “_shared_pool_reserved_pct”=10 scope=spfile;

Implement the above changes and restart the database. This will help to avoid the shared pool fragmentation and helps to avoid the ORA-04031/ORA-00600 [729] errors.

After applying above change ,the error has not occured  again.

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569