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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to Using coe_xfr_sql_profile.sql to force a good plan

  1. Bobby says:

    I used this today to fix a production issue. A merge statement with bind variables had started running with an inefficient plan – doing a full scan instead of using the primary key. I pulled up this blog post and substituted the sql_id and plan_hash_value and followed the steps and the batch process that was running the merge statement over and over again was able to progress past the merges. Pretty easy. Was made easier because we already had SQLT installed on the database server from the previous event described in the blog where the profile didn’t really help. I queried DBA_HIST_SQLSTAT to see the history of the query’s execution including the plan hash value. I used DBMS_XPLAN.DISPLAY_AWR to extract the two plans for the merge statement.

  2. Bobby says:

    Used this again today. We have been using this pretty consistently to resolve performance issues involving plans that change.

  3. Pingback: SQLT Emergency Plan Change | Bobby Durrett's DBA Blog

  4. Pingback: SQL Profile appears to not work but really does | Bobby Durrett's DBA Blog

Leave a Reply