Using Oracle Optimizer Hints in Entity Framework

With each new version of our Devart dotConnect for Oracle ADO.NET Entity Framework provider we pay special attention to improving its performance and configurability. It is pertinent to note some recent improvements: batch updates and flexible customization of Oracle Entity Framework provider behaviour. And now we are glad to present Oracle optimizer hints support in Entity Framework – a new feature for flexible tuning of SQL queries, generated by a user application.

Oracle Optimizer Hints

Oracle supports more than 60 different Oracle optimizer hints. You can see the complete list of Oracle optimizer hints and recommendations on their use in the Oracle Database documentation.

It is better to perform query optimization in external application, such as Devart dbForge Studio for Oracle, or any other tool for Oracle that you prefer. Queries, generated by Entity Framework, can be traced with dbMonitor and then copied and pasted to your SQL development application, where you can evaluate query performance, study execution plan, and choose optimizer hints to use.

Using Oracle Optimizer Hints as Oracle Function

Entity Framework provides not so many extensibility points for 3rd-party Entity Framework providers, so, to implement support for Oracle optimizer hints, we use database-specific functions feature in a non-standard way by adding the Devart.Data.Oracle.HINTS(string hints) function.

You can call this function directly in LINQ to Entities queries using the OracleFunctions class from the Devart.Data.Oracle.Entity assembly

var originalQuery = from emp in ctx.Emps
                    ...
                    select emp;
var queryWithHints = originalQuery.Where(e => 
    OracleFunctions.Hints("INDEX(\"Extent1\" index1)"));
var result = queryWithHints.ToList();

or using query builder functionality of ObjectQuery (only when using ObjectContext, not DbContext).

var originalQuery = ctx.Emps
                    .Where("Devart.Data.Oracle.HINTS('INDEX(\"Extent1\" index1)')")
                    ...
                    .Select(emp => emp);

Using Oracle Optimizer Hints as Extension Method

If you use ObjectContext, not DbContext, you can wrap this Devart.Data.Oracle.HINTS function and create an extension method, using the query builder feature of ObjectQuery.

public static class OracleExtensions {

    public static IQueryable<T> OracleHints<T>(
        this System.Data.Objects.ObjectQuery<T> source, string hints
    )
      where T : class {
      if (source == null)
        throw new ArgumentNullException("source");
      return source.Where("Devart.Data.Oracle.HINTS('" + hints + "')");
    }
}

Then you can use it in the following way:

var query = from emp in ctx.Emps.OracleHints("FULL(emp) PARALLEL(emp, 5)")
            ...
            select emp;
var result = query.ToList();

Using INDEX_HINT Function

The above examples show how to use the HINTS function to instruct the optimizer to use an index scan for the query table. However, in this the HINTS function requires you to specify the alias of the queried table, and this is not always convenient, especially if you are querying data from several tables. In order to solve this problem, we have added the INDEX_HINT function, that allows you to specify any entity property mapped to the column of the table, the index we are using belongs to, instead of the table alias.

The above examples of using Oracle optimizer hints as the Oracle function will look like the following if we use the INDEX_HINT function instead of HINTS.

var originalQuery = from emp in ctx.Emps
                    ...
                    select emp;
var queryWithHints = originalQuery.Where(e =>
    OracleFunctions.IndexHint(e => e.Id, "index1"));
var result = queryWithHints.ToList();

and

var originalQuery = ctx.Emps
                    .Where("Devart.Data.Oracle.INDEX_HINT(it.Id, 'index1')")
                    ...
                    .Select(emp => emp);

Specifics and Limitations of Oracle Optimizer Hints in dotConnect for Oracle

We decided to always place hints after the first, upper-level SELECT keyword because structure of a generated SQL query does not always correspond to the structure of the LINQ to Entities query structure, and there is no unambiguous way to specify hints for a specific subquery.

var query = from dept in ctx.Depts.OracleHints("FULL(dept) NOCACHE(dept)")
            ...
            select dept;

SELECT /*+ FULL(dept) NOCACHE(dept) */ last_name
  FROM dept;
...

If several hints are used, they are united in a single one.

var query = ctx.Depts
            ...
            select dept;
query = query.Where(e => OracleFunctions.Hints("FULL(dept)"));
query = query.Where(e => OracleFunctions.Hints("NOCACHE(dept)"));

SELECT /*+ FULL(dept) NOCACHE(dept) */ last_name
  FROM dept;

You cannot use string variables as OracleFunctions.Hints parameters, only string literals are allowed. For example, the following literal can be used:

var query = ctx.Emps.Where(e => OracleFunctions.Hints(
     "PARALLEL_INDEX(emp, index1, 3)"
));

and the following variable cannot:

string hints = "PARALLEL_INDEX(emp index1, 3)";
var query = ctx.Emps.Where(e => OracleFunctions.Hints(hints);

Afterword

Thus, now dotConnect for Oracle Entity Framework provider has a new feature for flexible customization of SQL generation. Surely, Oracle optimizer hints is not a silver bullet and cannot solve all your problem, besides, one need some specific knowledge to use them. However, Oracle optimizer hints can be very useful in some cases, especially when changing database structure (creating/editing/deleting indexes, etc.) cannot be done for some reason. Oracle optimizer hints should be considered as one of the useful tools for developing applications using Entity Framework.