Real Time statistics - Part V

Real time statistics feature by design is trying to obtain good values for total number of rows, column min/max values, without the need to do full scan on table data. 

For example lets say we got a table with 1000 rows having a column C1 with values from 1 to 100 then that column min/max value will be 1 and 100, but if we update those values to 20 and 120 – then it is easy to track 120 is higher that the existing max of 100. But it is impossible to know that 20 is the new minimum without scanning the whole table. 

Since we are trying to track the total number of rows and min/max, we are going to try to do our best to keep that in sync.

•          For insert we can adjust the total, min, max in sync.
•          For update, we can adjust the total and max in sync (total doesn’t change), we possibly loose min value

 

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> drop table t purge;
 
Table dropped.
 
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> 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>
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
      1000         16         STATS_ON_CONVENTIONAL_DML
 
demo@PDB19>
demo@PDB19> select column_name,num_nulls,num_distinct,low_value,high_value
  2  from user_tab_columns
  3  where table_name ='T';
 
COLUMN_NAM  NUM_NULLS NUM_DISTINCT LOW_VALUE  HIGH_VALUE
---------- ---------- ------------ ---------- ----------
N1                  0         1000 C102       C20B
N2                  0           32 C102       C121
N3                  0         1000 6569676874 74776F2068
                                              756E647265
                                              642074776F
 
 
demo@PDB19> select column_name,num_nulls,num_distinct,low_value,high_value,notes
  2  from user_tab_col_statistics
  3  where table_name ='T';
 
COLUMN_NAM  NUM_NULLS NUM_DISTINCT LOW_VALUE  HIGH_VALUE NOTES
---------- ---------- ------------ ---------- ---------- -------------------------
N1                  0         1000 C102       C20B       STATS_ON_LOAD
N2                  0           32 C102       C121       STATS_ON_LOAD
N3                  0         1000 6569676874 74776F2068 STATS_ON_LOAD
                                              756E647265
                                              642074776F
 
N2                  0              C102       C128       STATS_ON_CONVENTIONAL_DML
N3                  0              6569676874 7A6F6E6520 STATS_ON_CONVENTIONAL_DML
                                              74686F7573
                                              616E642074
                                              776F206875
                                              6E64726564
                                              206E696E65
                                              74792D6569
                                              676874
demo@PDB19> select column_name,num_nulls,num_distinct,low_value,get_stats_val(data_type,low_value) low_val,
  2      high_value,get_stats_val(data_type,high_value) high_val, histogram
  3  from user_tab_columns
  4  where table_name ='T';
 
COLUMN_NAM  NUM_NULLS NUM_DISTINCT LOW_VALUE  LOW_VAL    HIGH_VALUE HIGH_VAL             HISTOGRAM
---------- ---------- ------------ ---------- ---------- ---------- -------------------- ---------------
N1                  0         1000 C102       1          C20B       1000                 NONE
N2                  0           32 C102       1          C121       32                   NONE
N3                  0         1000 6569676874 eight      74776F2068 two hundred two      NONE
                                                         756E647265
                                                         642074776F
 
 
demo@PDB19>
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 two hu                 STATS_ON_CONVENTIONAL_DML
                                                         74686F7573 ndred ninety-eight
                                                         616E642074
                                                         776F206875
                                                         6E64726564
                                                         206E696E65
                                                         74792D6569
                                                         676874
 
 
demo@PDB19>
demo@PDB19> select min(n2), max(n2) from t;
 
   MIN(N2)    MAX(N2)
---------- ----------
        23         39

 

•         For delete, we can potentially change total, which would leave ‘min’ and ‘max’ out of sync, so we don’t do it. It is better for us to keep all three in sync. 

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> drop table t purge;
 
Table dropped.
 
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> delete from t where n1 > 800;
 
200 rows deleted.
 
demo@PDB19> commit;
 
Commit complete.
 
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
 
demo@PDB19> select column_name,num_nulls,num_distinct,low_value,high_value
  2  from user_tab_columns
  3  where table_name ='T';
 
COLUMN_NAM  NUM_NULLS NUM_DISTINCT LOW_VALUE  HIGH_VALUE
---------- ---------- ------------ ---------- ----------
N1                  0         1000 C102       C20B
N2                  0           32 C102       C121
N3                  0         1000 6569676874 74776F2068
                                              756E647265
                                              642074776F
 
 
demo@PDB19> select column_name,num_nulls,num_distinct,low_value,high_value,notes
  2  from user_tab_col_statistics
  3  where table_name ='T';
 
COLUMN_NAM  NUM_NULLS NUM_DISTINCT LOW_VALUE  HIGH_VALUE NOTES
---------- ---------- ------------ ---------- ---------- -------------------------
N1                  0         1000 C102       C20B       STATS_ON_LOAD
N2                  0           32 C102       C121       STATS_ON_LOAD
N3                  0         1000 6569676874 74776F2068 STATS_ON_LOAD
                                              756E647265
                                              642074776F

 So lone deletes are not tracked by real-time statistics.