This is a preview of the forthcoming book Oracle 11g New Features from Rampant TechPress, featuring John Garmany, Steve Karam and Lutz Hartmann.
The declarative nature of SQL syntax has always made it difficult to perform SQL tuning. The basic tenet of cost-based SQL optimization is that the person who writes a SQL query simply "declares" what columns they want to see (the SELECT clause), the tables where the columns reside (the FROM clause), and the filtering conditions (the WHERE clause). It's always up to the SQL optimizer to determine the optimal execution plan. This is a formidable challenge, especially in a dynamic environment, which is why Oracle introduced the 10g new feature of CBO dynamic sampling.
Oracle tuning consultants have know for many years that the best way to tune an Oracle system is to take a top-down approach, finding the optimal configuration for external factors (OS kernel settings, disk I/O subsystem) and determining the best overall setting for the Oracle instance (init.ora parameters).
Holistic tuning involves tuning a representative workload, adjusting global parameters in order to optimize as much SQL as possible. Only then is it prudent to start tuning individual SQL statements. Many Oracle professionals who adopt a bottom-up approach (tuning the SQL first) find all of their hard work undone when a change is made to a global setting, such as one of the SQL optimizer parameters or recomputing optimizer statistics. Oracle's holistic SQL tuning approach is new, and many Oracle professionals find it difficult to embrace, but this is about to change. The Oracle 11g SQL Performance Analyzer (SPA) is primarily designed to speed up the holistic SQL tuning process, automating much of the tedium.
Once you create a workload (called a SQL Tuning Set, or STS), Oracle will repeatedly execute the workload, using sophisticated predictive models (a regression testing approach) to accurately identify the salient changes to execu
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
tion plans, based on your environmental changes.
Using SPA, we can predict the impact of system changes on a workload, and we can forecast changes in response times for SQL after making any change, like parameter changes, schema changes, hardware changes, OS changes or Oracle upgrades; any change that influences SQL plans is a good candidate for SPA.
Decision support and expert systems technology
Oracle 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 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) and 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 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:
Instead of using theory and mathematical calculations, Oracle SPA tests 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.
Inside the Oracle 11g SQL Performance Analyzer
The Oracle 11g SQL Performance Analyzer is a step in the direction of fully automated SQL tuning, allowing the database administrator to create an STS "workload," a unified set of SQL which comes from either the cursor cache (Shared Pool) or from the AWR (Automated Workload Repository). The DBA can use exception thresholds to select the SQL for each STS, based on execution criteria such as disk reads, consistent gets, executions, etc. Once the DBA has chosen the STS, SPA allows them to run the workload while changing Oracle environmental factors, namely the CBO release level, init.ora parameters and customized hypothesis testing using the guided workflow option.
The central question becomes which Oracle initialization parameters would be the most appropriate within the SQL performance analyzer. Because the SPA is used to measure changes in SQL execution plans, it only makes sense that we would want to choose those Oracle parameters which will influence the behavior of the optimizer.
These would include the basic Oracle optimizer parameters (including optimizer_index_cost_adj, optimizer_mode, optimizer_index_caching), as well as other important initialization parameters. We also have non-optimizer parameters which effect SQL execution plan decisions.
Let's now see how the SPA captures changes in SQL execution plans.
A trip to the SPA
Until the advent of the Oracle 10g intelligent SQL tuning advisors (The SQLAccess advisor and SQLTuning Advisor), SQL tuning was a time-consuming and tedious task. That all started to change in Oracle 10g, and it's even more exciting in Oracle 11g, where Oracle has promised "fully automated" SQL tuning via the new SQL Performance Analyzer and improvements in the SQL advisories.
The Oracle 10g automatic tuning advisor allowed us to implement tuning suggestions in the form of SQL profiles that will improve performance. Now with Oracle11g, the DBA can tell Oracle to automatically apply SQL profiles for statements whenever the suggested profile gives three times better performance that the existing statement. These performance comparisons are done by a new 11g administrative task that is executed during a user-specified maintenance window. In a nutshell, the 11g fully automated SQL tuning works like this:
Tip: Also related is the Oracle 11g automated SQL tuning advisor, whereby you can automatically implement changes that cause your SQL to run more than three times faster. The Oracle 11g automated SQL tuning advisor will implement all execution plan changes via "SQL Profiles," a tool that is conceptually similar to stored outlines, a method to bypass the generation of execution plans for incoming SQL, replacing it with a pre-tuned access plan. The automatic SQL tuning advisor also recommends restructuring badly formed SQL and adding missing indexes and materialized views, but these require a manual decision.
Before we examine the nuances of the 11g fully automated SQL tuning features, let's briefly review the goals of SQL tuning.
The goals of holistic SQL tuning
Holistic tuning in Oracle 11g is a broad-brush approach that can save thousands of hours of tedious manual SQL tuning. By applying global changes, the DBA can tune hundreds of queries at once and implement them via SQL profiles.
DBAs who fail to do holistic SQL tuning first (especially those who tune SQL with optimizer directives) may find that subsequent global changes (e.g., optimizer parameter changes) may un-tune their SQL By starting with system-level tuning, the DBA can establish an "optimal baseline" before diving into the tuning of individual SQL statements:
Now, Oracle 11g does not have all of the intelligence of a human SQL tuning expert, but the 11g SQL Performance Analyzer (SPA) is a great way to test for the effect of environmental changes to your Oracle environment.
Let's take a closer look at how Oracle has automated the SQL tuning process with SPA.
Continue reading "Inside the Oracle 11g SQL Performance Advisor, part 2."
