Why was the RULE hint ignored?

Recently I got a call from a customer that had just upgraded to Oracle Database 11.2.0.2 and was panicking because they thought the Rule Based Optimizer (RBO) had been removed. The reason they thought this was because when they tried to add the RULE hint to a SQL statement they still got a Cost Based Optimizer (CBO) plan. I quickly reassured them that the RBO had not been removed in 11.2.0.2 and asked them to describe their scenario. It turned out that the statement they added the RULE hint to, already had several hints specified in it. The RULE mode is ignored if the statement contains hints other than the RULE hint itself, hence their cost based plan.


Although the RBO was depreciated in Oracle Database 10g, the RULE mode and hint are still honored assuming the query can actually use the RBO. The following object / query properties  force the CBO to be used even when the RULE mode / hint is specified:


  • Other hints are specified in the SQL statement

  • One or more partitioned tables are accessed in the SQL statement

  • One or more IOTs are accessed in the SQL statement

  • One or more Materialized views are accessed in the SQL statement

  • A SAMPLE clauses is specified in a SELECT statement

  • A spreadsheet clause is specified

  • Parallel execution is used

  • Grouping sets are used

  • Group outer-join is used

  • A create table with a parallel clause

  • A left outer join (ANSI) is specified

  • A full outer join (ANSI) is specified

  • Flashback cursor (AS OF) is used


You should also be aware that if you use the RULE hint you severely limit the Optimizer options. It will eliminate all cost-based transformation (including star transformation), and prevents certain access paths and join methods from being considered. For example,


  • Function based indexes

  • Bitmap indexes

  • Reverse key indexes

  • Index skip scans

  • Hash Joins


Although hints are some time necessary to work around a suboptimal execution plan, it is far wiser to use a specific hint then to try and revert back to RBO via the RULE hint. Ideally you should take advantage of SQL Plan Management (SPM) in these cases, rather than adding the hint directly to the SQL Statement. Using SPM you can associate a hinted plan with a non-hinted SQL statement.

+Maria Colgan

Comments:

"One or more Materialized views are accessed in the SQL statement"

Does this also apply when selecting from the MV directly and not relying on query transformation?

Thanks.

Posted by guest on December 19, 2011 at 06:17 PM PST #

Hi,
Am really interested in the "Tips for Preventing Suboptimal Execution Plans" presentation. Cannot get it from oracleus.wingateweb.com anymore.
Any idea from where I could download it?

Thanks.

Posted by guest on April 09, 2012 at 06:53 AM 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