Real Time Statistics

Introduction
=======
Oracle Database 19c introduces real-time statistics, which extends online statistics gathering to also include conventional DML statements.

Statistics are normally gathered by automatic statistics gather job which runs inside the database maintenance window – but that is just once a day.

But for volatile tables statistics can go stale between DBMS_STATS job executions, so the new Oracle 19c feature of real-time statistics can help the optimizer generate more optimal plans for such volatile tables.

Oracle Real Time statistics rely on fixed statistics, so it is important that you keep gathering fixed statistics as usual.

In Oracle19c real time statistics are applied by default, but they can be manipulated using the following hidden parameters:
_optimizer_gather_stats_on_conventional_config    	 	0              settings for optimizer online stats gathering on conventional DML 
 _optimizer_gather_stats_on_conventional_dml        		TRUE         optimizer online stats gathering for conventional DML
 _optimizer_stats_on_conventional_dml_sample_rate   	100       	sampling rate for online stats gathering on conventional DML
 _optimizer_use_stats_on_conventional_config        		0              settings for optimizer usage of online stats on conventional DML
 _optimizer_use_stats_on_conventional_dml           		TRUE       	use optimizer statistics gathered for conventional DML

How does real time statistics work?
=====================
• When a DML operation is currently modifying a table, Oracle Database dynamically computes values for the  most essential statistics. There is no call to DBMS_STATS visible in trace.  But you can see an  'OPITMIZER STATISTICS GATHERING' operation in the execution plan.
• The changes to the table are tracked in memory and are flushed to the data dictionary later on along with other monitoring data, as part of the table monitoring feature. (Table monitoring was introduced in 10g and is used to determine if statistics are stale. All table dml and truncates are tracked in memory and flushed to disk every 15 minutes by SMON.)

This feature is only available on Engineered Systems such as Exadata and Exadata Cloud Service. You can simulate this in a test environment as follows:

SQL> alter system set "_exadata_feature_on" = true scope = spfile;

 
Example using real time statistics
=====================
SQL> alter session set container=pdb1;

SQL> alter session set current_schema=pdbadmin;

SQL> CREATE TABLE TEST(id NUMBER, name VARCHAR2(20));

SQL>  begin
 for a in 1..500000 loop
 insert into TEST values (a,'test'||to_char(a));
 end loop;
 commit;
 end;
 /

--Generate statistics:
begin
  DBMS_STATS.GATHER_TABLE_STATS('PDBADMIN', 'TEST', METHOD_OPT=>'FOR ALL COLUMNS SIZE auto');
end;
 / 

--Check the statistics:
SET PAGES 5000 LINES 200
COL TABLE_NAME for a15
COL COLUMN_NAME FORMAT a13 
COL NOTES FORMAT a35
select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,SAMPLE_SIZE,LAST_ANALYZED,NOTES from dba_tab_col_statistics where table_name = 'TEST';

TABLE_NAME      COLUMN_NAME   NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED        NOTES
--------------- ------------- ------------ ----------- -------------------- ---------------
TEST            ID                  500000      500000 26-OCT-2020 09:02:34
TEST            NAME             500000      500000 26-OCT-2020 09:02:34

SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED,NOTES  from DBA_TAB_STATISTICS where table_name='TEST';

TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED        NOTES
--------------- ---------- ---------- ----------- -------------------- ----------------------
TEST                500000       1432          16 26-OCT-2020 09:02:34

--Now, let’s add another 100000 records:
begin
 for a in 500001..600000 loop
 insert into TEST values (a,'test'||to_char(a));
 end loop;
 commit;
end;
/ 

PL/SQL procedure successfully completed.

Now check the statistics again:
SQL> select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,SAMPLE_SIZE,LAST_ANALYZED,NOTES from dba_tab_col_statistics where table_name = 'TEST';

TABLE_NAME      COLUMN_NAME   NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED        NOTES
--------------- ------------- ------------ ----------- -------------------- -----------------
TEST            ID                  500000      500000 26-OCT-2020 09:02:34
TEST            NAME                500000      500000 26-OCT-2020 09:02:34
TEST            NAME                              1049 26-OCT-2020 09:25:40 STATS_ON_CONVENTIONAL_DML
TEST            ID                                 970 26-OCT-2020 09:25:40 STATS_ON_CONVENTIONAL_DML

SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED,NOTES 
from DBA_TAB_STATISTICS where table_name='TEST';
 
TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED        NOTES
--------------- ---------- ---------- ----------- -------------------- ------------------------
TEST                500000       1432          16 26-OCT-2020 09:02:34
TEST                600000       1718             26-OCT-2020 09:25:39 STATS_ON_CONVENTIONAL_DML

Let’s see what happens when we insert and select data from this table:
SQL> explain plan for insert into test values(1000001,'test1000001');

SQL> select * From table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |    16 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST |       |       |            |          |
---------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: statistics for conventional DML enabled

SQL> set autotrace traceonly
SQL> SELECT COUNT(*) FROM TEST WHERE ID > 590000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   471   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |  9895 |   125K|   471   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("ID">590000)

Note
-----
   - dynamic statistics used: statistics for conventional DML