Oracle Scratchpad

June 14, 2009

Undocumented Hints

Filed under: Hints,Oracle — Jonathan Lewis @ 6:26 pm BST Jun 14,2009

The bits of Oracle which aren’t documented always seem to be the bits that are hard to resist, so I thought I’d make a brief comment on undocumented hints.

Of course, you should not take advantage of any undocumented feature without first getting approval from Oracle support, but some hints seem to me to fall into a special category where you are more likely to get that approval – and here are my thoughts on why.

There seem to be four main reasons why some hints are not documented:

  • There are hints that you’re absolutely not supposed to know about. One such example is the infamous no_trigger hint that appeared briefly in a version that I will deliberately leave unspecified (in case anyone is still using it). And if anyone posts the version number in a comment, I will delete it.
  • There are hints which are actually internal hints that operate in situations that the end-user cannot manufacture. The index_rrs() hint that used to appear in some parallel query slave code (for parallel index fast full scans) was an example of this type of hint.
  • There are hints which I assume are not yet documented because there is some beta code that is still subject to finalisation sitting in the production release. The hint may become official, it may cease to exist. The not-quite-documented selectivity() hint of 9i – which subsequently disappeared – may have been such a case.
  • There are hints which never got into the (right place in the) documentation because no-one told the editors about it in time. I like to think that most of the undocunented hints like this – but I may be wrong. The dynamic_sampling_est_cdn() hint is probably an example of this type. It’s in the 9.2 Performance Tuning Guide and Reference – but not in the table of contents, the index, or the lists of hints given in the chapter on hints. Instead it appeared as an afterthought in the section on the dynamic_sampling() hint. It’s gone from the 10gR2 manual (and the documentation for hints has moved into the SQL Reference)

So when you look at undocumented hints, you have to ask yourself – are they supposed to be official, or will they soon be official, or are they actually official if only you can find where they’ve been documented.

Footnote: If you want to see a complete list of hints, including the version where they appeared, and the version where they got into the “stored outline” code, then 11g gives you the view v$sql_hint.

