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
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
Last edited by Sameer; 11-28-2002 at 08:12 AM.
don't get annoyed. I set all the parameters as you suggested. My DB version is 18.104.22.168.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.
nopes.. never mind!
Originally posted by pnrdba
don't get annoyed.
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.
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.
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);
Are you using outer join .. Becaue I found a bug reported for this version at Metalink..
9014 [BUG:2244789] ORA-4031 / excessive shared pool usage from query with full outer join / union
Last edited by Sameer; 11-28-2002 at 09:12 AM.
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.
Click Here to Expand Forum to Full Width