Adam McMahon <[email protected]> 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 10.10.1.1 > > 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 guide: http://db.apache.org/derby/docs/10.10/tuning/ctundepth32379.html > 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 individually. 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
