Oracle 19C new features

Real-time Statistics In 19c (Only Exadata)

This feature is only available on Exadata and Exadata Cloud Service.

Oracle Database 19c introduces real-time statistics, which extends online statistics gathering to also include conventional DML statements. Statistics can now be collected 'on-the-fly' during conventional DML operation

Oracle introduced new parameters

"_optimizer_gather_stats_on_conventional_dml" and "_optimizer_use_stats_on_conventional_dml" which are true by default
"_optimizer_stats_on_conventional_dml_sample_rate" at 100%

By default the "_optimizer_gather_stats_on_conventional_dml" is true so the real-time stats automatically kicks off. Can set these parameters to "FALSE" to disable the same. 

USER_TABLES -> NUM_ROWS (no changes)

USER_TAB_STATISTICS -> notes column -> STATS_ON_CONVENTIONAL_DML

USER_TAB_COL_STATISTICS -> notes column -> STATS_ON_CONVENTIONAL_DML

Direct Path INSERT ... SELECT -> No effect

Delete -> No effect

Gathering statistics for a table will wipe out the real-time statistic

High-Frequency Automatic Optimizer Statistics

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON’); EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','300'); Min value allowed is 60 and max is 900 (seconds). 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;

Validate spfile parameters for Primary and Standby

VALIDATE DATABASE {database-name} SPFILE

Schema Only Accounts

It's a common practice to restrict the direct access to a schema owner, preventing people from accessing it using shared credentials. Instead, they access it to do schema changes via proxy connections, allowing you to audit which users performed which tasks. (9i onwards)

NO AUTHENTICATION clause help to connect user even schema owner get locked.


Automatic replication of restore points from Primary to standby

Restore point is replicated through the redo (MRP process) so primary should be in open mode

DML Operations on Active Data Guard Standby Databases

Enable dml redirect on Primary and Standby , run this on both.

alter system set adg_redirect_dml=true scope=both;

we need to connect using username/password." / as sysdba" will not work

Inline External Table - inline_ext_tab (Zero DDL)

There is no need for an external table to be explicitly created.

 

In below example, MY.txt is an external file which has 3 fields.

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;