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 3^rd 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.

---