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 configuration.

Historically, 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 DBA.

By relieving the Oracle 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 SQL statements.
Starting an Oracle 10g, Oracle introduced an exciting new feature called “SQL profiles, and they are enhanced in Oracle 11g.

Using SQL 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 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 scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.