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.
Comment