Oracle 19c Features and Enhancements

This document highlights several key features and enhancements introduced or improved in Oracle Database 19c, focusing on their functionality and practical implications.

1. Real-time Statistics (Exadata Only)

Oracle Database 19c introduces real-time statistics, extending online statistics gathering to include conventional DML statements. This feature allows statistics to be collected "on-the-fly" during INSERT, UPDATE, and MERGE operations.

• Availability: This feature is only available on Exadata and Exadata Cloud Service.

• Functionality: Statistics are gathered dynamically during conventional DML, providing the optimizer with up-to-date information for plan generation.

• Parameters (Default TRUE):

  • _optimizer_gather_stats_on_conventional_dml: Controls whether real-time statistics are gathered.

  • _optimizer_use_stats_on_conventional_dml: Controls whether the optimizer uses real-time statistics.

  • _optimizer_stats_on_conventional_dml_sample_rate: Defaults to 100%, indicating the sampling rate for collection.

• Disabling: You can set these parameters to FALSE to disable real-time statistics.

• Impact on Dictionary Views:

  • USER_TABLES.NUM_ROWS: This column does not reflect real-time statistics changes.

  • USER_TAB_STATISTICS.NOTES: Will show STATS_ON_CONVENTIONAL_DML if real-time stats are active.

  • USER_TAB_COL_STATISTICS.NOTES: Will also show STATS_ON_CONVENTIONAL_DML for columns.

• Limitations:

  • Direct Path INSERT ... SELECT: Real-time statistics have no effect.

  • DELETE operations: Real-time statistics have no effect.

  • Gathering statistics for a table (e.g., using DBMS_STATS) will wipe out the real-time statistics for that table.

2. High-Frequency Automatic Optimizer Statistics

This feature allows for more frequent, granular collection of optimizer statistics, improving the accuracy of execution plans.

• Enabling/Configuring:

  EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON'); -- Ensure auto tasks are on
  EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','300'); -- Set interval to 300 seconds (5 minutes)
  
  

• Interval: The minimum allowed value for AUTO_TASK_INTERVAL is 60 seconds, and the maximum is 900 seconds.

• Monitoring: You can check the execution status of automatic statistics gathering tasks using:

  SELECT OPID, ORIGIN, STATUS, TO_CHAR(START_TIME, 'DD/MM HH24:MI:SS' ) AS BEGIN_TIME,
         TO_CHAR(END_TIME, 'DD/MM HH24:MI:SS') AS END_TIME, COMPLETED, FAILED,
         TIMED_OUT AS TIMEOUT, IN_PROGRESS AS INPROG
  FROM DBA_AUTO_STAT_EXECUTIONS
  ORDER BY OPID;
  
  

3. Validate SPFILE Parameters for Primary and Standby

Oracle 19c introduces a command to validate SPFILE parameters, which is particularly useful in Data Guard environments to ensure consistency between primary and standby databases.

• Command:

  VALIDATE DATABASE {database-name} SPFILE;
  
  

  Replace {database-name} with the actual database name. This command checks for discrepancies or issues in the SPFILE.

4. Schema Only Accounts

Introduced earlier (from 9i onwards), schema-only accounts are a security best practice to restrict direct login access to schema owners.

• Purpose:

  • Restrict Direct Access: Prevents users from directly logging in as the schema owner using shared credentials.

  • Proxy Connections: Users access the schema to perform DDL/DML changes via proxy connections, where a different user (with a password) connects and then proxies to the schema-only account.

  • Auditing: Allows for better auditing, as you can track which specific proxy user performed which tasks within the schema.

• NO AUTHENTICATION Clause: When creating a schema-only account, the NO AUTHENTICATION clause is used. This allows a user to be created without a password, meaning direct connections are impossible, but proxy connections are enabled. This ensures that even if the schema owner account conceptually becomes "locked" (e.g., due to no password), proxy connections can still function.

5. Automatic Replication of Restore Points from Primary to Standby

In Oracle Data Guard, restore points can now be automatically replicated from the primary database to the standby database.

• Mechanism: Restore points are replicated through the redo stream, which is then applied by the Managed Recovery Process (MRP) on the standby.

• Primary Database State: For this replication to occur, the primary database must be in OPEN mode.

6. DML Operations on Active Data Guard Standby Databases

Oracle 19c enhances DML redirection capabilities for Active Data Guard, allowing DML operations to be performed on a read-only standby database by transparently redirecting them to the primary.

• Enabling DML Redirect: This feature needs to be enabled on both the Primary and Standby databases.

  ALTER SYSTEM SET adg_redirect_dml=TRUE SCOPE=BOTH;
  
  

• Connection Requirement: When connecting to the standby to perform DML that will be redirected, you must connect using a username/password. Connecting with sqlplus / as sysdba (OS authentication) will not work for DML redirection.

7. Inline External Table - EXTERNAL Clause (Zero DDL)

Oracle 19c introduces the ability to define external tables directly within a SQL query using the EXTERNAL clause, eliminating the need for separate DDL statements to create the external table object. This is often referred to as "Zero DDL" for external tables.

• Concept: There is no need for an external table to be explicitly created as a database object. The definition is embedded directly in the SELECT statement.

• Example: This example reads data from MY.txt (located in the MY_DIR directory), assuming it's a CSV file with three fields: object_id, owner, and object_name.

  SELECT *
  FROM EXTERNAL (
  (
  object_id NUMBER,
  owner VARCHAR2(128),
  object_name VARCHAR2(128)
  )
  TYPE oracle_loader
  DEFAULT DIRECTORY MY_DIR
  ACCESS PARAMETERS (
  RECORDS DELIMITED BY NEWLINE
  BADFILE MY_DIR
  LOGFILE MY_DIR:'inline_ext_tab_as_%a_%p.log'
  DISCARDFILE MY_DIR
  FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  MISSING FIELD VALUES ARE NULL (
  object_id,
  owner,
  object_name)
  )
  LOCATION ('MY.txt')
  REJECT LIMIT UNLIMITED
  ) inline_ext_tab
  ORDER BY 1;
  
  

  • object_id NUMBER, owner VARCHAR2(128), object_name VARCHAR2(128): Defines the column structure of the external data.

  • TYPE oracle_loader: Specifies the access driver.

  • DEFAULT DIRECTORY MY_DIR: Specifies the database directory object where the external file is located.

  • ACCESS PARAMETERS (...): Defines how the data is parsed (e.g., RECORDS DELIMITED BY NEWLINE, FIELDS CSV).

  • LOCATION ('MY.txt'): Specifies the external data file.

  • REJECT LIMIT UNLIMITED: Allows all rows to be processed, even if some have errors.

  • inline_ext_tab: This is an alias for the inline external table definition.