Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Chris.

Asked: November 26, 2015 - 5:10 pm UTC

Last updated: December 16, 2015 - 1:20 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hello Chris, Connor, and Tom,

I have a question regarding how the Oracle 12c optimizer is coming up with cardinality estimates.

me@db> select banner from v$version;
 
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
 
 
Elapsed: 00:00:00.21
 
 
me@db> explain plan for
  2  select *
  3  from   t_statement  ts
  4  join   t_user       tu  on  ts.last_update_user_pk = tu.user_pk
  5  where  ts.invoice_id = :var_invoice_id
  6  ;
 
 
Explained.
 
 
Elapsed: 00:00:00.13
me@db>
me@db> select *
  2  from   table(dbms_xplan.display)
  3  ;
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 2154571128
 
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                           |    59 | 68499 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                         |                           |    59 | 68499 |    10   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                           |    59 | 68499 |    10   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_STATEMENT               |     3 |  2862 |     7   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | T_STATEMENT_BILLING_IDX01 |    10 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                  | T_USER_PK                 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | T_USER                    |    19 |  3933 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 
   3 - filter("TS"."LAST_UPDATE_USER_PK" IS NOT NULL)
   4 - access("TS"."INVOICE_ID"=TO_NUMBER(:VAR_INVOICE_ID))
   5 - access("TS"."LAST_UPDATE_USER_PK"="TU"."USER_PK")
 
 
20 rows selected.
 
 
Elapsed: 00:00:00.22
me@db>


Here's my interpretation of the plan:
- At ID 4 in the plan we are range scanning an index and the optimizer is estimating 10 rows to be returned - This is a good estimate. There is on average 10 rows in the T_STATEMENT table per INVOICE_ID.
- At ID 3 in the plan we are further filtering the rows obtained in #4, whittling it down to 3 rows - Again, this is a good estimate as far as the actual data is concerned.
- At ID 2 in the plan, for each of the rows from #3 we are going to do a scan of a unique index at ID 5, which happens to be the primary key index of the table T_USER, and the estimate is 1 row from T_USER for each of the rows in #3. Good stuff.
- At ID 1 in the plan, for each row we're now going to use the ROWID obtained from the index unique scan in #5 to get the actual row data via ID 6 "Table Access By Index ROWID", and an estimate of 19 rows???

Where is the estimate of 19 rows coming from? I thought we just did a unique index scan 3 times - once for each row obtained from the T_STATEMENT table after the index range scan and filtering.?

This is where the plan loses me. The final cardinality estimate of 59 I believe is some sort of approximation of 3 multiplied by 19 = 57.

Stats are up to date on both of these tables and all the associated indexes.

Here are the relevant (I hope) stats from the data dictionary.

me@db> SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE table_name IN ('T_STATEMENT','T_USER')
  2  /
 
 
TABLE_NAME                         NUM_ROWS LAST_ANALYZED
------------------------------ ------------ --------------------
T_STATEMENT                        39008518 24-NOV-2015 21:08:48
T_USER                               130292 24-NOV-2015 21:26:17
 
 
Elapsed: 00:00:00.16
me@db>
me@db> SELECT table_name, column_name, last_analyzed, sample_size, num_nulls, num_distinct, density FROM dba_tab_columns WHERE table_name IN ('T_STATEMENT','T_USER') AND column_name IN ('USER_PK','LAST_UPDATE_USER_PK')
  2  /
 
 
TABLE_NAME                     COLUMN_NAME                    LAST_ANALYZED         SAMPLE_SIZE    NUM_NULLS NUM_DISTINCT   DENSITY
------------------------------ ------------------------------ -------------------- ------------ ------------ ------------ ---------
T_USER                         USER_PK                        24-NOV-2015 21:26:16         8591            0       130292   .000008
T_STATEMENT                    LAST_UPDATE_USER_PK            24-NOV-2015 21:04:37         6358     27302792         9286   .000061
 
 
Elapsed: 00:00:00.32
me@db>
me@db> SELECT table_name, uniqueness, index_name, last_analyzed, sample_size, num_rows FROM dba_indexes WHERE index_name  = 'T_USER_PK'
  2  /
 
 
