Of the five statements, four of them improved,
and one had no change. A section for one of the
statements that improved appears as follows.
Figure 8.4:
Results
Table
The execution plan section shows that a full
table scan was used before and the index was
used after the change.
Figure 8.5:
Execution Plan Comparisons
Miscellaneous items
All in all, this was not too hard to setup and
get running. There may be odd errors along the
way when varying the input parameters (ORA-01478
array bind error), so try using a different
format with respect to named versus positional
parameters.
If there is a need to start over, use the DELETE
or DROP_SQLSET procedure in
DBMS_SQLTUNE. Try to make the
statements as clean as possible such as avoiding
extra SELECT statements and making the statement
identification easy to find. For example, a
table could be aliased with XXX, so that string
would be unusual to find in Oracle but easy to
filter on.
Enterprise Manager offers a streamlined
interface into the SQL Performance Analyzer.
Even though the tuning set and report were
manually created, they will still be available
in OEM. As an example, drilling down to
TASK_35 shows a graphical
comparison between sets. The two regressed SQL
statements were related to internals of
executing the two packages.
Figure 8.6:
SQL Performance Analyzer Graphical Results
As mentioned in the beginning, SPA can capture
differences due to most anything that affects an
execution plan. Aside from DDL operations such
as index creation or using hints, two other
major elements can factor in to changes. One is
the compatibility parameter and the other is
generic parameter changes.
A change in the optimizer setting is specific
enough to not be counted as a parameter change.
Although it is a parameter, the setting is
intrinsic to how the optimizer works as a whole.
An example of a normal parameter change would be
changing the
OPTIMIZER_INDEX_COST_ADJ or
DB_FILE_MULTIBLOCK_READ_COUNT
settings.
Now that the API interface is now visible, that
is, the actual
DBMS_SQLPA and
DBMS_SQLTUNE to create the SQL
tuning set, the guided workflow in Enterprise
Manager will be much easier to follow along.
To sum up so far, the SQL Performance
Analyzer can be used to help validate or explore
changes. It can help overcome a 100% copy of the
production environment, and unlike ADRCI, SPA
can be back ported to releases prior to 11g.
MetaLink note 560977.1, “Real Application
Testing Now Available for Earlier Releases,”
details the applicable versions.
After applying a patch via the opatch utility,
one can capture on the older versions. However,
only replay on version 11g and higher.
Finally, since SQL Performance Analyzer falls
under Real Application Testing, it must be
licensed. Database Replay, SQL Performance
Analyzer and SQL Tuning Sets (STS) are
licensable. STS can be used if the DBA licensed
the Tuning Pack.
Other Utilities
The advisory framework introduced in Oracle 10g
opened up quite a few portals into Oracle’s
internals. The RDBMS is far more instrumented
than it has ever been, and hopefully that trend
will continue. If one works in a split, with
respect to releases, environment, how many times
one wished feature X in 10g were available in
8i? That is not just limited to advisory and
diagnostics, either.
Flashback tabling to before some SCN is much
easier to perform than a tablespace point in
time recovery.
It can take hours to recover from an errant
commit in an older version while the time in a
newer one could be a matter of seconds.
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
