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.
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
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.
« Previous
Next Topic »
(Oracle Tuning- Tuning SGA)
|