Hi I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production. Its a three node RAC. I have below setup for each of the node
sga_target - 160GB for each node
Memory_target -0 for each node
memory_max_target-0 for each node
pga_agreegate_target - 60G for each node
shared_pool_size-20G for each node
large_pool_size-4G for each node
java_pool_size-2G for each node
streams_pool_size - 512M for each node
shared_pool_reserved_size - 1154272460 bytes for one node and 1181116006 bytes for other two.
open_cursors is 2500 for each node
We had 100+ report failure due to connection issue, they were erroring out with below error to one of DB node.
"Database access error. Reason ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","SELECT ObjectID, Version, La...","sga heap(1,0)","kglsim object batch")#012. (FWB 00090) " . It got resolvd after bouncing the node but As per techops/oracle guys, this might happen due to bug Bug 10082277: EXCESSVIE ALLOCATION IN PCUR OF “KKSCSADDCHILDNO” CUASE ORA-4031 ERRORS.and they have provided multiple solutions as below and i need expert suggestion in finding the best fit solution as per my DB configuration.also how can i set/configure alert to catch this error in first place, as because we get this issue noticed after 24hrs of occurrence and have huge customer escalation due to this.
Regarding Option-1 ,in this case, i am afraid disabling adaptive cursor sharing may badly impact execution plan of sql queries those using bnd variable but having major difference in data pattern of specific bind value. in case of Option 5:-As i believe, we have taken care of all of the possible places in our application code where bind variable is required, so is there any historical view from which i can check if any specific sql cause the memory bottleneck during the issue which has caused ORA-04031 error.and fixing that sql will fix the issue permanently.
Solution Options:
1. We see large memory allocations in KGLH0 memory structure. To decrease the memory allocation in KGLH0 structure, apply Patch 13456573 on top of 11.2.0.3 or use the workaround:
Disable extended or adaptive cursor sharing with either:
_optimizer_extended_cursor_sharing = none
or
_optimizer_extended_cursor_sharing_rel = none
To download the patch use the link:
SR1390476030790 " >https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=13456573
More details in:
Bug 13456573 - Many child cursors / ORA-4031 with large allocation in KGLH0 using extended cursor sharing ( Doc ID 13456573.8 )
+
2. Since the ASMM (you have sga_target>0) is used, set _shared_pool_reserved_pct to 10 or 15 to ensure that when the Shared Pool grows or shrinks automatically, the Reserved Area will change as well.
SQL> alter system set "_shared_pool_reserved_pct"=15 scope=spfile;
--restart the instance
FYI: By default, Oracle configures a small Reserved Pool (or Reserved Area) inside the Shared Pool. This memory can be used for operations such as PL/SQL and trigger compilation or for temporary space while loading Java objects. After the memory allocated from the Reserved Pool is freed, it returns to the Reserved Pool. 5% of the Shared Pool is set aside as the Reserved Pool to handle allocations of memory higher than defined by the hidden parameter _shared_pool_reserved_pct. In some application environments, 5% is too small.
+
3. Set _kghdsidx_count to 5 in order to be sure the shared pool will be divided only in 5 subpools and not more. You currently seem to have 3 subpools according to the information dumped in the trace file.
connect / as sysdba
SQL > alter system set "_kghdsidx_count"=5 scope=spfile;
- restart the database
FYI: Starting with Oracle 9i the shared_pool will be divided into multiple sub-heaps. Until 9.0, the shared pool was always allocated in one large heap rather than multiple subheaps. The number of subpools even if produced a better performance, also it
could produce ora-4031 errors when there is not space in the subpool as a result of fragmentation. One process will use only a specific subpool and if an error is reported, it wont be migrated to other subpool. Oracle 9i implemented multiple subpools to avoid shared pool latch contention. But, the cost of the multiple pools is that the size of each subpool will be smaller and hence more susceptible to an ORA-4031. So, the parameter _kghdsidx_count can be used to override the default for the number of sub pools.
More details in -
Troubleshooting and Diagnosing ORA-4031 Error [Video] ( Doc ID 396940.1 )
+
4. If possible - Decrease the number of open_cursors from 1500 to 1000 or even 600. (Current value - open_cursors=2048)
The open_cursors parameter is defined per session. Having many cursors open per session is too much and it means that the cursors are not correctly handled/closed at application level.
600 cursors created per sessions should be enough for any application.
+
5. As I the value of cursor_sharing parameter is EXACT . Please check it and try to change the applications to use bind variables instead of literals in case they do not do that already. Find also details about that in Doc ID 62143.1 , section : Eliminating Literal SQL. Otherwise, if the application cannot be changed, set cursor_sharing=force.
Supplementary,
6. The above will limit the excessive allocations in KGH0 and will reduce fragmentation and caching of cursors to a minimum. In case you still get ORA-4031 it may be also a case of insufficient memory, and you will need to supplementary increase the value of sga_target. Currently, the size of sga_target does not appear, nevertheless, to be the main cause of the error, so it is a matter of monitoring in the future.