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 SQL Hints Tuning

Oracle Database Tips by Burleson Consulting

Using Hints to tune SQL
Mike Ault

There are many Oracle hints available to the developer for use in tuning SQL statements that are embedded in PL/SQL.

You should first get the explain plan of your SQL and determine what changes can be done to make the code operate without using hints if possible. However, Oracle hints such as ORDERED, LEADING, INDEX, FULL, and the various AJ and SJ Oracle hints can tame a wild optimizer and give you optimal performance.

Oracle hints are enclosed within comments to the SQL commands DELETE, SELECT or UPDATE or are designated by two dashes and a plus sign. To show the format the SELECT statement only will be used, but the format is identical for all three commands.

 

SELECT      /*+ hint --or-- text */    statement body  
            -- or --      
SELECT          --+ hint --or-- text      statement body

 

Where:

      • /*, */  -  These are the comment delimiters for multi-line comments
      • --  -  This is the comment delimiter for a single line comment (not usually used for hints)
      • +  -  This tells Oracle a hint follows, it must come immediately after the /*
      • hint  -  This is one of the allowed hints
      • text  -  This is the comment text

 

Oracle Hint

Meaning

+

Must be immediately after comment indicator, tells Oracle this is a list of hints.

ALL_ROWS

Use the cost based approach for best throughput.

CHOOSE

Default, if statistics are available will use cost, if not, rule.

FIRST_ROWS

Use the cost based approach for best response time.

RULE

Use rules based approach; this cancels any other hints specified for this statement.

Access Method Oracle Hints:

 

CLUSTER(table)

This tells Oracle to do a cluster scan to access the table.

FULL(table)

This tells the optimizer to do a full scan of the specified table.

HASH(table)

Tells Oracle to explicitly choose the hash access method for the table.

HASH_AJ(table)

Transforms a NOT IN subquery to a hash anti-join.

ROWID(table)

Forces a rowid scan of the specified table.

INDEX(table [index])

Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.

INDEX_ASC (table [index])

Same as INDEX only performs an ascending search of the index chosen, this is functionally identical to the INDEX statement.

INDEX_DESC(table [index])

Same as INDEX except performs a descending search. If more than one table is accessed, this is ignored.

INDEX_COMBINE(table index)

Combines the bitmapped indexes on the table if the cost shows that to do so would give better performance.

INDEX_FFS(table index)

Perform a fast full index scan rather than a table scan.

MERGE_AJ (table)

Transforms a NOT IN subquery into a merge anti-join.

AND_EQUAL(table index index [index index index])

This hint causes a merge on several single column indexes. Two must be specified, five can be.

NL_AJ

Transforms a NOT IN subquery into a NL anti-join (nested loop).

HASH_SJ(t1, t2)

Inserted into the EXISTS subquery; This converts the subquery into a special type of hash join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

MERGE_SJ (t1, t2)

Inserted into the EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

NL_SJ

Inserted into the EXISTS subquery; This converts the subquery into a special type of nested loop join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

Oracle Hints for join orders and transformations:

 

ORDERED

This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join.

STAR

Forces the largest table to be joined last using a nested loops join on the index.

STAR_TRANSFORMATION

Makes the optimizer use the best plan in which a start transformation is used.

FACT(table)

When performing a star transformation use the specified table as a fact table.

NO_FACT(table)

When performing a star transformation do not use the specified table as a fact table.

PUSH_SUBQ

This causes nonmerged subqueries to be evaluated at the earliest possible point in the execution plan.

REWRITE(mview)

If possible forces the query to use the specified materialized view, if no materialized view is specified, the system chooses what it calculates is the appropriate view.

NOREWRITE

Turns off query rewrite for the statement, use it for when data returned must be concurrent and can't come from a materialized view.

USE_CONCAT

Forces combined OR conditions and IN processing in the WHERE clause to be transformed into a compound query using the UNION ALL set operator.

NO_MERGE (table)

This causes Oracle to join each specified table with another row source without a sort-merge join.

NO_EXPAND

 Prevents OR and IN processing expansion.

Oracle Hints for Join Operations:

 

USE_HASH (table)
 

This causes Oracle to join each specified table with another row source with a hash join.

USE_NL(table)

This operation forces a nested loop using the specified table as the controlling table.

USE_MERGE(table,[table, - ])

This operation forces a sort-merge-join operation of the specified tables.

DRIVING_SITE

The hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.

LEADING(table)

The hint causes Oracle to use the specified table as the first table in the join order.

Oracle Hints for Parallel Operations:

 

[NO]APPEND

This specifies that data is to be or not to be appended to the end of a file rather than into existing free space. Use only with INSERT commands.

NOPARALLEL (table

This specifies the operation is not to be done in parallel.

PARALLEL(table, instances)

This specifies the operation is to be done in parallel.

PARALLEL_INDEX

Allows parallelization of a fast full index scan on any index.

Other Oracle Hints:

 

CACHE

Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list when the table is full table scanned.

NOCACHE

Specifies that the blocks retrieved for the table in the hint are placed at the least recently used end of the LRU list when the table is full table scanned.

[NO]APPEND

For insert operations will append (or not append) data at the HWM of table.

UNNEST

Turns on the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to FALSE.

NO_UNNEST

Turns off the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to TRUE.

PUSH_PRED

 Pushes the join predicate into the view.


As you can see, a dilemma with a stubborn index can be easily solved using FULL or NO_INDEX Oracle hints. You must know the application to be tuned. The DBA can provide guidance to developers but in all but the smallest development projects, it will be nearly impossible for a DBA to know everything about each application. It is clear that responsibility for application tuning rests solely on the developer's shoulders with help and guidance from the DBA. 

Using Global Hints

While Oracle hints normally refer to table in the query it is possible to specify a hint for a table within a view through the use of what are known as Oracle GLOBAL HINTS. This is done using the Oracle global hint syntax. Any table hint can be transformed into an Oracle global hint.

The syntax is:

/*+ hint(view_name.table_in_view) */

For example:

 /*+ full(sales_totals_vw.s_customer)*/

If the view is an inline view, place an alias on it and then use the alias to reference the inline view in the Oracle global hint.

See also:

         Oracle SQL undocumented tuning hints

         Guard against performance issues when using Oracle9i hints and views

         Oracle leading vs. ordered hints

         Oracle10g SQL optimizer SQL execution spread_min_analysis

         Oracle hidden utilities undocumented parameters

         Oracle Performance Tuning Training

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

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.