ANBOB™

BOTH FEATURES ARE EXADATA ONLY（19c Features available on）

•  Oracle Database Enterprise Edition on Engineered Systems (EE-ES)
•  Oracle Database Exadata Cloud Service (ExaCS)

Table Monitoring

TABLE MONITORING Introduced in 10g， Enabled by default since 11g，by SMON, approximately every 15 minutes，can be flushed out manually with dbms_stats.flush_database_monitoring_info。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.The changes to the table are tracked in memory and are flushed to the data dictionary later along with other monitoring data. they can be flushed with dbms_stats.flush_database_monitoring_info。

Notes

• Real-time column statistics really are real-time because they are immediately visible to the optimizer
• Real-time table statistics do not become visible until the table monitoring information has been flushed.
• Creation of real-time statistics does not invalidate cursors.
• Concept of real-time statistics doesn’t fit with GTTs （GLOBAL TEMPORARY TABLES）

realtime stats Row Cache

SQL> select indx,KQRSTCID,KQRSTTXT from X$KQRST where KQRSTTXT like '%realtime%';

      INDX   KQRSTCID KQRSTTXT
---------- ---------- --------------------------------
        38         62 dc_realtime_colst
        39         63 dc_realtime_tabst

realtime stats parmeters

SQL> @pd "conventional%dml"
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
      5278  149E _optimizer_stats_on_conventional_dml_sample_rate         100                            sampling rate for online stats gathering on conventional DML
      5279  149F _optimizer_gather_stats_on_conventional_dml              FALSE                          optimizer online stats gathering for conventional DML
      5280  14A0 _optimizer_use_stats_on_conventional_dml                 FALSE                          use optimizer statistics gathered for conventional DML
      5281  14A1 _optimizer_use_stats_on_conventional_config              0                              settings for optimizer usage of online stats on conventional DML
      5282  14A2 _optimizer_gather_stats_on_conventional_config           0                              settings for optimizer online stats gathering on conventional DML
      5293  14AD optimizer_real_time_statistics                           FALSE                          optimizer real time statistics on conventional DML

6 rows selected.

What caused this change?

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
...

Plan hash value: 1236776825

---------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |      |       |     2 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL         | TAB1 |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |      |     1 |     2   (0)| 00:00:01 |
...
---------------------------------------------------------------------------------


[xxx]_TAB_STATISTICS.
TABLE_NAME   NUM_ROWS NOTES
---------- ---------- -------------------------
...
TAB1             ... STATS_ON_CONVENTIONAL_DML


then query the table 

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
...
...

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

Note：

1. the DBMS_XPLAN.DISPLAY_CURSOR table function and we can see an “OPTIMIZER STATISTICS GATHERING” operation in the plan.
2. USER_TAB_COL_STATISTICS and USER_TAB_STATISITICS has columns NOTES tell real time statistics have been used. “STATS_ON_CONVENTIONAL_DML”.
3. the bottom tells use that “statistics for convention DML” were used for this execution plan.

Disable HINT

SQL> @hint statistics

NAME                                                             VERSION                   VERSION_OUTLINE           INVERSE
---------------------------------------------------------------- ------------------------- ------------------------- ----------------------------------------------------------------
GATHER_PLAN_STATISTICS                                           10.1.0.3
GATHER_OPTIMIZER_STATISTICS                                      12.1.0.1                                            NO_GATHER_OPTIMIZER_STATISTICS
NO_GATHER_OPTIMIZER_STATISTICS                                   12.1.0.1                                            GATHER_OPTIMIZER_STATISTICS

DISABLE PARAMETERS

Setting the “_optimizer_gather_stats_on_conventional_dml” parameter at session or system level will stop the optimizer gathering real-time statistics.

ALTER SESSION SET "_optimizer_gather_stats_on_conventional_dml"=FALSE;

Setting the “_optimizer_gather_stats_on_conventional_dml” parameter at session or system level will stop the use real-time statistics.

ALTER SESSION SET "_optimizer_use_stats_on_conventional_dml"=FALSE;

Setting the “_optimizer_gather_stats_on_load” parameter at session or system level will stop Online Statistics Gathering for Bulk-Load

ALTER system SET "_optimizer_gather_stats_on_load"=FALSE;

–Check

SELECT a.ksppinm AS parameter,
       a.ksppdesc AS description,
       b.ksppstvl AS session_value,
       c.ksppstvl AS instance_value
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE  a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm in ('_optimizer_gather_stats_on_load','_optimizer_gather_stats_on_conventional_dml','_optimizer_use_stats_on_conventional_dml')
ORDER BY a.ksppinm;