The New Home of Oracle-wiki.net

All things oracle...

A website for IT Professionals involved with Oracle database.

LinkLinkedIn

Max Idle Blocker Time

Introduction

In version 12c, a new feature was introduced called "online statistics". Online statistics were gathered during certain load statements. For example, CTAS (Create table as select) and direct-path inserts. In 19c, this feature has been extended to include conventional DML as is being called "real-time statistics". The idea behind this feature, is that statistics can go stale between stats jobs, so wouldn’t it be great if the database could capture up to date statistics in real-time.

The key difference between "online statistics" and "real-time" statistics is that bulk load operations gather all necessary statistics whereas, real-time statistics supplement, rather than replace standard statistics. Subsequently, it is critical to continue gathering statistics regularly using your preferred method.


Enabling Real-Time Statistics

A new initialisation parameter has been introduced called OPTIMIZER_REAL_TIME_STATISTICS. When set to true, Oracle will automatically gather statistics during conventional DML operations. The default is FALSE.

Note: Its good to see this feature set to false by default. There have been a few instances recently, where a new feature has been enabled by default causing all kinds of chaos to applications when upgrading


The How

During DML operations Oracle will capture and compute statistics for the key statistics. For example, when adding or deleting a large number of rows to a table, oracle will capture the details and update the row count statistic. Subsequent runs of the query, assuming it is hard parsed, can make use of these new statistics in generating the query plan.


Managing Real-Time Statistics

You can manage and access real-time statistics through PL/SQL, dictionary views, hints and query plans.


PLSQL

The following PLSQL packages now have a real-time statistic parameter


EXPORT_TABLE_STATS and EXPORT_SCHEMA_STATS
IMPORT_TABLE_STATS and IMPORT_SCHEMA_STATS
DELETE_TABLE_STATS and DELETE_SCHEMA_STATS
DIFF_TABLE_STATS_IN_STATTAB
DIFF_TABLE_STATS_IN_HISTORY


Note
The stat_category parameter includes real-time statistics by default.
The REALTIME_STATS value specifies only real-time statistics.
Real-time statistics are always included in the diff packages


Dictionary Views

When real-time statistics are collected, then the notes column on the following views are updated. See below.


DBA_TAB_COL_STATISTICS
DBA_TAB_STATISTICS


Notes
Partition-level statistics are not supported.
Real-time statistics are indicated by STATS_ON_CONVENTIONAL_DML in the notes column.
The ALL_* and USER_* views are also contain the notes column


Hints

To stop real-time statistics being collected when the optimizer paramter is set, the following SQL hint can be used.


NO_GATHER_OPTIMIZER_STATISTICS


Query Plan

When real-time statistics have been used in a optimizer plan, then the query plan will contain a note. See below


Note

-----

   - dynamic statistics used: stats for conventional DML





Published on 

Published 25th October 2024