As Published In March/April 2004

Advice and Consent
By Kimberly Floss

Learn how to use Oracle 10g's new advisors, and take the guesswork out of tuning.

The word "automatic" seems to have been liberally applied to a raft of new features in Oracle Database 10g—Automatic Database Diagnostic Monitor (ADDM); Automatic Workload Repository (AWR); automatic space management, and automatic SQL tuning, for instance. However, before you think Oracle's trying to put us all out of work, think automatic as in "automatic pilot," not "automatic can opener." No one would ever suggest removing the captain from the cockpit simply because the aircraft's instruments have some intelligence built in to help keep it aloft.

Likewise, when it comes to database tuning, even experts can use some intelligent advice. We've all made use of tools such as TKPROF, Explain Plan, and Statspack to help ensure optimal performance. We've rerun statistics, dropped statistics, fiddled with init.ora parameters, built indexes, dropped indexes, rewritten SQL, and jumped through a variety of hoops in search of better performance. Finding the solution to trouble spots using the DBA's bag of tricks has its rewards—but it's also repetitive and time-consuming. The automatic tuning capabilities built into Oracle Database 10g encompass these capabilities and a whole lot more, and get to the end state—optimally performing databases—much more quickly. It all starts with a new, intelligent infrastructure built into this release of the database.

Intelligent Infrastructure

Oracle Database 10g's comprehensive, intelligent infrastructure provides instrumentation throughout the database, enabling the database to monitor and diagnose itself on an ongoing basis and alert the DBA to problems so that he or she can take effective corrective action.

Briefly, a few of the key components of the new intelligent infrastructure in Oracle Database 10g include AWR, ADDM, and an array of "automatic advisors" that take a lot of the guesswork and repetition out of the DBA's tasks. In simple terms, AWR subsumes the functionality provided by Statspack, and it gathers a wide range of new statistics. AWR collects, processes, and maintains performance statistics (by default, AWR takes statistics snapshots every 60 minutes) for problem detection and self-tuning purposes, storing the data gathered in the database where it can be analyzed by ADDM.

Housing the collective expertise of Oracle experts, both inside and outside the company, ADDM provides the low-level knowledge and analysis required to effectively monitor and diagnose database performance. It performs root-cause analysis and provides detailed recommendations across several important classes of database objects, such as applications, schemas, and memory utilization.

So, for example, ADDM might determine that for a particular schema object, "read and write contention on database blocks was consuming significant database time," and report this finding (in an ADDM report generated at the command line, or via Oracle Enterprise Manager [OEM] Console). Additional details about a finding such as this one might include the fact that there were a high level of inserts into a table that needed freelists, with a recommendation to "consider using Oracle's automatic segment space management in a locally managed tablespace...." Recommendations can also include suggestions to run a specific advisor session on a SQL statement that's consuming more than its share of database resources.

I'll explore ADDM, AWR, and some of the other new advisors that harness the intelligence of this new infrastructure in future columns.

Optimizer Enhancements

One of the quickest wins for DBAs in this raft of new tools and intelligent infrastructure is the ability to tune SQL statements quickly and easily. SQL Tuning Advisor lets you tune SQL statements without modifying source code. This feature will come in handy especially with packaged applications, while you're waiting for patches from vendors, for instance, but it also can be used to tune any SQL (from the cursor cache, or given a string of SQL text, for example).

Before going into the details, let's start with a brief overview of some of the under-the-covers functionality upon which this particular advisor relies—specifically, the optimizer.

Generally speaking, at the core of Oracle SQL performance is the Oracle cost-based optimizer (CBO), the component that evaluates possible paths to data and generates optimal execution plans from among the many possible alternatives.

Execution plans define the combination of the steps Oracle Database uses to execute a statement; they include an access method for each table that the statement accesses and an ordering of the tables (the join order).

The optimizer determines the most efficient way to execute a particular SQL statement. Given the potential number of available optional paths for any specific SQL statement, the optimizer quickly evaluates them in order to generate an execution plan in less than a second.

