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 Optimizer hint tips

Oracle Tips by Burleson Consulting

February 21, 2012


With each new release of Oracle, there are more tuning controls and opportunities for improving the execution speed of SQL. In an article about speeding up SQL execution in Oracle, I discussed some common SQL tuning hints and, in another article, I warned about performance issues when using SQL hints inside views.

 


   - Download the Oracle hint quick reference

   - Also note these important notes on Oracle hint tuning.

Oracle tuning with hints has always been complex, and the Oracle Technical Network has a great overview into the processes of using hints to tune Oracle SQL. With the introduction of 10g database, there are many new optimizer hints available to control the optimizer behavior. Let?s take a quick tour of these powerful new hints:

spread_min_analysis:  With this hint, you can omit some of the compile time optimizations of the rules on spreadsheets, such as detailed dependency graph analysis. Other optimizations, such as creating filters to selectively populate spreadsheet access structures and limited rule pruning, are still used.

This hint helps to reduce compilation time because spreadsheet analysis may be lengthy if the number of rules is significantly large, such as more than several hundred.

For example:

SELECT /*+ SPREAD_MIN_ANALYSIS */ ...

spread_no_analysis:  With this hint, it's possible to have no spreadsheet analysis. Rule-pruning and filter generation are also omitted when this hint is used. If there is spreadsheet analysis, the compilation time is minimized.

For example:

SELECT /*+ SPREAD_NO_ANALYSIS */ ...

use_nl_with_index:  This hint causes the CBO to join the specified table to another row source with a nested loops join. It uses the specified table as the inner table, but only under this condition: If no index is specified, the CBO must be able to use some index with at least one join predicate as the index key. If an index is specified, the CBO must be able to use that index with at least one join predicate as the index key.

For example:

SELECT /*+ USE_NL_WITH_INDEX (polrec polrind) */ ...
 

CARDINALITY:  This hint specifies the estimated cardinality returned by a query or portions of the query. Note that if no table is specified, the cardinality is the total number of rows returned by the entire query.

For example:

SELECT /*+ CARDINALITY ( [tablespec] card ) */

SELECTIVITY:  This hint specifies the estimated selectivity of a query or portions of the query. If only one table is specified, the selectivity is the fraction of rows of that table that satisfies all single-table predicates on the specified table. If a list of tables is specified, the selectivity is the fraction of rows of the result of joining those tables in any order that satisfies all applicable predicates.

For example:

SELECT /*+ SELECTIVITY ( [tablespec] sel ) */

However, note that if both CARDINALITY and SELECTIVITY hints are specified on the same set of tables, both hints will be ignored.

no_use_nl:  The no_use_nl hint causes the CBO to exclude nested loops to join each specified table to another row source, using the specified table as the inner table. With this hint, only the hash join and the sort-merge joins will be considered for the specified tables.

For example:

SELECT /*+ NO_USE_NL ( employees ) */ ...

no_use_merge:  This hint causes the CBO to exclude sort-merge to join each specified table to another row source, using the specified table as the inner table.

For example:

SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...

no_use_hash:  This hint causes the CBO to exclude hash joins to join each specified table to another row source, using the specified table as the inner table.

For example:

SELECT /*+ NO_USE_HASH ( employees dept ) */ ...

no_index_ffs:  This hint causes the CBO to exclude a fast full-index scan of the specified indexes on the specified table.

Syntax: /*+ NO_INDEX_FFS ( tablespec indexspec ) */

no_index_ss:  This hint causes the CBO to exclude a skip scan of the specified indexes on the specified table.

Syntax: /*+ NO_INDEX_SS (tablespec indexspec ) */

no_star_transformation:  This hint causes the CBO to omit star query transformation.

Syntax: /*+ NO_STAR_TRANSFORMATION */

index_ss:  This hint explicitly chooses an index skip scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition.

Syntax: /*+ INDEX_SS (tablespec indexspec) */

index_ss_asc:  This hint explicitly chooses an index skip scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition.

Syntax: /*+ INDEX_SS_ASC (tablespec indexspec) */

index_ss_desc:  This hint explicitly chooses an index skip scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.

Syntax: /*+ INDEX_SS_DESC (tablespec indexspec) */

cpu_costing:  This hint turns CPU costing on for the SQL statement. This is the default cost model for the optimizer. The optimizer estimates the number and type of IO operations and the number of CPU cycles the database will perform during execution of the given query. It uses system statistics to convert the number of CPU cycles and number of IO(s) to the estimated query execution time. The CPU_COST column of the PLAN_TABLE stores the CPU cost.

Syntax: /*+ CPU_COSTING (tablespec indexspec) */

no_cpu_costing:  This hint turns off CPU costing for the SQL statement. CBO then uses the IO cost model, which measures everything in single-block reads and ignores CPU cost.

Syntax: /*+ NO_CPU_COSTING */

As the Oracle optimizer becomes more sophisticated, Oracle professionals must constantly add to their arsenal of tools for tuning SQL statements. While it is far beyond the scope of this article to discuss all of the intricacies of the new Oracle10g SQL hints, you can get additional information about Oracle10g in Mike Ault?s latest book Oracle Database 10g New Features.
 
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.
 
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.


 

 

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.


 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster