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

 
 Home
 E-mail Us
 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 hints tips

Oracle Tips by Burleson Consulting
 December 4, 2007

For a full treatment of tuning with Oracle hints see my book "Oracle Tuning: The Definitive Reference", or our training in advanced SQL Tuning.

The use of Oracle hints allows you to change suboptimal SQL execution plans.  Some professionals misunderstand hints, believing that they overcome shortcomings of the optimizer.  This is not always true, as Oracle hints are used to supply additional information to the optimizer, and compensate to cardinality estimates for complex queries.


Oracle hints can be used to tune SQL as "optimizer directives", overrides that change the execution plans of the SQL.  There are times when Oracle hints are legitimate for tuning.

Tuning with hints
 

There are many hints for every possible step within execution plans:
Global hints:   rule, first_rows, first_rows_n all_rows, driving_site

Table join hints:  use_nl, use_hash

Index hints:  Specifies an index name

Table access hints:  parallel, full, cardinality
 
Table join hints:  ordered
 
You can tune Oracle with hints by placing them after the SELECT statements, but Oracle hints can also be used in subqueries:
 
SELECT
a.Author_last_name, a.author_first_name
FROM author a
WHERE author_key in
(select /*+ FULL(a) FULL(b) */ b.author_key
from sales a, book_author b
Where a.book_key=b.book_key);
 

Tips for tuning with Oracle hints

Carefully check the hint syntax. It is always a good idea to use the full-comment syntax for a hint. For example, the /+* hint */ syntax is generally preferred to the ? ?+ hint syntax.

Use the table alias Whenever you have a query that specifies an alias for a table, you cannot use the table name. Instead, you must specify the table alias name. For example, the following query will invoke the index hint because the emp table is aliased with ?e?:

select /*+ index(e,dept_idx) */ * from emp e;

Never reference the schema name in a hint Hints will be ignored when the schema owner is specified in the hint. For example, the following hint will be ignored:

select /*+ index(scott.emp,dept_idx) */ * from emp;
 

Inappropriate Oracle hints

 
First, please read this link to understand why Oracle SQL can ignore hints.
 
Some Oracle hints are contradictory and invalid, and a hint will be ignored if it assumes an access path that is not available. For example,
  • Specifying an index hint on a table that has no indexes
  • Specifying a parallel hint for an index range scan
You need to be especially careful with validation of hints because it is not always obvious that a hint is contradictory with the query. For example, consider the following query in the emp table with no index on the ename column.

select /*+ first_rows */ * from emp order by ename;

The following hint is invalid because first_rows access and parallel access are mutually exclusive. That is because parallel access always assumes a full-table scan and first_rows favors index access.

-- An invalid hint
select /*+ index (emp emp_idx) parallel(emp,8)*/
  emp_name
from
  emp
where
  emp_type = 'SALARIED';


Some Oracle professionals will place hints together to reinforce their wishes. For example, if there is a SMP server with eight or more CPUs, one may want to use Oracle Parallel Query to speed up legitimate full-table scans.

Spelling - If table name or index name is spelled incorrectly, then the hint will not be used. Here we see a query with a misspelled table name:

select /*+ index(erp, dept_idx) */ * from emp;


Table Name - The table name is mandatory in the hint. For example, the following hint will be ignored because the table name is not specified in the query:

select /*+ index(dept_idx) */ * from emp;
 

Other Oracle hints tips:

Here are guidelines for using Oracle hints for tuning:

  • Because hints are placed inside comments, they will be ignored if the hint is incompatible with the existing execution plan or when the hint is formatted improperly.
     
  • When using the RBO, hints can be used to change specific queries to use the CBO. Always remember to analyze all table and indexes that participate in the query.
     
  • When using the CBO, you can start tuning a suspect SQL statement by adding the rule or first_rows hint.
     
  • Hints can be applied to subqueries, but a hint in the outer query will not carry over into the subquery.
     

Tuning with the parallel hint


When using parallel query, one should seldom turn on parallelism at the table level, alter table customer parallel 35, because the setting of parallelism for a table influences the optimizer. This causes the optimizer to see that the 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 parallel(emp,35)*/
   emp_name
from
   emp
order by
   ename;


Lets take a look at one of the most important hints for Oracle 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 the optimizer is given the best order to join the tables.
 

Tuning with the cardinality hint

The use of the cardinality hint compensates for the optimizers inability to estimate the inter-table join result set.  This is NOT a problem with the optimizer, as no amount of metadata will help when joining tables with complex, multi-column where clauses.

The cardinality hint is used in two general cases, complex joins and dynamically created tables like global temporary tables (and possibly using materializations using the WITH clause):

select /*+ cardinality( gtt 500 ) */
stuff
from global_temp_table;

The dynamic sampling hint

The use of the 10g dynamic_sampling hint is very useful for highly volatile tables and global temporary tables.

In 10g, you can use the dynamic_sampling hint to direct Oracle to estimate the cardinality of highly volatile tables.

The dynamic sampling hint is also useful for estimating the size of dynamically created objects such as global temporary tables (GTT?s)

select /*+ dynamic_sampling(customer 4) */ pol_no, sales_id, sum_assured, premium
from customer;

 
 
Here are my related notes on Oracle hints:
 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

 

��  
 
 

 
 
 
 
oracle dba poster
 

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

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.