In addition to this so-called "normal" mode of the optimizer, in Oracle Database 10g there's also now a "tuning" mode (sometimes referred to in Oracle literature as "Automatic Tuning Optimizer"). As its name implies, the tuning mode of the optimizer is specifically used during SQL tuning sessions (using the SQL Tuning Advisor and the SQL Access Advisor) to generate additional information that can be used at runtime to speed performance. The tuning mode subsumes the behavior of the normal mode and has extended functionality, enabling it to perform additional analysis during the process of building execution plans.

In tuning mode, the optimizer performs four key levels of analysis to generate statistics that might augment the information the optimizer uses to return results of a SQL statement:

SQL statistics analysis. The optimizer checks for missing or stale statistics and makes appropriate recommendations—collect statistics for a certain database object, for example—to ensure that the optimal execution plan can be generated. (The optimizer also generates additional information, which is stored in a SQL profile that it can use at runtime, if the recommended action was not taken.)

SQL profiling. The optimizer performs more-extensive analysis and puts together the necessary additional information that will make a query run more optimally, storing this information in a SQL profile. SQL profiles contain collections of information that allow the SQL compiler to optimize the execution plan of a particular SQL text. The SQL profile is then used at runtime (when the optimizer is back in normal mode) to improve the performance of SQL without changing the source.

