Manage Optimizer Statistics on Autonomous Data Warehouse

Describes Autonomous Data Warehouse commands to run when you need to gather optimizer statistics or enable optimizer hints.

Managing Optimizer Statistics

Autonomous Data Warehouse gathers optimizer statistics automatically for tables loaded with direct path operations issued in SQL (direct path load operations that bypass the SQL data processing, such as SQL*Loader direct path, do not collect statlistics). For example, for loads using the DBMS_CLOUD package the database gathers optimizer statistics automatically.

If you have tables modified using conventional DML operations you can run commands to gather optimizer statistics for those tables. For example, for the SH schema you can gather statistics for all tables in the schema using the following command:

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

This example gathers statistics for all tables that have stale statistics in the SH schema.

Note:

For more information about direct-path loads see Loading Tables.

For more information on optimizer statistics see Database Concepts.

Managing Optimizer Hints

Autonomous Data Warehouse ignores optimizer hints and PARALLEL hints in SQL statements by default. If your application relies on hints you can enable optimizer hints by setting the parameter OPTIMIZER_IGNORE_HINTS to FALSE at the session or system level using ALTER SESSION or ALTER SYSTEM. For example, the following command enables hints in your session:

ALTER SESSION
   SET OPTIMIZER_IGNORE_HINTS=FALSE;

You can also enable PARALLEL hints in your SQL statements by setting OPTIMIZER_IGNORE_PARALLEL_HINTS to FALSE at the session or system level using ALTER SESSION or ALTER SYSTEM. For example, the following command enables PARALLEL hints in your session:

ALTER SESSION 
   SET OPTIMIZER_IGNORE_PARALLEL_HINTS=FALSE;