Oracle 12.1.2 wrong cardinality on equal predicate even though I have gathered the latest table stats

Question

The following example is a smaller version of the query that I am trying to tune as I noticed that the wrongness of the plan emanates on this particular operation.

select  count (*) from XVIEWMGR.XV_CASE_STAGES_DATA
where  stage_id = max_initial_or_reopen_id

As mentioned, table stats and corresponding indexes have been recently gathered but no histograms has been created for each column as they are highly distinct. I've even created extended statistics for those to columns to no avail.

    OWNER                TABLE_NAME                       NUM_ROWS LAST_ANALYZED      
-------------------- ------------------------------ ---------- -------------------
XVIEWMGR             XV_CASE_STAGES_DATA                314079 09-11-2018 14:29:01


    TABLE_NAME                     COLUMN_NAME                    LAST_ANAL SAMPLE_SIZE  NUM_NULLS NUM_DISTINCT    DENSITY
------------------------------ ------------------------------ --------- ----------- ---------- ------------ ----------
XV_CASE_STAGES_DATA            STAGE_ID                       09-NOV-18      314079          0       308733 .00000323904474
XV_CASE_STAGES_DATA            MAX_INITIAL_OR_REOPEN_ID       09-NOV-18      314079          0       308728 .0000032390972

I hope you guys can help.

Answer 1

Dynamic statistics (also known as dynamic sampling) is pretty good at filling in gaps like this.

Test setup:

create table xv_case_stages_data
( stage_id integer
, max_initial_or_reopen_id integer );

insert into xv_case_stages_data (stage_id, max_initial_or_reopen_id)
select rownum, rownum
from dual connect by rownum <= 1000;

commit;

call dbms_stats.gather_table_stats(user, 'XV_CASE_STAGES_DATA');

Test execution plans in 12.2 using xplanx.sql (some output removed for readability):

SQL> select count (*) from xv_case_stages_data where stage_id = max_initial_or_reopen_id

SQL> @xplanx

Plan hash value: 1750917958

----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |      1 |        |      1 |00:00:00.01 |       7 |
|   1 |  SORT AGGREGATE    |                     |      1 |      1 |      1 |00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL| XV_CASE_STAGES_DATA |      1 |      1 |   1000 |00:00:00.01 |       7 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("STAGE_ID"="MAX_INITIAL_OR_REOPEN_ID")

With dynamic sampling at level 4:

SQL> select /*+ dynamic_sampling(4) */ count (*) from xv_case_stages_data where stage_id = max_initial_or_reopen_id

SQL> @xplanx

Plan hash value: 1750917958

----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |      1 |        |      1 |00:00:00.01 |       7 |
|   1 |  SORT AGGREGATE    |                     |      1 |      1 |      1 |00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL| XV_CASE_STAGES_DATA |      1 |   1000 |   1000 |00:00:00.01 |       7 |
----------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("STAGE_ID"="MAX_INITIAL_OR_REOPEN_ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=4)