Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    46

    Unanswered: Kernel Parameters

    Hi,

    I am working in ORacle 9.2.0.7 under HP UX 11 platform.

    I very often get this error :

    error 4031 in job queue process
    ORA-04031: unable to allocate 27704 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","session param values")
    OPIRIP: Uncaught error 447. Error stack:
    ORA-00447: fatal error in background process

    The SGA size is as follows

    NAME
    ----------------------------
    VALUE
    ----------------------------
    shared_pool_size
    167772160

    shared_pool_reserved_size
    8388608

    Is there anything to do with the Kernel Parameters?

    I have taken the values of the Parameters :

    sema 1 - 1
    semaem 16384 - 16384
    semmap 3322 - (SEMMNI+2)
    semmni 3320 - (NPROC*5)
    semmns 16600 - (SEMMNI*5)
    semmnu 660 - (NPROC-4)
    semmsl 2048 Y 2048
    semume 256 - 256
    semvmx 32768 - 32768
    sendfile_max 0 - 0
    shmem 1 - 1
    shmmax 0x40000000 Y 0X40000000
    shmmni 1024 - 1024
    shmseg 1024 Y 1024


    Can anyone advice me whether the values are sufficient. Or is there anything else I have to check.

    Thanks

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    increase the size of your sga. 160M: to me sounds rather small.

    you have to change the init.ora settings for sga=300M ?

  3. #3
    Join Date
    May 2006
    Posts
    46
    Hi,

    Thanks for the response. The production environment has more than 300 MB sga size. still we face the same shared pool size problem.


    Should I have to do anything with the shmmax and shmseg. How do I say that the values assigned are sufficient.

    Please explain.

    Thanks

  4. #4
    Join Date
    May 2006
    Posts
    46

    New Values

    Hi,

    I have got the existing values of my Performance Testing Database where we face this 04031 error.

    The values are :

    shared_pool_size 218103808
    shared_pool_reserved_size 10905190

    Kernel Parameter Values are ;

    Parameter Current Dyn Planned Module Version
    ================================================== =============================
    semmap 4528 - (SEMMNI+2)
    semmni 4526 - 4526
    semmns 16384 - 16384 (Expected value semmni*5 = 22630)
    semmnu 4092 - (NPROC-4)
    semmsl 2048 Y 2048
    semume 256 - 256
    semvmx 32767 - 32767
    sendfile_max 0 - 0
    shmem 1 - 1
    shmmax 4204789760 Y 4204789760 -- 4 GB
    shmmni 3400 - 3400
    shmseg 1024 Y 1024

    Can u pl. check the values given for sga and shmmax values.
    Do I have to increase any of the values here to improve the performance and rectify the ora-04031 error.

    Thanks

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Most times I see people hitting ORA-04031, generally, is they are not using bind variables on the code, and thus the shared pool get easily full. Perhaps you should trying increasing your shared pool on the performance testing database until you hit it no more.

  6. #6
    Join Date
    Oct 2005
    Posts
    61
    What is your database size?
    How many users use the database concurrently?
    Check for the usage (read it efficiency) of the shared pool at peak times?
    Check the code for the usage of bind variables as someone pointed out...this is VERY IMPORTANT for scalability and performance.

    Until you find these things one can't say whether you need to increase or decrease the shared pool size.

    Are you using Streams? If yes, then you would be better off to contact Oracle Support as you would need to set some parameters.

    Thanks,
    Srinivas

  7. #7
    Join Date
    Oct 2005
    Posts
    61
    And yes, apart from the above first check if the O.S parameters have been set appropriately for your environment.

    Srinivas

Posting Permissions

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