Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle Hint List

Oracle Tips by Burleson Consulting

Oracle hints are optimizer directives that are used in SQL de-bugging to change the default execution plan for SQL.  Once the SQL has been optimized by adding the hint, the Oracle professional will adjust schema statistics (using dbms_stats) to replicate the execution plan without the hint. 

The exceptions are hints that may be left inside the SQL, hints such as ordered, which tell the optimizer the optimal table join order and the main optimizer hints (first_rows, all_rows).  These hints may be left inside production SQL. 

To learn how to use hints to tune your SQL, see my book "Oracle Tuning: The Definitive Reference". You can buy it at the link above for 30%-off and get immediate access to the code depot.

Here is a complete list of all documented Oracle 10g hints:

all_rows
first_rows
first_rows_1
first_rows_100
choose
rule
full
rowid
cluster
hash
hash_aj
index
no_index
index_asc
index_combine
index_join
index_desc
index_ffs
no_index_ffs
index_ss
index_ss_asc
index_ss_desc
no_index_ss
no_query_transformation
use_concat
no_expand
rewrite
norewrite
no_rewrite
merge
no_merge
fact
no_fact
star_transformation
no_star_transformation
unnest
no_unnest
leading
ordered
use_nl
no_use_nl
use_nl_with_index
use_merge
no_use_merge
use_hash
no_use_hash
parallel
noparallel / no_parallel
pq_distribute
no_parallel_index
append
noappend
cache
nocache
push_pred
no_push_pred
push_subq
no_push_subq
qb_name
cursor_sharing_exact
driving_site
dynamic_sampling
spread_min_analysis
merge_aj
and_equal
star
bitmap
hash_sj
nl_sj
nl_aj
ordered_predicates
expand_gset_to_union

Oracle also has undocumented hints, hints which can be very useful but are not in the Oracle documentation.  You should always carefully research any undocumented hints and test it carefully before using any undocumented hints in production.  You can also log a service request with MOSC when considering using an undocumented hint.  Here is a list of Oracle 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
 

 

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.