Using coe_xfr_sql_profile.sql to force a good plan

Sunday night we worked with Oracle Support on a performance issue with a query that had been running for over 14 hours even though it ran the previous weekend in five minutes.  Oracle Support showed us how to use coe_xfr_sql_profile.sql to force the query to run using the same plan it had used on the previous weekend in an attempt to improve the query’s performance.

I’ve built a simplistic example to demonstrate how to use coe_xfr_sql_profile.sql .  First I run a simple query dumping out the plan with its hash value and sql id:

ORCL:SYS>SELECT sum(blocks) from test;

SUM(BLOCKS)
-----------
     237045

ORCL:SYS>select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID  gpm6wzuy8yabq, child number 0
-------------------------------------
SELECT sum(blocks) from test

Plan hash value: 1950795681

Then I generate and run the script to create the sql profile and enforce it:

-- build script to load profile

@coe_xfr_sql_profile.sql gpm6wzuy8yabq 1950795681

-- run generated script

@coe_xfr_sql_profile_gpm6wzuy8yabq_1950795681.sql

After running these scripts if I redo my test it shows the profile in use:

Note
-----
 - SQL profile coe_gpm6wzuy8yabq_1950795681 used for this statement

I referred to the following Oracle documents:

Using Sqltxplain to create a ‘SQL Profile’ to consistently reproduce a good plan [ID 1487302.1]

SQLT (SQLTXPLAIN) – Tool that helps to diagnose a SQL statement performing poorly or one that produces wrong results [ID 215187.1]

This is pretty cool.  I’ve done something similar with SQL Plan Management but this is a nice Oracle Support provided tool.

Alas, in our case on Sunday forcing the previous week’s plan didn’t help.  So, if a query suddenly starts running for a long time the plan may not be the issue even if it has changed.  In our case there was a big change to the data which caused the plan to change and the performance to degrade.  But it was cool to learn about this tool that I haven’t used before in the process and anticipate it being helpful in future cases where a change in plan does result in a performance issue.

– Bobby