| |
 |
|
11g Decision Support
and Expert Systems Technology
Oracle 11g New Features Tips by Burleson
Consulting
July 13, 2008 |
Oracle 11g SQL New Features Tips
Oracle had made a commitment to Decision Support
Systems (DSS) Technology starting in Oracle 9i when they started to
publish “advisory” utilities, the result of monitoring the Oracle
instance and coming up with estimated benefits for making a change to
the database configuration. In the world of applied artificial
intelligence, an expert system (e.g. AMM, ASM) solves a
well-structured problem for the DBA, while a decision support system
solves a semi-structured problem with the DBA, who supplies the human
intuition required to solve a complex problem.
Oracle has made a commitment to distinguish
themselves in the database marketplace, and this is one of the major
reasons that they command a major market share. One of the most
exciting areas of Oracle technology is automation, especially the
self-management features. Oracle has now automated many critical
components, including memory advisors (AMM), automated storage
management (ASM), and Oracle is now working to enhance more
intelligent utilities including ADDM, the Automated Database
Diagnostic Monitor, and the brand new 11g SQL Performance Analyzer
(SPA).
The Oracle 11g SPA functions as a DSS, helping
the DBA by automating the well-structured components of a complex
tuning task, such as hypothesis testing. In SPA, the DBA defines a
representative workload and then tests this workload empirically,
running the actual queries against the database and collecting
performance metrics. SPA allows the DBA to obtain real-world
performance results for several types of environmental changes:
- Optimizer
software levels – You can compare SQL execution between
different releases of the cost-based optimizer (CBO)
- Initialization
parameters – You can pre-test changes to global parameters, most
often the Oracle optimizer parameters (optimizer_mode,
optimizer_index_cost_adj, optimizer_index_caching). Prior to
Oracle 10g, adjusting these optimizer parameters was the only way to
compensate for sample size issues with dbms_stats.
- Guided workflow
– This is a hypothesis testing option that allows the DBA to
create customized experiments and validate their hypotheses using
empirical methods.
Instead of using theory and mathematical
calculations, Oracle SPA tests the SQL Tuning Set (STS) workload in a
real-world environment, running the workload repeatedly while using
heuristic methods to tally the optimal execution plan for the SQL.
The DBA can then review the changes to the execution plans and tune the
SQL (using the SQL Tuning Advisor) to lock-in the execution plans
using SQL profiles.
Let’s take a closer look at SPA and see how
holistic SQL tuning can remove the tedium of tuning SQL statements.
This is an
excerpt from the new book
Oracle 11g New Features: Expert Guide to the Important New
Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain,
Brian Carr.
You can buy it direct from the publisher for 30% off. |