TABLE_NAME                     UNIQUENESS           INDEX_NAME                     LAST_ANALYZED         SAMPLE_SIZE     NUM_ROWS
------------------------------ -------------------- ------------------------------ -------------------- ------------ ------------
T_USER                         UNIQUE               T_USER_PK                      24-NOV-2015 21:26:33       130292       130292
 
 
Elapsed: 00:00:00.14
me@db>
me@db> SELECT table_name, column_position, column_name FROM dba_ind_columns WHERE index_name = 'T_USER_PK'
  2  /
 
 
TABLE_NAME                     COLUMN_POSITION COLUMN_NAME
------------------------------ --------------- ------------------------------
T_USER                                       1 USER_PK
 
 
Elapsed: 00:00:00.15
me@db>


Are you able to shed some light on this?

Perhaps worth mentioning is that generating the explain plan with the hint "optimizer_features_enable('11.2.0.4')" produces the plan and cardinalities that I would expect.

me@db> explain plan for
  2  select /*+ optimizer_features_enable('11.2.0.4') */*
  3  from   t_statement  ts
  4  join   t_user       tu  on  ts.last_update_user_pk = tu.user_pk
  5  where  ts.invoice_id = :var_invoice_id
  6  ;
 
 
Explained.
 
 
Elapsed: 00:00:00.11
me@db>
me@db> select *
  2  from   table(dbms_xplan.display)
  3  ;
 
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 278994850
 
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |     4 |  4644 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                           |       |       |            |       |
|   2 |   NESTED LOOPS                |                           |     4 |  4644 |    10   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T_STATEMENT               |     3 |  2862 |     7   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T_STATEMENT_BILLING_IDX01 |    10 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | T_USER_PK                 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T_USER                    |     1 |   207 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 
   3 - filter("TS"."LAST_UPDATE_USER_PK" IS NOT NULL)
   4 - access("TS"."INVOICE_ID"=TO_NUMBER(:VAR_INVOICE_ID))
   5 - access("TS"."LAST_UPDATE_USER_PK"="TU"."USER_PK")
 
 
20 rows selected.
 
 
Elapsed: 00:00:00.21
me@db>


Thanks in advance.

-Chris

and Connor said...

I'm not disputing your findings, but curiously, I'm struggling to reproduce it

SQL> create table txns ( pk int, u int, data char(20), invoice int);

Table created.

SQL> create table users (  u int, data char(100));

Table created.

SQL> insert /*+ APPEND */ into txns select rownum, case when mod(rownum,3) = 0 then mod(rownum,10000)+10000 end, 'x', mod(rownum,2000000)
  2  from ( select 1 from dual connect by level <= 1000000 ),
  3       ( select 1 from dual connect by level <= 20 );

20000000 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into users select rownum,  'x'
  2  from dual
  3  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> alter table users add primary key ( u ) ;

Table altered.

SQL> alter table txns add primary key ( pk ) ;

Table altered.

SQL> create index txns_ix on txns ( invoice ) ;

Index created.

SQL>
SQL>
SQL> @exp1
  5  select *
  6  from   txns  ts
  7  join   users       tu  on  ts.u = tu.u
  8  where  ts.invoice = :invoice_id
  9  ;

SQL> @exp9

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------
Plan hash value: 3428334775

------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |     3 |   420 |    17   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                         |              |     3 |   420 |    17   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |              |     3 |   420 |    17   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TXNS         |     3 |   102 |    14   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | TXNS_IX      |    10 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                  | SYS_C0014333 |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | USERS        |     1 |   106 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   3 - filter("TS"."U" IS NOT NULL)
   4 - access("TS"."INVOICE"=TO_NUMBER(:INVOICE_ID))
   5 - access("TS"."U"="TU"."U")

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

24 rows selected.

SQL>

SQL> sho parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
compatible                           string      12.1.0.2.0
noncdb_compatible                    boolean     FALSE
SQL> sho parameter optimizer_fea

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
optimizer_features_enable            string      12.1.0.2


If you can fudge my example to get it to "fail" as per yours, we can work from there, but if you can't (or its a time hassle for you to do so) I think you might need to take it up with Support - see if there's anything platform specific.

If you like, send a 10053 trace to asktom_us@oracle.com


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

