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 


 

 

 


 

 

 
 
 

Swapping SQL Profiles

Oracle Database Tips by Donald BurlesonJuly 30, 2015


Question:  I'm running a medical tracking software package that uses Oracle, and I need to tune several SQL statements.  I've been able to find a faster execution plan, but I cannot directly change the SQL because the SQL is inside a compiled executable.  How can I implement tuning changes when I cannot get to the SQL statements to add the tuning hints?

Answer:  As an alternative to swapping SQL profiles, Chris Foot has this article showing how to swap stored outlines (a feature of optimizer plan stability).  Also see: this article on swapping stored outlines.   See the eBook Tuning Third-party Vendor Oracle Systems for details on this technique.

If you use a vendor package with Oracle, you may be one of the thousands of shops that struggle with optimizing and tuning vendor systems that you did not write and cannot change.  So, how do you tune SQL when you cannot access the SQL source code?

In traditional SQL tuning, changes are made by re-writing the SQL statement into a more efficient form or by changing the execution plan by adding hints to the select clause.  With SQL that hides inside a compiled program, changing the SQL is impossible.  In other cases, the software vendor may explicitly prohibit any changes to the source code, you must come-up with a creative way to tune the SQL without touching the source code.

Oracle provides a "trick" technique for tuning SQL when you cannot "touch" the source code.  Cases where you cannot change the SQL source code include dynamically generated SQL, SQL inside 3rd party vendor packages, and compiled programs with embedded SQL. 

Before SQL profiles were introduced, we used to swap stored outlines to make changes to SQL, where we cannot touch the statement. 

Prior to Oracle10g, 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

As of Oracle 10g and beyond, the swapping of stored outlines has been superseded by a similar technique for swapping the new SQL profiles.

Swapping SQL Profiles

The central idea behind "swapping" SQL profiles is simple.  You define a SQL profile that specifies the SQL statement that you want to tune, and an alternative execution plan, in the form of hints.  When this SQL is executed and hits the library cache, Oracle detects that a SQL profile exists for this statement, and automatically applies the hints to change the execution plan.

Hence, we can tune SQL statements without ever touching the SQL statement itself.  To do this we use the DBMS_SQLTUNE package which has an import_sql_profile procedure which allows you to swap hints from one SQL profile into another SQL profile.

dbms_sqltune.import_sql_profile(
   sql_text => 'select * from emp',
   profile => sqlprof_attr('ALL_ROWS','IGNORE_OPTIM_EMBEDDED_HINTS')
   category => 'DEFAULT',
   name => 'change_emp',
   force_match => &&6
);

To see how you can swap-out SQL profiles, let's start by examining the dbms_sqltune package and the import_sql_profile procedure.

Christian Antognini notes how he can use stored outlines to change the execution plan for a query.  In the example below, he forces a query that specifies first_rows_10 to change to all_rows.  He does this trick when he executes dbms_sqltune.import_sql_profile with the profile sqlprof_attr('ALL_ROWS','IGNORE_OPTIM_EMBEDDED_HINTS'):

1 - First, he executes the query and display the execution plan which shows an index full scan:

 
SQL> select /*+ first_rows(10) */ * from sh.customers order by cust_id;
 
----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |
|   2 |   INDEX FULL SCAN           | CUSTOMERS_PK |
----------------------------------------------------

2 - Next, he imports the all_rows hint into any query that matches the original query:

begin
  dbms_sqltune.import_sql_profile(
    name     => 'test',
    category => 'DEFAULT',
    sql_text => 'select /*+ first_rows(10) */ * from sh.customers order by cust_id',
    profile  => sqlprof_attr('ALL_ROWS','IGNORE_OPTIM_EMBEDDED_HINTS')
  );
end;
/

3 - Finally, we re-execute the original query and see that the plan has changed:

 
SQL> select /*+ first_rows(10) */ * from sh.customers order by cust_id;
 
----------------------------------------
| Id  | Operation          | Name      |
----------------------------------------
|   0 | SELECT STATEMENT   |           |
|   1 |  SORT ORDER BY     |           |
|   2 |   TABLE ACCESS FULL| CUSTOMERS |
----------------------------------------

Internally, SQL profiles are stored in the data dictionary with the SQL profile name, an attribute name, and the attribute value (the hint that is to be applied to the SQL).  Here is a query to display the hints within a SQL profile:

select
   attr_val         hint_name
from
   dba_sql_profiles prof,
   sqlprof$attr     hnt
where
   prof.signature = hnt.signature
and
   name like ('&profile_name')
order by
   attr#;
 
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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.