Oracle Scratchpad

July 26, 2016

Lost Concatenation

Filed under: Bugs,CBO,Oracle — Jonathan Lewis @ 10:46 am GMT Jul 26,2016

This note models one feature of a problem that came up at a client site recently from a system running 12.1.0.2 – a possible bug in the way the optimizer handles a multi-column in-list that can lead to extremely bad cardinality estimates.

The original query was a simple three table join which produced a bad plan with extremely bad cardinality estimates; there was, however, a type-mismatch in one of the predicates (of the form “varchar_col = numeric”), and when this design flaw was addressed the plan changed dramatically and produced good cardinality estimates. The analysis of the plan, 10053 trace, and 10046 trace files done in-house suggested that the problem might relate in some way to an error in the handling of SQL Plan Directives to estimate cardinalities.

This was one of my “solve it in a couple of hours over the internet” assignments and I’d been sent a sample of the original query with the 10046 and 10053 trace files, and a modified version of the query that bypassed the problem, again including the 10046 and 10053 trace files, with a request to explain the problem and produce a simple test case to pass to Oracle support.

The first thing I noticed was that there was something very strange about the execution plan. Here’s the query and plan in from my simplified model, showing the same anomaly:


select  /*+ no_expand */
        count(*)
from    t1, t2
where
        t2.shipment_order_id = t1.order_id
and     (t1.id, t2.v1) in ( (5000, 98), (5000, 99))
;

-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |       |       |       |   331 (100)|          |
|   1 |  SORT AGGREGATE                       |       |     1 |    19 |       |            |          |
|*  2 |   HASH JOIN                           |       |     1 |    19 |  2056K|   331   (5)| 00:00:01 |
|   3 |    TABLE ACCESS FULL                  | T2    |   100K|   878K|       |   219   (3)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |   100K|   976K|       |     2   (0)| 00:00:01 |
|   5 |     BITMAP CONVERSION TO ROWIDS       |       |       |       |       |            |          |
|   6 |      BITMAP OR                        |       |       |       |       |            |          |
|   7 |       BITMAP CONVERSION FROM ROWIDS   |       |       |       |       |            |          |
|*  8 |        INDEX RANGE SCAN               | T1_PK |       |       |       |     1   (0)| 00:00:01 |
|   9 |       BITMAP CONVERSION FROM ROWIDS   |       |       |       |       |            |          |
|* 10 |        INDEX RANGE SCAN               | T1_PK |       |       |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."SHIPMENT_ORDER_ID"="T1"."ORDER_ID")
       filter((
                  (TO_NUMBER("T2"."V1")=98 AND "T1"."ID"=5000) 
               OR (TO_NUMBER("T2"."V1")=99 AND "T1"."ID"=5000)
       ))
   8 - access("T1"."ID"=5000)
  10 - access("T1"."ID"=5000)

Before going on I meed to remind you that this is modelling a production problem. I had to use a hint to block a transformation that the optimizer wanted to do with my data set and statistics, I’ve got a deliberate type-mismatch in the data definitions, and there’s a simple rewrite of the SQL that would ensure that Oracle does something completely different).

The thing that caught my eye was the use of the bitmap transformation (operations 5,7,9) using exactly the same index range scan twice (operations 8,10). Furthermore, though not visible in the plan, the index in question was (as the name suggests) the primary key index on the table and it was a single column index – and “primary key = constant” should produce an “index unique scan” not a range scan.

Once you’ve added in the fact that operations 8 and 10 are the same “primary key = constant” predicates, you can also pick up on the fact that the cardinality calculation for the table access to table t1 can’t possibly produce more than one row – but it’s reporting a cardinality estimate of 100K rows (which happens to be the number of rows in the table.)

As a final point, you can see that there are no “Notes” about Dynamic Statistics or SQL Directives – this particular issue is not caused by anything to do with 12c sampling. In fact, having created the model, I ran it on 11.2.0.4 and got the same strange bitmap conversion and cardinality estimate. In the case of the client, the first pass the optimizer took went through exactly the same sort of process and produced a plan which was (probably) appropriate for a query where the driving table was going to produce (in their case) an estimated 4 million rows – but not appropriate for the actual 1 row that should have been identified.

In my example, if I allowed concatenation (i.e. removed the no_expand hint) I got the following plan:


------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |       |       |       |     8 (100)|          |
|   1 |  SORT AGGREGATE                        |       |     1 |    19 |            |          |
|   2 |   CONCATENATION                        |       |       |       |            |          |
|   3 |    NESTED LOOPS                        |       |     1 |    19 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID        | T1    |     1 |    10 |     2   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN                 | T1_PK |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2    |     1 |     9 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN                  | T2_I1 |     1 |       |     1   (0)| 00:00:01 |
|   8 |    NESTED LOOPS                        |       |     1 |    19 |     4   (0)| 00:00:01 |
|   9 |     TABLE ACCESS BY INDEX ROWID        | T1    |     1 |    10 |     2   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN                 | T1_PK |     1 |       |     1   (0)| 00:00:01 |
|* 11 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2    |     1 |     9 |     2   (0)| 00:00:01 |
|* 12 |      INDEX RANGE SCAN                  | T2_I1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."ID"=5000)
   6 - filter(TO_NUMBER("T2"."V1")=99)
   7 - access("T2"."SHIPMENT_ORDER_ID"="T1"."ORDER_ID")
  10 - access("T1"."ID"=5000)
  11 - filter((TO_NUMBER("T2"."V1")=98 AND (LNNVL(TO_NUMBER("T2"."V1")=99) OR
              LNNVL("T1"."ID"=5000))))
  12 - access("T2"."SHIPMENT_ORDER_ID"="T1"."ORDER_ID")

This is a much more appropriate plan – and similar to the type of plan the client saw when they eliminated the type-mismatch problem (I got a completely different plan when I used character values ’98’ and ’99’ in the in-list or when I used a numeric column with numeric literals).

Examining my 10053 trace file I found the following:

  • In the BASE STATISTICAL INFORMATION, the optimizer had picked up column statistics about the order_id column, but not about the id column in the in-list – this explained why the cardinality estimate was 100K, Oracle had “lost” the predicate.
  • In the “SINGLE TABLE ACCESS PATH”, the optimizer had acquired the statistics about the id column and calculated the cost of using the t1_pk index to access the table for a single key (AllEqUnique), then calculated the cost of doing a bitmap conversion twice (remember we have two entries in the in-list – it looks like the optimizer has “rediscovered” the predicate). But it had still kept the table cardinality of 4M.

After coming up with a bad plan thanks to this basic cardinality error, the 10053 trace file for the client’s query then went on to consider or-expansion (concatenation). Looking at this part of their trace file I could see that the BASE STATISTICAL INFORMATION now included the columns relevant to the in-list and the SINGLE TABLE ACCESS PATH cardinalities were suitable. Moreover when we got to the GENERAL PLANS the join to the second table in the join order showed a very sensible cost and cardinality – unfortunately, having been sensible up to that point, the optimizer then decided that an SQL Plan Directive should be used to generate a dynamic sampling query to check the join cardinality and the generated query again “lost” the in-list predicate, resulting in a “corrected” cardinality estimate of 6M instead of a correct cardinality estimate of 1. As usual, this massive over-estimate resulted in Oracle picking the wrong join method with a huge cost for the final join in the client’s query – so the optimizer discarded the or-expansion transformation and ran with the bad bitmap/hash join plan.

Bottom line for the client – we may have seen the same “lose the predicate” bug appearing in two different ways, or we may have seen two different “lose the predicate” bugs – either way a massive over-estimate due to “lost” predicates during cardinality calculations resulted in Oracle picking a very bad plan.

Footnote:

If you want to do further testing on the model, here’s the code to generate the data:


create table t1
nologging
as
with generator as (
        select  rownum id
        from    dual
        connect by
                level <= 1e4
)
select
        rownum                                  id,
        rownum                                  order_id,
        rpad('x',100)                           padding
from
        generator, generator
where
        rownum <= 1e5
;

execute dbms_stats.gather_table_stats(user,'t1')

alter table t1 modify order_id not null;
alter table t1 add constraint t1_pk primary key(id);


create table t2
nologging
as
with generator as (
        select  rownum id
        from    dual
        connect by
                level <= 1e4
)
select
        rownum                                  shipment_order_id,
        mod(rownum-1,1000)                      n1,
        cast(mod(rownum-1,1000) as varchar2(6)) v1,
        rpad('x',100)                           padding
from
        generator, generator
where
        rownum <= 1e5
;

execute dbms_stats.gather_table_stats(user,'t2')

alter table t2 modify shipment_order_id not null;
create index t2_i1 on t2(shipment_order_id);

The interesting question now is WHY does Oracle lose the predicate – unfortunately my model may be too simplistic to allow us to work that out, but it might be sufficient to make it easy for an Oracle developer to see what’s going on and how best to address it. There is one bug on MoS (23343961) that might be related in some way, but I wasn’t convinced that the description was really close enough.

Update

This issue is now recorded on MoS as: Bug 24350407 : WRONG CARDINALITY ESTIMATION IN PRESENCE OF BITMAP OR

 

