Oracle DBA

In earlier versions of Oracle Database, statistics were collected mainly through scheduled jobs using DBMS_STATS. This often resulted in stale statistics between collection intervals, especially in OLTP environments with frequent DML operations.

Oracle 19c introduces Real-Time Statistics, a feature that collects optimizer statistics immediately during DML operations such as INSERT, UPDATE, MERGE, and CREATE TABLE AS SELECT. This ensures that the cost-based optimizer always has accurate information for generating execution plans.

Why Real-Time Statistics Matter

• Eliminates performance issues caused by stale statistics
• Reduces the need for frequent manual or scheduled statistics gathering
• Improves query plan accuracy for frequently updated tables
• Works seamlessly with automatic statistics collection

For example, in a high-volume sales table where thousands of rows are inserted every hour, traditional scheduled statistics jobs may leave the optimizer working with outdated data. Real-Time Statistics solve this problem automatically.

How Real-Time Statistics Work

When you perform DML operations such as INSERT, UPDATE, MERGE, or CREATE TABLE AS SELECT, Oracle automatically collects and updates column-level and table-level statistics in real time.

This allows the optimizer to generate the most accurate execution plans without having to wait for the next scheduled statistics gathering job.

Checking Real-Time Statistics in Oracle 19c

You can verify if Real-Time Statistics is enabled by running the following query:

SELECT name, value
FROM v$sys_optimizer_env
WHERE name = 'optimizer_real_time_statistics';

If the value is TRUE, Real-Time Statistics are enabled. If FALSE, the feature is not active.

Demonstration of Real-Time Statistics

Step 1: Create a test table

CREATE TABLE orders (
  order_id     NUMBER,
  customer_id  NUMBER,
  order_date   DATE,
  amount       NUMBER
);

BEGIN
  FOR i IN 1..50000 LOOP
    INSERT INTO orders VALUES (i, MOD(i, 1000), SYSDATE - MOD(i, 365), DBMS_RANDOM.VALUE(100, 5000));
  END LOOP;
  COMMIT;
END;

Step 2: Check statistics before enabling real-time stats

SELECT num_rows, last_analyzed 
FROM dba_tables 
WHERE table_name = 'ORDERS';

If no statistics exist, Oracle will either gather them at runtime or during the next scheduled job.

Step 3: Enable Real-Time Statistics

ALTER SYSTEM SET "_optimizer_use_stats_on_conventional_dml" = TRUE;

Step 4: Perform DML operations

INSERT INTO orders VALUES (50001, 1001, SYSDATE, 2000);
COMMIT;

Oracle updates the statistics for the table immediately after the DML operation.

Step 5: Validate updated statistics

SELECT num_rows, last_analyzed
FROM dba_tables
WHERE table_name = 'ORDERS';

You will notice that num_rows increases immediately without running DBMS_STATS.

Real-Time Statistics with Direct-Path Operations

Real-Time Statistics also work with direct-path operations. For example:

CREATE TABLE new_orders AS SELECT * FROM orders;

When this table is created, statistics are available immediately without a separate DBMS_STATS.GATHER_TABLE_STATS call.

Monitoring Real-Time Statistics Operations

You can monitor statistics operations using the following query:

SELECT operation, target, start_time, end_time
FROM dba_optstat_operations
ORDER BY start_time DESC;

This query helps DBAs review when and how statistics were updated.

Best Practices

• Use Real-Time Statistics for OLTP environments where tables are frequently updated.
• Combine Real-Time Statistics with incremental statistics for large partitioned tables in data warehouses.
• Verify feature activation with v$sys_optimizer_env before enabling in production.
• Monitor overhead using DBA_OPTSTAT_OPERATIONS.

Advantages of Real-Time Statistics

• Immediate updates eliminate the problem of stale statistics.
• Reduces the need for manual statistics collection.
• Optimizer uses the most recent data, resulting in better execution plans.
• Simplifies database maintenance by reducing dependency on scheduled jobs.

Real-Time Statistics in Oracle 19c bring DBAs closer to automated performance tuning. By ensuring that statistics remain accurate as soon as data changes, you achieve faster queries, reduced maintenance overhead, and more reliable optimizer decisions.

Pro Tip: Use Real-Time Statistics together with Automatic Indexing for a fully self-optimizing Oracle 19c database environment.