20 Comments »

  1. v$sql_hint is one of my favorite views of 11g – thenk you for pointing to it in “Hints on hints” document.

    My list of hints which I’d likte to be documented:

    CARDINALITY
    CONNECT_BY_COST_BASED / NO_CONNECT_BY_COST_BASED
    NATIVE_FULL_OUTER_JOIN / NO_NATIVE_FULL_OUTER_JOIN
    NO_ELIMINATE_JOIN
    NO_ELIMINATE_OBY
    OPT_ESTIMATE
    SWAP_JOIN_INPUTS / NO_SWAP_JOIN_INPUTS
    USE_HASH_AGGREGATION / NO_USE_HASH_AGGREGATION
    

    with CARDINALITY in the very top of the list.

    Comment by Timur Akhmadeev — June 15, 2009 @ 7:40 am BST Jun 15,2009 | Reply

  2. Timur,

    I’ll work on writing notes about the ones I know. But I’m not sure that worrying about cardinality() is a good idea anymore – I think it has been deprecated (at least internally) in favour of a couple of the opt_estimate() options.

    The only hint I would really like to see documented (hence made legal) is opt_estimate(). If I know why the the optimizer is getting it’s arithmetic wrong, I want to give it a hint that tells it how to correct it.

    Comment by Jonathan Lewis — June 15, 2009 @ 9:42 am BST Jun 15,2009 | Reply

    • Jonathan,
      would you use some hints for table() function? Are there any legal hints for correcting cardinality?
      I tend to use cardinality hint when I’m working with collections in DML.

      Comment by radino83 — June 15, 2009 @ 11:47 am BST Jun 15,2009 | Reply

    • Well, CARDINALITY is just simpler then OPT_ESTIMATE :) and is invaluable for TABLE expressions.
      I don’t know if it’s really deprecated and where it is used internally, but it seems to work as expected in 11g too. That’s good, since lots of code relies on it.

      Comment by Timur Akhmadeev — June 15, 2009 @ 11:52 am BST Jun 15,2009 | Reply

    • Radino,

      At present it is possible to make the cardinality() hint work on query blocks that use the table() operator. There are other hints that are better, though, and give more detail – but can’t be used (opt_estimate, col_stats).

      Timur,
      “deprecated (at least internally) There are a couple of places (e.g. materialized view refresh) where the code once used the cardinality() hint but now uses the opt_estimate() hint. But at present cardinality() is the best we have.

      It’s only easy to use when it’s easy to use ;) But it’s very limited in use because you often need to supply information like “number of distinct values” for columns at the same time; otherwise you just end up putting in the entire structural set of hints to define the plan.

      Comment by Jonathan Lewis — June 15, 2009 @ 1:41 pm BST Jun 15,2009 | Reply

      • This is a timely discussion because I’ve almost finished writing up an article on four different ways to supply cardinality for pipelined and table functions.

        In addition to the (unsupported) CARDINALITY and OPT_ESTIMATE hints, from 11.1.0.7, dynamic sampling works with pipelined functions (not table functions it seems and only via the DYNAMIC_SAMPLING hint and not the OPTIMIZER_DYNAMIC_SAMPLING parameter). But beware as this takes a 100% sample of the pipelined function’s rowsource regardless of the sampling level used (between 2 and 10).

        What I wanted to show here for Radino’s benefit was the only supported method I know of and that is to use the Extensible Optimiser and the ODCIStatsTableFunction method available from 10g. I’ve put in a quick demo below – the full write-up will be on my website in a few days time.


        SQL> -- 1. sample collection type and pipelined function
        SQL>
        SQL> CREATE TYPE collection_type AS TABLE OF NUMBER;
         2  /

        Type created.

        SQL>
        SQL> CREATE FUNCTION pipelined_function(
         2                  p_cardinality IN INTEGER
         3                  ) RETURN collection_type PIPELINED AS
         4  BEGIN
         5     FOR i IN 1 .. p_cardinality LOOP
         6        PIPE ROW (i);
         7     END LOOP;
         8     RETURN;
         9  END pipelined_function;
        10  /

        Function created.

        SQL>
        SQL> -- 2. statistics type to supply cardinality to the CBO
        SQL>
        SQL> CREATE TYPE cardinality_type AS OBJECT (
         2
         3     dummy NUMBER,
         4
         5     STATIC FUNCTION ODCIGetInterfaces (
         6                     p_interfaces OUT SYS.ODCIObjectList
         7                     ) RETURN NUMBER,
         8
         9     STATIC FUNCTION ODCIStatsTableFunction (
        10                     p_function    IN  SYS.ODCIFuncInfo,
        11                     p_stats       OUT SYS.ODCITabFuncStats,
        12                     p_args        IN  SYS.ODCIArgDescList,
        13                     p_cardinality IN INTEGER
        14                     ) RETURN NUMBER
        15  );
        16  /

        Type created.

        SQL>
        SQL> CREATE TYPE BODY cardinality_type AS
         2
         3     STATIC FUNCTION ODCIGetInterfaces (
         4                     p_interfaces OUT SYS.ODCIObjectList
         5                     ) RETURN NUMBER IS
         6     BEGIN
         7        p_interfaces := SYS.ODCIObjectList(
         8                           SYS.ODCIObject ('SYS', 'ODCISTATS2')
         9                           );
        10        RETURN ODCIConst.success;
        11     END ODCIGetInterfaces;
        12
        13     STATIC FUNCTION ODCIStatsTableFunction (
        14                     p_function    IN  SYS.ODCIFuncInfo,
        15                     p_stats       OUT SYS.ODCITabFuncStats,
        16                     p_args        IN  SYS.ODCIArgDescList,
        17                     p_cardinality IN INTEGER
        18                     ) RETURN NUMBER IS
        19     BEGIN
        20        p_stats := SYS.ODCITabFuncStats(p_cardinality);
        21        RETURN ODCIConst.success;
        22     END ODCIStatsTableFunction;
        23
        24  END;
        25  /

        Type body created.

        SQL>
        SQL> -- 3. associate the function with the interface type
        SQL>
        SQL> ASSOCIATE STATISTICS WITH FUNCTIONS pipelined_function USING cardinality_type;

        Statistics associated.

        SQL>
        SQL> -- 4. test it - generate 7 rows
        SQL>
        SQL> set autotrace on
        SQL>
        SQL> SELECT * FROM TABLE(pipelined_function(7));

        COLUMN_VALUE
        ------------
                  1
                  2
                  3
                  4
                  5
                  6
                  7

        7 rows selected.

        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 1115071714

        ------------------------------------------------------------------------
        | Id  | Operation                         | Name               | Rows  |
        ------------------------------------------------------------------------
        |   0 | SELECT STATEMENT                  |                    |     7 |
        |   1 |  COLLECTION ITERATOR PICKLER FETCH| PIPELINED_FUNCTION |       |
        ------------------------------------------------------------------------

        As I mentioned, more detail will be available in a few days.

        Regards,
        Adrian

        Comment by Adrian Billington — June 16, 2009 @ 7:26 pm BST Jun 16,2009 | Reply

        • Adrian,

          Thanks for the example.
          I’ve done a quick edit to make it more readable – and I’ve re-activated the link to your blog to make it easier for people to get back to your website in a few days, but feel free to put a new comment here when you publish the article

          Comment by Jonathan Lewis — June 20, 2009 @ 5:29 pm BST Jun 20,2009

        • Jonathan,

          Thanks for re-formatting the example – I used the code tags suggested on your How To Comment page and saw how I’d failed to take care of the whitespace after I had submitted it!

          For those who are interested, my article on table/pipelined function cardinality is now available.

          Regards,
          Adrian

          Comment by Adrian Billington — June 21, 2009 @ 7:28 pm BST Jun 21,2009

  3. Jonathan,

    thank you very much for pointing out the new view v$sql_hint.
    This seems to answer a question I once asked you: how to get all available hints
    (
    http://jonathanlewis.wordpress.com/2007/01/16/full-hinting/#comment-1804
    )

    Comment by Sokrates — June 15, 2009 @ 7:37 pm BST Jun 15,2009 | Reply

    • I used to refer to V$RESERVED_WORDS for all hints in older versions
      srivenu

      Comment by srivenu kadiyala — June 16, 2009 @ 5:33 am BST Jun 16,2009 | Reply

  4. Following Timur’s comment about wanting more information on undocumented hints, here’s an idea for a collaborative effort.

    If there’s a hint that you want to know more about, leave a comment.

    If a hint appeared on the list and you know where there’s some OFFICIAL documentation, leave a comment.

    If a hint appears on the list and you know how it works, or where some unofficial documentation is, leave a comment.

    Remember to use the Reply button to link related comments together.

    If this effort does take off and the chain gets messy, I may end up collating the results and merging the content of some of the comments.

    Comment by Jonathan Lewis — June 15, 2009 @ 10:23 pm BST Jun 15,2009 | Reply

    • Jonathan,

      good initiative to combine info about hints in one place. Probably some wiki engine would suit better for such collaborative task.
      My “Hints wish list” to know something about:

      BITMAP_TREE
      BUFFER
      CACHE_CB
      CUBE_GB
      DB_VERSION
      FBTSCAN
      INCLUDE_VERSION
      MODEL_*
      NESTED_TABLE_FAST_INSERT
      NLJ_*
      NUM_INDEX_KEYS - found Bug 5152325 - Enh: Add NUM_INDEX_KEYS hint
      OLD_PUSH_PRED
      OVERFLOW_NOMOVE
      PX_JOIN_FILTER - most likely to force bloom filter usage in PX join
      SUBQUERY_PRUNING - probably partition prunning based on subquery results
      

      Comment by Timur Akhmadeev — June 16, 2009 @ 9:39 am BST Jun 16,2009 | Reply

  5. Adding to Timur’s wishlist here are two I would like to see (do tell me if they are already possible) :

    DYNAMIC_SAMPLING (table_name, blocks)
    – dynamic sampling for the table, ignoring existing table statistics

    IGNORE_BIND_PEEKED (table_name)
    – ignore previously peeked binds for this table and re-peek in this execution.

    Hemant

    Comment by Hemant K Chitale — June 22, 2009 @ 3:15 am BST Jun 22,2009 | Reply

    • Hemant,

      The dynamic_sampling() hint allows you to specify a table and a level and samples blocks from that table unconditionally. Level 1 is (by default 32 blocks, and each level doubles the sample up to level 9, then level 10 is the whole table).

      A directive to peek sounds nice. The adaptive cursor sharing in 11g is roughly in that area – except it has to do it wrong before it tries again. And if you’re using cursor_sharing=force there is a cursor_sharing_exact hint (from 9i) which, again, is working in the same area.

      Comment by Jonathan Lewis — June 24, 2009 @ 7:37 am BST Jun 24,2009 | Reply

  6. Re: USE_HASH_AGGREGATION

    Apparently, the hint has no effect when DML is involved:

    sqlplus xyz/xyz
    
    SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 7 18:41:59 2010
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> set line 10000
    SQL> set autot on explain
    SQL> create table t1(x int, y int);
    
    Table created.
    
    SQL> select x, count(unique y) from t1 group by x;
    
    no rows selected
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3640378487
    
    ----------------------------------------------------------------------------------
    | Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |           |     1 |    26 |     3  (34)| 00:00:01 |
    |   1 |  HASH GROUP BY       |           |     1 |    26 |     3  (34)| 00:00:01 |
    |   2 |   VIEW               | VM_NWVW_1 |     1 |    26 |     3  (34)| 00:00:01 |
    |   3 |    HASH GROUP BY     |           |     1 |    26 |     3  (34)| 00:00:01 |
    |   4 |     TABLE ACCESS FULL| T1        |     1 |    26 |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL> create table t2 as select * from t1;
    
    Table created.
    
    SQL> insert into t2 select/*+USE_HASH_AGGREGATION*/ x, count(unique y) from t1 group by x;
    
    0 rows created.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3946799371
    
    ---------------------------------------------------------------------------------
    | Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT         |      |     1 |    26 |     3  (34)| 00:00:01 |
    |   1 |  LOAD TABLE CONVENTIONAL | T2   |       |       |            |          |
    |   2 |   SORT GROUP BY          |      |     1 |    26 |     3  (34)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL     | T1   |     1 |    26 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL>
    
    

    This is a pity because in our specific scenario, doing unique count through hashing is 2.4 times faster than by sorting (sort group by). Another database whose name shall remain unspoken does hash group by in the insert/select statement just fine. Is there some hidden hint per chance that would force Oracle to do the proper thing ?

    Thanks.

    Comment by Val — March 7, 2010 @ 11:56 pm BST Mar 7,2010 | Reply

    • 10053 gives a hint as to what can be done:

      select x, count(y) cnt from
      (
      select x, y from t1 group by x, y
      )
      group by x

      The above is what Oracle does with the original ‘select x, count(unique y) from t1 group by x’.

      Adding an ‘insert’ in front the re-written statement does not break the plan any more. Why Oracle abandons a more performant ‘hash group by’ plan when you add an insert to the original statement is unclear.

      Comment by Val — March 8, 2010 @ 7:06 pm BST Mar 8,2010 | Reply

    • @Val

      This is bug 9148171. It’s since been fixed so it works with CTAS.

      explain plan for
      create table foo
      as
      SELECT deptno, mgr, 
             sum(sal) sum_sal, count(sal) cnt_sal, 
             max(hiredate) max_hd, 
             count(distinct hiredate) dist_hd_cnt 
      FROM emp 
      GROUP BY deptno, mgr;
      
      select * from table(dbms_xplan.display(format=>'outline basic')); 
      
      Plan hash value: 192513176
      
      --------------------------------------------------
      | Id  | Operation                     | Name     |
      --------------------------------------------------
      |   0 | CREATE TABLE STATEMENT        |          |
      |   1 |  LOAD AS SELECT               | FOO      |
      |   2 |   HASH GROUP BY               |          |
      |   3 |    VIEW                       | VW_DAG_0 |
      |   4 |     HASH GROUP BY             |          |
      |   5 |      TABLE ACCESS STORAGE FULL| EMP      |
      --------------------------------------------------
      
      Outline Data
      -------------
      
        /*+
            BEGIN_OUTLINE_DATA
            USE_HASH_AGGREGATION(@"SEL$5771D262")
            FULL(@"SEL$5771D262" "EMP"@"SEL$1")
            USE_HASH_AGGREGATION(@"SEL$C33C846D")
            NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D")
            TRANSFORM_DISTINCT_AGG(@"SEL$1")
            OUTLINE(@"SEL$5771D262")
            OUTLINE(@"SEL$1")
            OUTLINE_LEAF(@"SEL$C33C846D")
            TRANSFORM_DISTINCT_AGG(@"SEL$1")
            OUTLINE_LEAF(@"SEL$5771D262")
            NO_PARALLEL
            ALL_ROWS
            DB_VERSION('11.2.0.1')
            OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
            IGNORE_OPTIM_EMBEDDED_HINTS
            END_OUTLINE_DATA
        */
      

      Comment by Greg Rahn — June 7, 2010 @ 4:55 am BST Jun 7,2010 | Reply

  7. can somebody help me to tunup the query utilizing hints?

    [SQL statement removed by JPL]

    Thanks in advance
    Igor

    Comment by Igor — May 5, 2011 @ 4:56 pm BST May 5,2011 | Reply

    • Igor,

      This is not the forum for getting SQL fixed. You might try the OTN Database or SQL forum.

      Before you do so, though, read up on using dbms_xplan to generate a complete plan, and especially the option for collecting and reporting rowsource execution statistics to see where the work was done. At present If you select dbms_xplan from the Categeries drop-down list at the top right of this block you will find 12 articles on the topic.

      Comment by Jonathan Lewis — May 7, 2011 @ 8:05 am BST May 7,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,454 other followers