Different parameter sizes result in inefficient query plan cache

Question

The Nhibernate profiler shows a lot of error messages in terms of request:

Different parameter sizes lead to inefficient use of the query plan cache

This also leads to an explanation at http://nhprof.com/Learn/Alerts/UncachedQueryPlan and warns you about using the prepare_sql = true parameter when creating a session. I do it so freely:

 .ExposeConfiguration(configuration => configuration .SetProperty("current_session_context_class", "thread_static") .SetProperty("prepare_sql", "true") .SetProperty("generate_statistics", "true") ) 

But it does not seem to work, as error messages still exist. Is this a limitation on OracleClientConfiguration or am I doing it wrong?

Edit To provide more information about this ...

In my repository I do this

 session.Query<TEntity>.Where(predicate).ToList(); 

and this is a challenge

 var value = ParameterRepository.First(p => (p.Pipeline.Id == pipelineId && p.Name == name)); 

For example, these are two SQL generated from this call, and that the nhibernate profiler shows that "the sizes of the DIfferent parameters lead to inefficient use of the query plan cache"

 select GUID1_12_, PARAMETER2_12_, PARAMETER3_12_, GUID4_12_ from (select pipelineex0_.GUID_PIPELINE_EXEC_PARAMETER as GUID1_12_, pipelineex0_.PARAMETER_NAME as PARAMETER2_12_, pipelineex0_.PARAMETER_VALUE as PARAMETER3_12_, pipelineex0_.GUID_PIPELINE_TRACKING as GUID4_12_ from FCT_PIPELINE_EXEC_PARAMETER pipelineex0_ where pipelineex0_.GUID_PIPELINE_TRACKING = 'A5916E73CF1E406DA26F65C24BFBF694' /* :p0 */ and pipelineex0_.PARAMETER_NAME = 'lid' /* :p1 */) where rownum <= 1 /* :p2 */ 

and second

 select GUID1_12_, PARAMETER2_12_, PARAMETER3_12_, GUID4_12_ from (select pipelineex0_.GUID_PIPELINE_EXEC_PARAMETER as GUID1_12_, pipelineex0_.PARAMETER_NAME as PARAMETER2_12_, pipelineex0_.PARAMETER_VALUE as PARAMETER3_12_, pipelineex0_.GUID_PIPELINE_TRACKING as GUID4_12_ from FCT_PIPELINE_EXEC_PARAMETER pipelineex0_ where pipelineex0_.GUID_PIPELINE_TRACKING = 'A5916E73CF1E406DA26F65C24BFBF694' /* :p0 */ and pipelineex0_.PARAMETER_NAME = 'period' /* :p1 */) where rownum <= 1 /* :p2 */ 

IMHO is PARAMETER_NAME with a cap and a period that generates different query plans.

early

Answer 1

To generate the same plan every time a parameter must be set to the same length regardless of the parameter value.

You can configure the driver implementation to set the length of the query parameter to the field length specified in your mapping.

 public class CustomOracleClientDriver : OracleClientDriver { protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType) { base.InitializeParameter(dbParam, name, sqlType); if (sqlType.LengthDefined) dbParam.Size = sqlType.Length; } } 

(NOTE: Inherit from OracleDataClientDriver if you are using ODP.Net)

If you are using Fluent NHibernate, you register your driver implementation as follows:

 Fluently.Configure() .Database( OracleDataClientConfiguration.Oracle10 .ConnectionString(c => c.FromAppSetting("ConnectionString")) .Driver<CustomOracleClientDriver>()) 

Answer 2

I tested this with an overridden OracleClientDriver (using the old Microsoft Oracle driver, not ODP.NET), similar to the code in the answer from mattk, and I did not see differences in Oracle performance, although the string parameters now had a common size.

Here is my post at DBA Stackexchange.

Oracle Enterprise Manager did not detect duplicate queries for my NHibernate-based SQL server, and in both versions each call caused parsing (up to 1000 for lengthy testing), but very few hard parsing, no difference between variable and fixed length parameter.

In fact, Oracle created duplicate query plans only for queries without binding parameters, but with values ​​associated with the SQL string (which can be avoided in encoded SQL). So now it seems to me that the size of the parameter does not matter to Oracle when it comes to reusing query plans (or, in Oracle's terms, exchanging cursors).

Oracle probably only compares the SQL string to match the plan, while SQL Server also checks the parameter definitions. You can also see the difference when viewing dynamic SQL commands EXECUTE IMMEDIATE (Oracle) and sp_executesql (SQL Server): sp_executesql also receives a string with parameter definitions (in a string, and not as a parameter for calling sp_executesql !). I know that NHibernate / ADO.NET uses sp_executesql when sending parameterized queries to SQL Server, so it probably has different processing in SQL Server. In addition, when connecting to SQL Server through NHibernate, all string parameters have unique sizes (from the NHibernate mapping or the default maximum length), so the problem was probably fixed where necessary. Correct me if I am wrong!

Using Prepare / prepare_sql in ADO.NET/NHibernate has some disadvantages: depending on the implementation, before executing any SQL query, the send request must be sent to the database, the application must save the handle of the prepared statement, and it can be used for only one connection . Meaning: New pens should be created often. When I tested with Oracle and ODP.NET, it was somewhat slower than the unprepared version, although the query on the desktop itself is (slightly) more efficient than parameterized SQL, which is mapped to a row equality database. Most likely, Prepare is good if the application uses the same query many times in the same DB or NHibernate connection.