ORA 4031 error, urgent

Hi all,
I'm getting ORA-4031 error :unable to allocate 6666240 bytes of shared memory("large pool","object","hash-join subh") error.

My current shared_pool_size is 220 MB. I want to set it to 320 MB. I'm running oracle 9i Rel 2 on linux 7.2 .C Can i adjust this parameter dynamically, then the DB is up and running , if yes, how can i do that. Can any help me with the steps please. I want to adjust the shared_pool_reserve_size also. Please let me know the steps asap, as all my users can't connect and are waiting. or else DO i need to restart my DB after setting in init.ora file?

Thanks in advance

that would be ure kernel parameters in /etc/system which need bumping up, shared memory and semaphores

Have you tried setting large_pool_size. Don't just go on increasing size of shared_pool_size.

Set PARALLEL_AUTOMATIC_TUNING to true even though you are not going to use any parallel clause. Because of this Oracle will automatically calculate value for large_pool_size.

thanks for your immediate response, even though i set my shared_pool_size to 320Mb, its not working. My large_pool_size is 10MB. my DB version is 9.0.1.4.0, Do i need to set the parameter parallel_automatic_tuning=true in my init.ora? and what about the large_pool_size.

thanks

are you using MTS

Just set parallel_automatic_tuning to true and do not set the value for large_poo_size... Oracle will automatically calculate value for large_pool_size...

Pando,
we are not using MTS.

Sameer, i set the parallel_automatic_tuning=true in my init.ora file and restarted the DB, and still giving the same problem.

thanks

If not MTS, then it won't be of much use.. Sorry I forgot to ask you before.. what are you other settings?.. what is the value for db_cache_size?.., Are you pinning large objects?

my java_pool_size is set to 0. Do i need to increase?? please help me, all my users are waiting

thanks

Is java application using database, then set it to 50M+ (atleast)

Sameer,
my application is using Java, and i set my java_pool_size to 60MB. still giving the same error, ORA -4031, unable to allocate 6666240 bytes. Can you help me in solving this probelm please.

thanks in advance

Oh boy!!

One of my database is accessed by java Application.. its 8.1.7
I have set

shared_pool : 90M
java_pool : 60M
db_block_size : 8192
processes : 150
open_cursors : 300
db_block_buffer : 65536

try reducing shared_pool and incresing java_pool size.. You have 9.2, Are you setting too much of db_cache_size? and btw hoe much physical memory you have.. hope you are not going out of it :D

Sameer,
don't get annoyed. I set all the parameters as you suggested. My DB version is 9.0.1.4.0. From my application (JAVA) some reports are working and some huge reports which involves huge SQL queries are not. Still not yet solved my problem. Please give me some suggestion.

Thanks a lot for your help sameer. waiting for your suggestions.

Quote:

---

Originally posted by pnrdba
don't get annoyed.

---

nopes.. never mind!

Is it possible for you to test those reports in stages.. Instead of just gpoing on tuning shared_pool and java_pool.. Is it possible to tune those huge queries... It might be other option..

I would try executing those reports for smaller range... You knwo what I mean!..

yes, i did it already. When i executed them in stages, and complete code either from TOAD or SQLPLUS client, its executing just like that with in no time, but from the application, its not.

thanks again

No... If you test in SQL*Plus or in TOAD, it won't use much of memory.. There is definately a difference in testing report thru application and in SQL*plus.. I mean try to test it using Java Application... Give smaller range while running rep thru' application and then check.. If it work for smaller range then increase it .. You will get a point where it gives the error.. Then try changing mem parameters or looking at the code..

I did it, its working for small reports, but its not working for huge reports. i didn't understand why, i increased my shared_memory to 500M also, still not working.

thanks

Does your huge reports run with small range? Do you pin huge objects?

what is o/p for

SELECT NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL='large pool' GROUP BY ROLLUP (NAME);

Quote:

---

9014 [BUG:2244789] ORA-4031 / excessive shared pool usage from query with full outer join / union

---

Are you using outer join .. Becaue I found a bug reported for this version at Metalink..

it´s complaining about large pool I dont understand why you keep on increasing shared pool

Hi...this could be the problem with ur OS.

open the kernel parameter file and

and increase the value of SHMMAX parameter.


Hope this will work.


Joe.

hmm first time I heard shmmax could cause ORA-04031

Any experience with that error related to SHMMAX?

Quote:

---

it´s complaining about large pool I dont understand why you keep on increasing shared pool

---

Pando,
You are right. When i tried increasing the shared pool size from 200M to 350M it didn't, but when i increased large_pool_size from 6M to 10M, it worked fine. But the only thing i'm worried about is, when my users are continuosly calling the same huge reports for testing purpose, it worked fine for 1-2 times, but later again, it gave the same old error ORA-4031. In the same application, when we are running small reports based on some particular client, its working fine ,but when we are trying to run a huge reports of all Currently these are my statistics:

Shared_pool_size=480M
large_pool_size=10M
Sort_area_size=64M

I can't figure out where exactly the problem is, can you please help me.

thanks in advance

If the database runs in DEDICATED MODE, I would choose to set the LARGE_POOL_SIZE=0 instead of 10M.

The following use LARGE POOL:

1, Parallel Query, to cache parallel message buffer.

2, Recovery Manager, to cache I/O buffer.

3, Shared Server, session memory for each client, the UGA.

Quote:

---

If the database runs in DEDICATED MODE, I would choose to set the LARGE_POOL_SIZE=0 instead of 10M.

---

If i'm using LARGE_POOL_SIZE=0, then my reports are not at all working . how can i make reports working then?

Really? Sounds strange, Server processes would allocate memory from shared pool if the large_pool_size=0. Have you set dbwr_io_slaves to a non-zero value? Try to show parameter large_pool_size when it go to failure.

Or you can set Large_pool_size=50M, why not? In Oracle 9i, you can modify Large_pool_size dynamically.

your reports are using parallel query then

Quote:

---

Have you set dbwr_io_slaves to a non-zero value?

---

Nope. its value is 0 only. Yes, when i'm using large_pool_size=0,
but when i use 10M, now the reports are working fine and my users are wokrking.

Pando,
my query is not using parallel option.

how do you know it is NOT using parallel query?

have you checked the queries execution plans?

parallel query works when it´s set at instance level, segment level or via HINTS

I checked in the execution plan. Here's the plan O/P.

SQL> select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' '
2 ||decode(id,0,'Cost = '||position) "Query Plan"
3 from plan_table
4 where statement_id='exp4';

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT Cost = 220
SORT GROUP BY
HASH JOIN
HASH JOIN
TABLE ACCESS FULL M_PROJECT
TABLE ACCESS FULL I_IMAGE

Thanks

looks a small report to me, plus I dont think you query to get the plan table is correct to see parallel query plans

you have to run

?/rdbms/admin/utlxplp.sql