Re: SQL STATEMENT CACHE Informix

On 13 Mar, 16:22, chav...@gmail.com wrote: > Hello, >         Recently we enabled SQL STATEMENT CACHE in Informix 10.FC8, I > have been monitoring the chache since then, one thing I observed is > the statements are being removed from the cache well before (50-70MB) > the configured max 100MB cache size limit, because the server can > still cache another 50-20MB of statements. Any comments on why the > statements are being removed well before it reaches max limit. Below > are the configured values. > > STMT_CACHE                         2 > STMT_CACHE_HITS                2 This is set to 2 hence statements are not fully inserted into the cache until they are referenced twice, that could be it. Also what are the other onconfig parameters for this set to (STMT_CACHE_NOLIMIT,STMT_CACHE_NUMPOOL)? > STMT_CACHE_SIZE       102400> > Thank you. > -- Chavan

On Mar 13, 5:14 pm, "da...@smooth1.co.uk" <da...@smooth1.co.uk> wrote: > On 13 Mar, 16:22, chav...@gmail.com wrote: > > > Hello, > >         Recently we enabledSQLSTATEMENTCACHEin Informix 10.FC8, I > > have been monitoring the chache since then, one thing I observed is > > the statements are being removed from thecachewell before (50-70MB) > > the configured max 100MBcachesize limit, because the server can > > stillcacheanother 50-20MB of statements. Any comments on why the > > statements are being removed well before it reaches max limit. Below > > are the configured values. > > > STMT_CACHE                         2 > > STMT_CACHE_HITS                2> >  This is set to 2 hence statements are not fully inserted into thecacheuntil they are referenced twice, >  that could be it. > >  Also what are the other onconfig parameters for this set to > (STMT_CACHE_NOLIMIT,STMT_CACHE_NUMPOOL)? > > > STMT_CACHE_SIZE       102400> > > Thank you. > > -- Chavan I wanted to insert the statements into cache after they are referenced twice, but already inserted statements size is not reaching to STMT_CACHE_SIZE. I did not configure the following two parameters STMT_CACHE_NOLIMIT - I did not want to allocate cache size more than 102MB ,STMT_CACHE_NUMPOOL -- Chavan