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

 

 

   
 

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