5 Comments »

  1. Hi Jonathan,

    It also seems optimizer severe under-estimate BITMAP CONVERSION TO ROWIDS plan step (or even doesn’t estimate it at all).

    Let me share a simplified use case:

    
    select unit_id from IDXAUX m
    
    Plan hash value: 2648162211
    
    ---------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
    ---------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |            |      1 |        |       |  2037 (100)|          |     10M|00:00:15.17 |
    |   1 |  BITMAP CONVERSION TO ROWIDS |            |      1 |     10M|    38M|  2037   (0)| 00:00:41 |     10M|00:00:15.17 |
    |   2 |   BITMAP INDEX FAST FULL SCAN| IDXAUX_IX1 |      1 |        |       |            |          |   6426 |00:00:00.04 |
    ---------------------------------------------------------------------------------------------------------------------------
    
    
    select distinct unit_id from IDXAUX  m
    
    Plan hash value: 3029051316
    
    ---------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | 
    ---------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |            |      1 |        |       |  2269 (100)|          |   4962 |00:00:00.04 |
    |   1 |  HASH UNIQUE                 |            |      1 |   4962 | 19848 |  2269  (11)| 00:00:46 |   4962 |00:00:00.04 |
    |   2 |   BITMAP INDEX FAST FULL SCAN| IDXAUX_IX1 |      1 |     10M|    38M|  2037   (0)| 00:00:41 |   6426 |00:00:00.02 |
    ---------------------------------------------------------------------------------------------------------------------------
    

    you can see the same cost (2037) for index access part, however rowsource statistics show that BITMAP CONVERSION TO ROWIDS step consumes majority of resources.

    Comment by dmitryremizov — August 2, 2016 @ 8:22 pm GMT Aug 2,2016 | Reply

    • Dmitry,

      Thanks for the example. I wrote up a slightly more complex example of a very similar effect some months ago, with an example where an internal transformation did a conversion to rowids but the manually written equivalent didn’t: https://jonathanlewis.wordpress.com/2015/01/19/bitmap-counts/

      It’s still fairly easy to find anomalies with Oracle’s handling, or costing, of anything to do with bitmaps.

      Comment by Jonathan Lewis — August 3, 2016 @ 12:24 pm GMT Aug 3,2016 | Reply

      • Thank you very much for the link,

        Totally agree with “redundant conversion, and it may be expensive”
        It looks very similar to my “real life” scenario when I had to stop a query after generation of 0.5TB of temp because of redundant BITMAP CONVERSION TO ROWIDS.

        Comment by dmitryremizov — August 3, 2016 @ 2:46 pm GMT Aug 3,2016 | Reply

  2. Jonathan,

    If you don’t mind here is one more simple example of almost the same stuff:

    select count (distinct a.UNIT_ID) from  
     TESTTBL PARTITION (P_20160805)  a
     
    Plan hash value: 
     
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                         | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |                 |       |       |       |    29M(100)|          |       |       |
    |   1 |  SORT AGGREGATE                   |                 |     1 |   512 |       |            |          |       |       |
    |   2 |   PARTITION HASH ALL              |                 |  8370K|  4087M|       |    29M  (5)|115:46:25 |     1 |    32 |
    |   3 |    VIEW                           | VW_DAG_0        |  8370K|  4087M|       |    29M  (5)|115:46:25 |       |       |
    |   4 |     HASH GROUP BY                 |                 |  8370K|   143M|   374G|    29M  (5)|115:46:25 |       |       |
    |   5 |      PARTITION RANGE SINGLE       |                 |    16G|   280G|       |    23   (0)| 00:00:01 |  1874 |  1874 |
    |   6 |       BITMAP CONVERSION TO ROWIDS |                 |    16G|   280G|       |    23   (0)| 00:00:01 |       |       |
    |   7 |        BITMAP INDEX FAST FULL SCAN| TESTTBL_IDX     |       |       |       |            |          | 59937 | 59968 |
    -----------------------------------------------------------------------------------------------------------------------------
    

    However it is not as awful as optimizer believe :), at least from temp space consumption perspective.

    Comment by dmitryremizov — August 10, 2016 @ 6:19 pm GMT Aug 10,2016 | Reply

    • Dmitry,

      All warnings are useful – I assume this is a local index on unit_id.

      Interesting how the combination of partitioning and bitmaps re-introduces a problem that has been fixed for simple heap tables. (Or, perhaps more accurately, interesting that a fix for the simple heap table case hasn’t got as far as the partitioned case. I can reproduce with a hash-partitioned table, I haven’t tried it yet with a range-partitioned table.)

      I guess the dramatic TempSpc requirement is a very pessimistic 24 * original rowcount – which would be another error introduced by playing with bitmaps.

      Comment by Jonathan Lewis — August 15, 2016 @ 5:47 pm GMT Aug 15,2016 | 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

Blog at WordPress.com.