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.