10053 trace generated

Chris, December 01, 2015 - 4:53 pm UTC

Connor,

Thank you for taking the time to look into this. Much like you I've been unable to replicate the issue with a standalone test case.

I've generated the 10053 trace and will email it to the address provided.

I've also taken a look in the trace file and the plan found within is better, but still not ideal.

Starting SQL statement dump
user_id=98 user_name=RDA module=SQL*Plus action=
sql_id=3z3z0rmxpzmvw plan_hash_value=1776672208 problem_type=3
----- Current SQL Statement for this session (sql_id=3z3z0rmxpzmvw) -----
select *
from   t_statement  ts
join   t_user       tu  on  ts.last_update_user_pk = tu.user_pk
where  ts.invoice_id = :var_invoice_id
sql_text_length=136
sql=select *
from   t_statement  ts
join   t_user       tu  on  ts.last_update_user_pk = tu.user_pk
where  ts.invoice_id = :var_invoice_id
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
------------------------------------------------------------------+-----------------------------------+
| Id  | Operation                              | Name             | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                       |                  |       |       |     9 |           |
| 1   |  NESTED LOOPS                          |                  |     7 |  8127 |     9 |  00:00:01 |
| 2   |   NESTED LOOPS                         |                  |     7 |  8127 |     9 |  00:00:01 |
| 3   |    TABLE ACCESS BY INDEX ROWID BATCHED | T_STATEMENT      |     3 |  2862 |     6 |  00:00:01 |
| 4   |     INDEX RANGE SCAN                   | T_STATEMENT_IDX01|     8 |       |     3 |  00:00:01 |
| 5   |    INDEX UNIQUE SCAN                   | T_USER_PK        |     1 |       |     0 |           |
| 6   |   TABLE ACCESS BY INDEX ROWID          | T_USER           |     2 |   414 |     1 |  00:00:01 |
------------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - filter("TS"."LAST_UPDATE_USER_PK" IS NOT NULL)
4 - access("TS"."INVOICE_ID"=:VAR_INVOICE_ID)
5 - access("TS"."LAST_UPDATE_USER_PK"="TU"."USER_PK")
Content of other_xml column
===========================
  db_version     : 12.1.0.2
  parse_schema   : RDA
  dynamic_sampling: 2
  plan_hash_full : 3368995141
  plan_hash      : 1776672208
  plan_hash_2    : 3368995141
Peeked Binds
============
  Bind variable information
    position=1
    datatype(code)=2
    datatype(string)=NUMBER
    precision=0
    scale=0
    max length=22
    value=3212823


As you can see we're still not getting 1 row back from the unique index scan. This plan shows 2 rows, which is far better than 19 but still "wrong" in my opinion.

Thanks again,

Chris
Chris Saxon
December 03, 2015 - 1:07 pm UTC

I asked around a few friends, and the cause of "2" can most commonly be traced back to optimizer statistics, especially when the stats are from sampling (as opposed to exact).

If the num_rows in the table is more than the num_rows in the associated PK index (due to sampling), then the rows per index entry ends up being (say) 1.1 which could get rounded upwards to 2

Hope this helps.

Update

Chris, December 15, 2015 - 5:33 pm UTC

Connor, Chris, & Tom

I thought I'd provide an update on this thread in the event that it helps someone else facing the same issue.

It turns out that the histogram statistics on the primary key column are what is causing the plan to go astray.

me@db>set lines 10000
me@db>set pages 10000
me@db>
me@db>BEGIN
  2      DBMS_STATS.gather_table_stats(ownname => user, tabname=>'T_USER', method_opt => 'for columns user_pk size 254', no_invalidate => false);
  3  END;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.61
me@db>
me@db>explain plan for
  2  select *
  3  from   t_statement  ts
  4  join   t_user       tu  on  ts.last_update_user_pk = tu.user_pk
  5  where  ts.invoice_id = :var_invoice_id
  6  ;

Explained.

