Using hints with coe_xfr_sql_profile.sql

In an earlier blog post I showed how I used coe_xfr_sql_profile.sql from the sqlt toolkit from Oracle support to force a particular sql statement to run with a more efficient plan that it had used in the past.

Today, I’m trying a slight variation.  Take the problem query, run it with hints, and use coe_xfr_sql_profile.sql to apply the resulting plan to the original query.  I built a quick and dirty test to make sure it works.

-- create test table with index and stats

SQL> create table test as select * from dba_tables;

SQL> create index testi on test(owner);

SQL> execute dbms_stats.gather_table_stats(NULL,'TEST');

-- run query unhinted

SQL> select sum(blocks) from test where owner='SYS';

SUM(BLOCKS)
-----------
    1042402

-- show plan - uses the index

SQL> select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a0nw2xjnmjcxd, child number 0
-------------------------------------
select sum(blocks) from test where owner='SYS'

Plan hash value: 1551939256

--------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |
|   1 |  SORT AGGREGATE              |       |     1 |     9 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |  2122 | 19098 |
|*  3 |    INDEX RANGE SCAN          | TESTI |  2122 |       |
--------------------------------------------------------------

-- use hint to force full scan

SQL> select /*+full(test) */ sum(blocks) from test 
where owner='SYS';

SUM(BLOCKS)
-----------
    1042402

-- get plan with full scan

SQL> select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5521zhmq67vun, child number 0
-------------------------------------
select /*+full(test) */ sum(blocks) from test 
where owner='SYS'

Plan hash value: 3467505462

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   295 (100)|
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL| TEST |  2122 | 19098 |   295   (1)|
----------------------------------------------------------------

So, let’s pretend that sql_id=a0nw2xjnmjcxd is the real production query you are trying to tune.  Without hints it is doing an index range scan.  With a hint you run a similar query forcing a full scan.  The new query, sql_id=5521zhmq67vun has a plan with this hash value: 3467505462.  So, I used coe_xfr_sql_profile.sql to force the unhinted original query to run with the hinted query’s plan:

cd /var/opt/oracle/admin/sqlt/utl

-- login to sqlplus as system

@coe_xfr_sql_profile.sql a0nw2xjnmjcxd 3467505462

-- login to sqlplus as system

@coe_xfr_sql_profile_a0nw2xjnmjcxd_3467505462.sql

Now when I run the original query it runs with the new plan and indicates that the profile is in use:

SQL> select sum(blocks) from test where owner='SYS';

SUM(BLOCKS)
-----------
    1042402

SQL> select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a0nw2xjnmjcxd, child number 0
-------------------------------------
select sum(blocks) from test where owner='SYS'

Plan hash value: 3467505462

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   295 (100)|
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL| TEST |  2122 | 19098 |   295   (1)|
----------------------------------------------------------------

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

I edited the output to make it more readable.  See the zip with the full scripts and logs.

So, if you have a recurring production SQL query that runs faster with hints just run that query once with the hints and then use coe_xfr_sql_profile.sql to force the real query to run unchanged but with the same plan as the hinted query.

– 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 have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

10 Responses to Using hints with coe_xfr_sql_profile.sql

  1. kuljeet singh says:

    i tried but its not working, as saying plan with below hash value not found.

    • Bobby says:

      Thank you for your comment. I’m not sure that I can help without knowing more details, but I have one idea. You have to run some query that gets the desired plan hash value before you create the SQL Profile. I have taking the problem query and added hints to it to force the good plan and run it once. Then I could create a SQL Profile for the original un-hinted query and apply the good plan. So, maybe a query has not run on the database in question with the desired plan. Maybe you got the plan hash value from a different database but that plan has not run on the new database?

  2. Pingback: SQL Profile to fix slow inserts | Bobby Durrett's DBA Blog

  3. Satish says:

    Hi Bobby,

    In first(un-hinted) case what is the cost of the sql? I think it’s less than second case(hinted). if yes we don’t need to change the right ?

    I think, to simulate a situation you removed the cost and explained.

    Good show. Thanks for the post.

    Thanks
    Satish

    • Bobby says:

      Satish,

      Thank you for your comment. You are correct that the cost of the first plan is lower than the second. If you click on the link for the words “a quick and dirty test to make sure it works” you will see the actual output of the SQL scripts including the costs of the plans. The database will pick the lower cost plan if you do not intervene. If you find that a higher cost plan actually runs faster than a slower one you have to do something to override the database’s choice or change the way it calculates the cost. A hint or a SQL Profile forces the database to run a higher cost plan. Changing the way statistics are gathered might change the way the cost is calculated so that the better plan becomes lower cost than the slow plan. But this post is about the first option. It is about using a SQL Profile to make the database run a higher cost plan because you know the higher cost plan actually runs faster than the lower cost one. But, my example was just an illustration. The higher cost plan probably is slower in this example so in real life you wouldn’t need the SQL Profile for this situation.

      Bobby

  4. Satish says:

    Perfect.. Thanks Bobby

    I have one more question.. can i get the reasons for execution plan change ?
    and how to put scheduler job/alert to know whenever an execution plan got changed..

    Please provide, if you have any article on the same.

    Thanks in advance.

    Thanks
    Satish

    • Bobby says:

      Here is a post about the monitor I use to detect plan changes:

      https://www.bobbydurrettdba.com/2015/08/31/plan-change-monitor-prevents-user-impact-from-bad-plan/

      I see plan changes in SQL statements that have bind variables. I think that when a SQL statement has not run for a while its plan is no longer stored in memory. When a SQL statement with bind variables has its plan recreated the plan is based on the current values of the bind variables. The current bind variable values may result in a different plan than the values that were used the last time the plan was created. I have one system with a partitioned table with some empty partitions. If the bind variable value refers to an empty partition it produces a different plan than it does if the bind variable value points to a full partition. Bind variables are great at reducing parsing but they are a pain if the SQL gets parsed with a set of bind variable values that require a plan that is inefficient with other sets of bind variable values.

      Bobby

  5. Pingback: Tuning Example With Outline Hints and SQL Profile | Bobby Durrett's DBA Blog

  6. Anonymous says:

    Hi, thanks for your post! This is great one.

    Question1 : Is there any data dictionary to view these profiling info?

    Question2: how do we delete the profile? Just in case, if there is any expected issues, we need to have rollback plan. How do we rollback the changes?

    Question3: What is the difference between COE PROFILING and SPM(sql plan management)? Sounds like both same? SPM does the same thing?

    Question4 : What is difference between SQL profiling and COE PROFILING? Just curious to know.

    thank you!

    • Bobby says:

      1:

      Script of mine that gets SQL Profile information from the AWR:

      https://github.com/bobbydurrett/OracleDatabaseTuningSQL/blob/master/awr/profiles.sql

      2:

      The script generated by coe_xfr_sql_profile.sql outputs the command to delete the profile. It is very easy.

      3:

      The COE scripts let you fix one SQL statement at a time. SPM, which I have never used in production, gathers a larger number of SQL statements. I was scared off of SPM when someone at a conference described all the tens of thousands of SQL statements SPM might capture and the performance impact of saving all those plans. I use the COE scripts for firefighting mostly and I figure the overhead of having ten or 20 SQL profiles would be reasonable instead of having 10,000 in SPM.

      4:

      I’m not sure what you mean by SQL profiling. If you mean getting a profile of how a SQL statement runs and how much CPU and waits it spends time on that is completely different. A COE profile is really just an outline hint applied to SQL statements that meet a certain pattern.

      Thanks for your comment and questions!

      Bobby

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.