Oracle 12c and Easter Bunnies

Most of you will think now: What have easter bunnies to do with Oracle 12c? If you attended the Trivadis course SQL Optimizer and Performance Workshop in the last few years, you probably know what I’ writing about.

One of the exercises in this course is to find the best execution plan for the following query, based on the example schema of the course:

SELECT count(prs.firstname),
       count(adr.zip_additional),
       count(mlg.dispatch_date)
FROM   formsofaddress foa,
       persons prs,
       mailings mlg,
       recipients rec,
       addresses adr
WHERE  foa.id = prs.foa_id
AND    prs.id = adr.prs_id
AND    adr.id = rec.adr_id
AND    mlg.id = rec.mlg_id
AND    mlg.product = 'Easter bunny'
AND    adr.zip like '8%'

Without going into details – the future attendees of the course should still have the chance to find the best solution – this exercise is a nice example to show what happens if the cardinality estimated by the Oracle optimizer does not fit the real number of rows. Although I originally wrote this example years ago for the Oracle 8i version of the course, I’m still surprised (and a little bit proud) that the same exercise works since years. Just this week, I teached the course on an Oracle 11.2.0.3 database, and the query above is still a challenge for most of the course participants – and for the optimizer.

At the moment, I’m playing around with some of the new optimizer features of Oracle 12c and wanted to know whether the 12c optimizer can solve the problem of the wrong estimation know. I ran the script easterbunny.sql and realized that the execution plan is still the same as in Oracle 11.2:

---------------------------------------------------------------------------------
| Id  | Operation                       | Name       | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |            |      1 |        |      1 |
|   1 |  SORT AGGREGATE                 |            |      1 |      1 |      1 |
|*  2 |   HASH JOIN                     |            |      1 |   1566 |     35 |
|*  3 |    HASH JOIN                    |            |      1 |   1565 |     35 |
|*  4 |     TABLE ACCESS FULL           | ADDRESSES  |      1 |   6397 |  27211 |
|   5 |     NESTED LOOPS                |            |      1 |        |    324 |
|   6 |      NESTED LOOPS               |            |      1 |  61729 |    324 |
|*  7 |       TABLE ACCESS FULL         | MAILINGS   |      1 |      1 |      1 |
|*  8 |       INDEX RANGE SCAN          | REC_MLG_ID |      1 |  49384 |    324 |
|   9 |      TABLE ACCESS BY INDEX ROWID| RECIPIENTS |    324 |  49384 |    324 |
|  10 |    TABLE ACCESS FULL            | PERSONS    |      1 |    154K|    154K|
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PRS"."ID"="ADR"."PRS_ID")
   3 - access("ADR"."ID"="REC"."ADR_ID")
   4 - filter("ADR"."ZIP" LIKE '8%')
   7 - filter("MLG"."PRODUCT"='Easter bunny')
   8 - access("MLG"."ID"="REC"."MLG_ID")

Note
-----
   - this is an adaptive plan

There is one little difference in 12c: The note “this is an adaptive plan” at the end. Adaptive plans in Oracle 12c are execution plans with different choices, called subplans. The optimizer is able to decide at runtime which of the subplans should be executed. Unfortunately the subplans for the easter bunny exercise do not really solve our problem of the wrong estimation. But another feature of the Oracle 12c optimizer finally solves the problem: Dynamic statistics.

With dynamic statistics, the optimizer is able to estimate the cardinality not only based on the statistics of all involved tables, but also on join predicates. This is exactly what is required here. Dynamic statistics are enabled with the new level 11 of the parameter optimizer_dynamic_sampling.

SQL> alter session set optimizer_dynamic_sampling = 11;

After enabling the feature, I ran the script easterbunny.sql again and was very pleased:

------------------------------------------------------------------------------------------
| Id  | Operation                                | Name       | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |            |      1 |        |      1 |
|   1 |  SORT AGGREGATE                          |            |      1 |      1 |      1 |
|   2 |   NESTED LOOPS                           |            |      1 |        |     35 |
|   3 |    NESTED LOOPS                          |            |      1 |     34 |     35 |
|   4 |     NESTED LOOPS                         |            |      1 |     34 |     35 |
|   5 |      NESTED LOOPS                        |            |      1 |    326 |    324 |
|*  6 |       TABLE ACCESS FULL                  | MAILINGS   |      1 |      1 |      1 |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| RECIPIENTS |      1 |    326 |    324 |
|*  8 |        INDEX RANGE SCAN                  | REC_MLG_ID |      1 |  49384 |    324 |
|*  9 |      TABLE ACCESS BY INDEX ROWID         | ADDRESSES  |    324 |      1 |     35 |
|* 10 |       INDEX UNIQUE SCAN                  | ADR_PK     |    324 |      1 |    324 |
|* 11 |     INDEX UNIQUE SCAN                    | PRS_PK     |     35 |      1 |     35 |
|  12 |    TABLE ACCESS BY INDEX ROWID           | PERSONS    |     35 |      1 |     35 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - filter("MLG"."PRODUCT"='Easter bunny')
   8 - access("MLG"."ID"="REC"."MLG_ID")
   9 - filter("ADR"."ZIP" LIKE '8%')
  10 - access("ADR"."ID"="REC"."ADR_ID")
  11 - access("PRS"."ID"="ADR"."PRS_ID")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - this is an adaptive plan

The execution plan contains the perfect join order and join methods for our example query. This execution plan is what most course attendees usually have at the end of the workshop, but implemented with several hints. In Oracle 12c the exercise is now solved by the optimizer. Manual intervention using optimizer hints are not needed anymore.

The good news is that the optimizer in Oracle 12c is now able to estimate more appropriate values for the cardinality of joins. The bad news is that we have to write a new exercise for our course because the optimizer is too smart now.

This is just one little example of optimizer enhancements in Oracle 12c. More about the optimizer extensions and many other features of Oracle 12c are explained in the Trivadis TechnoCircle New Features Oracle Database 12c.

Advertisements

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