Oracle 11g tuning: Oracle 11g and Expert Systems Technology
Oracle Tips by Burleson Consulting
Oracle 11g and Expert Systems Technology
Oracle 11g is starting to employ Expert Systems Technology, an
exciting innovation. For more details on these 11g
expert system features, see my book "Oracle
11g New Features", available in Fall 2007.
Oracle had made a commitment to Expert System 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
Oracle has made a commitment to distinguishing themselves in the database
marketplace, and this is one of the major reasons that they have more market
share than SQL Server and DB2 put together. One of the most exciting areas of
Oracle technology is in 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 automated SQL tuning utility.
The Oracle database administrator does not rely entirely on well structured
decision rules. Within the realm of “expert systems” technology, we see that the
goal of expert systems is to automate the well-structured decision casts of the
person for whom the system is written, in this case the Oracle Remote DBA.
By relieving the Oracle Remote DBA of the tedious well-structured decision task, Oracle
frees up the database administrator to do more semi structured tasks, tasks
which require pure human intuition. Let’s take a closer look at expert systems
technology and its application within the Oracle database arena.
Oracle has the habit of introducing new expert systems technology first as an
advisor utility, and later after it has been thoroughly debugged, Oracle will
close the loop, and fully implement the technology. For example, Oracle SQL
tuning has always been challenging, but one of its hallmarks is that it’s
distinguished by well-defined decision rules. Let’s take a closer look.
Way back in the 1990's, Oracle introduced utilities such as Optimizer Plan Stability
“stored outlines”, that would allow the Oracle database administrator to change
code without effecting the actual SQL source code. This is a godsend for
application that relied on vendor tools, such that the database administrator is
not allowed the SQL source code, but still has the need to tune the individual
Starting an Oracle 10g, Oracle introduced an exciting new feature called “SQL
profiles, and they are enhanced in Oracle 11g.
Profiles, Oracle allows the database administrator to override the default
execution plan for a SQL statement, thereby allowing Oracle to change the
execution for a SQL statement without touching the actual SQL source code. This
is an extension of Oracle’s query rewrite capability, commonly used with Oracle
materialized views, whereby aggregations and table joins can be pre-aggregated,
and the SQL rewritten to automatically access the pre-computed results.
Oracle has made exciting headway with SQL profiles in Oracle 11g, by closing the
loop and allowing the SQL profiles to be automatically implemented.
Traditionally, Oracle has tune SQL by making external recommendations
“recommending new indexes, recommending new materialized views”, but the Oracle
11g automated SQL tuning takes this one step further.
In Oracle 11g
automated SQL tuning, the Remote DBA defines a representative workload, and Oracle
tests this work load empirically, against the database. Instead of using theory
and calculations, Oracle tests the SQL in a real world environment running it
repeatedly and determining heuristically the optimal execution plan for the SQL.
Oracle then builds SQL profile and implements it directly.
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.