Oracle’s OPT_ESTIMATE hint: Usage Guide

Jun 21, 2010 / By Christo Kutrovsky

Tags: ,

It’s one of those hints that you’ve heard about many times, but all the information is scattered, and finding what you need is really hard.

Many (including myself) have expressed the deepest desire to see it documented, but that never happened. I’ve just troubleshot a problem and used this hint a lot during my “what if” scenario testing, and found this hint to be quite useful. Thus, I decided to document it here on the blog.

I will come back and update this blog as I discover new parameters, and feel free to suggest what you’ve found in the comments. If you can, include an example to illustrate usage.

Note that this usage guide is not official, and is built upon by my best understanding on how this hint operates.

OPT_ESTIMATE

Purpose

To influence costing and cardinality estimates by the optimizer in the hope that a better execution path is selected. This hint supersedes the “CARDINALITY” hint.

Usage

Parameters separate by space (optionally by comma). “[]” means that “query block” is optional.

/*+ OPT_ESTIMATE( [query block] operation_type identifier adjustment ) */

  • query block

    optional and in the format “@QB_NAME”. For example, in the lack of any QB_NAME hints, you could say: OPT_ESTIMATE(@SEL$1 TABLE….)

  • operation_type

    QUERY_BLOCK
    unknown

    TABLE
    refers to table

    Example:

    select /*+OPT_ESTIMATE(TABLE DD ROWS=10)*/
    count(*) from dual DD
    

    INDEX_FILTER
    adjusts index filtering – number of rows produced by the index lookup, does not affect the cost to scan the index

    Example INDEX_FILTER – changes output rows ONLY:

    select /*+ OPT_ESTIMATE(INDEX_FILTER C CKK$ID ROWS=1234) */* 
    from ckk c  where id > 9000000
    

    INDEX_SCAN / INDEX_SKIP_SCAN
    For index range or skip scans – how many rows will be scanned in the index – affects the COST of the index access and the number of rows outputted

    Example INDEX_SCAN – changes COST and ROWS:

    select /*+ OPT_ESTIMATE(INDEX_SCAN C CKK$ID ROWS=1234) */* 
    from ckk c  where id > 9000000
    

    JOIN
    the join result of 2 tables – oracle joins 2 tables at a time. Note that you can adjust join cardinalities between 2 tables in order to make that join not to be used.
    Example:

    select /*+ OPT_ESTIMATE(JOIN (C S) ROWS=123456) */* 
    from ckk c join small s on (s.id = c.id)
    
  • identifier

    qry_block is always optional

    TABLES: tab_alias@qry_block

    INDEX: tab_alias@qry_block index_name

    JOINS: JOIN(tab1_alias@qry_block tab2_alias@qry_block)

  • adjustment

    ROWS=number

    SCALE_ROWS=number – multiplier to be used. For example 0.1 is 10 times less rows than originally estimated

    MIN=minimum number of rows from step – very useful to prevent ‘insane’ plans

    MAX=maximum number of rows from step

 

Updated:
2013 April 17: Added: Examples for INDEX_FILTER, INDEX_SCAN and added one more reference with examples
2012 Dec 06: Added MIN/MAX description
2012 June 21: Added References

References.

These are sites that gave me syntax details, or usage details. The examples and experiments however are my own.

http://jonathanlewis.wordpress.com/2007/02/11/profiles/

http://dioncho.wordpress.com/2009/12/17/trivial-research-on-the-cardinality-feedback-on-11gr2/

http://www.freelists.org/post/oracle-l/How-are-SQL-Profiles-calculated

http://www.oaktable.net/content/oracles-sql-tuning-pack-part-2

4 Responses to “Oracle’s OPT_ESTIMATE hint: Usage Guide”

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>