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



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Making the Default Optimizer Mode Permanent
Oracle Tips by Burleson

To make sure that your SQL baseline remains permanent, it is a good idea to add the appropriate hint or stored outline to all of the SQL that uses the default optimizer mode. This can save hundreds of hours of work when you migrate to a later release of Oracle and you do not want the improved CBO to change SQL that you have already tuned.

For example, let’s say that your evaluation showed that your best overall SQL performance is with optimizer_mode=first_rows. After seeing the initialization parameter, you should take the time to firm up all queries that benefit from the system-wide default.

This is done by adding the first_rows hint to the SQL source code, or by creating stored outlines for the SQL to ensure that they will always use the first_rows method. This will ensure that the default baseline remains, and it prevents changes to CBO statistics and upgrades from changing the execution plans.

Instance-Wide Parameters to Improve the Speed of Full-Table Scans

Once we have verified that a full-table scan is legitimate, based on the number of rows requested, we can look at improving the performance of the full-table scan. While I will be discussing this in detail in Chapter 10, we should review the initialization parameters that relate to the speed of full-table scans.

As a quick summary, we need to verify the following initialization parameters:

  • Multiblock read parameters are set according to the number of CPUs on your database server. These include db_file_multiblock_read_count, hash_multiblock_io_count, and sort_multiblock_read_count.

  • Oracle parallel query is properly configured for full-table scans. This includes setting the parameters parallel_max_servers, optimizer_percent_parallel, and parallel_automatic_tuning.

To enable parallel query, the DBA will locate those SQL statements that require full-table scans and then ensure that these queries utilize parallel query by adding the full and parallel hints to the SQL and making the change persistent with optimizer plan stability or by changing the SQL source code to include the hints.

WARNING: It is very dangerous to enable parallel query for a table with the alter table command. Once a table is marked for parallel query, the CBO may change the execution plan for existing queries to use parallel full-table scans instead of index scans. This well-intentioned mistake has crippled many databases.

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