Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Initialization Parameters that Affect CBO Index Behavior
Oracle Tips by Burleson
 

Several initialization parameters control the way that the cost-based optimizer treats indexes.

The fast_full_scan_enabled Parameter

The fast_full_scan_enabled parameter is obsolete in Oracle8i, where it is the default behavior, but in releases prior to Oracle8i, it controls whether the optimizer will consider an index fast full scan when deriving the execution plan for your query. It is important to note that the fast_full_scan_enabled parameter does not disable the index_ffs hint but instead controls whether CBO will consider index fast full scans in the absence of any cost-based hints.

If you want to force a range scan rather than a fast full scan at the SQL statement level, you can use the index_asc and index_desc hints.

The optimizer_index_cost_adj Parameter

The optimizer_index_cost_adj parameter is an initialization parameter that can be very useful for SQL tuning. It is a numeric parameter with values from zero to 1,000 and a default value of 1,000. It can also be enabled at the session level by using the alter session set optimizer_index_caching = nn syntax. This parameter lets you tune the optimizer behavior for access path selection to be more or less index friendly, and it is very useful when you feel that the default behavior for the CBO favors full-table scans over index scans.

The default value is 1,000, and any value less than 1,000 makes the CBO view indexes less expensive. If you do not like the propensity of the CBO first_rows mode to favor full-table scans, you can lower the value of optimizer_index_cost_adj to 10, thereby telling the CBO to always favor index scans over full-table scans.

TIP: If you are having slow performance because the CBO first_rows mode is favoring too many full-table scans, you can reset the optimizer_index_cost_adj parameter to immediately tune all of the SQL in your database to favor index scans over full-table scans. This is a “silver bullet” that can improve the performance of an entire database in cases where the database is OTLP and you have verified that the full-table scan costing is too low.

Even in Oracle 8.1.7, the CBO sometimes falsely determines that the cost of full-table scan is less than the cost of an index access. The optimizer_index_cost_adj parameter is a great approach to whole-system SQL tuning, but you will need to evaluate the overall effect by slowly resetting the value down from 1,000 and observing the percentage of full-tale scans. You can also slowly bump down the value of optimizer_index_cost_adj when you bounce the database and then either use the access.sql scripts (see Chapter 9) or reexamine SQL from the STATSPACK stats$sql_summary table to see the net effect of index scans on the whole database.

Conclusion

This chapter has been concerned with the default behavior of the cost-based optimizer and has explored how the CBO handles complex table joins, subqueries, and complex Boolean expressions. We also investigated some important initialization parameters and showed how they affect the costing estimates for cost-based optimization.

The major points in this chapter include these:

  • When using the CBO, you must make a philosophical choice between dynamic execution plans and static execution plans. This decision affects your use of stored outlines and how often you reanalyze CBO statistics.

  • Oracle provides several CBO hints that can improve the choice of join methods.

  • For data warehouse queries that join more than five tables, the optimizer_max_permutations parameter can be used to ensure that a query evaluates all possible table join combinations. After tuning, it is critical that these queries use stored outlines to avoid SQL parse times that can often exceed 30 minutes.

  • You can change the default behavior of the CBO by adjusting numerous initialization parameters. This approach often allows you to establish a very fast baseline before starting the individual tuning of SQL statements.

  • The CBO sometimes falsely determines that the cost of a full-table scan is less than the cost of an index access. The optimizer_index_cost_adj parameter will change the costing for index scans, making them more attractive.

Next, let’s take a look at tuning with the rule-based optimizer and see how it can be used to tune SQL queries.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter