1) Decrease the number of subpools in use, or increase SHARED_POOL_SIZE, depending on the current number of subpools: NOTE: Decreasing the number of subpools may help alleviate the imbalance problem without affecting performance as heavily. • Flushing the shared pool will cause that all the cursor that are not in use are removed from the library cache. Check if Fragmentation o Library Cache Hit Ratio. For sake of this experiment I set the _kghdsidx_count variable to 7, this parameter can be used to force the number of subpools you want. If the current number of subpools is 2, increase the parameter SHARED_POOL_SIZE by 15%

In such cases you would see the shared pool free memory drop to near-zero in V$SGASTAT.

The level of impact is dependent on many factors. If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a result of dynamic SQL fragmenting the shared pool. AND ksmchcls=’free’ SELECT '1 (140-267)' bucket, ksmchcls, ksmchidx,20*TRUNC(ksmchsiz/20), So from above output I see that in my instance all 7 shared pool subpools are in use. All 7 latches are always there, even if you have less subpools in use, that number is hardcoded into Oracle. V$SGASTAT unfortunately just shows a sum of all subpools: However when we look into the source code of GV$SGASTAT we see this (output edited for readability): V$SGASTAT gets its shared pool stats from x$ksmss. This column contains the subpool number in it! freeabl 4768 7514504 1.54k then consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to allow the database putting more objects into the shared pool reserved space and then increase the SHARED_POOL_SIZE if the problem is not resolved. • SHARED_POOL_RESERVED_SIZE – 10% of ahred_pool_size Again testing of the change in your environment is the only reliable method to determine the performance impacts in your database. So be advice that flushing the shared pool is not always the solution for a bad application. R-free 12 8059200 655.86k The “2” in “(2,0)” means that the failure happened in shared pool sub pool number 2 and the “0” shows sub-sub-pool number 0.

Looks like the KTSL subheap allocations below are done from duration 0. (adsbygoogle = window.adsbygoogle || []).push({ There are few different ways for detecting how many subpools you have in use. But you can see how many latches have a significant number of gets against them. TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total" R-freea 24 960 .04k 11G Automatic Memory Management is enabled when the parameter MEMORY_TARGET is greater than zero and the current setting can be obtained quering the V$MEMORY_DYNAMIC_COMPONENTS and V$MEMORY_RESIZE_OPS views. 1. The best way is by modifying the application to use bind variables. This may be useful in cases where due some specific issue some subpool always needs much more memory than others. SELECT '3-5 (524-4107)' bucket, ksmchcls, ksmchidx, 500*TRUNC(ksmchsiz/500), Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools [Video] [ID 146599.1]. GROUP BY ksmchcls, ksmchidx, 20*TRUNC(ksmchsiz/20) To resolve this consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower the number of objects being cached into the shared pool reserved space and increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the available memory in the shared pool reserved space.

There’s an additional concept called sub-sub-pool starting from 10.2 where each shared pool sub-pool is split futher into 4 areas (allocations with different expected lifetime/durations go into different sub-sub-pools, but the same sub-pool latch protects all activity in sub-sub pools too). 4) If the latest patchset recommended by the above notes have been applied and ORA-04031 errors persist, please submit a new Service Request to have the issue investigated by Oracle Support.

JAVASCRIPT IS DISABLED. So, if you have ORA-4031 out of shared pool memory errors or suspect that shared pool memory pressure is the cause of some performance problem (like shared pool latch contention and excessive library cache evictions/reloads) then you’d want to monitor shared pool memory breakdown at the subheap level.

However, changing this particular hidden parameter will not have side-effects within the database and can be modified as a workaround.

COUNT(*), MAX(ksmchsiz) , }); Your email address will not be published. o Not using bind variables. This parameter along with the SHARED_POOL_RESERVED_MIN_ALLOC parameter, can be used to avoid the occurrence of this error from situations where shared pool fragmentation forces Oracle to search for and free chunks of unused pool to satisfy the current request. GROUP BY ksmchcls, ksmchidx, 500*TRUNC(ksmchsiz/500) o Instructions for capturing a heapdump from the next occurrence of an ORA-04031 error are shown below. causes of ORA-4031 errors. Instead, you must increase your JAVA_POOL_SIZE, restart your server, and try again. ORA-04031: unable to allocate 4224 bytes of shared memory Posted on Aug 26, 2008 at 01:07 PM | 217 Views Follow Italic strings can show various different values but essentially they’re just some metadata describing for what did we try to allocate that memory. ** This is what I’m explaining in current post. NOTE:811974.1 – ORA-4031 In Partition Maintenance Job From Subpool Imbalance (Doc ID 811974.1) NOTE:4184298.8 – Bug 4184298 – Subpool imbalance for “session parameters” can lead to ORA-4031. This allows some subpools to grab more memory than others if they have more allocations after instance startup. I will talk about troubleshooting this problem in a separate post. The following SQL shows the chunks available in the free list: SELECT ‘0 (<140)' bucket, ksmchcls, ksmchidx, 10*TRUNC(ksmchsiz/10) "From", GROUP BY ksmchcls, ksmchidx, 1000*TRUNC(ksmchsiz/1000); You can also use this view as follows to review overall memory usage in the SGA: CLASS NUM SIZ AVG SIZE
The “partitions” are called shared pool subpools and there can be up to 7 subpools. Database instance crash after ORA-4031 ("shared pool","unknown object","KGSKI schedule","KGKP cache")The database instance alert log would report the ORA-4031 [KGKP cache], followed by the instance crash: Errors in file /u01/app/oracle/diag/rdbms///trace/_dbrm_pppp.trc  (incident=680300):ORA-04031: unable to allocate nnn bytes of shared memory ("shared pool","unknown object","KGSKI schedule","KGKP cache")Incident details in: /u01/app/oracle/diag/rdbms///incident/incdir_680300/_dbrm_pppp_i680300.trcDumping diagnostic data in directory=[cdmp_20140709203001], requested by (instance=N, osid=pppp (DBRM)), summary=[incident=680300].Use ADRCI or Support Workbench to package the incident.See Note 411.1 at My Oracle Support for error and packaging details.Errors in file /u01/app/oracle/diag/rdbms///trace/_dbrm_pppp.trc:ORA-04031: unable to allocate nnn bytes of shared memory ("shared pool","unknown object","KGSKI schedule","KGKP cache")DBRM (ospid: pppp): terminating the instance due to error 4031System state dump requested by (instance=N, osid=pppp (DBRM)), summary=[abnormal instance termination].System State dumped to trace file /u01/app/oracle/diag/rdbms///trace/_diag_98129.trcORA-1092 : opitsk aborting processLicense high water mark = 1970Instance terminated by DBRM, pid = pppp.
This is not intended to be a long term fix.

The mention of “shared pool” in the error message is a misleading reference to running out of memory in the “Shared Global Area”. Using this view you will be able to find out how the free space is currently allocated, which will be helpful to undrestand the level of fragmentation of the shared pool. The bold part above is the total memory reserved for shared pool. free 331 151736448 447.67k 1) Decrease the number of subpools in use, or increase SHARED_POOL_SIZE, depending on the current number of subpools.

(from 10g anyway).