Introduction
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 precise 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 significant 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 some 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, bypassing the buffer cache.
No trigger processing is performed.
The combination of these features makes 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 continuously increase the high table watermark, even if there is lots of free space within the table. In tables that regularly have rows deleted, the use of 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 modern 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 requested sign can be a considerable performance help when the SQL is entering a 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 indications 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 costly 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 complicated than the simple select, insert, delete statements, and incomplete or misleading business requirements can make it even more complicated. 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 is essentially preserved. BYPASS_UJVC hint is an undocumented hints, which means that Oracle no longer supports it
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 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 comments 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 are 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 internal hints that operate in situations that the end-user cannot manufacture. The index_rrs() sign that used to appear in some parallel query slave code (for identical 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 suggestion 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() advice 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 suggestions. Instead, it appeared as an afterthought in the section on the dynamic_sampling() indication.
So when you look at undocumented hints, you have to ask yourself – are they supposed to be official, or will they soon be formal, or are they official if only you can find where they’ve been documented.
DEPRECATED HINTS:
Some of the deprecated hints are as follows,
- AND_EQUAL
- HASH_AJ
- MERGE_AJ
- NL_AJ
- HASH_SJ
- MERGE_SJ
- NL_SJ
- EXPAND_GSET_TO_UNION
- ORDERED_PREDICATES
- STAR hints
These hints have been deprecated since Oracle 10g and should not be used.
Summary
Apart from the mentioned hints, there are many other types of suggestions 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 suggestions using the EXPLAIN PLAN statement. Changing database conditions as well as query performance enhancements in subsequent releases can have a 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 signs may not continue to result in improved performance over the long term. Hence we must be careful while using the hints.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
