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 


 

 

 


 

 

 
 

SQL Profiles to replace stored outlines

Oracle Database Tips by Donald BurlesonOctober 16,  2015

Oracle author and SQL programming Guru Laurent Schneider found this tidbit in the Oracle documentation; SQL profiles will soon officially deprecate the old-fashioned stored outlines (a.k.a. optimizer plan stability):

Performance Tuning Guide

Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases.

However, Oracle strongly recommends that you use SQL plan management for new applications.

SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.

As a review, stored outlines (optimizer plan stability) allow the DBA to ?freeze? SQL execution plans, and more important, change execution plans without touching the SQL source, a critical tool for tuning third-party vendor systems where you cannot touch the source code.

Note:  Implementing SQL profiles locks in a execution plan and disables dynamic sampling for that SQL statement.

The stored outline facility was tricky and clumsy, and it's great that SQL profiles are replacing plan stability.  See 11g SQL execution plan management tips for details on this powerful new tool.

In Oracle8i and Oracle9i, MOSC note 92202 .1 describes a procedure to tune SQL that you cannot touch by performing these steps:

  1. Identify the sub-optimal SQL and create a stored outline

  2. Tune an equivalent query with a faster execution plan and create a stored outline

  3. Swap the bad stored outline for the tuned stored outline
     

Oracle provides this example for swapping the outlines:

UPDATE
   OUTLN.OL$HINTS
SET
   OL_NAME=DECODE 
     (OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL')
WHERE
   OL_NAME IN ('HINTSQL','ORIGINALSQL');
Commit;

SQL Profiles

The Oracle 10g automatic tuning advisor allowed us to implement tuning suggestions in the form of SQL profiles that will improve performance, and SQL profiles can be used the same way as stored outlines (optimizer plan stability).  The SQL Profile is a collection of the historical information of prior runs of the SQL statement, comparison details of the actual and estimated cardinality and predicate selectivity, etc.

A SQL Profile is stored persistently in the data dictionary, so it does not require any application code changes.

A SQL profile helps generate a better execution plan than the normal optimization because it is tested against a real-world workload in the SQL Tuning Set (STS). Additional tasks like checking for advanced predicate selectivity, correlation between columns, join skews, and complex predicates such as functions, help in profiling the SQL statement. Once a SQL statement is profiled and stored, differing execution plans can be invoked at will.


 

 

��  
 
 
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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.