Decision Support and Expert Systems Technology

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 distinguishing 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 Advisory (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 release 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).
  
• 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 execution plans and tune the SQL (using the SQLTuning 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