Oracle Articles, Oracle Tools, Oracle Tips, Database Articles and DBA Tips  

The Largest Online Resource for Oracle Articles, Oracle Tips, Oracle Scripts & Oracle Tools!!


Enter your Email:
 
Navigate at FreeMegaZone Home      Articles      Tools      Jobs      Games      Support      Submit Content      Advertise
Advertise at http://www.articles.freemegazone.com

Advertise at FreeMegaZone

Give tremendous boost to your business by advertising at FreeMegaZone. Contact webmaster@freemegazone.com
 

Rating: *****                                             Rate this article:    

 Author: Burleson

Page: 1 2

We hear two different views about Oracle SQL optimization. According to one view there is only one optimal execution plan for their SQL versus while supporters of other side want their execution plans to change as optimizer statistics and parameters change.  

Sensitivity of Oracle optimizer:

Oracle optimizer is sensitive to changes to alterations in metadata statistics. Mostly database administrators want the execution plans to change along with the data.  However, in my experience, over 70% of Oracle shops will not benefit from changes to SQL execution plans.

Schema Re-analyze:

Many shops make the mistake of scheduling a re-analyze of their schema every Sunday as thousands of execution plans change. It must be remembered that the only reason to re-analyze CBO statistics is to alter SQL execution plans.

Persistent Shops:

According to persistent SQL philosophy there exist only one optimal execution plan for any SQL statement.

Shops that subscribe to this philosophy are characterized by stable OLTP applications that have been tuned to use host variables instead of literal values in all SQL queries.

Persistent shops have tables and indexes whose recomputed statistics rarely change the execution plan for their SQL queries no matter how often the statistics are recomputed.

Many persistent shops have all of their SQL embedded inside PL/SQL packages. This insulates all of the SQL from the application programs and ensures that all applications execute identical SQL. It also ensures that all of the SQL has been properly tuned.

Execution Plan Management – The Evolution:

We see many different tools with the evolution of Oracle SQL execution plan (explain plan) management. These include

Stored Outlines / Optimizer plan stability

  • Stored outlines were burdensome to manage.
  • It was difficult to swap execution plans with plan stability.

10g SQL Profiles

  • In Oracle 10g SQL Profile approach allows SQL tuning Set to be tested as a workload.
  • Oracle 10g SQL Profiles allows the database administrator to implement changes to execution plans.

11g SQL Plan management

  • Oracle 11g provides an easy-to-use approach for locking-down SQL execution plans. 
  • In Oracle 11g the 10g SQL profile approach is deprecated and optimizer_capture_sql_plan_baselines and optimizer_use_sql_plan_baselines parameters are used only.

Oracle 11g SQL Plan Management (SPM):

Oracle 11g execution plan management lock-down critical SQL execution plans and tests execution timings before implementing changes.Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Reveal the shades of Oracle result_cache!!
   Step by Step Guide to Oracle Parsing
   Database Tuning Guide for Third Party Applications
   What if your database lock gets blocked??


FreeMegaZone Jobs!!

 
 
HOME      ABOUT US      SUPPORT      SITE MAP      PRIVACY POLICY      TERMS OF USE      SUBMIT CONTENT      ADVERTISE
Copyright © 2007 - 2010 Oriole Intellect Inc. All rights reserved.

The name Oracle is a trademark of Oracle Corporation. Any other names used on this website may be trademarks of their respective owners