#56 how to empty prepared statement cache?

v1.0 (example)
open
nobody
None
5
2013-08-21
2013-08-17
AdamWozPL
No

Hello

I'm using c3p0 (+Hibernate 3.6) in my application (since 2007).

My configuration

Hibernate 3.6.x
c3p0: current version
Oracle JDBC driver (version Oracle 10, afair).

Question

Is it possible to tell c3p0 to empty its prepared statement cache?

Best regards,
Adam

PS.
Explanation why I want to (sometimes) empty c3p0 prepared statement cache:
My application is a batch application which runs about 10 different processing phases (one phase after another - not concurrently). Every phase using completely other set of queries toward database.
I have found in the memory dump viewer that some prepared statements allocates very big arrays. Finally I have found that (at least for Oracle JDBC drivers which i am using) those very large arrays are related to queries like this (Hibernate piece of code):

Query q = session
.createQuery("from MyTable")
.setFetchSize(5000)
.list();

I have realized that those values in setFetchSize method call are responsible for those very large arrays in application memory.

Currently I have reduced those values in setFetchSize but I want to increase them in the future. But to avoid OutOfMemoryError I would like to know hot to empty c3p0 prepared statement cache programmatically to free memory related to prepared statements with large setFetchSize setting (in my case I would like to empty prepared statement cache, for example, after every processing phase).

Related

Support Requests: #56

Discussion

  • Steve Waldman
    Steve Waldman
    2013-08-21

    hi, currently there is no exposed API for resetting just the statement cache. such an api certainly could be added, but i'm not sure how high a priority i'd make of it, yours sounds like a bit of an unusual situation. an easy (albeit imperfect) workaround is just to perform a softReset() between phases. that will cause Connections to be replaced, not just the evacuation of the Statement cache, so it has a higher overhead than you'd like. but it should resolve your memory problem, and unless you run through your processing phases in high frequency repetition, the overhead may well be tolerable. i can think about exposing the Statement cache's closeAll() method, which exists and would do what you want, but no promises for now!

     
  • AdamWozPL
    AdamWozPL
    2013-08-21

    Hi Steve

    1. Will softReset() empty prepared statement cache?
    2. Will softReset() empty prepared statement cache when there are other connections still in use by application?
    3. Do you know where can I find a piece of code how to call softReset() method?

    btw:
    In my case resetting (recreating) a whole connection pool is acceptable.

    Kind regards,
    Adam

    PS. Thank you for your reply.

     
  • Steve Waldman
    Steve Waldman
    2013-08-21

    hi,

    softReset will cause all incumbent Connections to be closed, which implies elimination of all incumbent Statements from the cache (since Statements are children of Connections, elimination of a Statement from the pool provokes removal of its Statements from the cache).

    check out the API docs for AbstractPoolBackedDataSource http://www.mchange.com/projects/c3p0/apidocs/com/mchange/v2/c3p0/impl/AbstractPoolBackedDataSource.html#softResetAllUsers()

    you just call a method on your DataSource -- e.g.

    ComboPooledDataSource cpds = new ComboPooledDataSource();

    // configuration stuff here

    // do stuff here

    cpds.softResetAllUsers()

    // do more stuff

    there are three versions of softReset(), owing to the fact that a single DataSource might host multiple Connection pools, if users have requested Connections with multiple authentications (user/password pairs). for obvious reasons, connections with different authentications are segregated into different pools.

    however in the common use case, only the default authentication is ever used, and the DataSource hosts a single pool. So softResetDefaultUser() and softResetAllUsers() will do the same thing. I'd probably just use softResetAllUsers().

    i hope this helps!

     smiles,
         steve
    

    On Aug 21, 2013, at 3:03 AM, AdamWozPL wrote:

    Hi Steve

    • Will softReset() empty prepared statement cache?
    • Will softReset() empty prepared statement cache when there are other connections still in use by application?
    • Do you know where can I find a piece of code how to call softReset() method?
    btw:
    In my case resetting (recreating) a whole connection pool is acceptable.

    Kind regards,
    Adam

    PS. Thank you for your reply.

    [support-requests:#56] how to empty prepared statement cache?

    Status: open
    Created: Sat Aug 17, 2013 03:01 PM UTC by AdamWozPL
    Last Updated: Wed Aug 21, 2013 04:57 AM UTC
    Owner: nobody

    Hello

    I'm using c3p0 (+Hibernate 3.6) in my application (since 2007).

    My configuration

    Hibernate 3.6.x
    c3p0: current version
    Oracle JDBC driver (version Oracle 10, afair).

    Question

    Is it possible to tell c3p0 to empty its prepared statement cache?

    Best regards,
    Adam

    PS.
    Explanation why I want to (sometimes) empty c3p0 prepared statement cache:
    My application is a batch application which runs about 10 different processing phases (one phase after another - not concurrently). Every phase using completely other set of queries toward database.
    I have found in the memory dump viewer that some prepared statements allocates very big arrays. Finally I have found that (at least for Oracle JDBC drivers which i am using) those very large arrays are related to queries like this (Hibernate piece of code):

    Query q = session
    .createQuery("from MyTable")
    .setFetchSize(5000)
    .list();

    I have realized that those values in setFetchSize method call are responsible for those very large arrays in application memory.

    Currently I have reduced those values in setFetchSize but I want to increase them in the future. But to avoid OutOfMemoryError I would like to know hot to empty c3p0 prepared statement cache programmatically to free memory related to prepared statements with large setFetchSize setting (in my case I would like to empty prepared statement cache, for example, after every processing phase).

    Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/c3p0/support-requests/56/

    To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

     

    Related

    Support Requests: #56