Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Unanswered: "ORA-04031: unable to allocate 4200 bytes" while there's a lot of free memory

    I'm occasionally getting this error when a Java application attempt to connect to my Oracle 8.1.7 database:

    Code:
    ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","DATABASE","sga heap","state objects")
    At the same time I don't see any problems with SGA: there's plenty free space and I think no fragmentation:
    Code:
    SQL> select * from v$sgastat
      2  /
    
    POOL        NAME                            BYTES
    ----------- -------------------------- ----------
                fixed_sga                       73888
                db_block_buffers            419430400
                log_buffer                     131072
    shared pool free memory                   9791432
    ...
    large pool  free memory                    614400
    java pool   free memory                  17747968
    java pool   memory in use                 3223552
    
    34 rows selected.
    Code:
    SQL> SELECT free_space, avg_free_size, used_space,
          avg_used_size, request_failures, last_failure_size
    FROM   v$shared_pool_reserved;  2    3
    
    FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
    ---------- ------------- ---------- ------------- ---------------- -----------------
    
    
       2621440       2621440          0             0             2191              4132
    Any ideas? Could that be a hardware problem with one of the memory modules on the server? Is there anything else I should look at?

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112
    If you are using Java in the database , Oracle use Javal pool and it seems to be too small ;

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, there are a couple of Java functions that get called once in a while but I don't believer that is where the problem is.

    As you can probably guess from my questions, I haven't got much experience with Oracle. I googled around and from what I've read I suspect that the problem arises not from insufficient free space but from SGA fragmentation. Moreover, it doesn't seem to be happening in the SQL area because 1) judging by the parse/execute ratio of 0.09% it's being used quite efficiently and 2) flushing shared pool didn't help a bit. Does my reasoning make sense?

    The error happens during either connect or call to a stored procedure. I understand that by pinning most frequently used packages I should be able to solve the issue. Does that sound right? Are there any other ways to reduce fragmentation (or may be coalesce free memory fragments)?

    Thanks

  4. #4
    Join Date
    May 2003
    Location
    France
    Posts
    112
    The ORA-4031 can also be produced by large_pool too small when using stored proc or MTS

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Increasing your shared_pool size would probably be worthwhile as you do seem to be a bit close to the limit. As a minor point the log_buffer parameter may be a bit low unless your application is mainly doing reads rather than writes.

    Alan

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    you might also try to pin some of the sql in the SGA... Those errors are because there is not sufficient contiguous memory in the pool. As sql is aged
    from the sga, it will leave a "hole" ... these "holes" get refilled as long as the
    incoming sql can "fit"... 8.1.7 also had a bug... This is an undocumented parameter that you can put in your INIT.ORA file to try and work around some of those errors... Check it out on MetaLink

    _db_handles_cached = 0


    HTH
    Gregg

Posting Permissions

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