Just to reinforce Filip's point about Oracle doing a lot of caching already in the server, it's not clear how much benefit there is to the Java client side stuff. That being said, it looks like you have to turn on the caching by calling some Oracle specific methods, it's not on by default. Turning it on is really setting a cache size > 0. It supports both implicit caching, using a match of the SQL text, and explicit matching using a user supplied key working out of the same cache. Seems a bit confusing to me to have 2 ways and the same cache. The explicit caching just manages the cache data structures within the size specified, otherwise it looks like a HashMap. You can check the 8.1.7i JDBC docs on the www.oracle.com site for more information. There is a whole chapter in the book on statement caching. My 2c is I'd like to see this feature able to be turned off and on in minerva since we have the cursor limits in Oracle, and it already does quite a lot of optimization of this in the server; not to mention this JDBC statement caching built into the JDBC driver. Cheers -----Original Message----- From: Bill Burke [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 22, 2001 11:32 PM To: Jay Walters Cc: [EMAIL PROTECTED] Subject: Re: [JBoss-dev] Re: [JBoss-user] A little BMP philosophy/understa Filip, Thanks for explaining so much about how everything works with compiled-sql caching, you've been a big help and I really appreciate your long emails. I think Jay has a good point though. Minerva doesn't know what database you're working with so maybe it should do some PreparedStatement caching. This feature should be configurable so that you can turn it off where the driver/database caches this. Can you be sure that JBoss supported JDBC databases all do compiled-sql or PreparedStatement caching? My question to Jay - With the Oracle 8.1.7 drivers, do you get PreparedStatement/Statement/compiled sql caching when you make connections through the DriverManager, or do you have to explicity manipulate specific Oracle JDBC Objects and Classes. Thanks mucho, Bill Jay Walters wrote: > I know you're both using Oracle, but what about other databases. I am > pretty sure Oracle is the only one with a cache of SQL statements in the > server, and as said before the 8.1.7 drivers also include client side > statement caching - check the docs. > > Cheers > Jay Walters > > -----Original Message----- > From: Filip Hanik [mailto:[EMAIL PROTECTED]] > Sent: Thursday, March 22, 2001 9:05 PM > To: Bill Burke > Cc: [EMAIL PROTECTED] > Subject: RE: [JBoss-dev] Re: [JBoss-user] A little BMP > philosophy/understanding > > >> My application is not caching prepared statements. Jboss is not caching >> prepared statements. Minerva(the connection pool implementation) is >> caching the prepared statements. > > > Yes Minerva is doing this, and it shouldn't do that anymore. This is for > drivers written a long long time ago that nobody is using. > You should not cache the statement, if you are using Oracle and are familiar > with the term Oracle session, you should know that caching a prepared > statement is useless. > A prepared statement is not based on client side logic. > >> Yes, our application code looks very similar to yours. We always close >> our statements and connections in a "finally" block as well. But I >> haven't been talking about application code, but rather the actual >> connection pool implementation itself(Minerva). Maybe there has been a >> miscommunication. > > > nope, no miscommunication. > > > this is the way it works, let me try be more clear. > > 1. A physical connection to the database is opened ( a user session is > established) > 2. A programmer gets a connection from a connection pool. this is a pass > through reference to the physical connection > 3. A programmer prepares a statement, > 4. The driver sends the SQL to the database, the database interprets, > compiles and caches the SQL in memory associated with the session. > 5. The programmer executes the statement - the driver or the database > recognizes this statement and executes the precompiled statement (note! not > minerva, not jboss, not you as a programmer does this) > 6. The programmer closes the statement, indicating to the driver to free up > resources for this cursor. > 7. the programmer closes the connection indicating to the connection pool > (minerva) to return the physical connection to the pool. > 8. A programmer gets a connection from a connection pool. > 9. A programmer prepares a statement > 10. The driver or the database (not you, not minerva, and not jboss!) > recognizes that this statement is already prepared (string matching-very > simple) and ignores the compilation request. > 11. A programmer executes a query, this time, all the driver does is to set > the parameters and re-execute the precompiled statement. > > what is important to remember here, is that the precompiled SQL exists on > the database. there is no way a driver or minerva could implement anything > that makes precompilation faster on the database. it simply happens on the > database. > > I know that minerva cache prepared statements and I'm trying to explain to > you why this is not necessary and sometimes even harmful. Like with Oracle, > minerva could actually be causing problems by caching statements. > > If you want me to be even more clearer, call me tomorrow at work 650-875 > 1538, and I can explain to you the little secrets of pre-compiling sql > statements into temporary stored procedures. > > Filip > > > ~ > Namaste - I bow to the divine in you > ~ > Filip Hanik > Software Architect > [EMAIL PROTECTED] > www.filip.net > >> -----Original Message----- >> From: Bill Burke [mailto:[EMAIL PROTECTED]] >> Sent: Thursday, March 22, 2001 5:54 PM >> To: Filip Hanik >> Cc: [EMAIL PROTECTED] >> Subject: Re: [JBoss-dev] Re: [JBoss-user] A little BMP >> philosophy/understanding >> >> >> >> >> Filip Hanik wrote: >> >>>> Unless you actually close the PreparedStatement, it keeps an >>> >> open cursor >> >>>> into the database, at least with Oracle. Closing the PreparedStatement >>>> releases all of it's resources and makes it useless for caching. >>>> Somebody correct me if I'm wrong. >>> >>> >>> Bill, why would you cache the prepared statements on the >> >> application level - >> >>> please explain. >> >> My application is not caching prepared statements. Jboss is not caching >> prepared statements. Minerva(the connection pool implementation) is >> caching the prepared statements. >> >>> I tried to earlier explain why this wasn't necessary because >> >> the connection >> >>> keeps precompiled code alive on the DB server. it belongs to >> >> the connection >> >>> session, not to a statement. >>> Closing the statement (and you should always do this!), closes >> >> the cursor, >> >>> but keeps the pre compiled SQL as long as the connection is open. >> >> If your JDBC drivers support XAConnections, pooling, and statement >> caching, don't use Minerva. We're using Minerva so I need to fix how it >> caches PreparedStatements. JDBC doesn't allow you to re-use a >> PreparedStatement after it is closed. So if Minerva is caching >> PreparedStatements, it must wrap the creating and closing of them too so >> it can easily cache them. >> >> How does Minerva know that your DBS is caching pre-compiled SQL? >> Minerva was written to support all JDBC implementations. >> >> Take a look at the code. It's in the jbosscx archive under the external >> directory. minerva-b3src.zip or something like that. >> >>> if you look at this code, this is how JDBC code should close >> >> connections and >> >>> statements. >> >> Regards, >> Bill >> >>> public void mymethod() >>> { >>> Connection con = null; >>> PreparedStatement pstmt = null; >>> try >>> { >>> con = getConnection(); >>> pstmt = con.prepareStatement("my sql string"); >>> ResultSet rs = pstmt.executeQuery(); >>> ...bla bla bla... >>> rs.close(); >>> } >>> finally //always make sure resources are cleaned up!! >>> { >>> try >>> { >>> pstmt.close();//this should close dependent result sets >> >> too if there >> >>> are any open >>> con.close(); // if this is a connection pool, the connection gets >>> returned to the pool >>> }catch (Exception ignore() {} >>> }//finally >>> } >>> >>> ~ >>> Namaste - I bow to the divine in you >>> ~ >>> Filip Hanik >>> Software Architect >>> [EMAIL PROTECTED] >>> www.filip.net >>> >>>> -----Original Message----- >>>> From: [EMAIL PROTECTED] >>>> [mailto:[EMAIL PROTECTED]]On Behalf Of Bill >>>> Burke >>>> Sent: Thursday, March 22, 2001 3:29 PM >>>> To: Filip Hanik >>>> Cc: [EMAIL PROTECTED] >>>> Subject: Re: [JBoss-dev] Re: [JBoss-user] A little BMP >>>> philosophy/understanding >>>> >>>> >>>> Filip Hanik wrote: >>>> >>>>> oops, we are cross posting, no good, sorry about that. >>>>> >>>>>> I'm re-writing the PreparedStatement cache >>>>>> - so that it is configurable from jboss.jcml. I can no way >>>>> >> right now >> >>>>>> other than changing the source directly of configuring the PS >>>>> >>>> cache size. >>>> >>>>>> - so that connections watch all open cursors that are >>>>> >>>> created(Statements >>>> >>>>>> and PreparedStatements). Basically if you have 50 PreparedStatements >>>>>> cached, your max open cursors is 50, and you want to >>>>> >> createStatement, >> >>>>>> I'm making the connection release one of the cached >>>>> >>>> PreparedStatement so >>>> >>>>>> that the new createStatement won't fail. >>>>> >>>>> >>>>> remember that during a transaction, the connection (JDBC 1) used gets >>>>> associated with the transaction context. >>>>> hence, other transactions/threads will not be accessing the connection >>>>> during that time since they are not involved in this DB transaction. >>>> >>>> Good. Just making sure. (more comments follow) >>>> >>>>> why would you want to keep the cursors open. once you retrieved your >>>>> resultset, your are done with the cursor and should close it. >>>> >>>> the prepared >>>> >>>>> statement is nothing but precompiled (during runtime) SQL, and if the >>>>> connection should keep this precompiled statement alive on the >>>> >>>> database, not >>>> >>>>> through the PreparedStatement reference that the programmer >>>> >> holds in his >> >>>>> code. >>>>> >>>>> can somebody please tell *ME* to shut up, if I am completely >>>> >> off balance >> >>>>> here :) >>>>> >>>>> Filip >>>> >>>> Unless you actually close the PreparedStatement, it keeps an >>> >> open cursor >> >>>> into the database, at least with Oracle. Closing the PreparedStatement >>>> releases all of it's resources and makes it useless for caching. >>>> Somebody correct me if I'm wrong. >>>> >>>> BTW, please don't shut up. I'm no JDBC expert, just trying to make the >>>> PS caching work so that my application will succeed. :-) >>>> >>>> Bill >>>> >>>> >>>>> ~ >>>>> Namaste - I bow to the divine in you >>>>> ~ >>>>> Filip Hanik >>>>> Software Architect >>>>> [EMAIL PROTECTED] >>>>> www.filip.net >>>>> >>>>>> -----Original Message----- >>>>>> From: [EMAIL PROTECTED] >>>>>> [mailto:[EMAIL PROTECTED]]On >>>>> >> Behalf Of Bill >> >>>>>> Burke >>>>>> Sent: Thursday, March 22, 2001 2:57 PM >>>>>> To: [EMAIL PROTECTED] >>>>>> Cc: [EMAIL PROTECTED] >>>>>> Subject: [JBoss-dev] Re: [JBoss-user] A little BMP >>>>>> philosophy/understanding >>>>>> >>>>>> >>>>>> In JBoss 2.1 (minerva beta3?) the PreparedStatement cache does have a >>>>>> limit. Also, when the cache reaches it's limit, it removes the least >>>>>> recently used PS and closes it. >>>>>> >>>>>> - Is it useful to block if the max open cursors have been >>>>> >> reached when >> >>>>>> creating a new Statement or PreparedStatement? This would only be >>>>>> useful if more the one thread had access to the connection, but does >>>>>> that ever happen, and is it allowed to happen? >>>>>> >>>>>> Bill >>>>>> >>>>>> Mike Jau wrote: >>>>>> >>>>>>> So, the caching of the PreparedStatement is stored in the database >>>>>>> connection context and is not shared between the database >>>>>> >> connection. >> >>>>>>> I am thinking a work around way and it may solve the caching >>>>>>> issue. If we have the "named connection" from the pool with the >>>>>>> lifecyclye control to release the PreparedStatement from the >>>>>>> applicaiton which invoke the container specific API, it probably can >>>>>>> solve the problem. >>>>>>> >>>>>>> >>>>>>> >>>>>>> - Mike Jau >>>>>>> >>>>>>> -----Original Message----- >>>>>>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] >>>>>>> Sent: Thursday, March 22, 2001 1:25 PM >>>>>>> To: [EMAIL PROTECTED] >>>>>>> Subject: RE: [JBoss-user] A little BMP philosophy/understanding >>>>>>> >>>>>>> >>>>>>> In the original JBoss 2.0 version the PreparedStatement >>>>>> >> cache was >> >>>>>>> not discarded after the connection was returned to the pool >>>>>>> because more than likely you might want to issue that >>>>>> >> one of these >> >>>>>>> PreparedStatements again. To make matters worse there wasn't an >>>>>>> upper limit on the number of PreparedStatement objects in the >>>>>>> cache so things would continue to grow as you prepared new SQL >>>>>>> statements. If you happened to prepare the same exact SQL >>>>>>> statement then you received the previously cached >>>>>>> PreparedStatement object but otherwise you got a new >>>>>>> PreparedStatement that was also added to the cache. This would >>>>>>> continue until either a) the database complained or b) >>>>>> >> you ran out >> >>>>>>> of memory which ever came first. On Oracle, for example, each >>>>>>> PreparedStatement takes memory on the database and once you hit >>>>>>> 100 or so the database throws an exception when you try to get >>>>>>> another one. >>>>>>> >>>>>>> I patched the code by releasing the PreparedStatement cache when >>>>>>> the Connection was released and submitted that fix but I'm not >>>>>>> sure it was accepted. What really needs to happen is that the >>>>>>> PreparedStatement cache needs to be enhanced so that an upper >>>>>>> bound can be established via a configuration variable so that >>>>>>> after x PreparedStatements have been cached new >>>>>> >> PreparedStatements >> >>>>>>> will push one of the old ones out of the cache. >>>>>>> >>>>>>> - Jon Harvie >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> Mike Jau <[EMAIL PROTECTED]> >>>>>>> Sent by: [EMAIL PROTECTED] >>>>>>> >>>>>>> 03/22/2001 12:42 PM >>>>>>> Please respond to jboss-user >>>>>>> >>>>>>> >>>>>>> To: "'[EMAIL PROTECTED]'" >>>>>>> <[EMAIL PROTECTED]> >>>>>>> cc: >>>>>>> Subject: RE: [JBoss-user] A little BMP >>>>>>> philosophy/understanding >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> Could you give me some background information about the >>>>>>> Preparedstaement >>>>>>> caching on the EJB container side? >>>>>>> >>>>>>> Since the connection get from pool need to return to >>>>>> >> pool once the >> >>>>>>> transaction done. I assumed that the resouce associate to this >>>>>>> connection >>>>>>> should be released and the released resoure include the >>>>>>> preparedstatement. >>>>>>> Later on, the create preparedstatement will be invoked >>>>>> >> again from >> >>>>>>> different >>>>>>> connection. How the preparedstatement cached is my question? >>>>>>> >>>>>>> >>>>>>> - Mike >>>>>>> >>>>>>> -----Original Message----- >>>>>>> From: Bill Burke [mailto:[EMAIL PROTECTED]] >>>>>>> Sent: Thursday, March 22, 2001 12:10 PM >>>>>>> To: [EMAIL PROTECTED] >>>>>>> Subject: Re: [JBoss-user] A little BMP philosophy/understanding >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> Dan Christopherson wrote: >>>>>>> >>>>>>> > On Fri, 23 Mar 2001, Peter Routtier-Wone wrote: >>>>>>> > >>>>>>> >>> Someone from this discussion group indicate that container >>>>>>> might cache >>>>>>> the >>>>>>> >>> PreparedStatement. >>>>>>> >> >>>>>>> >> I can't speak with authority on this, but that >>>>>> >> rings true. I'm >> >>>>>>> guessing >>>>>>> that >>>>>>> >> interception doesn't happen for the setEntityContext() >>>>>> >>>>>> method and >>>>>> >>>>>>> therefore >>>>>>> >> you actually create a PreparedStatement rather than >>>>>> >> receiving >> >>>>>>> one from >>>>>>> the >>>>>>> >> pool. >>>>>>> >> >>>>>>> >>> Just for kicks, I gave it a try but transactions weren't >>>>>>> completed and >>>>>>> >>> they'd just hang out there forever, blocking every other >>>>>>> persistence and >>>>>>> >>> finder method until they timed out. >>>>>>> >> >>>>>>> >> That would bollox lifecycle management, and the described >>>>>>> behaviour >>>>>>> wouldn't >>>>>>> >> be at all surprising. >>>>>>> > >>>>>>> > This is also a common bean bug: 'close()' should be >>>>>> >>>>>> called on every >>>>>> >>>>>>> > resultset, statement, and connection in a finally clause so >>>>>>> that you know >>>>>>> > it happens every time. >>>>>>> > >>>>>>> >> On the other hand, I'd have thought that PreparedStatements >>>>>>> would be far >>>>>>> >> less costly to manufacture than Connections, and >>>>>> >> therefore not >> >>>>>>> worth the >>>>>>> >> overhead of managing a pool. I think I'll poke my nose into >>>>>>> the source >>>>>>> and >>>>>>> >> see what's there. >>>>>>> > >>>>>>> > There's often communication with the database to create the >>>>>>> > PreparedStatement. That way it can pre-compile a query plan. >>>>>>> There is a >>>>>>> > prepared statement cache in JBoss: in JBoss 2.0, it caused >>>>>>> problems with >>>>>>> > Oracle's cursor limit (fixed in 2.1). >>>>>>> >>>>>>> >>>>>>> I'm re-writing the minerva PreparedStatement caching so >>>>>> >> it handles >> >>>>>>> cursor limit better. I'll submit the code tomorrow after >>>>>> >>>> I test it. >>>> >>>>>>> Bill >>>>>>> >>>>>>> >>>>>>> >>>>>>> _______________________________________________ >>>>>>> JBoss-user mailing list >>>>>>> [EMAIL PROTECTED] >>>>>>> http://lists.sourceforge.net/lists/listinfo/jboss-user >>>>>>> >>>>>>> _______________________________________________ >>>>>>> JBoss-user mailing list >>>>>>> [EMAIL PROTECTED] >>>>>>> http://lists.sourceforge.net/lists/listinfo/jboss-user >>>>>>> >>>>>>> >>>>>> _______________________________________________ >>>>>> Jboss-development mailing list >>>>>> [EMAIL PROTECTED] >>>>>> http://lists.sourceforge.net/lists/listinfo/jboss-development >>>>>> >>>> >>>> _______________________________________________ >>>> Jboss-development mailing list >>>> [EMAIL PROTECTED] >>>> http://lists.sourceforge.net/lists/listinfo/jboss-development >>>> > > > _______________________________________________ > Jboss-development mailing list > [EMAIL PROTECTED] > http://lists.sourceforge.net/lists/listinfo/jboss-development > > _______________________________________________ > Jboss-development mailing list > [EMAIL PROTECTED] > http://lists.sourceforge.net/lists/listinfo/jboss-development > > > _______________________________________________ Jboss-development mailing list [EMAIL PROTECTED] http://lists.sourceforge.net/lists/listinfo/jboss-development
