Real Time statistics - Part VIII

In this blog post we are going to see about the effect of real time statistics feature with global temporary tables (GTT) in place.
 
demo@PDB19> create global temporary table t(
  2     n1 number primary key,
  3     n2 number ,
  4     n3 varchar2(80) )
  5  on commit preserve rows    ;
 
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> insert into t(n1,n2,n3)
  2  with rws as ( select rownum+1000 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> 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
---------- ---------- ---
                      NO
 
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          5
 
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                                                                                       NONE
N2                                                                                       NONE
N3                                                                                       NONE
 
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
N2                  0           32 C102       1          C121       32                   NONE
N3                  0         1000 6569676874 eight      74776F2068 two hundred two      NONE
                                                         756E647265
                                                         642074776F
 
 
demo@PDB19>
 
the concept of real-time statistics does not fit with GTT. Statistics on GTT have always been problematic and statistics gathered on one session can be used by the sql’s parsing in another session, to solve this problem Oracle 12c introduced a concept called session level statistics on GTT