>>
You're reading...
Oracle

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

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.

About these ads

About Saurabh K. Gupta

Product Manager at Oracle, Author and blogger

Discussion

4 thoughts on “Oracle Hints

  1. Hi,

    Does OLTP means On-line transfer Protocol or it means On-Line transaction Processing? YOu have mentioned OLTP as On-Line Transfer Protocol in your post section “Hints for Optimization Approaches and Goals”

    Posted by Prabhu | September 9, 2011, 11:24 am
  2. Thanks for the share!
    Nancy.R

    Posted by Nancy | October 4, 2011, 8:40 am
  3. Very rapidly this web site will be famous among all
    blogging visitors, due to it’s pleasant articles or reviews

    Posted by malwarewiz.wordpress.com | October 4, 2014, 1:27 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Saurabh K. Gupta

Saurabh K. Gupta

Product Manager at Oracle, Author and blogger

Personal Links

View Full Profile →

Twitter Profile

My Book

Oracle Advanced PL/SQL Developer Professional Guide

Disclaimer

SBHOracle is an independent blog and all the posts are based on my self experience and hands on with the technologies. It shares no relations with any of my current projects or from those in the past.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 371 other followers

SbhOracle Blog stats

  • 43,676 hits
Aggregated by OraNA
Follow

Get every new post delivered to your Inbox.

Join 371 other followers

%d bloggers like this: