What should I do with old hints in my workload?

We promised in an earlier post to cover some of the questions from the Optimizer round table discussion at Oracle Open World. Here's our first in a series of posts that will address these questions.

Q: When moving from 10g to 11g, should hints in existing SQL be removed?

A: I was glad to see this question at the round table, since I think this is something people often overlook when adding hints to an application. Over time, hints can become stale. You may have added a hint to a query because of a weakness in an old version of the optimizer, but the hint might no longer be necessary to get the best plan. Even worse, the hint may force a plan which is suboptimal, for a couple of reasons:
  • Depending on how your data change over time, the plan choice that the hint enforces might no longer be a good choice. This problem can occur even without a database upgrade. For example, if the distribution of values in a column change over time, an old access path hint may no longer be appropriate.
  • The hint might prevent new optimizations from taking place. So after an upgrade, you will be stuck using the old hint-enforced plan, when the optimizer could have chosen something better. For example, you may have hinted a plan for a query with bind variables, to avoid a bad plan choice due to bind peeking in the pre-11g optimizer. In 11g, you will not get the benefits of adaptive cursor sharing because of the hints in the query.
These are just some of the reasons why it is good to re-evaluate the hints in your application from time to time. You can test this out using the _optimizer_ignore_hints parameter. Setting this parameter to TRUE will cause the optimizer to ignore the hints embedded in queries. You can set this on the session level, run your workload, and compare the performance to your baseline performance (with the hints). I recommend using SQL Performance Analyzer (SPA) to do this. Read the SPA white paper for more information on how to do that.

If you find that some of your queries really do still require hints to get an optimal plan, you should consider creating a SQL plan baseline, and allowing SPM to manage the plan for you. If you do this, then you will get the hinted plan for now, but if a better plan comes along later (for one of the reasons mentioned earlier), you will eventually get the benefits of the new plan, using the SPM evolve process. There are two ways that you can create a SQL plan baseline based on your hints that will be applied to the query without hints. The first way requires that a SQL plan baseline already exist for the unhinted query. That's described in an earlier post. Here are the steps to use if you don't already have a SQL plan baseline for the unhinted query.

1. Run the query with hints, and confirm that the plan is what you want:
var pid number
exec :pid := 100;
select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid; 
PROD_NAME                                          AMOUNT_SOLD CALENDAR_YEAR
-------------------------------------------------- ----------- -------------
...
select sql_id from v$sql where sql_text like 'select /*+ leading(t) */ p.prod_name%';
SQL_ID
-------------
2qtu6hy4rf1j9
select * from table(dbms_xplan.display_cursor(sql_id=>'2qtu6hy4rf1j9', 
                                              format=>'basic note'));
EXPLAINED SQL STATEMENT:
------------------------
select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year from sales s, products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and p.prod_id < :pid

Plan hash value: 2290436051
---------------------------------------------------------------
| Id  | Operation                            | Name           |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |
|   1 |  HASH JOIN                           |                |
|   2 |   HASH JOIN                          |                |
|   3 |    TABLE ACCESS FULL                 | TIMES          |
|   4 |    PARTITION RANGE ALL               |                |
|   5 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   6 |      BITMAP CONVERSION TO ROWIDS     |                |
|   7 |       BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|   8 |   TABLE ACCESS BY INDEX ROWID        | PRODUCTS       |
|   9 |    INDEX RANGE SCAN                  | PRODUCTS_PK    |
---------------------------------------------------------------

2. Load SQL plan baseline from cursor cache, and associate it with the unhinted query:

var sqltext clob;

begin
:sqltext := 'select p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id > :pid';
end;
/

exec :pls := dbms_spm.load_plans_from_cursor_cache( -
              sql_id => '2qtu6hy4rf1j9', -
              plan_hash_value => 2290436051, -
              sql_text => :sqltext);

3. Run the query without hints, and check that the SQL plan baseline was used.

select p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid;

PROD_NAME                                          AMOUNT_SOLD CALENDAR_YEAR
-------------------------------------------------- ----------- -------------
...

select * from table(dbms_xplan.display_cursor(sql_id=>'a1ax3265pq8x7',
                                              format=>'basic note'));

EXPLAINED SQL STATEMENT:
------------------------
select p.prod_name, s.amount_sold, t.calendar_year from sales s,
products p, times t where s.prod_id = p.prod_id and s.time_id =
t.time_id and p.prod_id < :pid

Plan hash value: 2290436051



---------------------------------------------------------------
| Id  | Operation                            | Name           |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |
|   1 |  HASH JOIN                           |                |
|   2 |   HASH JOIN                          |                |
|   3 |    TABLE ACCESS FULL                 | TIMES          |
|   4 |    PARTITION RANGE ALL               |                |
|   5 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   6 |      BITMAP CONVERSION TO ROWIDS     |                |
|   7 |       BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|   8 |   TABLE ACCESS BY INDEX ROWID        | PRODUCTS       |
|   9 |    INDEX RANGE SCAN                  | PRODUCTS_PK    |
---------------------------------------------------------------
Note ----- - SQL plan baseline SQL_PLAN_4rw2dhryc2w5h888547d3 used for this statement
Comments:

This doesn't work in 11.2.0.3. dbms_spm.load_plans_from_cursor_cache refuses to load the baseline from sql_id of the original statement and plan_hash_value from the hinted statement. There is no error reported, the function simply returns 0 if it fails to load. If the plan_hash_value and sql_id belong to the same statement, there is no problem.

Posted by Mladen Gogala on October 11, 2011 at 06:20 AM PDT #

So, looks like since 11.2.0.3 we are unable to use this technique as well as that http://blogs.oracle.com/optimizer/entry/how_do_i_deal_with_a_third_party_application_that_has_embedded_hints_that_result_in_a_sub-optimal_ex described in Your blog.
Correct ?
Regards
GregG

Posted by guest on December 19, 2011 at 11:14 PM PST #

Hi Greg,

I am not aware of any reason why this technique should not work in 11.2.0.3. I just tried it in my 11.2.0.3 environment and it worked. What made you think it didn't?

Maria

Posted by Maria Colgan on January 05, 2012 at 05:08 AM PST #

Hi Mladen,

The dbms_spm.load_plans_from_cursor_cache function returns a number, the number of plans loaded. If you are getting a 0 returned from this function they there is a problem, as no plans are getting loaded.

It may be easier to map the hinted plan to the non-hinted SQL statement using the SQL_HANDLE rather than the SQL_TEXT. In this case you must specify the SQL_ID AND the plan_hash_value for the hinted statement and SQL_HANDLE for the non-hinted SQL statement. For example,

exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'2qtu6hy4rf1j9', plan_hash_value => 2290436051,
sql_handle =>'SQL_4bf04d85fcc170b0');

Posted by Maria Colgan on January 05, 2012 at 05:25 AM PST #

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