Oracle automatic SQL tuning is a misnomer, a product of the Oracle
marketing machine.
As I note in my book "Oracle
Tuning: The Definitive Reference", SQL tuning is extremely complex,
and Oracle has never made a SQL tuning tool that can make anything more
than simple recommendations for missing indexes and materialized views.
The first attempt "Oracle Expert" was a total joke, a resource hungry
tool that cranked for hours and then made ridiculous recommendations.
In Oracle 11g, Oracle has made some progress toward automatic SQL tuning
by applying real-world workloads and measuring changes to execution
plans, but it still tunes with about the same skill as a trained
dolphin.
Just like it's predecessors, the Oracle automatic SQL tuning tool
still makes ludicrous recommendations, but it is a step in the right
direction. The process of tuning SQL is a semi-structured task,
where some decision rules exist, but it still requires human intuition
and there is no technology that will approach the ability of a human SQL
tuning expert.
Today, "fully automatic SQL tuning" is a DSS tool, as it should be.
In a decision support system, the tools managed the easy,
well-structured components of the SQL tuning process (the benchmark
testing), and the human expert sets-up the tuning tests.
The
human expert provides the intelligence and parameters, and Oracle "SQL
Tuning Advisor" grabs the real-world workload and manages the
well-structured component of the tuning process.
For more, see my related notes on Oracle automatic SQL tuning:
-
-
-
-
-
