Oracle Hints :: Oracle World

Post Views: 2,139

When the execution plan is not correct (for whatever reason) you have a chance to force it to use your way of execution. How? With the help of hints 🙂 Hints are a specific set of commands used to change the execution plan. I will not discuss all hints provided by Oracle simply because I don’t want to copy the Oracle’s documentation pages. I will discuss only the most commonly used hints.

Important: Hints should be your last solution for an optimization. Always exhaust all other options before you get to hints !!!

We have 4 main types of hints

Single-table – operates with one table or view only
Multi-table – can operate with more than one table or view
Query block – operates on a particular query block
Statement – applies to the SQL statement as a whole

Syntax

There are two ways how you can apply hints in your queries.

/* Single-line comment */
[SELECT,INSERT,UPDATE,MERGE,DELETE] --+ hint

/* Multi-line comment */ 
[SELECT,INSERT,UPDATE,MERGE,DELETE] /*+ hint */

As you can deduce from above, you use a comment code immediately followed by a plus (+) sign. The single-line comment requires all hints to be on one line only. Yes, you can combine multiple hints 🙂

The most commonly used HINTS

USE_HASH

Instruct the Optimizer to use HASH JOIN (instead of Nested Loops usually).

/**
 *
 *  usage: use_hash(table_A table_B)
 *           or
 *         use_hash(table_a_alias table_b_alias) -- when table alias is used
 *
 **/
SELECT /*+ use_hash(cnt inst) */
 *
FROM contract cnt
LEFT JOIN installment inst
ON cnt.id_contract = inst.id_contract
WHERE 1=1

MATERIALIZE

Materialize hint is an undocumented hint. It is used in a combination with the WITH clause and assures that the “virtual table” will be stored as a temporary table and therefore queried once only (from the performance perspective it prevents DRY principle violation). This way the table will be read-only once! Without this hint, the table would be read three times.

WITH w_inst_3 AS (
                   SELECT --+ materialize 
                     id_contract
                   , id_num_inst
                   , amt_inst
                   FROM installment
                   WHERE id_num_inst IN (1,2,3)
                 )

SELECT c.id_contract
, i1.amt_total as i1_amt_total
, i2.amt_total as i2_amt_total
, i3.amt_total as i3_amt_total
FROM contract c
--
LEFT JOIN w_inst_3 i1
ON i1.id_contract = c.id_contract
AND i1.id_num_inst = 1
--
LEFT JOIN w_inst_3 i2
ON i2.id_contract = c.id_contract
AND i2.id_num_inst = 2
--
LEFT JOIN w_inst_3 i3
ON i3.id_contract = c.id_contract
AND i3.id_num_inst = 3
--
WHERE 1=1

NO_INDEX

Forces the Optimizer to bypass the index usage.

/**
 *
 * usage: no_index(table_name index_name)
 *         or
 *        no_index(table_alias index_name) -- when table alias is used
 **/
SELECT --+ no_index(emp idx_id_emp)
*
FROM employee emp
WHERE id_emp = 1002;

FULL

Forces the optimizer to scan all records in a table (perform the Full Table Scan; usually to avoid index scan)

/**
 *
 * usage: full(table_name)
 *         or
 *        full(table_alias) -- when table alias is used
 **/
SELECT --+ full(emp)
*
FROM employee emp
WHERE id_emp = 1002;

LEADING

With this hint, you can decide which table will be the driving table out of the two joined tables. This is very important when you incorrectly write a query, where the small table is a driving table and the large one is a joined table or vice versa. The order matters a lot and might dramatically change the plan.

/**
 *
 * usage: leading(employee)
 *         or
 *        leading(emp) -- when table alias is used
 **/
SELECT --+ leading(dpt)
*
FROM employee emp, department dpt
WHERE emp.id_department = dpt.id_department
AND dpt.id_department = 20;

PARALLEL

This is a very powerful hint and must be used with extreme care. This allows a query to be executed in parallel. That means the query will create X (depends on the parallel degree you set) threads to obtain data. If you choose a too high degree of parallelism, it might be a killer for your database.

I suggest using parallel only in critical situations where there is no other way for you to get your data. Remember, if your query takes too long you are probably doing something wrong. It is either the incorrect design or your improper query logic. Either way, you should fix this first before you proceed with this hint!

If you really need to use parallel, please do not set the degree higher than 5 or 10 (depends on your server configuration and resources). Usually, go with 2 or 3 in the beginning and if needed increase.

SELECT --+ parallel(4)
*
FROM employee;

APPEND

APPEND hint (a.k.a. DIRECT LOAD) is a very handy script and might be very useful 🙂 This is most commonly used with TRUNCATE or GTTs. When you need to reload a table TRUNCATE & INSERT /*+ APPEND */ is your best friend 🙂 Even with Global Temporary Tables it works flawlessly. See the results and examples of use below. With APPEND hint the Optimizer skips buffer cache (see the Instance and storage article for more details) and writes data directly into Oracle datafiles (see the Database files article for more details). Contrary to the conventional load (the one without APPEND), the direct load does not try to reuse the free space within a table. This is why DELETE and INSERT –+ APPEND is the worst idea ever as I mentioned in the article about High Water Mark.

Comparison of loading 100k rows with different approaches.

Table Type	Operation	Buffer Gets	Disk Reads	Elapsed Time	CPU Time	User IO Time
GTT	INSERT	889	15	58917	52048	6855
GTT	INSERT --+APPEND	80	0	63324	61668	728
HEAP	INSERT	2442	145	123971	71459	54255
HEAP	INSERT --+APPEND	158	79	73801	68526	4943

_{* To understand what are those fields about, please visit the article Check the real cost of a query.}

As you can see, INSERT and +APPEND into GTT wins without doubts 🙂

Hints and Views

Hints work perfectly with views as well. You just cascade the hierarchy in your hint.

-- view definition
CREATE OR REPLACE VIEW v_order_list AS
SELECT ord.id_order
, ord.amt_total
, emp_store.name_store
, emp_store.name_full as employee_name
FROM orders ord
JOIN (
      SELECT e.id_employee
      , e.name_full
      , s.name_store
      FROM employee e
      JOIN emp_to_store e2s
      ON e2s.id_empoyee = e.id_employee
      AND sysdate BETWEEN e2s.dtime_from AND e2s.dtime_to
      JOIN store s
      ON s.id_store = e2s.id_store
     ) emp_store
ON ord.id_employee = emp_store.id_employee
WHERE 1=1
AND ord.dtime_order BETWEEN trunc(sysdate)-3 AND sysdate
AND ord.id_order NOT IN (SELECT id_order FROM test_order)
;


-- hint usage
SELECT /*+ use_hash(v.emp_store.e v.emp_store.e2s v.emp_store.s) full(ord) */  
* 
FROM v_order_list v;