Insights into Statistics, Query Optimization and the Oracle Optimizer
-
How do I
February 15, 2011
How do I deal with a third party application that has embedded hints that result in a sub-optimal execution plan in my environment?
I have gotten many variations on this question recently as folks begin to upgrade to Oracle Database 11g and there have been several posts on this blog and on others describing how to use SQL Plan Management (SPM) so that a non-hinted SQL statement can use a plan generated with hints. But what if the hint is supplied in the third party application and is causing performance regressions on your system?
You can actually use a very similar technique to the ones shown before but this time capture the un-hinted plan and have the hinted SQL statement use that plan instead. Below is an example that demonstrates the necessary steps.
1. We will begin by running the hinted statement
2. After examining the execution plan we can see it is suboptimal because of a bad join order.
3. In order to use SPM to correct the problem we must create a SQL plan baseline for the statement. In order to create a baseline we will need the SQL_ID for the hinted statement. Easy place to get it is in V$SQL.
4. A SQL plan baseline can be created using a SQL_ID and DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE. This will capture the existing plan for this SQL_ID from the shared pool and store in the SQL plan baseline.
5. We can check the SQL plan baseline got created successfully by querying DBA_SQL_PLAN_BASELINES.
6. When you manually create a SQL plan baseline the first plan added is automatically accepted and enabled. We know that the hinted plan is poorly performing plan so we will disable it using DBMS_SPM.ALTER_SQL_PLAN_BASELINE. Disabling the plan tells the optimizer that this plan not a good plan, however since there is no alternative plan at this point the optimizer will still continue to use this plan until we provide a better one.
7. Now let's run the statement without the hint.
8. Looking at the execution plan we can see that the join order is different. The plan without the hint also has a lower cost (3X lower), which indicates it should perform better.
9. In order to map the un-hinted plan to the hinted SQL statement we need to add the plan to the SQL plan baseline for the hinted statement. We can do this using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE but we will need the SQL_ID and PLAN_HASH_VALUE for the non-hinted statement, which we can find in V$SQL.
10. Now we can add the non-hinted plan to the SQL plan baseline of the hinted SQL statement using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE. This time we need to pass a few more arguments. We will use the SQL_ID and PLAN_HASH_VALUE of the non-hinted statement but the SQL_HANDLE of the hinted statement.
11. The SQL plan baseline for our statement now has two plans. But only the newly added plan (SQL_PLAN_gbpcg3f67pc788a6d8911) is enabled and accepted. This tells the Optimizer that this is the plan it should use for this statement. We can confirm that the correct plan (non-hinted) will be selected for the statement from now on by re-executing the hinted statement and checking its execution plan.
thanks for sharing this. I was wondering why the leading hint was not honored in the first place and the plan started with promotions table.
Regards,
Martin
The leading hint is actually being honored in the original plan. The table that is most indented from the left hand side is typically the leading table or the first table accessed in the plan. The table that is most indented in the original plan is actually the Sales table on line 7. You can also confirm the join order by looking at the outline for the plan. You can do this by executing the following command:
select * from table(dbms_xplan.display_cursor(format=>'BASIC +COST +OUTLINE'));
This command will output the following outline information under the execution plan
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
OPT_PARAM('_optimizer_table_expansion' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "S"@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
FULL(@"SEL$1" "P"@"SEL$1")
LEADING(@"SEL$1" "S"@"SEL$1" "T"@"SEL$1" "P"@"SEL$1")
SUBQUERY_PRUNING(@"SEL$1" "S"@"SEL$1" PARTITION)
USE_HASH(@"SEL$1" "T"@"SEL$1")
USE_HASH(@"SEL$1" "P"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$1" "P"@"SEL$1")
END_OUTLINE_D
*/
The line you are interested in is the sixth from the bottom and begins with the word LEADING. This line will indicate the exact join order used in the execution plan. In the original plan with the leading hint the join order was sales (S) joins to time (T) and then to promotions (P).
Thanks,
Maria
Could you add information, whether the functionality described here is ok to be used with a plain DB EE or whether it already requires a license (e.g. diagnostic/performance pack).
BR,
Martin
All of the functionality shown in the blog post and in my previous response are available for free in a plain DB EE. No additional licensing is required.
Thanks,
Maria
This is a great article!!
I was wondering if your dba_sql_plan_baselines.signature column matches for the two sql plan baselines?
Thanks!
Joe
The signature in dba_sql_plan_baselines is unique. So no two sql plan baselines will be have the same signature. You can think of it as being similar to a sql_id.
Great article with example! One question I had is what about instance/db restarts, do we have to do this again or there is/must be a way to preserve this across recycles.
Thanks,
Walter.
I am delighted to hear you found this post useful. SQL plan baselines are persistent across reboots / restarts so you only have to do these steps once. The SQL plan baseline will remain active until you disable it or something changes on the system (an index gets dropped) preventing us from being able to reproduce the desired plan.
Thanks,
Maria
Hi Maria,
Can we use the same method vice-versa i.e converting into parallel?
Pleas help.
Regards
Ranjit
Hi Maria,
For a few years now, we have solved this type of issue using outlines. All we need to do is to provide two versions of the SQL text, the original malperforming SQL and the SQL with hints and that is enough, we don't need to actally execute the any of the SQLs.
With the method described above, you actually must execute both SQL statements (original and hint-fixed) in order to capture baselines from the cursor cache.
This might not be straightforward, if the SQL has bind variables of various datatypes (e.g.: dates) that you will need to define and populate, or if the SQL is a DML that you don't just want to run in order to get it into the cursor cache.
Is there any way to implement this SPM solution without actually executing the SQLs ?
One way I can think of is to create an outline the way we have been doing, and then migrate the outline to a baseline - however I understand that outlines are being de-supported , in which case it won't be a good solution for the long term.
BTW - as of which Oracle version will outlines not work any more ?
Thanks,
Amit
Hi Amit,
You are correct, with this method you do need to execute the statements so you can capture the SQL_ID and PLAN_HASH_VALUE as well as the plan outline from the cursor cache.
However, if you have a method that allows you to create a stored outline using the hinted plan without having to execute the SQL statement then yes, you would easily migrate the stored outline to a SQL plan baseline without having to execute two versions of the statement.
Stored outlines were deprecated in Oracle Database 11g in favor of SQL plan baselines. However, SQL plan baselines are not available in all versions of Oracle so the stored outline code still exists and you can continue to use it without encountering an error message. However, if you run into a problem using stored outlines support will not help you. They will instruct you to migrate to SQL plan baselines.
Thanks,
Maria
Hi Maria:
I've used SQL plan baselines in the past without any issues. But, now I'm trying to force a SQL to use an optimal plan with my hinted SQL. For some reason, the optimizer is not honoring the enabled SQL plan baseline. Appreciate if you could also share the situations that leads the optimizer to ignore the SQL plan baselines.
Thanks,
Senthil