ORA 4031 error, urgent - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 30

Thread: ORA 4031 error, urgent

  1. #11
    Join Date
    Apr 2002
    Posts
    291
    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
    PNRDBA

  2. #12
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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
    Last edited by Sameer; 11-28-2002 at 08:12 AM.

  3. #13
    Join Date
    Apr 2002
    Posts
    291
    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.
    PNRDBA

  4. #14
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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!..

  5. #15
    Join Date
    Apr 2002
    Posts
    291
    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
    PNRDBA

  6. #16
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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..

  7. #17
    Join Date
    Apr 2002
    Posts
    291
    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
    PNRDBA

  8. #18
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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);

    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..
    Last edited by Sameer; 11-28-2002 at 09:12 AM.

  9. #19
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    it´s complaining about large pool I dont understand why you keep on increasing shared pool

  10. #20
    Join Date
    Aug 2002
    Location
    Bangalore
    Posts
    52
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width