SQL tuning case study using STATSPACK
In my book "Oracle
Silver Bullets", I note that single corrective actions (change CBO stats,
adding missing indexes, creating materialized views) can have a
profound impact on SQL response time. This effect
has been codified in Oracle 10g's
SQLAccess Advisor, which performs real-world tests and
recommends missing indexes and materialized views.
This publication also has a comment where someone claims an amazing
SQL performance improvement
of 80% using a similar approach:
"[I]t had to be restartable and STILL complete within in 1 hour. Therefore, it
had to complete in 30 minutes. Gulp. . .
we were able to get the critical path processing to around 8
minutes"
STATSPACK for SQL Tuning
In this case study, Lewis uses STATSPACK to identify a missing
index and applies hints to correct "misleading" optimizer statistics
(most likely cardinality estimates). Here we see Lewis making
an important judgment about the SQL statement.
In general, Oracle recommends fixing the core issue (in this
case, "misleading" CBO statistics), rather than treating the symptom
with optimizer directives (hints), but there are exceptions to this
rule, like when the Oracle dbms_stats analyze provides
"misleading" statistics, or a case where there exists one and only
one optimal execution plan for a SQL statement:
"supplying a stack of hints to lock in an execution path that
the optimizer was not going to derive from the gathered
statistics (which were misleading)."
This "lockdown" of execution plans with "a stack of hints" is a common
technique for DBA's who don't like surprises, but it should only be
used when the data distribution will never effect the optimal
execution plan. Oracle also provides Optimizer Plan
Stability (stored outlines) and 10g SQL profiles for making execution plans permanent,
but uses hints has the added benefit of documenting the desired
execution plans.
Measuring the results of SQL tuning
Lewis notes a large reduction in disk I/O from his new index and
hints and the corresponding improvement in
total response time:
"Execution time dropped to less than two minutes - which saved
us enough time to hit the target, with about 10 minutes to
spare."
One surprise was his note of reduced redo activity (lower log
file parallel writes) which is very unusual in tuning DML because
it's very rare to reduce the DML volume through tuning.
Using STATSPACK to identify the reasons for a
performance improvement
Lewis notes exactly how the run-time SQL performance was improved by
his changes, using another STATSPACK report to observe the specific
reductions in system resources:
"The direct impact of fixing the insert/select
was a reduction in
(a) db file scattered reads -
because I was using suitable indexes, and
(b) direct path reads and
direct path writes - because I wasn’t doing a
massive hash join an more.
An indirect benefit was a small reduction in
buffer busy waits (of the type that 10g calls
“read by other session”)"
A fascinating anomaly
However, "fixing the update" rarely reduces the number of rows
updated unless the original SQL was incorrectly
updating blocks with duplicitous information. If a SQL
statement is required to update 5m rows, it's required to update 5m
rows and anything less would give an incorrect result.
"The direct impact of fixing the update was a
reduction in log file parallel
writes and log file
syncs (because of the 5,000,000
rows that were no longer being updated - worth
about about 400MB of redo).
With the introduction of the index, we’ll
then have another significant drop in
db file scattered reads."
Evidently, this DML statement was making unnecessary updates, a
very strange event indeed:
"My first fix on the update was to
add the predicate “where flag != 0″. It’s surprising
how many times a simple detail like that is missed. Now, instead
of updating about 5 million rows per night, we update a few
hundred. "
Superfluous updates - Oracle flaw, or Oracle
feature?
If it's true that updating a data block with identical data (i.e.
UPDATE HISTORY SET FLAG=0 WHERE FLAG=0) caused the
data block to be marked as dirty (causing redo overhead), Lewis may have identified a
subtle shortcoming of Oracle.
It appears that the DML UPDATE flaw was something like this,
where the DML fails to test for a true SET clause in the WHERE
statement:
-- zillion row update
UPDATE HISTORY SET FLAG=0 WHERE CLASS='X'
-- hundred row update
UPDATE HISTORY SET FLAG=0 WHERE CLASS='X' AND FLAG!=0
We would have expected (at least, I did, anyway) that Oracle would check to see if a SQL
update statement failed to result in any row changes.
A simple test could protect against "false updates", and
perhaps bypass the update and send an alert log message that a DML statement
is doing superfluous updates.
Here are other tips for
Oracle update
optimal performance best practices.
If you are new to STATSPACK, try out
www.statspackanalyzer.com,
an expert system to help you learn how to spot system-wide
performance bottlenecks.
 |
If you want to learn more about Oracle SQL tuning with STATSPACK, see my book "Oracle
Tuning: The Definitive Reference". It has several hundred pages
devoted to SQL tuning with elapsed-time AWR and STATSPACK reports.
|
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
|
