Oracle Scratchpad

November 30, 2010

Collection Costs

Filed under: Bugs,Execution plans,Hints,Oracle — Jonathan Lewis @ 7:22 am GMT Nov 30,2010

Here’s an extract from an execution plan I found on a client site recently. I’ve collapsed lines 5 to 42 into a single line representing the rowsource produced by a fairly messy execution plan, leaving just the last three stages of execution on view. Each of three operations joins the same collection variable (using the table() operator) to the row source – once through a hash join, then twice more (joining to two other columns) through nested loop outer joins:

The resulting estimates of row counts and costs are quite entertaining and, fortunately, not very accurate:

| Id  | Operation                                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                           |        |       |       |  5865M(100)|          |
|   1 |  NESTED LOOPS OUTER                        |        |   478G|   207T|  5865M  (1)|999:59:59 |
|   2 |   NESTED LOOPS OUTER                       |        |  5830M|  1895G|    18M  (1)| 50:38:00 |
|*  3 |    HASH JOIN                               |        |    71M|    14G|   266K  (2)| 00:44:49 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH      |        |       |       |            |          |
|   5 |     {join-based row source}                |        | 87049 |    18M|   266K  (2)| 00:44:49 |
|  43 |   VIEW                                     |        |    82 | 10578 |            |          |
|* 44 |     COLLECTION ITERATOR PICKLER FETCH      |        |       |       |            |          |
|  45 |   VIEW                                     |        |    82 | 10578 |            |          |
|* 46 |    COLLECTION ITERATOR PICKLER FETCH       |        |       |       |            |          |

The system was running, and the optimizer clearly has some problems (still) with the arithmetic of collection types.

Here’s a suggestion for use with the table() operator, by the way. The optimizer assumes that the number of rows produced by the table() operator will be roughly the same as the number of bytes in the default block size – and this can lead to some very poor execution plans (watch out, by the way, if someone tells you to rebuild your database with a new default block size – there may be some unexpected side effects). As a general principle I advise people that if they have a reasonable idea of the number of rows that they will be passing into a query of this type that they tell the optimizer what that number. The simplest way of doing this is to change your SQL from something like this:

        tableX                        ty,
        table(collection_variable)    tc,
        tableY                        ty,

to something like this – where you introduce an inline view with a /*+ cardinality */ hint:

        tableX                        tx,
        select  /*+ cardinality(t 20) */ 
        from    table(collection_variable)  t
        )                              tc,
        tableY                        ty,

It’s possible to use a “global” hint in the main query with a query block name referencing the inline view, of course – but it can be very hard to make this work correctly in more complex cases – especially if you are using ANSI SQL – so a simple inline approach with a hint in the view is probably a much safer bet.


  1. You could also use extensible optimiser which is supported but not very comfortable way how to tell it to CBO.
    Adrian Billington has some nice posts on his site:

    Comment by Radoslav Golian — November 30, 2010 @ 8:50 am GMT Nov 30,2010 | Reply

  2. So where is this documented?

    Comment by Stew Ashton — November 30, 2010 @ 10:30 am GMT Nov 30,2010 | Reply

    • Stew,

      I’d like to give you the metalink document ID for the article that supplies the complete description of how the cardinality hint works – but at the moment I can’t find it.

      Comment by Jonathan Lewis — November 30, 2010 @ 11:30 am GMT Nov 30,2010 | Reply

      • Jonathan, thank you but I have not yet obtained access to Metalink from my company. I am glad to know such information exists.

        Comment by Stew Ashton — November 30, 2010 @ 1:27 pm GMT Nov 30,2010 | Reply

        • Stew,

          At the moment the best I can say is that it existed once, and appeared to be a deliberate note to explain the hint and its use.

          Right now the best I can do (apart from the reference to Tom’s comments supplied by Narenda) is a couple of bug reports where a user has called in a bug relating the the cardinality() hint and not been told: “unsupported feature”. For example, bugs: 7758650, 9024541, 4137713 (which is a multi table example), and 9499541 (where a suggested workaround is to use the cardinality hint).

          I’m at the UKOUG conference right now with some of the heavy hitters from the Optimizer team, so I’ll see what they say about it.

          Comment by Jonathan Lewis — November 30, 2010 @ 4:47 pm GMT Nov 30,2010

        • I’ve just been sent an email with a document ID.
          MOS (Metalink) note 29236.1 reports the cardinality hint.

          There are references to 7.2 and 8.1 in the note – so it has been around a long time; I think it must have been hidden for the last few years.

          Comment by Jonathan Lewis — May 19, 2011 @ 10:46 am BST May 19,2011

  3. Hi.

    The CARDINALITY hint is undocumented. I know it works but…

    An equivalent and supported option for table functions is to use the extensible optimizer. See Adrian Billington’s article:



    Comment by Tim Hall — November 30, 2010 @ 10:43 am GMT Nov 30,2010 | Reply

  4. Jonathan,

    As this is version, doesn’t the new feature of “cardinality feedback” mechanism help optimizer here ?

    Comment by Narendra — November 30, 2010 @ 1:35 pm GMT Nov 30,2010 | Reply

    • Here Tom actually explains the same with an example that uses collection.

      Comment by Narendra — November 30, 2010 @ 1:47 pm GMT Nov 30,2010 | Reply

    • Narenda,

      Good point – I’ll have to check to see why it didn’t. Whether it does or not, though, there’s an important principle that the optimizer seems to be doing something very strange in this particular code path. The reported paths are NL and HJ, but the arithmetic looks like Cartesian Merge Join.

      Thanks for the Tom Kyte reference

      Comment by Jonathan Lewis — November 30, 2010 @ 4:50 pm GMT Nov 30,2010 | Reply

  5. Oracle versions 11gR2 and can peek at the number of rows in collection if it is passed as a bind, so the issue could pass unnoticed in these releases.

    The optimizer assumes that the number of rows produced by the table() operator will be roughly the same as the number of bytes in the default block size

    I do remember reading a note on the Metalink saying that the number is derived as the number of 100 bytes length rows in 100 blocks. Still can’t find this note in MOS, so this may not be true.

    BTW, the VIEW operator in the step 44 has cardinality of 82 – and IIRC this number is also kind of “magic” in a way that after instance restart you may see it changed to something different (I was told this by co-worker; and don’t remember all the details, but I’m pretty sure there’s some oddity in this number too).

    Comment by Timur Akhmadeev — November 30, 2010 @ 5:59 pm GMT Nov 30,2010 | Reply

  6. […] Jonathan Lewis, on Oracle Scratchpad, blogs about optimizer issues with collection types and suggests a work-around. […]

    Pingback by Log Buffer #208, A Carnival of the Vanities for DBAs | The Pythian Blog — December 6, 2010 @ 5:12 am GMT Dec 6,2010 | Reply

  7. Cardinality Feedback was introduced in 11gR2:

    So it’s not available in

    Comment by DBA — June 13, 2011 @ 2:59 pm BST Jun 13,2011 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by