db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Do PreparedStatements with same SQL compile to multiple classes?
Date Mon, 30 Jun 2014 08:30:33 GMT
Adam McMahon <adam@cs.miami.edu> writes:

> Hi,
> I have some questions regarding Derby's compilation of PreparedStatements to
> Java bytecode, and how/when that byte/code is loaded/unloaded or released
> from memory.
> We use java verion 1.6.0_31 and derby
> We use a custom connection connection pool that does something similar to
> the following. I have labeled the lines of pseudo-code so I refer to them
> with specific questions.
> 1. // pool is requested for a connection, if non exists, create a new one.
> 2. con1 = DriverManager.getConnection()
> 3. ps1 = con1.prepareStatement(“select * from cities where id=?”)
> 4. ps1.setInt(1, n)
> 5. ps1.executeQuery()
> 6. //continue to use ps1 with different values...
> 7. // after con1 is idle for a period of time it is closed by the pool.
> 8. ps1.close and con1.close
> 9. // new request comes in, and a new connection is created
> 10. con2 = DriverManager.getConnection()
> 11. ps2 = con1.prepareStatement(“select * from cities where id=?”)  // same
> SQL as above
> 12. ps2.setInt(1, n)
> 13. ps2.executeQuery()
> 14. //continue to use ps2 with different values...
> 15. // after con2 is idle for a period of time it is closed by the pool.
> 16. ps2.close and con2.close()
> Here my questions with the above scenario:
> a) at step [2] and [10], two preparedstatments are created with the same
> SQL, but by different connections. When these are compiled, are two separte
> java classes created and loaded? Or is only one newly created/compiled class
> created and loaded, and then reused since they have the same sql? (even
> though they originate from different connections?)

Hi Adam,

Derby has a statement cache that holds 100 statements by default, and it
will attempt to use one of those instead of compiling a new one if it
can. More details about the statement cache can be found in the tuning


> b) Since Prepared Statements are compiled into byte code and loaded, are
> they ever unloaded? If the above sequence continues, will the PermGen (or
> another memory space) eventually fill up?

The generated classes can be unloaded by the garbage collector once
there are no references to their corresponding statements. Since a class
cannot be garbage collected until its class loader is eligible for
garbage collection, Derby creates a separate class loader for each
generated class so that the generated classes can be garbage collected

Because of the statement cache, though, a generated class may not become
eligible for garbage collection immediately after the application stops
referencing the statement. The statement will also have to be evicted
from the statement cache before the class can be garbage collected and
unloaded. In a typical application you'd expect the number of generated
classes to grow until the statement cache is full, and then stabilize.

Hope this helps,

Knut Anders

View raw message