数据库驱动程序中已编译的预处理语句是否仍需要在数据库中进行编译? [英] Does the compiled prepared statement in the database driver still require compilation in the database?

问题描述

在Oracle 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.

我的问题是，这是否意味着数据库永远不必编译那些准备好的语句? JDBC驱动程序是否发送一些预编译的表示形式，还是在数据库本身中进行某种解析/编译?

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?

推荐答案

当您使用隐式语句缓存(或将Oracle Extension用于显式语句缓存)时，Oracle驱动程序将在(！)之后缓存准备好的或可调用的语句. close()以与物理连接一起使用.

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.

那么会发生什么:如果使用准备好的Statement，并且物理连接从未见过，它将把SQL发送到DB.根据数据库是否之前看过该语句，它将执行硬解析或软解析.因此，通常，如果您有10个连接池，则将看到10个解析，其中一个解析是硬解析.

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.

在连接上关闭语句后，Oracle驱动程序会将解析后的语句(共享游标)的句柄放入LRU缓存中.下次在该连接上使用prepareStatement时，它将找到要使用的此缓存句柄，并且根本不需要发送SQL.这样将导致执行不具有任何分析.

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.

如果在物理连接上使用的(不同的)预备语句多于高速缓存的大小，则关闭最长的未使用的打开共享游标.下次再次使用该语句时，将导致另一个软解析-因为需要将SQL重新发送到服务器.

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.

这基本上与某些中间件数据源实现的功能相同(例如，JBoss中的prepared-statement-cache).只能使用两者之一，以避免双重缓存.

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

还请检查支持此功能并与FAN交互的Oracle统一连接池(UCP).

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

这篇关于数据库驱动程序中已编译的预处理语句是否仍需要在数据库中进行编译?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！