How to Swap 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.

1 – First, execute 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, import 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, 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 so.name,extractValue(value(h),'.') AS hint
   FROM sys.sqlobj$data od, sys.sqlobj$ so,
   table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
   WHERE so.name = 'test' AND
    so.signature = od.signature
   AND so.category = od.category
   AND so.obj_type = od.obj_type
   AND so.plan_id = od.plan_id;

credit: https://www.dba-oracle.com/t_swapping_sql_profiles.htm

Related posts: