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


run_hinted_stmt.png

2. After examining the execution plan we can see it is suboptimal because of a bad join order.
new_hinted_plan.png


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.

capture_id_1.png


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.

create_baseline.png


5. We can check the SQL plan baseline got created successfully by querying DBA_SQL_PLAN_BASELINES.


new_check_baseline_was_created.png


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.


new_disable_bad_plan.png


7. Now let's run the statement without the hint.

run_unhited_stmt.png


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.


new_unhinted_plan2.png


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.

capture_id_2.png


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.


new_add_new_plan_to_baseline.png


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.



new_proof_baseline_is_used.png

Comments:

Maria, 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

Posted by Martin Decker on February 15, 2011 at 04:08 PM PST #

Hi 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

Posted by Maria Colgan on February 18, 2011 at 07:38 AM PST #

Thank you for showing this feature so descriptive. 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

Posted by Martin on February 21, 2011 at 11:02 PM PST #

Hi 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

Posted by Maria Colgan on February 22, 2011 at 12:48 AM PST #

Hi 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

Posted by Joe C. on April 20, 2011 at 05:11 AM PDT #

Hi 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.

Posted by Maria Colgan on April 20, 2011 at 05:25 AM PDT #

Hi Maria, 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.

Posted by Walter on May 15, 2011 at 12:47 PM PDT #

Hi 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

Posted by guest on May 16, 2011 at 02:37 AM PDT #

Hi Maria,

Can we use the same method vice-versa i.e converting into parallel?
Pleas help.

Regards
Ranjit

Posted by Ranjit on March 01, 2012 at 10:38 PM PST #

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

Posted by guest on August 17, 2012 at 09:31 PM PDT #

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

Posted by Maria Colgan on August 20, 2012 at 09:26 AM PDT #

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

Posted by guest on June 07, 2013 at 12:39 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today