Oracle Hints

Introduction

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:- 

• Bugs
• 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) */

Notes 

• 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 

Illustration 

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 

                                 FROM DEPARTMENT

                                 WHERE LOCATION_ID=1009)

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
Hint	Function	Use
ALL_ROWS	Invokes the CBO	Used for batch processing, data warehousing systems
FIRST_ROWS	Invokes the CBO	Used for On Line Transfer Protocol (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 

CLUSTER	INDEX	INDEX_FFS
FULL	INDEX_ASC	INDEX_JOIN
HASH	INDEX_COMBINE	NO_INDEX
ROWID	INDEX_DESC	AND_EQUAL

Hints for Query Transformations 

FACT	NO_FACT
MERGE	NO_MERGE
NO_EXPAND	NOREWRITE
NO_EXPAND_GSET_TO_UNION	REWRITE
USE_CONCAT	STAR_TRANSFORMATION

Hints for Join Operations 

DRIVING_SITE	MERGE_AJ	USE_HASH
HASH_AJ	MERGE_SJ	USE_MERGE
HASH_SJ	NL_AJ	USE_NL
LEADING	NL_AJ

 Hints for Parallel Execution 

NO PARALLEL

PARALLEL

NOPARALLEL_INDEX

PARALLEL_INDEX

PQ_DISTRIBUTE

Miscellaneous Hints 

ANTIJOIN	DYNAMIC_SAMPLING
APPEND	INLINE
BITMAP	MATERIALIZE
BUFFER	NO_ACCESS
CACHE	NO_BUFFER
CARDINALITY	NO_MONITORING
CPU_COSTING	NO_PUSH_PRED
NO_PUSH_SUBQ	ORDERED_PREDICATES
NO_QKN_BUFF	PUSH_PRED
NO_SEMIJOIN	PUSH_SUBQ
NOAPPEND	NOCACHE
STAR	SEMIJOIN
SWAP_JOIN_INPUTS	SEMIJOIN_DRIVER
USE_ANTI	OR_EXPAND
USE_SEMI	ORDERED
QB_NAME	SELECTIVITY

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.

Undocumented Hints 

BYPASS_RECURSIVE_CHECK	CURSOR_SHARING_EXACT
BYPASS_UJVC	DEREF_NO_REWRITE
CACHE_CB	DML_UPDATE
CACHE_TEMP_TABLE	DOMAIN_INDEX_NO_SORT
CIV_GB	DOMAIN_INDEX_SORT
COLLECTIONS_GET_REFS	DYNAMIC_SAMPLING
CUBE_GB	DYNAMIC_SAMPLING_EST_CDN
SYS_PARALLEL_TXN	SYS_RID_ORDER
REMOTE_MAPPED	RESTORE_AS_INTERVALS
SYS_DL_CURSOR	NO_UNNEST
SQLLDR	USE_TTT_FOR_GSETS
NESTED_TABLE_GET_REFS	NESTED_TABLE_SET_SETID
NESTED_TABLE_SET_REFS	NO_FILTERING
EXPAND_GSET_TO_UNION	PIV_GB
FORCE_SAMPLE_BLOCK	TIV_GB
GBY_CONC_ROLLUP	SAVE_AS_INTERVALS
GLOBAL_TABLE_HINTS	NOCPU_COSTING
HWM_BROKERED	PQ_NOMAP
NO_PRUNE_GSETS	NO_ORDER_ROLLUPS
INDEX_RRS	SCN_ASCENDING
INDEX_SS	OVERFLOW_NOMOVE
INDEX_SS_ASC	PQ_MAP
INDEX_SS_DESC	NO_STATS_GSETS
LIKE_EXPAND	UNNEST
LOCAL_INDEXES	SKIP_EXT_OPTIMIZER
MV_MERGE	PIV_SSF
TIV_SSF	IGNORE_ON_CLAUSE
IGNORE_WHERE_CLAUSE

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.