Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2002
    Location
    Ireland
    Posts
    12

    Red face Unanswered: SPR's and Shared Memory

    Hi,

    I have a simple procedure of the following format:

    create procedure insert();

    Insert into (table.columns)
    select * from table
    where day >= 1
    and day < 2

    end procedure;

    When I run this procedure in DBACCESS, using execute procedure insert(), and then monitor shared memory, I find that the blocks used column keeps increasing until the procedure completes. When it completes these blocks are not being freed until I exit DBACCESS.

    If I run the insert...select in DBACCESS on its own, I notice the same increase in blocks used but when the query completes the memory is freed.

    Why is this ??


  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Blocks of resident shared memory are only freed during checkpoints.
    What do you use to monitor the pool?
    Blocks of virtual shared memory are freed whenever the server want.
    For normal sql it 's usually freed after the query finishes.
    For SPL, another pool is used: ralloc. check with onstat -g ses.
    It is not freed immediately, because it could be reused. That is the whole idea of a stored procedure. You write the code once, and it can be executed several times.
    rws

  3. #3
    Join Date
    Jun 2002
    Location
    Ireland
    Posts
    12
    Roelwe,

    Within the application I am using there is data for over 30 days.

    The procedure is actually a function to which I pass in the start and end day to.

    When the function is execute it is automated to do a day at a time.
    I use onstat -g seg to monitor the memory usage and find that as it goes from one day to the next the blocksused column keeps growing and eventually a new segment is added.

    This keeps happening and eventually Informix is running out of Virtual Shared memory.

    I thought that when the function processed a day the blocksused memory would be re-allocated to blocksfree and then the same segment of memory would be used for the next day and so on.

    Instead it just seems to be eating up virtual shared memory.

    What can I do to get around this problem.

    Cheers,

    Tom

  4. #4
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Hi,

    It might be possible you are hitting a memory leak problem.
    What is the exact version of your informix product?
    rws

  5. #5
    Join Date
    Jun 2002
    Location
    Ireland
    Posts
    12
    I am using Informix 9.2.1 UC 5

  6. #6
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    A good help would be if you could check with 'onstat -g mem'
    the name of what pool is leaking.
    rws

  7. #7
    Join Date
    Jun 2002
    Location
    Ireland
    Posts
    12
    Roelwe,

    I checked the session using onstat -g mem and also used onstat -g afr and onstat -g ufr, and there is no evidence of a memory leak.

    The only strange thing I found was that when I ran onstat -g opn, the open tables were being left around. Is this right ??

    Cheers,

    Tom

  8. #8
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Do you specify a thread id?
    rws

Posting Permissions

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