Databases Reference
In-Depth Information
Likewise, it is common on Exadata to deploy data warehouses or other databases with large, partitioned tables.
Incremental statistics is a means to reduce statistics collection times and system resource utilization for partitioned
tables and is a good thing to consider when deploying on Exadata.
One of the exciting optimizer statistics enhancements provided in recent patch levels on Exadata is the
“Exadata-aware” system statistics. Prior to this enhancement, it was common for the optimizer to favor index scans
over smart-scan capable full scans simply because the optimizer's costing algorithms had no way of quantifying the
performance benefit of Smart Scans. With
DBMS_STATS.GATHER_SYSTEM_STATS('EXADATA')
, Oracle is able to seed
meaningful system statistics that the optimizer is able to evaluate in costing alternate access paths. This can lead the
optimizer to favor full-scan operations over index range scans and take advantage of the power of Exadata
Smart Scans.
Before winding down this recipe, it is worth mentioning that the tips provided above are certainly general in
nature; optimizer statistics collection preferences and procedures definitely are not a “one-size-fits-all” solution.
We have provided advice for using automatic statistics collection with AutoTasks (the default in 11g) as well as a series
of database-wide or system-wide automatic statistics preferences. Depending on your workload, you can override
these settings at a table or segment level to provide the statistics you desire. Some common things to consider include
the following:
DBMS_AUTO_TASK_ADMIN.DISABLE
procedure. This is sometimes required if your application requires a non-standard statistics
collection framework, such as Oracle e-Business Suite or other commercial off-the-shelf
applications.
•
Disabling automatic statistics collection using the
•
Using extended statistics or multi-column statistics to provide the optimizer with additional
statistics information for columns with correlated values.
•
Using expression statistics for columns in which a function is applied to a column in query
predicates.
•
Adjusting statistics preferences on a per-table, per-column, or per-segment basis in situations
when you have a better idea of what the statistics should be than
DBMS_STATS
ever would.
As each of these points and more are truly based on knowledge of your application and workloads, we will not
cover any detail on any of the above. Please refer to Oracle's documentation on the optimizer and
DBMS_STATS
for
more information.