'Statements' as per this thread, result in Cursors in the database engine's allocated memory, these server side cursors are transient/implicitly allocated or they are explictly allocated and named and so stay around as long as the connection handle persists. In the even that transient cursors have been reused sufficiently they remain in cache for quick access, if they are not reused, then lru mechanism kicks the older unused ones out.
Also a thing to keep in mind is that statements are only reusable as long as their hash values are exact, should another statement get prepared with slight alterations to the SQL this will not deliver a match... In the middleware connection layer statements are associated with a transaction, and clearing of statements will occur as transactions are committed or connections are closed. With the Oracle dbms a dba can force a cursor to stay in database server side memory by puportedly 'pinning' the cursor/sql id to memory.... sigh, this requires some thought as pinning too many of them will leave little room for larger objects to be placed into the cache. Also change control is tricky... If you want true reusability, stored procedures are the way to go for fast execution and easy access across all connections. Rather a long explanation, sorry... more than ya ever wanted to know about those things,no? cheers. m > Subject: [jdjlist] RE: PreparedStatement > Date: Fri, 10 May 2002 20:20:54 -0600 > From: "Don Brown" <[EMAIL PROTECTED]> > Reply-To: "JDJList" <[EMAIL PROTECTED]> > To: "JDJList" <[EMAIL PROTECTED]> > > I believe in Oracle, the prepared statement will kick around until the database is >shut down, or until statistics are recalculated. > > Don Brown > > -----Original Message----- > From: Kevin Mukhar [mailto:[EMAIL PROTECTED]] > Sent: Friday, May 10, 2002 4:24 PM > To: JDJList > Subject: [jdjlist] RE: PreparedStatement > > > "Matveyenko, Wade" wrote: > > > > In addition, PreparedStatement versus Statement depends on your database and > > driver. In the case of Oracle, this reference > > http://www.onjava.com/lpt/a//onjava/excerpt/oraclejdbc_19/index.html states > > that a you have to do at least 65 iterations of a PreparedStatement to > > overcome the initial overhead and make it more performant than a Statement. > > In terms of execution speed, that may be correct. However, with Oracle, there is > also the memory advantage. An SQL PreparedStatement (with placeholders) executed > 50 times (setting the placeholders with 50 different parameter values) will take > up memory for one SQL statement. The equivalent SQL Statement (no placeholders) > executed 50 times (literal data statically embedded into the SQL) will take up > memory for 50 statements. > > To change your membership options, refer to: > http://www.sys-con.com/java/list.cfm > > To change your membership options, refer to: > http://www.sys-con.com/java/list.cfm To change your membership options, refer to: http://www.sys-con.com/java/list.cfm
