Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Unanswered: Urgent ! Error wtih Shared_pool_size

    Hello,

    I have an Oracle 9.2.0.1 Database running on HP-UX 9000. I get the following error when I run appworx(a scheduling program) ..


    44;1H0KAppWorx RMI Server
    Copyright (C) 1999-2004 AppWorx Corporation. All Rights Reserved.
    Release: v6.0.4 Build: 3720 Thu Jul 29 11:58:16 PDT 2004
    AxRmiServer
    Java VM: Hewlett-Packard Co. 1.4.1.02

    ErrorMsg: AwE-5001 Database Query Error (9/29/04 9:47 AM)
    Details: {? = call aw_web_api.aw_inc_update (?) }
    0 maxSeq: IN:NUMERIC:java.math.BigDecimal:548

    java.sql.SQLException: ORA-04031: unable to allocate 4080 bytes of shared memory ("shared pool","AWAPI3","PL/SQL MPCODE","BAMIMA: Ba
    m Buffer")
    ORA-06508: PL/SQL: could not find program unit being called
    ORA-06512: at "APPWORX6.AWAPI2", line 367
    ORA-06512: at "APPWORX6.AW_WEB_API", line 949
    ORA-06512: at line 1


    It looks to me that I have to increase the shared_pool_size. Here is what I have in the parameter file.

    shared_pool_size = 40MG
    Shared_pool_reserved_Size = 2.4MG

    I run a script to find out the dictionary cache ratio and it is 3.18%

    What I want to know is
    1. how can I determine by how much to increase the shared_pool_size.
    2. How can I increase it. Is it simply by changing the value in the parameter file.
    3. do I have to shutdown the DB and restart it?

    Please advise.

    Thanks
    Desta

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Leaping lizards, your shared pool is a litle small. AT our site, we run from 240 meg on our smaller servers, up to 1024meg on our main server.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Oct 2004
    Posts
    145

    answers

    1. This depends on your application code. I would take previous postings advice and keep an eye on usage of shared pool. You can always try flushing the shared pool as well "alter system flush shared_pool"
    2. Yes it is simple as changing the parameter in the init file.
    3. Yes you must restart your instance.

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    You can keep an eye on the free memory in the Shared Pool by running this:

    select bytes from v$sgastat
    where pool = 'shared pool'
    and name = 'free memory';

    BTW, I had this same problem, caused by one procedure that ate up the shared pool memory; by running this command every so often as the procedure was running I was able to see the shared pool free memory being used up.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    you can change shared_pool_size on the fly in 9i using alter system ... scope=both (probably in 8i aswell) but you are limited by sga_max_size which you cant change dynamically (need to bounce the database).

    Alan

Posting Permissions

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