Real Time statistics - Part VI

In the previous blog post we discussed about how lone deletes are not tracked by real-time statistics, however there is an exception to that. Deletes are tracked by real-time statistics if they were followed by an update statement.
 
demo@PDB19> show parameter optimizer_real_time_statistics
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_real_time_statistics       boolean     FALSE
demo@PDB19> alter session set optimizer_real_time_statistics=true;
 
Session altered.
 
demo@PDB19> create table t(
  2     n1 number primary key,
  3     n2 number ,
  4     n3 varchar2(80) );
 
Table created.
 
demo@PDB19> insert /*+ append */ into t(n1,n2,n3)
  2  with rws as ( select rownum n from dual connect by level <= 1000 )
  3  select n, ceil(sqrt(n)),
  4     to_char(to_date(n,'j'),'jsp')
  5  from rws;
 
1000 rows created.
 
demo@PDB19> commit;
 
Commit complete.
 
demo@PDB19> update t
  2  set n2 = ceil(sqrt(n1+1000)) ,
  3      n3 = 'z'||to_char(to_date(n1+1000,'j'),'jsp')
  4  where n1 <= 500;
 
500 rows updated.
 
demo@PDB19> commit;
 
Commit complete.
 
demo@PDB19> delete from t where n1 > 800;
 
200 rows deleted.
 
demo@PDB19> commit;
 
Commit complete.
 
The reduction in the number of rows by this delete command was tracked by real-time statistics.
 
demo@PDB19> exec dbms_stats.flush_database_monitoring_info;
 
PL/SQL procedure successfully completed.
 
demo@PDB19> select num_rows,blocks,monitoring
  2  from user_tables
  3  where table_name ='T';
 
  NUM_ROWS     BLOCKS MON
---------- ---------- ---
      1000          8 YES
 
demo@PDB19> select num_rows,blocks,stale_stats,notes
  2  from user_tab_statistics
  3  where table_name ='T';
 
  NUM_ROWS     BLOCKS STALE_S NOTES
---------- ---------- ------- -------------------------
      1000          8 YES
       800         16         STATS_ON_CONVENTIONAL_DML
 
However looking at the column level statistics, no real-time statistics were observed for the column N1, since it was not updated.
 
demo@PDB19> select t1.column_name,t1.num_nulls,t1.num_distinct,t1.low_value,get_stats_val(t2.data_type,t1.low_value) low_val,
  2      t1.high_value,get_stats_val(t2.data_type,t1.high_value) high_val, t1.histogram , t1.notes
  3  from user_tab_col_statistics t1,
  4       user_tab_columns t2
  5  where t1.table_name ='T'
  6  and t1.table_name = t2.table_name
  7  and t1.column_name = t2.column_name;
 
COLUMN_NAM  NUM_NULLS NUM_DISTINCT LOW_VALUE  LOW_VAL    HIGH_VALUE HIGH_VAL             HISTOGRAM  NOTES
---------- ---------- ------------ ---------- ---------- ---------- -------------------- ---------- -------------------------
N1                  0         1000 C102       1          C20B       1000                 NONE       STATS_ON_LOAD
N2                  0           32 C102       1          C121       32                   NONE       STATS_ON_LOAD
N3                  0         1000 6569676874 eight      74776F2068 two hundred two      NONE       STATS_ON_LOAD
                                                         756E647265
                                                         642074776F
 
N2                  0              C102       1          C128       39                              STATS_ON_CONVENTIONAL_DML
N3                  0              6569676874 eight      7A6F6E6520 zone thousand twenty            STATS_ON_CONVENTIONAL_DML
                                                         74686F7573 -nine
                                                         616E642074
                                                         77656E7479
                                                         2D6E696E65
 
Now parsing a sql having a predicate on the column N1 - that was part of our “Delete” statement, but don’t have real-time column level statistics on it yet -  produces a plan like this.
 
demo@PDB19> select /*+ gather_plan_statistics */ count(*) from t where n1 > 800;
 
  COUNT(*)
----------
         0
 
demo@PDB19> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  cfbuu2gdzj40k, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t where n1 > 800
 
Plan hash value: 496403668
 
-------------------------------------------------------------------------------------------
| Id  | Operation         | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE   |             |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| SYS_C007942 |      1 |    160 |      0 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("N1">800)
 
Note
-----
   - dynamic statistics used: statistics for conventional DML
 
The plan estimates were very way off from the actuals - were 160 rows – that is because the predicate N1 got low/high values as 1 and 1000 and the estimated cardinality is calculated as
 
Estimated cardinality = number of rows * ( high_value – limit / high_value – low_value )
Estimated cardinality = 800 * ( (1000 – 800 ) / (1000 – 1 ) ) = 160.16
 
So real-time table level statistics were tracked for those delete statement followed by an update statement, but no real time column level statistics for the predicates from delete statement. This lack of column level statistics could potentially lead to sub-optimal plans.