Articles Comments

AskDba.org Weblog » Entries tagged with "ora-4031"

Simplified Approach to Resolve ORA-4031

July 21st, 2008 | 4 Comments

After writing few Case studies and other related articles, I will be sharing my approach for Resolving ORA -4031 error. First we will see what ORA-4031 actually means. 04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\,\"%s\")" // *Cause: More shared memory is needed than was allocated in the shared pool. // *Action: If the shared pool is out of memory, either use the // dbms_shared_pool package to pin large packages, // reduce your use of shared memory, or increase the amount of // available shared memory by increasing the value of the // INIT.ORA parameters "shared_pool_reserved_size" … Read entire article »

Filed under: Featured, database, oracle

ORA- 4031 – A Case Study

June 20th, 2008 | 1 Comment

Today I will be taking up one ORA-4031 issue we faced on one of our client database. Please note that SQL statements mentioned below have been changed and does not reveal any confidential information. Our client was facing ORA-4031 on a 9.2 database.This was a new Database which had gone Live recently. Following error messages were recorded in alert log. ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","select owner#,name,namespace...","sga heap(1,0)","library cache") Initial shared pool at time of failure was 160 Mb. After the failure the shared_pool_size was increased to 1 Gb. We did not have any other information and were asked to perform RCA for the same. Luckily we had Statspack configured on the server, so we took … Read entire article »

Filed under: database, oracle

Application Design and ORA – 4031

April 27th, 2008 | 1 Comment

Much has been written on designing good application (which uses bind variables) so as to have better performance and avoid shared pool memory issues. Tom Kyte explains this on one of his post consequences of not using bind variables If you do not use bind variables and you flood the server with hundreds/thousands of unique queries you will -run dog slow -consume a ton of RAM (and maybe run out) -not scale beyond a handful of users, if that among other really bad side effects. This is very true and have observed it myself many times. But today we will see one more kind of bad application design which I came across working on a ORA – 4031 issue. Environment was already using CURSOR_SHARING=SIMILAR (application developers were not ready to change code, so it was Kind … Read entire article »

Filed under: database, oracle

Tweaking _Shared_pool_reserved_min_alloc and ORA – 4031

April 7th, 2008 | 3 Comments

Here comes one more article dedicated towards diagnosing ORA- 4031 and related parameters. While googling on ORA-4031 error or working on TAR with Oracle Support you will most likely hit this parameter “_Shared_pool_reserved_min_alloc “ and find a advice for altering this parameter to 4000 or so. And many times you go and implement it as this advice has come from Oracle support or you find it on one of your Favourite Oracle DBA’s site. But how many times have you exactly gone ahead and tried to understand the validity of this suggestion. Oracle Shared pool is managed by Heap Memory manager and is divided into memory chunks (Can visualize as blocks in tablespace) and the free memory chunks are managed by memory freelists. When a SQL statement is parsed in shared … Read entire article »

Filed under: database, oracle

Shared Sub Pools

March 25th, 2008 | 1 Comment

Today I will discuss about Shared pool subpools which have been introduced in release 9iR2 and higher (To be precise >9.2.0.5) and can be controlled by a hidden parameter “_kghdsidx_count “ Most of you would not have come across this term/parameter unless you have faced ORA-4031 error. As you all are aware that Shared Pool (part of SGA) contains the library cache, the dictionary cache , buffers for parallel execution messages, and control structures and also UGA in case of Shared Server Configuration(If Large pool is configured then UGA would be stored in Large Pool). The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles. Shared Pool Latch is used … Read entire article »

Filed under: database, oracle