Oracle Tuning - Hints

Oracle database selects a best execution plan for each SQL Statement because of Optimizer. If you don’t know What is the Optimizer and how Oracle Optimizer works, read the following blog before this.
Sometimes These execution plans are actually not the best and there is a better plan than Optimizer’s. But Optimizer doesn’t select that plan and you should instruct it forcibly.
Hints are used to alter execution plans and force the optimizer to use the optimal execution plan. Oracle Hints influence the Optimizer decisions and select a certain execution plan based on the specific criteria.

How to Use Hints in Oracle :
For example: Your query is not using Index, but it should use it. You can use the hint in this case to force the Optimizer to use this index.
The usage of Hints are as follows.
ora-tuning
You can use the Index hint as follows. 

    SQL> select deptno from emp where empno=7698;                           

    DEPTNO
    ----------
    30
You can add /*+ index(c COMP_NDX) */ clause the SQL Statements as follows to force using COMP_NDX Index. 

    SQL> SELECT /*+index(emp pk_emp) */ deptno from emp where empno=7698;

    DEPTNO
    ----------
    30
You can use the Parallel hint to force parallel running the SQL Statement 

    1  SELECT /*+ PARALLEL(emp 4) */ emp.ename
    2  FROM   emp
    3* WHERE  deptno=20
    SQL> /

    ENAME
    ----------
    SMITH
    JONES
    SCOTT
    ADAMS 
    FORD
You need to place hints immediately after the first SQL keyword of a statement block (MERGE, SELECT, INSERT, DELETE, or UPDATE)

Each SQL Statement can have only one hint comment, but it can contain multiple hints as follows. 

    SELECT /*+ LEADING(emp2 emp1) USE_NL(emp1) INDEX(emp1 emp_emp_id_pk) USE_MERGE(jh) FULL(jh) */ emp1.first_name, 
            emp1.last_name, jh.job_id, sum(emp2.salary) total_sal
    FROM employees emp1, employees emp2, job_history jh
    WHERE emp1.employee_id = emp2.manager_id
    AND emp1.employee_id = jh.employee_id
    GROUP BY emp1.first_name, emp1.last_name, jh.job_id
    ORDER BY emp1.first_name;
Hint Types in Oracle :
There are 4 types of hints in the Oracle database as follows.
  • Single-table hints ( one table or view ) such as INDEX and USE_NL hints
  • Multitable hints ( Lots of tables or views ) such as LEADING hint.
  • Query block hints ( a single query block ) such as STAR_TRANSFORMATION and UNNEST hints
  • Statement hints ( entire SQL statement ) such as ALL_ROWS hint.


    • Hints Categories are as follows :
  • Optimizer Approaches ( ALL_ROWS and FIRST_ROWS )
  • Parallel execution
  • Join orders
  • Join operation
  • Access paths ( Index Access, Full access and etc.. )
  • Query transformations


    • Oracle Hints and Definitions :
    Most important and popular Oracle hints and their definitions are as follows.
  • INDEX : use the specified index for the related table. If your query is not using the Index, you can use this hint to force using It.
  • FULL : It is used for a full table scan
  • PARALLEL : Use parallelism for the related table. You can use this hint especially for the large tables and high load SQL Statements.
  • USE_NL : Use the Nested loop join for the specified table, If a large table is joined with a small table.
  • USE_HASH : Use the Hash join for the specified table, If a large table is joined with a large table.
  • ALL_ROWS : Selects All Rows for the best throughput.
  • FIRST_ROWS(n) : It is used to optimize an individual SQL statement for fast response.
  • CLUSTER : It is used for using a cluster scan
  • HASH : It is used for using a hash scan
  • ROWID : It is used for accessing via ROWID
  • INDEX_ASC : It is used for Scanning an index in ascending order

    • ora-tuning

  • INDEX_COMBINE : It is used for choosing a bitmap access path.
  • INDEX_JOIN : It is used for the optimizer to use an index join as an access path.
  • INDEX_DESC : It is used for an index scan in descending order.
  • INDEX_FFS : It is used for a fast-full index scan.
  • INDEX_SS : It is used for an index skip scan.
  • NO_INDEX : It is used for not allowing indexes usage.
  • APPEND : It is used for direct-path INSERT
  • NOAPPEND : It is used for regular INSERT
  • CURSOR_SHARING_EXACT : It is used to Prevent replacing literals with bind variables
  • CACHE : It is used for Overrides the default caching specification of the table
  • PUSH_PRED : It is used for Pushes join predicate into view
  • PUSH_SUBQ : It is used for Evaluates nonmerged subqueries first
  • DYNAMIC_SAMPLING : It is used for dynamic sampling Control.


    • (Oracle Tuning- Tuning SGA)