Elapsed: 00:00:00.10
me@db>
me@db>
me@db>SELECT *
  2  FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 1776672208

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |    48 | 56352 |    11   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                         |                   |    48 | 56352 |    11   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                   |    48 | 56352 |    11   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_STATEMENT       |     3 |  2913 |     8   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | T_STATEMENT_IDX01 |    11 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                  | T_USER_PK         |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | T_USER            |    16 |  3248 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - filter("TS"."LAST_UPDATE_USER_PK" IS NOT NULL)
   4 - access("TS"."INVOICE_ID"=TO_NUMBER(:VAR_INVOICE_ID))
   5 - access("TS"."LAST_UPDATE_USER_PK"="TU"."USER_PK")

20 rows selected.

Elapsed: 00:00:00.04
me@db>
me@db>
me@db>BEGIN
  2      DBMS_STATS.gather_table_stats(ownname => user, tabname=>'T_USER', method_opt => 'for columns user_pk size 1', no_invalidate => false);
  3  END;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.75
me@db>
me@db>explain plan for
  2  select *
  3  from   t_statement  ts
  4  join   t_user       tu  on  ts.last_update_user_pk = tu.user_pk
  5  where  ts.invoice_id = :var_invoice_id
  6  ;

Explained.

Elapsed: 00:00:00.10
me@db>
me@db>
me@db>SELECT *
  2  FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 1776672208

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |     3 |  3522 |    11   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                         |                   |     3 |  3522 |    11   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                   |     3 |  3522 |    11   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_STATEMENT       |     3 |  2913 |     8   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | T_STATEMENT_IDX01 |    11 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                  | T_USER_PK         |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | T_USER            |     1 |   203 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - filter("TS"."LAST_UPDATE_USER_PK" IS NOT NULL)
   4 - access("TS"."INVOICE_ID"=TO_NUMBER(:VAR_INVOICE_ID))
   5 - access("TS"."LAST_UPDATE_USER_PK"="TU"."USER_PK")

20 rows selected.

Elapsed: 00:00:00.05
me@db>


As you can see the second plan has the correct cardinality estimates after removing histograms on the primary key column.

I'm not sure why the scheduled automated stats gathering job determined that a histogram on the column was required. (I realize that I explicitly gathered histogram stats in the example above. That was merely for demonstration purposes). The column is populated via a sequence so it is sequential, mostly gap free, and therefore evenly distributed and not skewed at all. I could maybe understand if there were massive gaps in the values due to large numbers of sequence values being "burned" periodically, but that's not the case. Another hunch is that we do have composite column indexes that contain the primary key for performance purposes. For example, something like (USER_PK, USER_FULL_NAME). We do this so that when we are performing large searches or running large reports we can avoid a table lookup by having all data in the index itself. This actually does dramatically improve performance in these scenarios. Perhaps the optimizer/stats job sees that combination and determines that a histogram makes sense. It seems that the "black box" portions of the optimizer become deeper and darker with each release :)

This was killing us though. We have multiple audit columns on the T_STATEMENT table that track which users made certain changes to the data. As you can imagine, when the cardinality estimates are multiplied by 16 each time we join to the T_USER table on the primary key, the estimates can blow up very quickly and the plans start asking for full table scans on everything subsequently joined to.

Regardless of the presence/use of histograms or not I still view this as a bug. Any table access by index rowid from an index unique scan of a primary key index should always have a cardinality estimate of 1. Simple as that.

Thanks again for your time.

Chris


Chris Saxon
December 16, 2015 - 1:20 am UTC

Nice piece of analysis.

In terms of histograms, by default, histograms are set to "auto" which means we track when you parse queries and store some information in a table called COL_USAGE$.

In a nutshell, if you use a predicate on a column that might have been assisted with a histogram, then we'll calculate one next gather.

eg if you ran

select * from T_USER where PK > 10

then next time we gather, we'll probably get a histogram on PK.

SQL> desc sys.col_usage$
 Name                          Null?    Type
 ----------------------------- -------- ----------
 OBJ#                                   NUMBER
 INTCOL#                                NUMBER
 EQUALITY_PREDS                         NUMBER
 EQUIJOIN_PREDS                         NUMBER
 NONEQUIJOIN_PREDS                      NUMBER
 RANGE_PREDS                            NUMBER
 LIKE_PREDS                             NUMBER
 NULL_PREDS                             NUMBER
 TIMESTAMP                              DATE


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.