Oracle Autonomous Data Warehouse

Buğra PARLAYAN

12 months ago

In today’s article, we will be cover about Oracle Autonomous Data Warehouse Optimizer Statistics Management.

Autonomous Data Warehouse has the ability to automatically collect statistical information for tables added directly on SQL.

For example, optimizer statistics are collected automatically for processes using the DBMS_CLOUD package.

If you have a table that changes using the DML operations we routinely use, you can do the following to gather statistics for those tables.

The example shows the collection of statistics for all tables using the “SH” schema.

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS('SH', options=>'GATHER AUTO');
END;
/

BEGIN

DBMS_STATS.GATHER_SCHEMA_STATS('SH', options=>'GATHER AUTO');

END;

However, Autonomous Data Warehous by default ignores the use of optimizer and Parallel hint in SQL statements. In other words, it comes disabled.

If your application uses hint, you need to edit the ” OPTIMIZER_IGNORE_HINTS ” parameter at system or session level.

It can be activated by setting the parameter “FALSE”.

ALTER SESSION
   SET OPTIMIZER_IGNORE_HINTS=FALSE;

1 2	ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=FALSE;

ALTER SESSION 
   SET OPTIMIZER_IGNORE_PARALLEL_HINTS=FALSE;

1 2	ALTER SESSION SET OPTIMIZER_IGNORE_PARALLEL_HINTS=FALSE;