数据库驱动程序中已编译的预备语句是否仍需要在数据库中进行编译？
在 Oracle JDBC 驱动程序中，有一个选项可以缓存准备好的语句。我对此的理解是，准备好的语句由驱动程序预编译，然后缓存，这提高了缓存的准备好的语句的性能。
我的问题是，这是否意味着数据库永远不必编译那些准备好的语句？ JDBC 驱动程序是否发送一些预编译的表示形式，或者数据库本身是否仍然发生某种解析/编译？
In the Oracle JDBC driver, there is an option to cache prepared statements. My understanding of this is that the prepared statements are precompiled by the driver, then cached, which improves performance for cached prepared statements.
My question is, does this mean that the database never has to compile those prepared statements? Does the JDBC driver send some precompiled representation, or is there still some kind of parsing/compilation that happens in the database itself?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
当您使用隐式语句缓存（或显式语句缓存的 Oracle 扩展）时，Oracle 驱动程序将在 close() 之后缓存准备好的或可调用的语句，以便与物理连接重用。
所以会发生的情况是：如果使用了准备好的语句，并且物理连接从未见过它，它会将 SQL 发送到数据库。根据数据库之前是否见过该语句，它将执行硬解析或软解析。因此，通常如果您有 10 个连接池，您将看到 10 次解析，其中之一是硬解析。
连接上的语句关闭后，Oracle 驱动程序会将已解析语句（共享游标）的句柄放入 LRU 缓存中。下次您在该连接上使用prepareStatement 时，它会找到要使用的缓存句柄，并且根本不需要发送SQL。这会导致执行时没有 PARSE。
如果物理连接上使用的（不同的）准备语句多于缓存的大小，则关闭最长的未使用的打开共享游标。这会导致下次再次使用该语句时再次进行软解析 - 因为 SQL 需要再次发送到服务器。
这基本上与一些中间件数据源更通用地实现的功能相同（例如 JBoss 中的准备语句缓存）。仅使用两者之一以避免双重缓存。
您可以在此处找到详细信息：
http://docs。 oracle.com/cd/E11882_01/java.112/e16548/stmtcach.htm#g1079466
另请查看支持此功能并与 FAN 交互的 Oracle 统一连接池 (UCP)。
When you use the implicit statement cache (or the Oracle Extension for the explicit Statement Cache) the Oracle Driver will cache a prepared- or callable statement after(!) the close() for re-use with the physical connection.
So what happens is: if a prepared Statement is used, and the physical connection has never seen it, it sends the SQL to the DB. Depending if the DB has seen the statement before or not, it will do a hard parse or a soft parse. So typically if you have a 10 connection pool, you will see 10 parses, one of it beein a hard parse.
After the statement is closed on a connection the Oracle driver will put the handle to the parsed statement (shared cursor) into a LRU cache. The next time you use prepareStatement on that connection it finds this cached handle to use and does not need to send the SQL at all. This results in a execution with NO PARSE.
If you have more (different) prepared statements used on a physical connection than the cache is in size the longest unused open shared cursor is closed. Which results in another soft parse the next time the statement is used again - because SQL needs to be sent to the server again.
This is basically the same function as some data sources for middleware have implemented more generically (for example prepared-statement-cache in JBoss). Use only one of both to avoid double caching.
You can find the details here:
http://docs.oracle.com/cd/E11882_01/java.112/e16548/stmtcach.htm#g1079466
Also check out the Oracle Unified Connection Pool (UCP) which supports this and interacts with FAN.
我认为这回答了你的问题：（抱歉，它是 powerpoint，但它定义了如何将准备好的语句发送到 Oracle，Oracle 如何将其存储在共享 SQL 池中、处理它等）。从准备好的语句中获得的主要性能提升是，在第 1+n 次运行时，您可以避免对 sql 语句进行硬解析。
http://www.google.com/url?sa=t& source=web&cd=2&ved=0CBoQFjAB&url=http%3A%2F%2Fchrisgatesconsulting.com%2FpreparedStatements.ppt&rct=j&q=java%20oracle%20sql%20prepared%20statements&ei=z0iaTJ3tJs2InQeClPwf&usg= AFQjCNG9Icy6hmlFUWHj2ruUsux7mM4Nag&cad=rja
Oracle（或选择的数据库）将存储准备好的语句，java只需发送数据库将从中选择的相同语句（但是，这是有限的资源，在没有查询共享的x时间之后sql 将被清除，尤其是非常见查询），然后需要重新解析——无论它是否缓存在您的 java 应用程序中。
I think that this answers your question: (sorry it is powerpoint but it defines how the prepared statement is sent to Oracle, how Oracle stores it in the Shared SQL pool, processes it, etc). The main performance gain you are getting from Prepared statements is that on the 1+nth run you are avoiding hard parses of the sql statement.
http://www.google.com/url?sa=t&source=web&cd=2&ved=0CBoQFjAB&url=http%3A%2F%2Fchrisgatesconsulting.com%2FpreparedStatements.ppt&rct=j&q=java%20oracle%20sql%20prepared%20statements&ei=z0iaTJ3tJs2InQeClPwf&usg=AFQjCNG9Icy6hmlFUWHj2ruUsux7mM4Nag&cad=rja
Oracle (or db of choice) will store the prepared statement, java just send's it the same statement that the db will choose from (this is limited resources however, after x time of no query the shared sql will be purged esp. of non-common queries) and then a re-parse will be required -- whether or not it is cached in your java application.