 |
|
Swapping SQL Profiles
Oracle Tips by Burleson Consulting
July 30, 2009
|
Question:
I’m running a medical racking 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:
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, MetaLink note 92202 .1 describes a
procedure to tune SQL that you cannot touch by performing these steps:
-
Identify the sub-optimal SQL and create a stored
outline
-
Tune an equivalent query with a faster execution
plan and create a stored outline
-
Swap the bad stored outline for the tuned stored
outline
Chris Foot has this article showing how to swap stored outlines (a
feature of optimizer plan stability).
See the book
Tuning Third-party
Vendor Oracle Systems
for details on this technique.
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#;
