Manage Optimizer Statistics on Autonomous Database
Describes Autonomous Database commands to run when you need to gather optimizer statistics or enable optimizer hints.
There are differences in the commands to run to gather optimizer statistics or enable optimizer hints, depending on your workload: Data Warehouse, Transaction Processing, or JSON Database.
- Manage Optimizer Statistics and Hints with Data Warehouse Workloads
Describes Autonomous Database commands to run when you need to gather optimizer statistics or enable optimizer hints with Data Warehouse workloads. - Manage Optimizer Statistics and Hints with Transaction Processing and JSON Database Workloads
Describes Autonomous Database commands to run when you need to gather optimizer statistics or enable optimizer hints.
Parent topic: Performance Monitor and Management
Manage Optimizer Statistics and Hints with Data Warehouse Workloads
Describes Autonomous Database commands to run when you need to gather optimizer statistics or enable optimizer hints with Data Warehouse workloads.
Manage Optimizer Statistics with Data Warehouse Workloads
Autonomous Database with Data
Warehouse workloads 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 statistics). 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.
For more information about direct-path loads see Loading Tables.
For more information on optimizer statistics see Database Concepts.
Manage Optimizer Hints with Data Warehouse Workloads
Autonomous Database with 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;
Parent topic: Manage Optimizer Statistics on Autonomous Database
Manage Optimizer Statistics and Hints with Transaction Processing and JSON Database Workloads
Describes Autonomous Database commands to run when you need to gather optimizer statistics or enable optimizer hints.
Manage Optimizer Statistics with Transaction Processing and JSON Database Workloads
Autonomous Database 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 Database and runs in a standard maintenance window.
The automatic statistics gathering maintenance window is different than the maintenance window on the Oracle Cloud Infrastructure console. The Oracle Cloud Infrastructure maintenance window shows system patching information.
For more information on automatic statistics gathering maintenance window times and automatic optimizer statistics collection, see Database Administrator’s Guide.
For more information on optimizer statistics see SQL Tuning Guide.
Manage Optimizer Hints with Transaction Processing and JSON Database Workloads
Autonomous Database with
Transaction Processing and JSON Database workloads 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;
Parent topic: Manage Optimizer Statistics on Autonomous Database