Does the prepared statement compiled in the database driver still require compilation in the database?

Question

The Oracle JDBC driver has an option to cache prepared statements. My understanding of this is that prepared statements are precompiled by the driver and then cached, which improves performance for cached prepared statements.

My question is: does this mean that the database should never compile prepared statements? Is the JDBC driver sending some precompiled view, or is there some kind of parsing / compilation that happens in the database itself?

Answer 1

When using an implicit statement cache (or an Oracle extension for an explicit statement cache), the Oracle driver will cache the prepared or called statement after (!) The close () function for reuse with a physical connection.

So what happens: if the prepared statement is used and the physical connection has never seen it, it sends SQL to the database. Depending on whether the database saw the expression earlier or not, it will perform hard parsing or soft parsing. So usually, if you have 10 connection pools, you will see 10 parses, one of which will be a strong analysis.

After closing the connection operator, the Oracle driver will place the handle in the analyzed operator (general cursor) in the LRU cache. The next time you use prepareStatement on this connection, it discovers that this cached handle is being used and there is no need to send SQL at all. This leads to execution without NO PARSE.

If you have more (different) prepared statements used for physical connection than cache size, then the longest unused open shared cursor is closed. Which leads to another soft analysis the next time the statement is used again, since SQL needs to be sent back to the server.

This is basically the same function as some middleware data sources implemented more generally (for example, the cache of a ready-made application in JBoss). Use only one of them to avoid double caching.

Details can be found here:

http://docs.oracle.com/cd/E11882_01/java.112/e16548/stmtcach.htm#g1079466

Also check the Oracle Unified Connection Pool (UCP), which supports this and interacts with FAN.

Answer 2

I think this answers your question: (sorry this is a powerpoint, but it determines how the prepared statement is sent to Oracle, how Oracle stores it in the shared package pool, processes it, etc.). The main performance gain that you get from prepared statements is that in the 1 + n-th run you avoid hard parsing the sql statement.

http: //www.google.com/url AFQjCNG9Icy6hmlFUWHj2ruUsux7mM4Nag & cad = rja

Oracle (or db by choice) will save the prepared statement, java will just send it the same statement that it will select from db (these are limited resources, however, after x time of no query, the general SQL code will be deleted by esp. Not common queries), and then reanalysis is required - regardless of whether it is cached in your Java application.