• 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. ORA-00031: session marked for kill
  2. oracleasm Command Examples (Cheat Sheet)
  3. How to Connect After ORA-20 has Occurred
  4. Oracle Database 12.2 RMAN Cross Platform Tablespace Transport Over Network
  5. Auto Space Management for Flashback Logs in the Fast Recovery Area – Oracle 19c New Feature
  6. How to Create a Physical Standby Database by Using SQL and RMAN Commands
  7. How Realms Work in Oracle Database Vault
  8. Common Init.ora Parameters and Unix, Linux Kernel Parameters and Relationship Between Them
  9. What are Oracle Key Vault Roles
  10. Upgrading to Oracle 12c using RMAN DUPLICATE with “NOOPEN” clause and “BACKUP LOCATION”

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright