Manage Optimizer Statistics

Describes Autonomous Transaction Processing commands to run when you need to gather optimizer statistics or enable optimizer hints.

Managing Optimizer Statistics

Autonomous Transaction Processing gathers optimizer statistics automatically so that you do not need to perform this task manually and this helps to ensure your statistics are current. Automatic statistics gathering is enabled in Autonomous Transaction Processing and runs in a standard maintenance window.

Note:

For more information on maintenance window times and automatic optimizer statistics collection, see Oracle Database Administrator’s Guide.

For more information on optimizer statistics see Oracle Database SQL Tuning Guide.

Managing Optimizer Hints

Autonomous Transaction Processing honors optimizer hints and PARALLEL hints in SQL statements by default. You can disable optimizer hints by setting the parameter OPTIMIZER_IGNORE_HINTS to TRUE at the session or system level using ALTER SESSION or ALTER SYSTEM. For example, the following command disables hints in your session:

ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=TRUE;

You can also disable PARALLEL hints in your SQL statements by setting OPTIMIZER_IGNORE_PARALLEL_HINTS to TRUE at the session or system level using ALTER SESSION or ALTER SYSTEM.

ALTER SESSION SET OPTIMIZER_IGNORE_PARALLEL_HINTS=TRUE;