Oracle 23c New Features for DBA'sCloud Feature - Not working in On Prem installation.
Oracle Database 12c introduced online statistics gathering for CREATE TABLE AS SELECT statements and direct-path inserts.
Oracle Database 19c introduces real-time statistics, which extend online support to conventional DML statements. Because statistics can go stale between DBMS_STATS jobs, 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
- "_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.
- DBA_TAB_COL_STATISTICS and DBA_TAB_STATISITICS has columns NOTES tell real time statistics have been used. STATS_ON_CONVENTIONAL_DML
SELECT NVL(PARTITION_NAME, 'GLOBAL') PARTITION_NAME, NUM_ROWS, BLOCKS, NOTES FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'SALES' ORDER BY 1, 4;
PARTITION_NAM NUM_ROWS BLOCKS NOTES ------------- ---------- ---------- ------------------------- GLOBAL 1837686 3315 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
Thanks
Suresh
Follow Me!!!