Inside the Oracle 11g SQL Performance Advisor, part 1

This preview from the forthcoming book "Oracle 11g New Features" describes the goals of holistic SQL tuning and the steps to perform automated SQL tuning with Oracle 11g's new SQL Performance Advisor feature.

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 execution 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:

  • Optimizer software levels -- You can compare SQL execution between different releases 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 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.

  • db_file_multiblock_read_count -- When this parameter is set to a high value the Oracle CBO recognizes that scattered multiblock reads may be less expensive than sequential reads. (i.e., full table scans and full index scans).
  • parallel_automatic_tuning -- When set to "on" the Oracle optimizer will parallelize legitimate full table scans. Because we have told Oracle that parallel full table scans can be done very quickly using parallel query, the CBO will assign a higher cost index access, making the optimizer friendlier to full table scans.
  • hash_area_size (if not overridden by pga_aggregate_target) -- The setting for hash_area_size governs the propensity of Oracle's optimizer to favor hash joins over nested loop and for merge joins. This makes it an ideal testing parameter for changes to Oracle memory regions so that you can see how they would be affected within a production environment.
  • pga_aggregate_target -- The settings for pga_aggregate_target have a profound impact on the behavior of Oracle SQL statements, making this an interesting test case for the SQL performance analyzer, especially with regard to the propensity of the Oracle optimizer to do in memory sorts and hash joins.
  • sort_area_size (if not overridden by pga_aggregate_target) -- The sort_area_size parameter influences the CBO when deciding whether or not to perform index access or to perform a sort of the ultimate results set from the SQL query. The higher the value for sort_area_size the more likely it will be that the Oracle 11g optimizer will invoke a backend sort, because it knows that the sort can be performed in memory. Of course, this depends upon the Oracle optimizer's estimated cardinality for the results set of the SQL query.

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:

  1. Define the SQL workload -- The DBA defines a set of problematic SQL statements using exception thresholds (e.g., all SQL with greater than 100,000 disk reads), select from the cursor cache or the AWR. This is called the SQL Tuning set, or STS.
  2. Set up a changed environment -- Here you can choose to change your initialization parameters, test your performance against a previous release of the CBO (a very useful features when testing upgrades) or conduct custom experiments on the effect of environmental changes on your SQL tuning set.
  3. Schedule and run your tests -- The workload is scheduled for execution during low-usage periods, so that an empirical sample of real-world execution times can be collected and compared, using different execution plans.
  4. Implement the changes -- You can flag SQL statements for changes and tune them with the 10g SQLTuning advisor.

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:

  • Optimize the server kernel -- Always tune your disk and network I/O subsystem (RAID, DASD bandwidth, network) to optimize the I/O time, network packet size and dispatching frequency. Kernel settings have an indirect effect on SQL performance. For example, a kernel setting may speed up I/O, a change which is noted by the CBO workload statistics (using dbms_stats.gather_workload_stats). This, in turn, directly influences the optimizer's access decisions.
  • Adjust your optimizer statistics -- Always collect and store optimizer statistics to allow the optimizer to learn more about the distribution of your data to take more intelligent execution plans. Also, histograms can hypercharge SQL in cases of determining optimal table join order, and when making access decisions on skewed WHERE clause predicates. Also new in 11g, multi-column statistics can be gathered for use by the optimizer to determine optimal ways to run queries based upon multiple column criteria.
  • Adjust optimizer parameters -- Optimize_optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj.
  • Optimize your instance -- Your choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, etc.) can influence SQL performance.
  • Tune your SQL Access workload with physical indexes and materialized views -- Just as the 10g SQLAccess Advisor recommends missing indexes and missing materialized views, you should always optimize your SQL workload with indexes, especially function-based indexes, a godsend for SQL tuning.

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

This was last published in July 2007

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.