Search

Top 60 Oracle Blogs

Recent comments

Collection Costs

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 11.1.0.7, 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:


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

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

from
        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.