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
i tried but its not working, as saying plan with below hash value not found.
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?
Pingback: SQL Profile to fix slow inserts | Bobby Durrett's DBA Blog
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
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
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
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
Pingback: Tuning Example With Outline Hints and SQL Profile | Bobby Durrett's DBA Blog
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!
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