SQL access analysis. The optimizer analyzes the access path and verifies that indexes are being put to best use, and if not, recommends creating them as appropriate to facilitate faster access paths. (A separate SQL Access Advisor tool can be run separately to gather advice on all access structures—specifically, materialized views, materialized view logs, and indexes for entire SQL workloads. I'll cover this tool in a future column.)

SQL structure analysis. The optimizer analyzes the structure—semantic, syntactic, and design—of SQL statements, generating extensive annotations and diagnostics as it builds the execution plan and makes recommendations for improving performance. For example, to give you a significant performance boost, the optimizer may suggest replacing a NOT IN with NOT EXISTS, which may yield the same result, even though it is not semantically the same as NOT IN. (You would want to make this change, however, only if there are no NULL values in the related join columns of the query—which is why the SQL Tuning Advisor leaves it up to you to implement suggestions generated by structure analysis.)

All four analyses are conducted if you run the SQL Tuning Advisor in "comprehensive" mode; however, SQL statistics analysis, SQL access analysis, and SQL structure analysis are performed only in "limited" mode—the SQL profile is not generated. If you want to tune application code, such as the code comprising packaged applications, you'll want to use the comprehensive mode to ensure you get the SQL profile.

The optimizer's tuning mode is used during the SQL Tuning Advisor and SQL Access Advisor sessions.

Using the SQL Tuning Advisor in comprehensive mode to generate SQL profiles for one or more SQL statements can take a long time—the optimizer is busy gathering and generating additional statistics and annotations. However, you can limit the time the optimizer will spend on the specific tuning task by changing the value from the default setting of 30 minutes.

Nonetheless, using SQL profiles should provide a huge benefit when tuning SQL statements—especially when dealing with packaged applications, over whose SQL source code you have no direct control.

The SQL Tuning Advisor is available for launching in numerous places throughout the new OEM Web-based console interface, and is also accessible at the (SQL*Plus) command line through the DBMS_ SQLTUNE package. To use SQL Tuning Advisor (and any of the other advisors), you'll need the ADVISOR privilege (new in the Oracle Database 10g release; the DBA role has the ADVISOR privilege by default).

ADDM: The Backbone of Oracle 10g Diagnostics A revolutionary aspect of the new self-managing Oracle database is its ability to diagnose its own performance problems. Oracle Database 10g includes a self-diagnostic engine called Automatic Database Diagnostic Monitor (ADDM) built right into the database kernel. ADDM automatically monitors the state of the database at short, regular intervals (30 minutes by default), providing ongoing database performance diagnostics. Much of the data in ADDM (and in the advisors) is presented in graphical form—line graphs over time, bar charts, pie charts—as appropriate for the type of data, making it easy to get a sense of things at a glance. In addition to looking at the results of proactive ADDM analysis, you can also run ADDM manually from Oracle Enterprise Manager (OEM) or from the command line, using OEM's PL/SQL interface. ADDM does a top-down analysis of potential bottlenecks, coming up with a set of findings that includes root causes and recommendations with rationale. In addition to identifying problems, ADDM also reports how much impact each of the problems is having on overall system performance and how much benefit can be gained by resolving it. This impact-benefit analysis will help DBAs focus on problems whose resolutions result in the biggest performance gains.

Step-through of a Proactive Tuning Session

Whether you want to tune a single statement or a series of statements from various sources (ADDM, AWR Top SQL, or combinations), all tuning exercises start with creation of a tuning task. In the case of a packaged application, the SQL code that's occupying too many of your resources will likely show up in the Top SQL page of OEM identified by a specific SQL_ID (in Oracle Database 10g, each SQL statement is now identified by a SQL_ID). Here's how to use the DBMS_SQLTUNE package from the command line to tune SQL statements.

Step 1. Create a tuning task to identify your SQL statement. In this example, the SQL text refers to a statement that uses bind variables.

create_tuning_task(
sql_text => 'select * from emp where
emp_id = :bnd',
bind_list =>
sql_binds(anydata.ConvertNumber(100)),
user_name => 'scott',
scope => 'comprehensive',
time_limit => 60,
task_name => 'my_sql_tuning_task',
description => 'task to tune a query on
a specified employee');

Because the time limit in the task in the example is set to 60, we're allowing the optimizer to spend up to a full minute performing its analysis. Also, note the "comprehensive" setting for the scope parameter, which means that any additional analysis conducted by the optimizer to improve performance will be available in a SQL profile.

To tune the SQL from a packaged application, you'll need to pick up its SQL_ID—which you might find listed on the Top SQL page of OEM, if it's been giving you a problem, for example, or by querying tables of the new framework (SQL_ADVISOR_%)—and create a tuning task as follows:

create_tuning_task(sql_id =>			'q1rsx05369psft');

Depending upon how long the optimizer does take to perform its work, up to the limit, the create_ tuning_task function ultimately returns a unique character ID to identify the task; this ID can then be used with the other APIs (interrupt, cancel, and drop, for example).

Step 2. The task now exists with our parameter settings but does nothing until you execute it to start the process. To execute the tuning task:

execute_tuning_task(task_name => 			'my_sql_tuning_task');

When the task completes, the prompt returns. Behind the scenes, the results of executing the task are sent to the tables upon which this new framework (infrastructure) relies. You can query these tables using the many new views, such as the DBA_ADVISOR_% views (DBA_ADVISOR_FINDINGS, DBA_ADVISOR_ RECOMMENDATIONS, and so on) that store the findings and recommendations.

Step 3. See the results by calling the report_tuning_task procedure:

set long 10000;
select report_tuning_task(task_name => 'my_sql_tuning_task') from dual;

The report_tuning_task procedure generates a complete report of the task's results, including findings and recommendations, and outputs to the console. (The same level of detail is available in OEM.)

Next Steps READ more about performance tuning in Oracle Database 10g more about ADDM more about SQL Tuning OTN's Grid Technology Center GET information on Kim Floss's book Oracle SQL & Index Internals

Step 4. Implement the recommendation (or recommendations) as appropriate. Presuming you ran the tuning task in comprehensive mode and a SQL profile was generated, you can implement the SQL profile by executing the accept_sql_profile command:

accept_sql_profile(
task name => 'my_sql_tuning_task',
name => 'my_sql_profile');

The name in the example above is optional; if you don't provide a name, the system generates a unique name for the profile. Accepting the SQL profile stores it persistently to the data dictionary, where it will be used at runtime: the next time your application runs, the optimizer (back in "normal" mode) will use the profile behind the scenes to speed performance of the SQL statement to which it applies, regardless of source (packaged application or otherwise). What could be easier?

Conclusion

Oracle's new manageability enhancements in Oracle Database 10g are generous, to say the least, and provide today's way-too-busy DBA with an able copilot. This column has just scratched the surface of the new SQL Tuning Advisor functionality. The SQL tuning capabilities can easily improve the performance of any SQL code, including that of packaged applications, without modifying source code and without the DBA having to figure out which hints to send to the optimizer to improve performance. The capabilities this new infrastructure provides and the many new advisors won't replace DBAs, but they'll allow us to spend time focusing on the more challenging—and fun—parts of the job.