• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle 19c New Feature – Real-Time Statistics

by admin

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.

Checking for real-time statistics gathering

1. Generate explain plan/execution plan for the operation. The Note field shows that the query used real-time statistics.

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

2. If real-time statistics gathering is used, then the view “DBA_TAB_COL_STATISTICS” and “DBA_TAB_STATISTICS” will contain STATS_ON_CONVENTIONAL_DML in the NOTES column and SHARED in the SCOPE column.

Disabling real-time statistics gathering

The NO_GATHER_OPTIMIZER_STATISTICS hint prevents the collection of real-time statistics.

Example:

SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ ...

Filed Under: oracle, Oracle 19c

Some more articles you might also be interested in …

  1. How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command
  2. How long does Oracle retain the statistics for
  3. Understanding Oracle Database Recyclebin Features and How to Disable it
  4. Oracle Data Guard 12c New Feature: Far Sync Standby
  5. New Connections to the Database lead to ORA-12518 or TNS-12518
  6. Handling Exceptions in PL/SQL
  7. How to Check the Environment Variables for an Oracle Process
  8. Oracle Database – How to Rename a Datafile with Special Characters Created by Mistake
  9. Oracle RMAN: Monitoring Recovery Manager Jobs
  10. ORA-354 ORA-353 and ORA-312: Possible corruption in Online Redo Log File Members in a Redo Log Group

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright