Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005

    Question Unanswered: How to keep the SHARED_POOL clean ?


    I develop a VB6 software which allows to consult a table containing lots of data through a user interface looking like an Excel spreadsheet.
    On each column of the spreadsheet (and then of the table) the user can apply a sort and/or a filter on a given value (for example : display only the rows for which the amount is 100).
    The user can sort/filter as many columns as he wishes (1 sort and 1 filter on a given column at maximum).
    Moreover, which columns are displayed or not is also the choice of the user.

    The underlying SQL querie is thus totally dynamical and has nothing fixed : neither the columns list of the SELECT, nor the WHERE clause, nor the ORDER BY clause !

    So, I don't see how I could optimize anything, and above all avoid that these queries (always different from one to the next) full the SHARED_POOL, because this kind of message comes far too often :

    ORA-04031 : unable to allocate 8716 bytes of shared memory ("large pool", "unknown object","cursor work he","kllqas:kllsltba")

    I am not an expert in Oracle, so please excuse me in if my post is not very clear or confuses several notions.

    Your help would really be extremely precious !
    Thanks a lot in advance !

    Oracle is queried through an ADO interface

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    > How to keep the SHARED_POOL clean ?
    Use SQL statements with bind parameters.
    You just learned the downside cost for total use of single use SQL via EXECUTE IMMEDIATE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Liverpool, NY USA
    After you run the query, are you closing the cursor?
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Sep 2003
    It appears your problem is with the large pool

  5. #5
    Join Date
    Nov 2005

    Thumbs up


    Sorry for not having been able to post back for a long. My problem could be resolved by resizing the pool, and the message did not appear anymore.

    Thank you all for your replies and wish you a happy new Y2K6 !

Posting Permissions

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