IDS Forum

Re: Question about STMT_CACHE

Posted By: Martin Fuerderer
Date: Friday, 25 February 2011, at 4:09 a.m.

In Response To: Question about STMT_CACHE (SUSAN JONES)

Hi,

can you please clarify what exactly is the problem?

I read in your e-mail at the beginning:
> ... a query that is executed immediately after a stop
> and restart of the database instance takes much longer than the second
time...

but at the end you say:
> ... Still, the
> first query considerably faster than subsequent queries...

These two statements are somewhat contradicting each other (I think).

If the first it true, then you already got two answers. :)
It is having to read data from slow disk the first time after startup.
This will put the read pages into the buffer pool (also referred
to as "cache"), which is an Informix internal data structure in main
memory (and not to be msitaken for something liek a file system
(buffer) cache, which also exists on some operating systems and
can have influence, if the Informix chunks are cooked files and not
on a raw device). The second time the data will be taken from this
buffer pool only, where main memory is very fast compared to
slow disk reading.

If the second statement in your e-mail is true ...
Then I have to say that this really would be a somewhat strange
situation that needs further investigation. So for the moment
I'll believe that this is not the case.

Regards, Martin
--
Martin Fuerderer
IBM Informix Development Munich, Germany
Information Management

-- Go Cruising with Informix in 2011 ...
-- IIUG Informix Conference
-- Overland Park Marriott, Kansas
-- May 15 - 18, 2011

IBM Deutschland Research & Development GmbH
Chairman of the Supervisory Board: Martin Jetter
Board of Management: Dirk Wittkopp
Corporate Seat: Boeblingen, Germany
Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294

ids-bounces@iiug.org wrote on 02/24/2011 10:07:04 PM:
>
> AIX 5.3 IBM Informix Dynamic Server Version 11.50.FC8
>
> Hello,
> We have a situation where a query that is executed immediately after a
stop
> and restart of the database instance takes much longer than the second
time
> the query is executed. I assumed that was because I had STMT_CACHE set
to 2
> 'ON' and the second time the query was executed, the statement was
cached in
> memory. By the way, "Update Statistics" was executed just prior to
running
> this test.
>
> Both times the query was executed I had 'SET EXPLAIN ON' and the cost of
the
> queries are identical.
>
> So, I set STMT_CACHE, STMT_CACHE_HITS, STMT_CACHE_NOLIMIT, AND
> STMT_CACHE_NUMPOOL back to the default values "OFF", then stopped and
> restarted the instance before re-testing. The results are the same.
Still, the
> first query considerably faster than subsequent queries. Any ideas?
>
>
>
*******************************************************************************

> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• Question about STMT_CACHE
  SUSAN JONES -- Thursday, 24 February 2011, at 4:07 p.m.
  • RE: Question about STMT_CACHE
    Dan Mueller -- Thursday, 24 February 2011, at 4:11 p.m.
    • Re: RE: Question about STMT_CACHE
      SUSAN JONES -- Wednesday, 2 March 2011, at 3:01 p.m.
      • Re: RE: Question about STMT_CACHE
        Art Kagel -- Wednesday, 2 March 2011, at 3:18 p.m.
      • RE: RE: Question about STMT_CACHE
        Tristan Ball -- Wednesday, 2 March 2011, at 4:03 p.m.
  • Re: Question about STMT_CACHE
    Art Kagel -- Thursday, 24 February 2011, at 4:13 p.m.
  • Re: Question about STMT_CACHE
    Martin Fuerderer -- Friday, 25 February 2011, at 4:09 a.m.
  • RE: Question about STMT_CACHE
    Jones, Susan -- Friday, 25 February 2011, at 10:07 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.