BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Tuning with the Cost-Based Optimizer

Oracle Tips by Burleson Consulting

This chapter is devoted to techniques for getting the most out of the cost-based optimizer when tuning SQL. The material in this chapter is intended to provide a general framework for SQL tuning. The topics in this chapter include:

  • Cost-based optimization and SQL tuning
  • Cost-based initialization parameters
  • Analyzing tables and indexes
  • Using STATSPACK to monitor execution plans

Most Oracle databases have a hodgepodge of SQL statements that use hints and have queries that execute in both rule-based optimization and cost-based optimization. Hence, the focus of this chapter will be tuning your database when you have a cost-based default optimizer_mode, either all_rows or first_rows.

Statistics and Cost-Based Optimization

One of the requirements of cost-based optimization is the presence of statistics on tables and indexes. For tables, the statistics give a general idea of the number of rows in the table, the average row length within the table, and other statistics that are gathered by examining sample rows from the table. As you know, table statistics are generated using the analyze table command syntax. For indexes, the analyze index command is used in order to gather information about each index that participates in the cost-based query.

The statistics for indexes include information about the number of distinct values within the index, the number of entries in the index, and the physical storage characteristics of the index within the tablespace. In the case where an index has been analyzed for column histograms, we also see individual buckets that are created with statistics about the distribution of data within the index. As you know, column histograms are only useful in cases where an index has a highly skewed distribution of values, such that a query against one value in the index is faster with a full-table scan, whereas another query against the same index with a different column value would be better served by using an index range scan.

These statistics are used in conjunction with the cost-based optimizer to formulate a decision tree of different execution plans, each with an estimated cost for each plan (Figure 14-1).

Figure 1: Using statistics with the cost-based optimizer

The optimizer then evaluates the plan tree and chooses the execution plan with the lowest estimated cost.

Dynamic versus Static CBO Execution Plan Philosophy

While the basic concept of using the table and index characteristics in the formulation of the execution plan is quite good, there is a philosophical choice that needs to be made regarding the dynamic nature of SQL execution plans. The whole idea behind periodically reanalyzing tables and indexes is that the execution plans for your SQL may change as the characteristics of the data change. When adopting this dynamic approach, you must give full faith to the cost-based optimizer to choose the appropriate execution plan, and you cannot use optimizer plan stability (stored outlines) to make the SQL changes permanent. If you adopt the dynamic SQL execution philosophy, you may tune your SQL only by physically changing your SQL by adding hints to the source code. You must also ensure that any hints that you add are general enough (e.g., the first_rows hint) to allow the optimizer to evaluate several execution plans.

On the other hand, some SQL tuning professionals subscribe to the notion that there exists only one appropriate execution plan for any given SQL query, and they utilize stored outlines to ensure that once tuned, the same execution plan is always invoked. When adopting the static philosophy, the only exception to this rule is the case of skewed indexes, where the SQL tuning professional deliberately does not create stored outlines, allowing the optimizer to detect the most appropriate access plan given the skew of the index columns.


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


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter