Hi Lukas, Thanks for sharing your knowledge of the Oracle database behaviors!
> In my job, we run a > database with 700k users 2k-10k concurrent sessions and several tables > with about 1 billion records. There is a total of around 50 Weblogic > servers querying a single Oracle instance (quite a "classic" setup). If all of them execute the same limited set of queries, then the considerations are different from a handful of users executing hundreds of different queries. I read that Oracle prepares the statement as part of the first fetch, which means there is no round trip before actual execution. This is how it seems to happen on SQLServer though: http://msdn.microsoft.com/en-us/library/ms175528.aspx "Excess use of the prepare/execute model can degrade performance. If a statement is executed only once, a direct execution requires only one network round-trip to the server. Preparing and executing an SQL statement executed only one time requires an extra network round-trip; one trip to prepare the statement and one trip to execute it." > I'm not sure what you mean by "ad-hoc" queries...? I think I read this terminology on SQLServer documentation when talking about SQL statements in String form (not prepared). > We found Oracle 11g to be extremely good at this. That is the part of the joys of working with different implementations of the same concepts, what is applicable to one may be totally not recommended on another one! :) > In order to take advantage of this, you only have to be sure that jOOQ > renders the same SQL for the same query every time. In short, > PreparedStatements are better than Statements with hard-wired SQL, as > you can avoid hard-parses and reuse previous execution plans many > times. This is in the case where the same SQL statement may be executed more than once within the max number of data plans that are kept in the cache. Otherwise, each call will issue a query on the server to get prepared, then each call will actually execute. This doubles the number of server accesses compared to using ad-hoc queries, for no benefits if the queries are not similar. As I said, even if they were, SQLServer caches the data plans of ad-hoc queries too. Again, I am not a database expert so I may have misconceptions. But if I am right, then the exclusive approach of using prepared statements in jOOQ may make it not practical for us to use the fetch methods and we would have to call getSQL() to switch to external execution. Cheers, -Christopher
