Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New 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 Technology Network
Cost Control: Inside the Oracle Optimizer

By Donald K. Burleson
OTN Member since 2001

Designing new applications for the Oracle Cost-Based Optimizer? Here's the latest information about how it works.

This article has the following sections:

PART 3 - Proper Development Environment

Many infrastructure issues must be addressed in order to avoid surprises with SQL optimization. Shops that       do not create this infrastructure are plagued by constantly changing SQL execution plans and poor database performance.

The key to success with the CBO is stability and ensuring your success with the CBO involves several      important infrastructure issues.

  • Reanalyze statistics only when necessary. One of the most common mistakes made by Oracle DBAs is to frequently re-analyze the schema. Remember, the sole purpose of doing that is to change the execution plans for your SQL, and if it ain't broke, don't fix it. If you are satisfied with your current SQL performance, re-analyzing a schema could cause significant performance problems and undo the tuning efforts of the development staff. In practice, very few shops are sufficiently dynamic to require periodic schema re-analysis.
     
  • Force developers to tune their SQL. Many developers falsely assume that their sole goal is to write SQL statements that deliver the correct data from Oracle. In reality, formulating the SQL is only half their job. Successful Oracle shops always require that developers ensure that their SQL accesses the database in an optimal fashion and require migration forms that include the execution plan for all new   SQL.

     

  • Carefully manage CBO statistics. Successful Oracle shops carefully manage the CBO statistics to ensure that the CBO works the same in their test and production environments. A savvy DBA will collect high-quality statistics and migrate their production statistics into their test environments. This approach ensures that all SQL migrating into production has the same execution plan as it did in the test database.

     

  • Rarely change CBO parameters. The CBO parameters are very dangerous because a single parameter change could adversely affect the performance of an entire enterprise. Changes to critical CBO parameters such as optimizer_mode, optimizer_index_cost_adj, and optimizer_index_caching should only be made after careful system testing.

     

  • Ensure static execution plans. Nobody like surprises, and successful CBO shops lock down SQL execution plans by carefully controlling CBO statistics, using stored outlines optimizer plan stability, or adding detailed hints to their SQL.

 

Let's take a closer look at these issues.

Re-analyze statistics only when necessary. It is very rare for the fundamental nature of a schema to change; large tables remain large, and index columns rarely change distribution, cardinality, and skew. You should only consider periodically re-analyzing your total schema statistics if your database matches these criteria:

  • Data-analysis databases. Many scientific systems load experimental data, analyze the data, produce reports, and then truncate and reload a new set of experiments. For these types of systems it may be necessary to re-analyze the schema each time the database is reloaded.

     

  • Highly volatile databases. In these rare cases, the size of table and the characteristics of index column data changes radically. For example, if you have a table that has 100 rows one week and 10,000 rows the next week, then you may want to consider a periodic reanalysis of statistics.

Force developers to tune their SQL. It is amazing how many Oracle shops do not consider their SQL execution plans. They assume that because the CBO is intelligent and sophisticated it will always provide the best execution plan, no matter what.

Because SQL is a declarative language, a query can be written in many different ways, each with a different execution plan. For example, all of the following SQL queries give the correct answer, but with widely varying execution plans:

-- Form one using non-correlated subquery)
select
  book_title
from
  book
where
  book_key not in (select book_key from sales);
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=64)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=64)
   3    1     TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=25)


-- Form two using outer join
select
  book_title
from
  book  b,
  sales  s
where
  b.book_key = s.book_key(+)  
and
  quantity is null;

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=100 Bytes=8200)

1  0 FILTER
2  1   FILTER
3  2     HASH JOIN (OUTER)
4  3      TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=20 Bytes=1280)
5  3      TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=100 Bytes=1800)


-- Form three using correlated subquery
select
  book_title
from
  book
where
  book_title not in (
                select 
                distinct
                  book_title
                from
                  book,
                  sales
                where 
                  book.book_key = sales.book_key
                and
                  quantity > 0);

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=59)
1  0  FILTER
2  1   TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=59)
3  1   FILTER
4  3     NESTED LOOPS (Cost=6 Card=1 Bytes=82)
5  4       TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=90)
6  4       TABLE ACCESS (BY INDEX ROWID) OF 'BOOK' (Cost=1 Card=1)
7  6         INDEX (UNIQUE SCAN) OF 'PK_BOOK' (UNIQUE)

 

As we can see, the proper formulation of the query has a dramatic impact on the execution plan for the SQL. Savvy Oracle developers know the most efficient way to code Oracle SQL for optimal execution plans, and savvy Oracle shops train their developers to formulate efficient SQL.

Some techniques for assisting developers in tuning their SQL include:

  • Training them to use the autotrace and TKPROF utilities and to interpret SQL execution results. Oracle University has several excellent classes on CBO optimization techniques.

     

  • Forcing all SQL that is migrating into production to have verification that the SQL has been tuned.

     

  • Making performance an evaluation criterion. Instead of noting that the best developer is the developer who writes SQL the fastest, add the mandate that a good developer also writes SQL that performs efficiently.

Carefully manage CBO statistics. Because the CBO relies on information about database objects, it is imperative that the CBO has the best possible statistics and that the same excellent statistics be used in the production, test, QA, and development instances.

It is an important job of the Oracle DBA to properly gather and distribute statistics for the CBO. The goal of the DBA is to keep the most accurate production statistics for the current processing. In some cases, there may be more than one set of optimal statistics. For example, the best statistics for OLTP processing may not be the best statistics for the data warehouse processing that occurs each evening. In this case, the DBA will keep two sets of statistics and import them into the schema when processing modes change.

Exporting CBO statistics is done with the export_system_stats procedure in the dbms_stats package. In this example we export the current CBO statistics into a table called stats_table_oltp:

dbms_stats.export_system_Stats('stats_table_oltp');

When captured, we can move the table to other instances and use the import_system_stats procedure in dbms_stats to overlay the CBO statistics when processing modes change:

dbms_stats.import_system_stats('stats_table_oltp'); 
dbms_stats.import_system_stats('stats_table_dss');

Change CBO parameters only rarely. Many Oracle shops change the fundamental characteristics of their CBO by changing the global CBO parameters. Especially dangerous are changes to optimizer_mode, optimizer_index_cost_adj, and optimizer_index_caching, and these changes should only be made when a sound reason exists. Other CBO parameters such as hash_area_size and sort_area_size are less dangerous and can be set at the individual session level to change the CBO evaluates a query.

Ensure static execution plans. Remember, re-analyzing a schema could cause thousands of SQL statements to change execution plans. Many Oracle shops have implemented standards that require that all SQL, when tested and approved in their test environment, function identically in production.

The only way to achieve this mandate is to migrate the statistics that were used in SQL testing into the production environment when the SQL is migrated. However, the DBA must ensure that a migration of statistics from test into production does not adversely affect the execution plans of other SQL that touch the target table. Hence, the DBA will carefully manage the CBO statistics, ensuring that no SQL changes execution plans after it is migrated into production.


Our tuning tools:

Our Ion tool is the easiest way to analyze Oracle performance and Ion allows you to spot hidden performance trends.

 

 
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.

 

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational