Oracle 19c New Feature Real-Time Statistics

In data warehouse environment, we often have situations where tables are truncated and new data (often millions of rows) is loaded.But when reports are run against those tables with freshly loaded data unless we go and gather fresh statistics there is a possibility that the optimizer may choose a sub-optimal plan.

So to address this issue, Oracle Database 12c introduced online statistics gathering – but that was only for those tables where data was loaded via CREATE TABLE AS SELECT statements as well as direct-path inserts using the APPEND hint.

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.

Bulk load operations will gather all necessary statistics (pre Oracle 19c behavior)- however real-time statistics augment rather than replace traditional statistics.

We have a table called MYOBJECTS_19C which currently has 47974 rows.
 

SQL> select distinct object_type from myobjects_19c where owner='SYS';

OBJECT_TYPE
-----------------------
INDEX
CLUSTER
TABLE PARTITION
...
...
VIEW
JAVA RESOURCE

26 rows selected.

SQL> select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	8rbtnvw2uw67f, child number 0
-------------------------------------
select distinct object_type from myobjects_19c where owner='SYS'

Plan hash value: 1625058500

------------------------------------------------------------------------------------
| Id  | Operation	   | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		   |	   |	   |   258 (100)|	   |
|   1 |  HASH UNIQUE	   |		   |	26 |   364 |   258   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| MYOBJECTS_19C | 47974 |   655K|   257   (1)| 00:00:01 |
------------------------------------------------------------------------------------

 
We now insert some additional rows into the table – basically doubling the number of rows in the table.

In earlier versions there is now a possibility of sub-optimal plans being chosen by the optimizer as it is ‘not aware’ of the fact that some DML activity has happened on the table and now the number of rows in the table have increased two-fold.

But now in Oracle 19c, we can see that as part of the INSERT statement, an OPTIMIZER STATISTICS GATHERING operation was also performed.
 

SQL> insert into myobjects_19c
    select * from myobjects;

47974 rows created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	ahudb149n8f2f, child number 0
-------------------------------------
insert into myobjects_19c select * from myobjects

Plan hash value: 3078646338

--------------------------------------------------------------------------------------------------
| Id  | Operation			 | Name 	 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT		 |		 |	 |	 |   273 (100)| 	 |
|   1 |  LOAD TABLE CONVENTIONAL	 | MYOBJECTS_19C |	 |	 |	      | 	 |
|   2 |   OPTIMIZER STATISTICS GATHERING |		 | 47974 |    11M|   273   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL		 | MYOBJECTS	 | 47974 |    11M|   273   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

 
When a hard parse of the SQL statement occurs, we can see that that the optimizer has detected that additional rows have been added to the table,

This is also indicated in the Note section: dynamic statistics used: statistics for conventional DML
 

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> select distinct object_type from myobjects_19c where owner='SYS';

OBJECT_TYPE
-----------------------
INDEX
CLUSTER
TABLE PARTITION
...
...
VIEW
JAVA RESOURCE

26 rows selected.

SQL> select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	8rbtnvw2uw67f, child number 0
-------------------------------------
select distinct object_type from myobjects_19c where owner='SYS'

Plan hash value: 1625058500

------------------------------------------------------------------------------------
| Id  | Operation	   | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		   |	   |	   |   624 (100)|	   |
|   1 |  HASH UNIQUE	   |		   |	26 |  2054 |   624   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| MYOBJECTS_19C | 95948 |  7402K|   621   (1)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - filter("OWNER"='SYS')

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

 
Real-time statistics are indicated by STATS_ON_CONVENTIONAL_DML in the NOTES column in the *_TAB_STATISTICS and *_TAB_COL_STATISTICS views.
 

SQL> SELECT NUM_ROWS, BLOCKS, NOTES 
FROM   USER_TAB_STATISTICS
WHERE  TABLE_NAME = 'MYOBJECTS_19C' ;

  NUM_ROWS     BLOCKS NOTES
---------- ---------- --------------------------------------------------
     47974	  938
     95948	 2284 STATS_ON_CONVENTIONAL_DML