Getting Good Advice About Access

New SQL Access Advisor in Oracle 10g guides you in choosing indexes and materialized views.
It's not unusual to spend hours tuning SQL statements in hopes of getting better SQL performance, often relying on methods that are time-consuming at best or, worse, border on guesswork or wizardry. However, with the new SQL tuning tools available in Oracle Database 10g—specifically, SQL Tuning Advisor and SQL Access Advisor—tuning SQL is more science than sorcery.

We've looked at SQL Tuning Advisor (see "Advise and Consent,") and discussed how to use that particular tool to fix poorly structured SQL statements, detect stale statistics, or implement a SQL profile so that packaged applications can run optimally. During its analyses, SQL Tuning Advisor also performs an access-path analysis that's limited in scope—it assesses whether individual SQL statements can benefit from an index—often recommending that you run SQL Access Advisor in a separate step.

In addition, however, you can run SQL Access Advisor separately at any time to evaluate a complete SQL workload and obtain advice about indexes, materialized views, and materialized view logs. As every DBA knows, in data warehouse, decision support, and other environments in which complex, data-intensive queries are the norm, the appropriate use of indexes and materialized views can dramatically improve access paths to data. With the right materialized view on hand (and query rewrite enabled), the Oracle cost-based optimizer (CBO) can take a faster path to the data. Figuring out the right indexes and materialized views is streamlined when you use SQL Access Advisor, because even beginning DBAs who know little about these structures can obtain guidance.

Using the SQL Access Advisor
Like the other advisors in Oracle Database 10g, SQL Access Advisor is available from Enterprise Manager Database Control, from Advisor Central. Clicking on the SQL Access Advisor link launches the SQL Access Advisor wizard, which basically comprises a four-page process that starts by letting you select a SQL workload and then configure the schedule and several other options. When you finish stepping through the wizard, a task associated with the selected workload is created and becomes available from the Results table displayed on Advisor Central; if you configured the task to run immediately, it will do so.

Running the task invokes the tuning mode of the CBO, which analyzes the SQL workload and generates recommendations. This has an impact on performance—someting to keep in mind as you're stepping through the wizard, whose scheduling page lets you configure whether to run immediately, later at a specified time, or in the context of a maintenance window.

Various Types of Workloads
The workload can come from one of several sources, including SQL Tuning Sets that are available by default or that you've created from AWR (automatic workload repository) snapshots. For example, the Top SQL statements (statements using the largest percentage of system resources) comprise SQL Tuning Sets that are available by default, so you can select among them as you're stepping through the wizard. If you want to choose from AWR snapshots, you'll have to create these SQL Tuning Sets in advance, before launching SQL 
Access Advisor.

You can also select the cache of current and recent SQL activity as the workload. By default, all activity will be captured, but you can filter this initial capture (by selecting Show Advanced Options in the lower portion of the page) to include only statements belonging to a particular user.

Another option for creating a workload is the so-called hypothetical workload (an invaluable tool at design time), which is derived from the schema and table structures you identify. This option works only if the schema comprises dimensions and if the tables you identify have primary key and foreign key constraints defined.

You can also run SQL Access Advisor against a user-defined workload, a table containing SQL statements and the name of the user that executes each statement. Minimally, your table must include a column for the username and a column for the text of the SQL statement:

CREATE TABLE user_workload(username varchar2(30), sql_text clob);

So, for example, you can create a table containing all the SQL statements from a variety of applications that might be running concurrently and model different runtime scenarios, using various user-defined workload tables.

Once you've identified the workload you want to run through SQL Access Advisor, the wizard gives you options for the type of recommendations to obtain about the workload, specifically whether you want recommendations about indexes only, about materialized views only, or about both. In addition, you can choose to focus only on the highest-cost SQL statements.

The Cost-Based Optimized and Running a Task
Again, keep in mind that SQL Access Advisor invokes the tuning mode of the CBO, so running the task can have an impact on your system's performance. Obtaining advice about materialized views has a bigger performance impact than simply obtaining advice about indexes. Also, depending on the complexity of your SQL workload and other load factors, the comprehensive mode has a far greater impact than the limited mode.

In general, if you're obtaining advice about materialized views or if you're selecting comprehensive mode on the Recommendations Options page, you should schedule (on the third page of the wizard) the task to run during off-hours or during a scheduled maintenance window, such as WEEKEND_WINDOW.

Finally, when you've configured all your choices, you review all the settings you've made and then submit them. The process of submitting creates the task; if you've configured it to run immediately, it invokes the CBO and processes the workload to obtain the recommendations.

When this process is complete, you have a task associated with a workload, but until you run the task and generate recommendations, nothing happens. You can create multiple tasks to analyze a given workload in numerous ways; for example, you can create one task that looks at a workload in terms of indexes, another task that evaluates the same workload in terms of materialized views, another that performs both, and so on.

Because the CBO isn't invoked until you actually run the task, if you haven't scheduled the task to run immediately, there's no immediate impact on your system. Likewise, the performance improvements projected by SQL Access Advisor will not take effect until you actually implement a recommendation (or recommendations). In any case, you should always review all recommendations before implementing them.

Implementing Recommendations
You can view the expected impact of various recommendations in terms of overall performance impact relative to other recommendations (see Figure 1) or across all SQL statements. To implement the recommendation, you simply click on the Schedule Implementation button or save the SQL script that implements the recommendations to run later. As you might expect, implementing the recommendation creates the indexes and materialized views or performs other actions as detailed in the recommendation. (Although multiple recommendations can share the same action with other recommendations—you may see the same CREATE INDEX action in several different recommendations, for example—the implementation script [or the wizard's implement button] implements the action only once.)

Performing a QuickTune
Although Enterprise Manager Database Control supports almost all SQL Access Advisor functionality, you need to use the DBMS_ADVISOR package to perform a QuickTune on a single SQL statement or to perform several other activities, such as importing Oracle9i Summary Advisor workloads.

The QuickTune procedure is great for quickly getting materialized view and index advice about a single SQL statement. The procedure analyzes the SQL statement you pass it and generates recommendations, saving the results as a task of whatever name you pass it, as follows:

EXECUTE DBMS_ADVISOR.QUICK_TUNE(

DBMS_ADVISOR.SQLACCESS_ADVISOR, 

'KF QuickTune', 'SELECT avg(amount_sold) 

FROM sh.sales 

WHERE cust_id=3234'); 

When you refresh the Enterprise Manager display (for Advisor Central), you'll see the QuickTune task listed in the Results table. Click on the link (the name of the task), and the Recommendations for the task appear. You can execute the task by clicking on the Schedule Implementation button or saving the SQL script.

Conclusion
SQL Access Advisor provides yet another time-saving tool for DBAs. With even limited tuning experience, you can get started with Enterprise Manager Database Control. You can also easily start using the API, and you can find examples of using DBMS_ADVISOR procedures in the aadvdemo.sql file (you have to install the Oracle Database Server 10g Companion CD software to obtain it).

REFERENCES
Getting Good Advice About Access By Kimberly Floss