Performance is one of the primary objectives of a database. Fine tuned data retrieval and database operations can save lot of time for users and avoid troubleshoot nightmares of database developers.
The root cause of database performance degradation lies with the working of optimizer. Optimizer speed lowers and hence the query speed. There are multiple approaches and many ways to achieve database process performance. Thorough analysis of the data, explain plan is required to adopt the most feasible method to tune a SQL query.
Out of these many approaches, Oracle SQL hints are one of the tricks to tune a query. SQL hints are optimizer directives which were introduced in Oracle 8.1.0 release. They instruct optimizer to follow an alternative path of query execution. At times, a developer can analyze a complex data better than an Optimizer. Therefore, he may choose a better execution plan than the optimizer by enforcing hints in the queries. Like other approaches, it is not a full proof method of tuning but it is suggestible in case of complex queries.
SQL Hints Usage Below are some situations when Hints are required to be used when the Optimizer fails:-
- Use of Bind Variables which disable Histograms
- Poorly written SQLs
- High frequency of Change of Data
- Faulty (incomplete) Configuration Settings
- Dynamic vs. Static SQL
Other circumstances where the use of Hints may be required:-
- Hints can be used to join external tables.
- Hints can be used to force the optimizer to choose different join paths which will fetch different results in the query execution.
- Hints are used to tell the optimizer which data access method to use (as for example in a flashback query). The method may be a full table scan or a different index.
Note: – Sometimes, the optimizer can ‘lock’ the statistics when they look ideal. In such a case ‘Hints’ lose their importance.
Syntax:- Hints appear as comments to the SELECT and DML statements. Without these keywords, they hold no meeting and would only appear as a comment. Hints can be used in any of the following ways:
- /*+ hint */
- /*+ hint (argument) */
- /*+ hint (argument-1 argument-2) */
- Hints can be used in SELECT, INSERT, UPDATE or DELETE statements
- It can be a part of subquery too
- It can appear in any of the participating queries of a compound query using SET operator
- There should be no schema names in hints
- All hints except the /*+ rule */ cause the Cost Based Optimizer (CBO) to be used
- Hints operate on a simple view but not on a complex view
1. The query below uses ALL_ROWS hint to select employee details from EMPLOYEE table
SELECT /*+ ALL_ROWS */ EMPNO, ENAME, SALARY, DETPNO
FROM EMPLOYEE WHERE EMPNO=100
2. The SQL query below uses RULE hint to change the optimizer mode from COST based to RULE based
SELECT /*+ RULE */ EMPNO, ENAME, SALARY, DETPNO
FROM EMPLOYEE WHERE EMPNO=100
3. The FULL hint used in the below SQL query enforces optimizer to do FULL TABLE SCAN
SELECT /*+ FULL(E) */ EMPNO, ENAME, SALARY
FROM EMPLOYEE E WHERE DEPTNO IN (SELECT DEPTNO
Types of Hints
Based on their usage area, SQL hints can be classified as below.
- Hints for Optimization Approaches and Goals
- Hints for Access Paths, Hints for Query Transformations
- Hints for Join Orders
- Hints for Join Operations
- Hints for Parallel Execution
- Additional Hints
|Hints for Optimization Approaches and Goals|
|ALL_ROWS||Invokes the CBO||Used for batch processing, data warehousing systems|
|FIRST_ROWS||Invokes the CBO||Used for On Line Transfer Processing (OLTP) systems|
|CHOOSE||Invokes the CBO||It lets the server choose between ALL_ROWS and FIRST_ROWS, based on the statistics gathered by the CBO|
Hints for Access Paths
Hints for Query Transformations
Hints for Join Operations
Hints for Parallel Execution
Hint introduced in Oracle 11g
RESULT_CACHE – The hint was introduced to cache the result of the SELECT query for the same set of inputs and values. It results in better performance if the same query has to be executed for multiple numbers of times.
Obsolete Hints RULE – Its usage has been deprecated by Oracle after 9i release. Earlier, it used to work similar to the current COST hint i.e. to toggle over the optimizers.
- Understanding Recursive queries (everysolution.wordpress.com)
- dbms_xplan.display_sql_plan_baseline can also lie ! (coskan.wordpress.com)