Oracle DBA

Empowering the Optimizer with Precision at Every Change

In the symphony of data, timing is everything. The modern enterprise pulses with an unceasing flow of transactions—millions of data changes racing through OLTP engines. Amid this dynamism, the Oracle optimizer stands as a maestro, orchestrating query performance. Yet, its brilliance is shadowed if it relies on statistics that have already become relics of the past. Herein lies the revolutionary promise of Real-Time Statistics (RTS) in Oracle Database 19c—a feature that breathes new life into the cost-based optimizer, ensuring its guidance is always in harmony with the ever-evolving data landscape.

The Challenge: Stale Statistics and Their Impact

Traditionally, statistics in Oracle databases were collected by scheduled jobs, such as nightly or weekly DBMS_STATS executions. This cadence, while sufficient for static or slow-changing datasets, often falters in volatile environments where tables experience continuous DML (Data Manipulation Language) activity—INSERT, UPDATE, MERGE, DELETE. The optimizer, starved for fresh insights, crafts execution plans based on outdated or stale statistics, leading to suboptimal query performance, inefficient joins, and, ultimately, dissatisfied users.

Common Pain Points Before RTS:

• Queries running slower after heavy DML.
• Manual intervention required to refresh statistics.
• Batch jobs lagging behind real-time business operations.
• Complex solutions—such as frequent stats jobs—burdening the system with additional I/O.

Oracle 19c Real-Time Statistics: A Paradigm Shift

With Oracle 19c, Real-Time Statistics transforms this paradigm. The database itself, ever-vigilant, now gathers critical optimizer statistics in the moment—as DML occurs. Whether a record is inserted at noon, a bulk load at midnight, or a schema evolves on the fly, the optimizer remains perpetually informed. The result?
Execution plans crafted not on the shadows of yesterday, but the living, breathing reality of now.

Key DML Triggers for RTS:

• INSERT
• UPDATE
• MERGE
• CREATE TABLE AS SELECT (CTAS)
• Direct-Path Loads

How Real-Time Statistics Work – Under the Hood

Upon executing relevant DML operations, Oracle 19c:

1. Automatically collects column and table statistics at the point of data change.
2. Integrates these stats into the dictionary and memory—making them immediately available to the optimizer.
3. Skips waiting for the next DBMS_STATS job, thus removing any window for statistics staleness.

Oracle Internal Process Flow:

• DML triggers an internal stats collection engine.
• Stats are maintained in an internal cache, then persisted to data dictionary tables.
• The cost-based optimizer queries these up-to-the-moment stats for every plan generation.

Configuring and Validating Real-Time Statistics

Is Real-Time Statistics Enabled?

Check with:

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

• TRUE: Feature is active and operational.
• FALSE: Feature is disabled; stats are updated only by DBMS_STATS jobs.

How to Enable or Tune:

By default, RTS is enabled for conventional DML in Oracle 19c. For advanced tuning or legacy upgrades, the following parameter is relevant:

ALTER SYSTEM SET "_optimizer_use_stats_on_conventional_dml" = TRUE SCOPE=BOTH;

Note: The underscore parameter is undocumented; use with caution and always consult Oracle Support before changing in production environments.

Deep Dive: Demonstration of Real-Time Statistics

Step 1: Create and Populate a 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: Observe Initial Statistics

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

• If statistics are missing, Oracle 19c will estimate as needed or leverage RTS on subsequent DML.

Step 3: Execute DML & Witness RTS Magic

INSERT INTO orders VALUES (50001, 1001, SYSDATE, 2000);
COMMIT;
SELECT num_rows, last_analyzed 
FROM dba_tables 
WHERE table_name = 'ORDERS';

• num_rows and last_analyzed update instantly—no DBMS_STATS job required.

Step 4: CTAS and Direct-Path Inserts

CREATE TABLE new_orders AS SELECT * FROM orders;
SELECT num_rows, last_analyzed 
FROM dba_tables 
WHERE table_name = 'NEW_ORDERS';

• Observe that the statistics are instantly available.

Monitoring Real-Time Statistics Operations

Oracle empowers DBAs to audit the invisible hand of RTS via:

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

• Review when stats were gathered and which tables/partitions were involved.

Advanced Considerations & Integration with Other Features

RTS and Incremental Statistics

• For large, partitioned tables, RTS integrates with incremental stats to ensure partition maintenance does not overwhelm stats gathering.

RTS and Automatic Indexing

• Oracle’s self-driving vision shines brightest when Real-Time Statistics work in tandem with Automatic Indexing—letting the optimizer not only learn from the freshest data but also reshape indexes proactively.

Performance Overhead?

• RTS is lightweight for OLTP and modest batch workloads. For extreme ETL or batch jobs, monitor DBA_OPTSTAT_OPERATIONS to assess impact.

Best Practices for Real-Time Statistics

• Leverage in OLTP/Hybrid environments: Where data is volatile, RTS brings the most benefit.
• Monitor regularly: Use DBA views to validate that stats are current and system performance is optimal.
• Combine with legacy stats gathering for safety: In data warehouses, periodic full stats may still be prudent for edge-case accuracy.
• Test before production: Every system is unique—pilot RTS on a non-critical schema before wide deployment.

Key Advantages: A Quick Recap

• Eliminates Stale Statistics: The optimizer’s vision is always sharp.
• Reduces Manual Intervention: Less need for DBMS_STATS scripts and jobs.
• Boosts Performance: Execution plans are aligned with reality.
• Enhances Automation: Paves the way to true self-tuning databases.
• Simplifies DBA Life: Less routine maintenance, more value-add activity.

Real-World Insights: When Real-Time Statistics Shine

Imagine a financial institution where trading records are updated by the second, or an e-commerce platform handling a tidal wave of transactions during a festival sale. In such scenarios, relying on periodic stats is a recipe for performance debt. Real-Time Statistics, combined with features like SQL Quarantine, Automatic Indexing, and Adaptive Plans, transforms the Oracle database into a living, learning organism—agile, responsive, and always a step ahead.

Pro Tip for Modern DBAs

For next-level automation:
Combine Real-Time Statistics with Automatic Indexing and SQL Plan Management.
Let Oracle do the heavy lifting while you focus on strategy, architecture, and business enablement.

In Oracle 19c, Real-Time Statistics are not merely a feature—they are a leap toward a self-driving database future. By erasing the lag between data changes and optimizer insight, Oracle empowers businesses to operate at the speed of thought. Let your queries dance in time with your data—no delays, no uncertainty, just pure performance.