About real time statistics oracle 19c only for particular table

Question

Reg Real-Time statistics which extends online statistics gathering(12c) to also include conventional DML statements. Real-time statistics helps the optimizer generate more optimal plans.Whereas bulk load operations gather all necessary statistics, real-time statistics augment rather than replace traditional statistics.

Oracle introduced new parameters

_optimizer_gather_stats_on_conventional_dml and _optimizer_use_stats_on_conventional_dml which are true by default and _optimizer_stats_on_conventional_dml_sample_rate at 100%

How does real time statistics works?

By default the _optimizer_gather_stats_on_conventional_dml is true so its automatically kicks off

When a DML operation is currently modifying a table (conventional), Oracle Database dynamically computes values for the most essential statistics if the above parameter is on.

Consider a example of table that is having lot of inserts and rows are increasing. Real-time statistics keep track of the increasing row count as rows are being inserted. If the optimizer performs a hard parse of a new query, then the optimizer can use the real-time statistics to obtain a more accurate cost estimate.

USER_TAB_COL_STATISTICS and USER_TAB_STATISITICS has columns NOTES tell real time statistics have been used. "STATS_ON_CONVENTIONAL_DML".

Execution Plan shows

|Id| Operation                        | Name|Rows|Bytes|Cost (%CPU)|Time| Pstart|Pstop|
---------------------------------------------------------------------------------------
| 0| INSERT STATEMENT                 |     |    |     |910 (100)|        |     |     |
| 1|  LOAD TABLE CONVENTIONAL         |SALES|    |     |         |        |     |     |
| 2|   OPTIMIZER STATISTICS GATHERING |     |918K|  25M|910   (2)|00:00:01|     |     |
| 3|    PARTITION RANGE ALL           |     |918K|  25M|910   (2)|00:00:01|   1 |  28 |
| 4|     TABLE ACCESS FULL            |SALES|918K|  25M|910   (2)|00:00:01|   1 |  28 |

Also the explain plan in the query used will tell in note section
    Note

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

LOAD TABLE CONVENTIONAL and OPTIMIZER STATISTICS GATHERING are the new parameters that have been added to the explain plan for this new feature.

Can we apply real-time statistics only for a particular set of tables? if yes, how?

We know getting real time statistics for any dml operation thats occurring for more than 100k rows is very costly operation

I tried finding out the way, but was not successful

pifor pifor · Accepted Answer · 2020-05-18T06:46:43

Dynamic statistics and real-time statistics are different.

Note that OPTIMIZER STATISTICS GATHERING is actually a 12.1 execution plan operation that is not related to real-time statistics according to Mike Dietrich blog. It looks like that 12.1 online statistics feature has been modified in 19c for Exadata and renamed real-time statistics.

Real-time statistics are only available on Exadata according to the licensing guide: you cannot have real-time statistics on usual Entreprise Edition databases.

Do you have an Exadata system available ? If no you cannot test this feature.

About real time statistics oracle 19c only for particular table

1 Answers