 |
|
Important notes for global SQL optimization
Oracle Tips by Burleson Consulting
October 14, 2007
|
Oracle professionals must always
undertake to perform holistic tuning before delving into the
tuning of specific SQL statements, an approach that has been the
bread and butter of successful corporate DBA's since the
earliest days of Oracle6.
In Oracle 11g, we see this
holistic approach codified in the SQL Performance Analyzer (SPA),
a new tool to simplify the setting of optimizer statistics and
initialization parameters. In SPA, the DBA chooses a
representative workload and runs it, comparing the overall SQL
execution plans with different sets of CBO statistics and
settings for the silver bullet initialization parameters.
Prior to Oracle 10g, adjusting the
optimizer_index_cost_adj parameter was the only
cost-effective way for large shops to compensate for sampling
issues in dbms_stats. As of 10g, the use of
dbms_stats.gather_system_stats and improved sampling within
dbms_stats had made adjustments to these parameters far
less important. Today, the savvy DBA always optimizes their CBO
statistics before adjusting their silver bullet parms.
Dealing with time constraints
In a busy Oracle shop, the DBA
often does not have the luxury of undertaking the time-consuming
task of reorganizing fragments tables with dbms_redefinition
and manually gathering optimal CBO statistics, especially the
tedious task identifying columns that require histograms.
Hence, some DBA managers want a quick-fix and undertake to
optimize their workload by lowering their value for
optimizer_index_cost_adj.
For more details on holistic
tuning with parameters, see my book "Oracle
Tuning: The Definitive Reference".
Next, let's see how holistic
workload tuning has changed within each release of Oracle.
A release-centric approach to
holistic optimization
Each release of Oracle brings
enhancements and changes to the way that the Oracle DBA
optimizes their system-wide workloads. In general, these
approaches to global SQL optimization are highly dependent on
the release of Oracle:
-
Oracle6 - Oracle7 - In Oracle7 there
were significant shortcomings to the cost-based optimizer, and many Oracle
professionals were forced to tweak the CBO throttles to achieve the largest
amount of optimized SQL. These techniques include setting
optimizer_mode=rule, and adjusting optimizer_index_cost_adj.
We also saw the introduction of the CBO histogram, an important feature for
SQL optimization.
-
Oracle8 - Oracle8i - In the Oracle8
series of releases, the cost-based optimizer was ready for prime-time and we
saw the introduction of materialized views and enhanced CBO statistics
collection. We saw the introduction of function-based indexes to
alleviate unnecessary full-table scans, and we also saw the dbms_stats
package to allow better collection of schema metadata, to help the CBO
make more intelligent optimization decisions. The rule-based optimizer
(RBO) remained a popular tuning tool for simple databases, and the
adjustment of SQL workload optimization relied on changing
optimizer_mode, optimizer_index_cost_adj and optimizer_index_caching.
We also saw
global temporary tables for optimizing multi-step SQL queries.
Best of all, the BSTAT-ESTAT utility was enhanced with the STATSPACK tool to
allow for the historical storage of tuning information, indispensable for
proactive workload tuning.
-
Oracle9i - In Oracle9i we saw the new
v$sql_plan view to help tune SQL proactively and the dbms_stats
package was enhanced to be more intelligent. Still, the DBA was
frequently forced to optimize their SQL by adjusting the optimizer
parameters. We also saw the ability to collect a "deep" sample with
dbms_stats and save them, a tool that is especially useful for large
shops where production CBO statistics can be exported and imported into test
and development instances, allowing developers to optimize their SQL before
it is introduced into production. We also got the dbms_redefinition
package to allow online reorganization of fragmented tables.
-
Oracle 10g - Oracle 10g greatly aided
the optimization of large SQL workloads with the introduction of dynamic
sampling and root-cause optimization with dbms_stats. Oracle
acknowledged that the root cause of sub-optimal SQL execution plan related
to the quality of CBO statistics, and they introduced enhancements to
dbms_stats to allow for automatic histogram creation and the
gather_system_stats procedure to collecting all-important external
information, most notably the average disk access timings for index access
(sequential reads) and full-scan access (scattered reads). This
changed the landscape for SQL tuning, as it was now possible to address the
root cause of suboptimal execution plans. However, many DBA's who were
under time constraints were unable to undertake the time-consuming analysis
required to verify optimal statistics and they resorted to the "quick fix"
of adjusting the optimizer parameters.
-
Oracle 10g Release 2 - Starting in
Oracle 10g release2, Oracle recommends not setting the
db_file_multiblock_read_count parameter, allowing Oracle to
empirically determine the optimal setting. For more details,
see my notes on
10gR2 automatically tuned multi-block reads.
-
Oracle 11g - In Oracle 11g we see the
promise of a greatly improved dbms_stats package, running 2x faster
and automatically collecting a statistically significant sample size.
We also see the promise of better detection of columns for histograms.
Oracle technical support claims that adjusting the CBO statistics addresses
the root cause of sub-optimal execution and that changes to the
optimizer_index_cost_adj are "rarely" required. However, the DBA
still needs to set the optimal optimizer_mode and optimizer_index_caching.
(You can run
scripts to intelligently set an appropriate value for
optimizer_index_caching). For more details, see
Oracle 11g dbms_stats enhancements.
In sum, the techniques for holistic SQL
optimization change radically by release level, and in Oracle10g and Oracle11g
it is now less time-consuming to fix the root-cause of CBO issues by adjusting
the CBO statistics.
The general holistic SQL optimization steps for
9i, 10g and 11g include addressing the root cause (bad statistics) whenever
feasible:
-
Verify a correct sample size using
dbms_stats and determine an intelligent threshold for re-analyzing
statistics.
-
Examine the low-hanging fruit of high-impact
sub-optimal SQL and add histograms to improve access for queries against
skewed and out-of-bounds queries. Also, add histograms to improve the
CBO's ability to determine the optimal table join order.
-
Run dbms_stats.gather_system_stats to
get external I/O tuning for full-scan vs. index I/O.
-
Intelligently set optimizer_mode and
optimizer_index_caching, testing with a representative workload in a
real-world environment.
-
Optimize the silver bullet parameters
(db_cache_size, db_file_multiblock_read_count, etc.)
-
Set optimizer_index_cost_adj (if required) to
fix any remaining suboptimal execution plans.
-
Finally, tune individual SQL statements using
SQL profiles.
For more details on holistic SQL
tuning, see my book "Oracle
Tuning: The Definitive Reference" and see my related notes:
Tuning with optimizer parameters
