Statement Cache & Web Logic

Hello A developer is asking about Web Logic and their documentation and how that relates to informix and the database statement caching. here is what we have set: # Turn the sql caching on onmode -W STMT_CACHE_NOLIMIT 1 onmode -W STMT_CACHE_HITS 15 onmode -e on we have not had any issues with the db caching - here is what he has written as they were seeing errors that there were "too many cursors" -----------------------------------------------------------------------------------------------------------------------------------------------

I've had systems with over 15000 prepared statements per session with dozens of sessions in the past. I have clients now with hundreds of prepared statements per session and thousands of concurrent sessions. I can't say I've seen large numbers of open cursors (say more than 5 or 6) concurrently in a session, but there is no limit that I'm aware of except shared memory limits in the ONCONFIG file and enforced by the OS. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Feb 1, 2010 at 11:23 AM, Tom Lehr <tomcaml@gmail.com> wrote: > Hello > A developer is asking about Web Logic and their documentation and how > that relates to informix and the database statement caching. here is > what we have set: > > # Turn the sql caching on > onmode -W STMT_CACHE_NOLIMIT 1 > onmode -W STMT_CACHE_HITS 15 > onmode -e on> > we have not had any issues with the db caching - > > > here is what he has written as they were seeing errors that there were > "too many cursors" > > > ----------------------------------------------------------------------------------------------------------------------------------------------- > >From the WebLogic documentation: > > However, you must consider how your DBMS handles open prepared and > callable statements. In many cases, the DBMS will maintain a cursor > for each open statement. This applies to prepared and callable > statements in the statement cache. If you cache too many statements, > you may exceed the limit of open cursors on your database server. > > Does this make any sense about the cursor usage? I thinking it has to > do with those LRU's > > ----------------------------------------------------------------------------------------------------------------------------------------------- > > thanks for any insight - Tom > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On Feb 1, 10:23 am, Tom Lehr <tomc...@gmail.com> wrote: > Hello > A developer is asking about Web Logic and their documentation and how > that relates to informix and the database statement caching. here is > what we have set: > > # Turn the sql caching on > onmode -W STMT_CACHE_NOLIMIT 1 > onmode -W STMT_CACHE_HITS 15 > onmode -e on> > we have not had any issues with the db caching - > > here is what he has written as they were seeing errors that there were > "too many cursors" > > ----------------------------------------------------------------------------------------------------------------------------------------------- > From the WebLogic documentation: > > However, you must consider how your DBMS handles open prepared and > callable statements. In many cases, the DBMS will maintain a cursor > for each open statement. This applies to prepared and callable > statements in the statement cache. If you cache too many statements, > you may exceed the limit of open cursors on your database server. > > Does this make any sense about the cursor usage?  I thinking it has to > do with those LRU's > ----------------------------------------------------------------------------------------------------------------------------------------------- > > thanks for any insight - Tom For Informix I don't believe this to be the case. A statement in the servers statement cache, does not force a cursor to remain open for each statement. The Informix statement cache is a list of frequently used statements like "select col1 from table1"; "select max(col1) from table2"; Then when an application goes and tries to prepare a statement, we search the statement cache to see if it already exists. If it does and it's fully cached, then that session will link into the statement cache and then require a smaller amount of it's own session memory to be used for this statement. However, that's just declaring the statement. If you want to execute the statement there are two ways, execute the statement id (which only works if it's returning 1 row and you execute it into a host variable) or the application declares a cursor for that statement. Then the application has to open, fetch, and close (and free) the cursor. So cursors are a mechanism used to get the results for statements back to a client application. The statements are what's potentially cached in the Informix statement cache. The application is therefore still in control of the number of cursors it tries to declare and open and manage. I looked around in the server code and it appears the max number of possible prepared statements per session is 32767. That would also be the theoretical max number of cursors per session. If you tried to exceed that amount of prepared statements though it looked as if you would get SQL code 208 (which is out of memory error). I did not see any sort of too many cursors error in the server code. However, that could maybe be a client side specific check and error message that I'm not seeing, since I'm only looking at this from the server side. So I'm not sure if our client application code (like CSDK) has a lower defined number of cursors you can attempt to declare. To me it almost sounds like WebLogic is talking about some other statement cache (like it is attempting to do it's own statement cacheing or something) and not the actual Informix server statement cache. Jacques Renaut IBM Informix Advanced Support APD Team