X

Insights into Statistics, Query Optimization and the Oracle Optimizer

How do I deal with a third party application that has embedded hints that result in a sub-optimal execution plan in my environment?

Maria Colgan
Master Product Manager

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

Join the discussion

Comments ( 12 )
  • Martin Decker Wednesday, February 16, 2011
    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
  • Maria Colgan Friday, February 18, 2011
    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
  • Martin Tuesday, February 22, 2011
    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
  • Maria Colgan Tuesday, February 22, 2011
    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
  • Joe C. Wednesday, April 20, 2011
    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
  • Maria Colgan Wednesday, April 20, 2011
    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.
  • Walter Sunday, May 15, 2011
    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.
  • guest Monday, May 16, 2011
    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
  • Ranjit Friday, March 2, 2012

    Hi Maria,

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

    Pleas help.

    Regards

    Ranjit


  • guest Saturday, August 18, 2012

    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


  • Maria Colgan Monday, August 20, 2012

    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


  • guest Friday, June 7, 2013

    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


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services