DB Hints

DB HINTS

What is DB Hints?

DB Hints / Optimizer Hints can be used with SQL statements to alter execution plans.

What is the purpose of DB Hints?

DB Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know. Hints provide a mechanism to instruct the optimizer to choose a certain query execution plan based on the specific criteria.

What are the types of DB Hints?

NOCOPY:

The NOCOPY hint tells the PL/SQL compiler to pass OUT and IN OUT parameters by reference, rather than by value. When parameters are passed by value, the contents of the OUT and IN OUT parameters are copied to temporary variables which are then used by the subprogram being called. The process of copying large parameters, such as records, collections, and objects which requires both time and memory affects performance.  Thus when NOCOPY hints are used, the parameters are passed by reference. So no temporary buffer is needed and no copy forward and copy back operations happen.

APPEND:

The APPEND hint tells the optimizer to perform a direct-path insert, which improves the performance of ‘INSERT’, ‘SELECT’ operations for a number of reasons:

Data is appended to the end of the table, rather than attempting to use existing free space within the table.

Data is written directly to the data files, by-passing the buffer cache.

No trigger processing is performed.

The combination of these features make direct-path inserts significantly quicker than conventional-path inserts. Oracle ignores the APPEND hint and the data is loaded using conventional path enabled triggers are present on a table. As direct-path inserts append data to the end of the table, they constantly increase the table high water mark, even if there is lots of free space within the table. In tables that regularly have rows deleted, the use of the the APPEND hint can result in large tables containing lots of sparsely populated blocks. These will need to be managed by one of the following types of shrink operation.

Export the data, truncate the table and import the data.

TABLE ... AS SELECT" (CTAS) operation to build a Use a "CREATE new table with the data compacted, drop the original table and rename the new table to replace the original.

ORDERED:

Oracle has the ordered hint to join multiple tables in the order that they appear in the FROM clause. The ordered hint can be a huge performance help when the SQL is joining large number of tables, and you know that the tables should always be joined in a specific order. Oracle must spend a great deal of time parsing multiple table joins to determine the optimal order to join the tables. The ordered hint is commonly used in conjunction with other hints to suggest a proper join order.

The ordered hint requests that the tables listed in the FROM clause of a SQL statement be joined in the order specified, with the first table in the FROM clause specifying the driving table. The driving table should be the table that returns the smallest number of rows. Using the ordered hint will bypass the very expensive and time-consuming parsing operation and speed the execution of Oracle SQL.

BYPASS_UJVC:

UJVC stands for ‘updatable join view check’. The UPDATE statement is more complex than the simple select, insert, delete statements and incomplete or misleading business requirements can make it even more complex. The BYPASS_UJVC hint can be used to bypass some of the limitations that Oracle imposes on the updates that use a join, such as the fact that the join be key preserved. BYPASS_UJVC hint is an undocumented hints, which means that it is no longer supported by Oracle

STATISTICS GATHER:

The GATHER_PLAN_STATISTICS hint, can be used to obtain statistics about the execution plan during the execution of a statement. It is especially helpful when you intend to diagnose performance issues with a particular statement. It is definitely not meant to be used in production instances. There is also a GATHER_PLAN_STATISTICS, which Oracle lists under ‘Other hints’. It can be used to collect bulk-load statistics for CTAS statements and  INSERT INTO, SELECT statements that use a direct-path insert, which is accomplished with the APPEND hint.

USE NL:

The USE_NL hint instructs the optimizer to join each specified table to another row source with a nested loops join, using the specified table as the inner table.

In the following example, where a nested loop is forced through a hint, orders is accessed through a full table scan and the filter condition l.order_id = h.order_id is applied to every row. For every row that meets the filter condition, order_items is accessed through the index order_id.

For Example,

SELECT /*+ USE_NL(l h) */

    h.customer_id,

    l.unit_price * l.quantity

FROM

    orders h,

    order_items l

WHERE

l.order_id = h.order_id;

UNDOCUMENTED HINTS:

Reasons why some hints are not documented are as follows.

There are hints which are actually internal hints that operate in situations that the end-user cannot manufacture.  The index_rrs() hint that used to appear in some parallel query slave code (for parallel index fast full scans) was an example of this type of hint.

There are hints which are not yet documented because there is some beta code that is still subject to finalization. The hint may become official, it may cease to exist. The not-quite-documented selectivity() hint of 9i – which subsequently disappeared – may have been such a case.

There are hints which never got into the documentation because no-one told the editors about it in time. The dynamic_sampling_est_cdn() hint is probably an example of this type. It’s in the 9.2 Performance Tuning Guide and Reference – but not in the table of contents, the index, or the lists of hints given in the chapter on hints. Instead it appeared as an afterthought in the section on the dynamic_sampling() hint.

So when you look at undocumented hints, you have to ask yourself – are they supposed to be official, or will they soon be official, or are they actually official if only you can find where they’ve been documented.

DEPRECATED HINTS:

Some of the deprecated hints are as follows,

1. AND_EQUAL

2. HASH_AJ

3. MERGE_AJ

4. NL_AJ

5. HASH_SJ

6. MERGE_SJ

7. NL_SJ

8. EXPAND_GSET_TO_UNION

9. ORDERED_PREDICATES

10. STAR hints

These hints have been deprecated since Oracle 10g and should not be used.

Conclusion

Apart from the mentioned hints, there are many other types of hints like In Memory store hints, Join Operation Hints, Parallel Execution Hints, Query Transformation Hints, XML Hints, NO CACHE, etc.

Hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using the EXPLAIN PLAN statement. Changing database conditions as well as query performance enhancements in subsequent releases can have significant impact on how hints in your code affect performance.

Oracle documented that using hints rather than the more advanced tuning tools, we must be aware that any short-term benefit resulting from the use of hints may not continue to result in improved performance over the long term. Hence we must careful while using the hints.