Question: How does the
coe_xfr_sql_profile.sql script by Carolos Sierra of Oracle
Support forces a query to run using the same plan it had
used on a previous tuning attempt to improve the query's
The coe_xfr_sql_profile.sql script
generates another script that, in turn, contains the
commands to create a manual custom SQL Profile out of a
known plan that resides in RAM (library cache) or from the
AWR dba_hist_sqlplan table.
The manual custom
profile can be implemented into the same SOURCE system where
the plan was retrieved, or into another similar TARGET
system that has same schema objects referenced by the SQL
that generated the known plan.
Here is the
source code for coe_xfr_sql_profile.sql.
Bobby Durrett has a fantastic example of using
coe_xfr_sql_profile.sql to swap out a SQL execution plan.