Different parameter sizes result in inefficient query plan cache

Question

Nhibernate profiler shows lots of error messages about the query plan:

Different parameter sizes result in inefficient query plan cache usage

It also leads you to an explanation in http://nhprof.com/Learn/Alerts/UncachedQueryPlan and warns you about the use of prepare_sql = true parameter when building session. I do it that way with fluent:

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

But it seems that it isn't working as error messages are still there. Is that a limitation on OracleClientConfiguration or am I doing it wrong?

Edit To provide with some more information about this...

In my repository I do this

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

and this is the call

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

For instance those are two SQL generated from this call and that nhibernate profiler shows as "DIfferent parameter sizes result in inefficient query plan cache usage"

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 this PARAMETER_NAME with 'lid' and 'period' that is generating different query plans.

thanks in advance

I don't relly now how is oracle query plan, but the scenario is quite similar at [the one pointed out in ayende's description of the issue](http://nhprof.com/Learn/Alerts/UncachedQueryPlan). Briefly it says that nhibernate must be confiured to execute querie in a _friendly query plan way_ and, from what I see I doesn't work. — guillem, Mar 18 '12 at 09:14
well, i'd recommend a more analytical approach, otherwise it is trial and error. What are the errors? — steve, Mar 18 '12 at 18:25
The errors are warnings complaining that db has to do a different query plan for each query although many of them are quite the same and only differ in on where clause. My fear is that `prepare_sql` doesn't work in oracle databases as it should. — guillem, Mar 18 '12 at 18:58
@guillem: it's unlikely that `prepare_sql` doesn't work (like they say- "select isn't broken" http://www.codinghorror.com/blog/2008/03/the-first-rule-of-programming-its-always-your-fault.html). it's more likely that since your queries differ in their 'where' clauses, this forces the dbms to generate different query plans. If you could post your queries perhaps we can assist more. — J. Ed, Mar 19 '12 at 11:35
@sJhonny I have added more datails in the question body, let's see if this adds some light ;) — guillem, Mar 19 '12 at 13:53
@guillem Can you post the explain plan of both queries? Remember Oracle runs off of statistics, so changing the parameter value being passed in can and will affect things. If you use bind parameters, Oracle will more than likely use the same plan for both queries. — Nick, Jun 07 '12 at 02:02
You should check if it REALLY generates two different plans and if it is using bind variables. As it is, I don't see how bind variable size alone can force Oracle to reparse a query. — jva, Jul 19 '12 at 07:32
From Oracle's point of view these are two different queries and will be parsed and prepared separately. There should have been bind variables instead of constants. You will have to get an answer from someone who knows Hibernate on how to do that. — jva, Jul 24 '12 at 22:23
@sjhonny looking at the NHibernate source, it looks like prepare_sql will not work for oracle drivers. The code to set the parameter lengths is in SqlClientDriver.SetVariableLengthParameterSize — mattk, Sep 26 '12 at 10:05

score 0 · Answer 1 · answered Sep 26 '12 at 10:01

To generate the same plan each time the parameter needs to be set to the same length regardless of the parameter value.

You can customise the driver implementation to set the query parameter length 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 like this:

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

score 0 · Answer 2 · edited Apr 13 '17 at 12:42

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 didn't see any differences in the Oracle execution, although string parameters now had a common size.

Here's my post on Stackexchange DBA.

Oracle Enterprise Manager showed no duplicate queries for my NHibernate generated SQL, and in both versions, each call caused a parse (up to some 1000 for long testing), but very few hard parses, with no differences between variable and fixed parameter length.

In fact, Oracle created duplicate query plans only for queries without bind parameters, but with values concatenated into the SQL string (something to avoid in coded SQL). So it now seems to me that parameter size doesn't matter for Oracle, when it comes to reuse query plans (or, in Oracle terms, cursor sharing).

Oracle probably only compares the SQL string for plan matching, while SQL Server also checks the parameter definitions. You can also see a difference when looking at the dynamic SQL commands EXECUTE IMMEDIATE (Oracle) and sp_executesql (SQL Server): sp_executesql also gets a string with parameter definitions (in a string, not as parameters for the sp_executesql call itself!). I know NHibernate/ADO.NET uses sp_executesql when sending parameterized queries to SQL Server, so it likely has a different handling under SQL Server. Also, when connecting to SQL Server via NHibernate, all string parameters have unique sizes (from NHibernate mapping or default max length), so the problem has likely been fixed where relevant. Correct me if I'm wrong!

Using Prepare/prepare_sql in ADO.NET/NHibernate has some disadvantages: depending on implementation, before any SQL is executed, a Prepare request has to be sent to the database, the application has to keep a handle for the prepared statement, and it can be used only for one connection. Meaning: new handles must be created often. When I tested with Oracle and ODP.NET, it was somewhat slower than the non-prepared version, although querying by handle itself is (little) more performant than by parameterized SQL, matched on database by string equality. Likely, Prepare is good if the application uses many times the same query within the same DB connection or NHibernate session.

Different parameter sizes result in inefficient query plan cache

2 Answers2