Hey Jay,
Thanks for the great info! One question follows inlined.
Jay Walters wrote:
> 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.
"this JDBC statement caching build into the JDBC driver"...That's if you
turn it on as above, correct?
Thanks, you and Filip have been a great help on understanding the entire
problem,
Bill
>
> 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
>
>
>
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-development
