Oracle 11g environmental tuning with the SQL Performance Analyzer

Oracle 11g environmental tuning with the SQL Performance Analyzer

Oracle Tips by Burleson Consulting

The declarative nature of the SQL access 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 up to the SQL optimizer to always determine the optimal execution plan, a formidable challenge, especially in a dynamic environment.

Towards an expert system to tune Oracle SQL

In the world of artificial intelligence we see two common terms used to describe software that enables a human decision-making process the "expert" system and the "decision support" system. In a nutshell, and expert system works FOR the end-user while a Decision Support system works WITH the end-user:

Expert systems - A expert systems assists by providing a complete automated solution to a well-structured problem space. The expert system captures the well-defined decision rules and "solves" the problem for the end-user.
Decision Support Systems - In a decision support system (DSS), the end-user is solving a semi-structured task that required unquantifiable human intuition. Hence, a DSS can only quantify the well-structured decision rules, providing a "helper" for the end-user, relieving them of the tedious well-structured component.

The Oracle 11g SQL Performance Analyzer (SPA), is primarily designed to speed up the holistic SQL tuning process.

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 give 3-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, and note that the ONLY change from 10g is the automation of the implementation of the recommendations, for any SQL statements that run 3x faster with the changes:

1 - Define the SQL - The DBA defines a "set" of problematic SQL statements (or chooses a representative workload). This is called the SQL Tuning set, or STS.

2 - Set-up a changed environment - Here you can chose to change your initialization parms, 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 & 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.

3 - Implement the changes - For any statements that execute more then 3x faster, after the changes, Oracle 11g will automatically implement the changes via "SQL Profiles", a tool that bypasses the generation of an execution plans for incoming SQL, replacing it with the pre-tuned access plan.

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 SQL tuning because you can hundreds of queries at once within an STS. Remember, you MUST do your holistic SQL tuning first, else later changes (e.g. optimizer parameters, CBO release, etc.) may un-tune your SQL Remember, you must ALWAYS start by holistic SQL tuning by doing your system-level tuning, establishing an "optimal fit", before diving into the tuning of individual SQL statements:

Optimize the server kernel - You must 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.
Adjusting your optimizer statistics - You must 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.
Adjust optimizer parameters - Optimizer 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, &c), 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.

The SPA treatment

The SQL performance analyzer allows the DBA to define the SQL Tuning set (the STS), as a source for the test (usually using historical SQL from the AWR tables).

The SPA receives one or more SQL statements as input (via the SPA), and provides advice on which tuning conditions have the best execution plans, gives the proof for the advice, shows an estimated performance benefit, and allegedly has a facility to automatically implement changes that are more than 3x faster than the "before" condition".

Inside the SQL Tuning set

The SQL workload (the STS) can be thought of as a container for conducting and analyzing many SQL statements. The STS is fed to the SPA for real-world execution with before-and-after comparisons of changes to holistic "environmental" conditions:

Internally, the SPA is stored as a database object that contains one or more SQL statements combined with their execution statistics and context such as particular schema, application module name, list of bind variables, etc. The STS also includes a set of basic execution statistics such as CPU and elapsed times, disk reads and buffer gets, number of executions, etc.

When creating a STS, the SQL statements can be filtered by different patterns such as application module name or execution statistics, such as high disk reads. Once created, STS can be an input source for the SQL Tuning Advisor.

Typically, the following steps are used to define the STS using the dbms_sqltune package. The steps within the new 11g OEM screen for "guided workflow" are simple and straightforward, and serve as an online interface to the dbms_sqltune.create_sqlset procedure:

1 � Options � Choose a name for your SQL tuning set (STS). The SQL workload set is created using the dbms_sqltune.create_sqlset procedure. For example, the following script can be used to create a STS called SQLSET1:

exec dbms_sqltune.create_sqlset ( �SQLSET1�);

2 � Load methods - Here is where you can choose the source for your SQL workload, and to take historical SQL statements from AWR.

3 � Filter options - You can choose �filtering� conditions, based on your specific tuning needs. For example, if your database is disk I/O bound, you might choose only SQL statements that have more than 100k disk reads.

4 � Schedule � This is an interface to the dbms_scheduler package, allowing you to define and schedule a job.

5 � Review � Here you can see the actual source calls to dbms_sqltune.create_sqlset and the dbms_scheduler.create_job procedure call syntax.

In sum, the new 11g SQL Performance Analyzer is a great way to test for holistic tuning changes. Remember, the savvy Oracle DBA will always adjust their Oracle initialization parameters to optimizer as much of the workload as possible before diving into the tuning of specific SQL statements.

For more details on these exciting new 11g features, see my book "Oracle 11g New Features", available in Fall 2007.