Home > Database, Oracle, Query Tuning, Using Hint > Documented & Undocumented Hints

Documented & Undocumented Hints

As the optimizer becomes more sophisticated with each release, Oracle provides an increasing number of methods for changing the execution plans for your SQL. The most common use for Oracle hints is as a debugging tool. You can use the hints to determine the optimal execution plan, and then work backward, adjusting the statistics to make the vanilla SQL simulate the hinted query.

Using Oracle hints can be very complicated and Oracle developers only use hints as a last resort, preferring to alter the statistics to change the execution plan. Oracle contains more than 124 hints, and many of them are not found in the Oracle documentation. (See Listing 2)

Listing 2: Documented Oracle Hints:

ALL_ROWS
AND_EQUAL
ANTIJOIN
APPEND
BITMAP
BUFFER
BYPASS_RECURSIVE_CHECK
BYPASS_UJVC
CACHE
CACHE_CB
CACHE_TEMP_TABLE
CARDINALITY
CHOOSE
CIV_GB
COLLECTIONS_GET_REFS
CPU_COSTING
CUBE_GB
CURSOR_SHARING_EXACT
DEREF_NO_REWRITE
DML_UPDATE
DOMAIN_INDEX_NO_SORT
DOMAIN_INDEX_SORT
DRIVING_SITE
DYNAMIC_SAMPLING
DYNAMIC_SAMPLING_EST_CDN
EXPAND_GSET_TO_UNION
FACT
FIRST_ROWS
FORCE_SAMPLE_BLOCK
FULL
GBY_CONC_ROLLUP
GLOBAL_TABLE_HINTS
HASH
HASH_AJ
HASH_SJ
HWM_BROKERED
IGNORE_ON_CLAUSE
IGNORE_WHERE_CLAUSE
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
INDEX_JOIN
INDEX_RRS
INDEX_SS
 
INDEX_SS_ASC
INDEX_SS_DESC
INLINE
LEADING
LIKE_EXPAND
LOCAL_INDEXESMATERIALIZE
MERGE
MERGE_AJ
MERGE_SJ
MV_MERGE
NESTED_TABLE_GET_REFS
NESTED_TABLE_SET_REFS
NESTED_TABLE_SET_SETID
NL_AJ
NL_SJ
NO_ACCESS
NO_BUFFER
NO_EXPAND
NO_EXPAND_GSET_TO_UNION
NO_FACT
NO_FILTERING
NO_INDEX
NO_MERGE
NO_MONITORING
NO_ORDER_ROLLUPS
NO_PRUNE_GSETS
NO_PUSH_PRED
NO_PUSH_SUBQ
NO_QKN_BUFF
NO_SEMIJOIN
NO_STATS_GSETS
NO_UNNEST
NOAPPEND
NOCACHE
NOCPU_COSTING
NOPARALLEL
NOPARALLEL_INDEX
NOREWRITE
OR_EXPAND
ORDERED
ORDERED_PREDICATES
OVERFLOW_NOMOVE
PARALLEL
PARALLEL_INDEX
PIV_GB
PIV_SSF
PQ_DISTRIBUTE
PQ_MAP
PQ_NOMAP
PUSH_PRED
PUSH_SUBQ
REMOTE_MAPPED
RESTORE_AS_INTERVALS
REWRITE
RULE
SAVE_AS_INTERVALS
SCN_ASCENDING
SELECTIVITY
SEMIJOIN
SEMIJOIN_DRIVER
SKIP_EXT_OPTIMIZER
SQLLDR
STAR
STAR_TRANSFORMATION
SWAP_JOIN_INPUTS
SYS_DL_CURSOR
SYS_PARALLEL_TXN
SYS_RID_ORDER
TIV_GB
TIV_SSF
UNNEST
USE_ANTI
USE_CONCAT
USE_HASH
USE_MERGE
USE_NL
USE_SEMI
USE_TTT_FOR_GSETS

Undocumented Hints:

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

For example, the following hint is invalid because first_rows_n access and parallel access are mutually exclusive. That’s because parallel always assumes a full-table scan andfirst_rows_n favors index access.

-- An invalid hint
select /*+ first_rows_100 parallel(emp,8)*/ 
   emp_name 
from 
  emp 
order by 
   ename;

Some Oracle professionals will place hints together to reinforce their wishes. For example, if we have an SMP server with eight or more CPUs, we may want to use Oracle Parallel Query to speed-up legitimate full-table scans. When using parallel query, we seldom want to turn-on parallelism at the table level (alter table customer parallel 35;) because the setting of parallelism for a table influences the optimizer, causing the optimizer to see full-table scan is inexpensive. Hence, most Oracle professionals specify parallel query on a   query-by-query basis, combining the full hint with the parallel hint to ensure a fast parallel full-table scan:

-- A valid hint
select /*+ full(emp) parallel(emp,35)*/ 
   emp_name 
from 
   emp 
order by 
   ename;

Now that we have the general concept of hints, let’s take a look at one of the most important hints for optimizer tuning.

The ordered hint determines the driving table for the query execution and also specifies the order that tables are joined together. The ordered hint requests that the tables should be joined in the order that they are specified in the from clause, with the first table in the from clause specifying the driving table. Using the ordered hint can save a huge amount of parse time and speed SQL execution because you are telling the optimizer the best order to join the tables.

For example, the following query uses the ordered hint to join the tables in their specified order in the from clause. In this example, we further refine the execution plan by specifying that the emp to dept join use a hash join and the sal to bonus join use a nested loop join:

select 
/*+ ordered use_hash (emp, dept) use_nl (sal, bon) */
from
   emp,
   dept,
   sal,
   bon
where . . .
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: