Tuning SQL with Hints

Oracle Optimizer still makes use of "hints" to determine the fastest access path to the data. Hints are instructions that can be given to the optimizer to force it to choose a particular execution plan. Hints can be used to override the optimizer's default choices, or to provide information that the optimizer may not have otherwise known. There are a number of different types of hints available in Oracle.
Hints can be a useful tool for tuning the performance of Oracle queries. However, it is important to use hints sparingly. Too many hints can make the optimizer's job more difficult, and can actually lead to worse performance.
Here are some of the guidelines for using hints:

1. Use hints only when you know that the optimizer's default choices are not optimal.
2. Use hints only when you have a good understanding of the impact that they will have on the performance of the query.
3. Test the performance of the query with and without the hint to make sure that the hint is actually improving performance.

Overall, hints can be a useful tool for tuning the performance of Oracle queries. However, it is important to use hints sparingly and to understand the impact that they will have on the performance of the query.

However, here are some index-related hints related to SQL Tuning:

1. INDEX:
   • Explicitly suggests the optimizer to use a specific index or indexes for a table.
   • Syntax: `/*+ INDEX(table_name index_name1 index_name2 ...) */`
   • Example: `SELECT /*+ INDEX(employees emp_name_ix) */ * FROM employees WHERE name = 'John Smith';`
2. NO_INDEX:
   • Instructs the optimizer to avoid using a specific index or indexes for a table.
   • Syntax: `/*+ NO_INDEX(table_name index_name1 index_name2 ...) */`
   • Example: `SELECT /*+ NO_INDEX(employees emp_salary_ix) */ * FROM employees WHERE salary > 100000;`
3. INDEX_JOIN:
   • Hints the optimizer to use an index join for accessing a table.
   • Syntax: `/*+ INDEX_JOIN(table_name index_name) */`
   • Example: `SELECT /*+ INDEX_JOIN(employees emp_dept_ix) */ * FROM employees JOIN departments ON employees.dept_id = departments.id;`
4. INDEX_FFS:
   • Suggests the optimizer to use a fast full index scan.
   • Syntax: `/*+ INDEX_FFS(table_name index_name) */`
   • Example: `SELECT /*+ INDEX_FFS(employees emp_id_ix) */ * FROM employees;`
   
   
5. NO_INDEX_FFS:
   • Advises the optimizer to avoid using a fast full index scan.
   • Syntax: `/*+ NO_INDEX_FFS(table_name index_name) */`
   • Example: `SELECT /*+ NO_INDEX_FFS(employees emp_salary_ix) */ * FROM employees WHERE salary > 50000;`

Use hints with caution as they can override the optimizer's decisions, which might lead to suboptimal performance if not used correctly. Always test the impact of hints before using them in production environments.**

As we discussed in a prior lesson, the Oracle optimizer does not always make the best decision about the fastest access path to the data.
This is not always Oracle’s fault. No SQL optimizer can predict in advance the number of rows that will be returned by queries, and the number of rows that are returned by sub-queries can have a dramatic impact upon query performance.
Hence, Oracle provides a huge amount of hints that can be used to force the Oracle optimizer to change the way the table and indexes are accessed. Hints can be used to invoke parallelism, for the use of a specific index, as well as a host of other functions. To illustrate how it works, consider the query from the prior lesson. We saw that the EXPLAIN PLAN used nested loops to access the data.
View the Oracle SQL Code below.

 
OPERATION         OPTIONS        OBJECT_NAME
---------------- --------------- -----------------
SELECT STATEMENT  Cost = 87
SORT              ORDER BY
NESTED LOOPS
TABLE ACCESS      FULL           MON_TAB_STATS
TABLE ACCESS      BY INDEX ROWID MON_TAB_STATS
INDEX             RANGE SCAN     MON_TAB_STATS_TAB_IDX                                          

Let us assume that we have determined that this query will run faster with the rule-based optimizer.
We can change the SQL statement to add a hint that tells Oracle to use the rule-based optimizer: select /*+ rule */
Once this is added to the end of the SQL select line, we can re-explain the query and see that the access path has changed.
View the Code below.

Here we see a radically different access path to the data.
To verify which access path is fastest, many DBAs use the SET TIMING ON option in SQL*Plus and run the queries with the hints. We will cover hints in greater detail later in this course.
The next lesson concludes this module.