Using Oracle Optimizer Hints in Entity Framework
January 23rd, 2012With 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.Exployees
...
select emp;
var queryWithHints = originalQuery.Where(e =>
OracleFunctions.Hints("INDEX(employees index1)"));
var result = queryWithHints.ToList();
or using query builder functionality of ObjectQuery (only when using ObjectContext, not DbContext).
var originalQuery = ctx.Exployees
.Where("Devart.Data.Oracle.HINTS('INDEX(employees 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.Exployees.OracleHints("FULL(employees) PARALLEL(employees, 5)")
...
select emp;
var result = query.ToList();
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 emp in ctx.Exployees.OracleHints("FULL(employees) NOCACHE(employees)")
...
select emp;
SELECT /*+ FULL(employees) NOCACHE(employees) */ last_name FROM employees; ...
If several hints are used, they are united in a single one.
var query = ctx.Employees
...
select emp;
query = query.Where(e => OracleFunctions.Hints("FULL(employees)"));
query = query.Where(e => OracleFunctions.Hints("NOCACHE(employees)"));
SELECT /*+ FULL(employees) NOCACHE(employees) */ last_name FROM employees;
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.Employees.Where(e => OracleFunctions.Hints(
"PARALLEL_INDEX(employees, index1, 3)"
));
and the following variable cannot:
string hints = "PARALLEL_INDEX(employees, index1, 3)"; var query = ctx.Employees.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.
