Resolving ORA-04031-problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Resolving ORA-04031-problem

  1. #1
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    Resolving ORA-04031-problem

    Hello,

    On a production-server there's a db (Oracle 8.1.7.1.1) that has ORA-04031 errors when you connect to it.

    Flushing the shared pool didn't do the job. Installing package DBMS_SHARED_POOL (to pin objects in memory) failed also because of the ORA-04031 error.

    The application running on the db is the accounting software "Exact".

    The db is also in MTS-mode and I don't think it needs it (maximum number of concurrent users: less than 100!)
    Should I remove MTS?

    I want to resolve the ORA-04031 error by increasing SHARED_POOL_SIZE.

    Here are the current values:

    SHARED POOL : 50 MB
    BUFFER CACHE : 82 MB
    LARGE POOL : 15 MB
    JAVA POOL : 20 MB

    TOTAL SGA : 167 MB

    MAX CONCURRENT USERS : 700
    SORT AREA SIZE : 64 KB

    CURSOR_SHARING : EXACT
    CURSOR_SPACE_FOR_TIME : FALSE
    OPEN_CURSORS : 300
    SESSION_CACHED_CURSORS : 0

    I think of altering these values into:
    SHARED POOL : 80 MB
    LARGE POOL : 25 MB
    JAVA POOL : 10 MB
    MAX CONCURRENT USERS : 200


    I have some 290MB free physical memory, but there's a second (more important db on that server)

    I'll remove 10MB of memory from the Java pool and add this to the large pool. Java isn't in use here, I guess...(see the V$VGASTAT below)

    I read many articles about CURSOR_SHARING (Yes, I Read The Fantastic Manual!), but I can't find any conclusive evidence to alter it into 'FORCE'. Doing so could introduce new problems and being it a production database, I don't want any extra problems...
    Do you agree on this?

    And one last thing: I entered the on line learning course on 'tuning the shared pool'. The mentor said that any production database starts with a shared pool with the size of at least 150 MB! I'm not anything near of that size.
    Is the Oracle mentor right about this?

    I hope you guys can reflect on my questions. (Sunday morning I'll have the alter the system on my own, without the help of any experienced dba. So some confirmation of things would be great!)

    Thanks in advance,
    Erik
    very junior ocp-wannabe



    V$VGASTAT:
    POOL NAME BYTES
    ----------- -------------------------- ----------
    fixed_sga 75804
    db_block_buffers 86294528
    log_buffer 66560
    shared pool free memory 15173612
    shared pool miscellaneous 30766424
    shared pool VIRTUAL CIRCUITS 275752
    shared pool PL/SQL DIANA 312460
    shared pool fixed allocation callback 640
    shared pool PX msg pool 88508
    shared pool PLS non-lib hp 2096
    shared pool table columns 16860
    shared pool PX subheap 6176
    shared pool sessions 366520
    shared pool enqueue_resources 129024
    shared pool State objects 247360
    shared pool joxs heap init 4248
    shared pool db_files 370988
    shared pool KQLS heap 92532
    shared pool dictionary cache 168484
    shared pool KGFF heap 6552
    shared pool KGK heap 17556
    shared pool trigger inform 460
    shared pool message pool freequeue 124552
    shared pool library cache 5387548
    shared pool db_block_buffers 1432624
    shared pool sql area 1261708
    shared pool db_block_hash_buckets 299784
    shared pool PL/SQL MPCODE 19744
    shared pool transactions 166804
    shared pool event statistics per sess 584800
    large pool free memory 13230040
    large pool session heap 2498600
    java pool free memory 20606976
    java pool memory in use 364544
    Last edited by efrijters; 05-23-2003 at 10:51 AM.

  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591

    Re: Resolving ORA-04031-problem

    Originally posted by efrijters
    Hello,


    I think of altering these values into:
    SHARED POOL : 80 MB
    LARGE POOL : 25 MB
    JAVA POOL : 10 MB
    MAX CONCURRENT USERS : 200


    How did you decide on those figures?

    Try just increasing the large pool and then see...
    execute this as well...
    Code:
    SELECT NAME, SUM(BYTES) 
    FROM V$SGASTAT 
    WHERE POOL='LARGE POOL' 
    GROUP BY ROLLUP (NAME);
    That'll show free space...

    That 150mb shared pool is new to me, it all depends...
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  3. #3
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    Re: Re: Resolving ORA-04031-problem

    Thanks for your reply, Tarry

    Originally posted by Tarry
    How did you decide on those figures?

    Try just increasing the large pool and then see...
    execute this as well...
    Well, the size of the shared pool (80 MB) came from the other production db. Going from 50MB to 80MB is a great step for this db. It runned very well up to now... It's been up since november 2002.

    Running the statement results in:

    1 SELECT NAME, SUM(BYTES)
    2 FROM V$SGASTAT
    3 WHERE upper(POOL)='LARGE POOL'
    4* GROUP BY ROLLUP (NAME)
    SQL> /

    NAME SUM(BYTES)
    -------------------------- ----------
    free memory 13181680
    session heap 2546960
    15728640


    Okay, I have 15MB free memory, but I'm afraid that it's fragmented. Why would I otherwise receive those ORA-04031 errors?

    Any suggestions?

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    [1] What follows after the ora-04031 error? Any erroneous message etc.
    Post all of it here?
    [2] It's most porbably the "exact" application which is doing a lot of work(in hard parsing etc) that's causing the mem problem.
    [3] Your idea is apparently to pin those large queries or blocks in memory but you might as well take a look into using bind variables.

    Post the error code with details then we can see what might/can be done here.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    You might also want to include this undocumented init.ora setting
    ... You can find this on Metalink ... It made a world of difference
    for 1 of my clients:

    #### Work around for BAMIMA Buffer (4031) errors ####
    _db_handles_cached = 0

    Gregg

  6. #6
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    And BTW that MTS option for users less than 100 is a weird decision IMHO. Put it back to dedicated.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  7. #7
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Originally posted by Tarry
    [1] What follows after the ora-04031 error? Any erroneous message etc.
    Post all of it here?
    [2] It's most porbably the "exact" application which is doing a lot of work(in hard parsing etc) that's causing the mem problem.
    [3] Your idea is apparently to pin those large queries or blocks in memory but you might as well take a look into using bind variables.

    Post the error code with details then we can see what might/can be done here.
    The error message directly after connecting to SQLPLUSW:
    ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","unknown object","sga heap","state objects")

    There's no message after this. The connection succeeds though. I think the error is the result of SQLPLUSW selecting the version information that's being displayed as SQLPLUSW starts. This also happens when starting with "SQLPLUSW /NOLOG".

    The "Exact" software is a well known accounting solution in The Nehterlands. I have no way of altering the programm or the sql-statements.

    I just logged in and ... the error is gone! Probably because everyone's gone home... So, the higher the amount of concurrent users, the higher risk of running into the ORA-04031...

    To Gregg: I saw this undocumented init.ora setting in some documents. I too had the BAMIMA error a couple of times. I'll read the articles on Metalink and decide on what to do.

    Tarry, Gregg: Thanks sofar!!

    I'll post the result as soon as I bounced the db and when I get some feedback from the users!!

  8. #8
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Last night I had to stop the NT Service of the database, because there was no way to log in: ORA-00604 and ORA-04031's kept me from starting svrmgrl or sqlplus.

    After altering the shared_pool_size, shared_pool_reserved_size, large_pool_size and some other parameters that were due for altering, I restarted the database via svrmgrl and it works!!

    No more ORA-04031's!!

    I want to thank everyone for their support!

    I just hope I can return the favour someday.

    Erik
    Last edited by efrijters; 05-26-2003 at 05:53 AM.

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