How to tune Oracle SQL remotely

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.

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#;