SQL Access Advisor Revisited

Activating the SQL Access Advisor by Grid Control vs Command Line
I highly suggest you read my blog titled "10G SQL Access Advisor" before you continue. It provides a step-by-step instruction guide on how to run the SQL Access Advisor using 10G Grid Control. I also describe the various types of workloads that can be used as input to the advisor. Lastly, I review the different types of recommendations generated by the utility.

10G Grid Control automates the entire process. Administrators are able to quickly and easily capture or generate workloads, use them as input to the SQL Access Advisor and view the recommendations, along with their associated benefit ranking. The output also contains links to the DDL statements that administrators can use to implement the recommendations.

Like most of the advisors in Oracle, you can also activate the SQL Access Advisor by executing the PL/SQL package DBMS_ADVISOR. The Oracle10G Database Performance Tuning Guide dedicates an entire chapter to the SQL Access Advisor. After comparing the steps necessary to run the advisor using the command line interface to the quick and easy GUI interface provided by 10G Grid Control, I think I'll make every effort to use the 10G Grid Control tool to activate the SQL Access Advisor. Stating that the command line interface is more labor intensive than its Grid Control counterpart is like stating that the Titanic "sprung a small leak."

But the command line interface does provide users with the ability to tune a single SQL statement. The DBMS_ADVISOR.QUICK_TUNE procedure allows administrators to quickly and easily create a task for a workload that consists of one statement. The SQL Access Advisor will perform the same analysis and generate the same recommendations as it does with larger workloads. The Quicktune utility accepts a user-defined task name that identifies the task and a SQL statement to be analyzed. Here's an example of how to run Quicktune on a single SQL statement:

EXECUTE DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, 'my_first_quicktune', 'SELECT * from scott.dept where dept_id=140');

In addition, the command line method allows you to import Oracle Database 9i Summary Advisor workloads using the IMPORT_SQLWLD_SUMADB procedure.

Advice from your friendly ex-Oracle Instructor
Please remember that the quality of the output produced by the SQL Access Advisor will depend upon the quality of input. Make sure that the tables and indexes being analyzed have up-to-date statistics. You do this by generating statistics using the Oracle supplied package DBMS_STATS.

In addition, we know that an index has the possibility of influencing all SQL statements that access the table that the index is built upon. An index that provides fast access for one query may negatively impact other statements. Analyzing larger workloads will produce index and materialized view recommendations that take all of the queries contained in the representative workload into consideration. The larger the workload you use as input to the SQL Access Advisor, the more high-quality the recommendations will be.

It is important to note that the SQL Access advisor does generate some overhead during analysis. You need to balance the size of the workload with the impact the analysis process will have on database performance. My personal preference is to schedule my larger workload analysis during off-peak hours.

SQL Access Advisor Input
Before we continue, we need to understand what a task and a workload is. They are used together to generate SQL Access Advisor recommendations and can be created using either Grid Control or the command line interface.

Tasks
A task is an object that contains the setup and execution options of a particular SQL Access Advisor job. It allows administrators to tailor an evaluation to meet their specific requirements and can be used over and over again. A task can be created using Grid Control or by command line by executing the DBMS_ADVISOR.CREATE_TASK procedure. Command line users would then use the DBMS_ADVISOR.SET_TASK_PARAMETER to set attributes that control the workload collection, tuning analysis and final reporting.

These attributes provide a myriad of customization options to the administrator. You are able to establish default owner names, tablespaces and naming conventions for object creation recommendations.

The attributes also allow administrators to use workload filters to restrict the tuning analysis to a set of SQL statements that meet a given criteria. Administrators are able to filter on the module name, SQL statement syntax, table and schema names or the account executing the statement. In addition, administrators have the option of limiting the analysis to the top "n" resource consumers based on buffer gets, disk reads, executions, etc.. There are multiple filter options available that can be combined to narrow the scope of the analysis and recommendations. For a complete list of task attribute settings, please refer to the DBMS_ADVISOR package description contained in the 10G Oracle Database PL/SQL Packages and Types Reference Documentation Manual.

Workloads
Workloads consist of SQL statements and statistics and attributes that describe those statements. Workloads are classified as either full (representative of the entire application) or partial (representative of a portion of the application's entire workload). One important difference between full and partial workloads is the SQL Access Advisor will not generate drop recommendations for partial workloads.

If you read my previous blog on the SQL Access Advisor, you'll know that the advisor is able to accept several different types of workloads as input. The input workload can consist of a single SQL statement, SQL statements currently in the SQL Cache, a user defined set of SQL statements contained in a workload table or a Grid Control generated SQL Tuning Set. The SQL Access Advisor is also able to generate a hypothetical workload for a specified schema.

Templates
Once you fine tune your task and workload, you can create a template to simplify future operations. Both tasks and workloads can be used to create templates. The template can be created using either Grid Control or command line.

Analysis
The SQL Access Advisor performs an in-depth analysis on each statement contained in the workload that is not eliminated by the workload filtering options. The utility reviews every clause contained in the statement for efficiency. If it identifies that a statement accesses a table using a particular column, it will review the statistics on the accessed table to determine if a new index is warranted. Conversely, if it finds an index that is not used, it may generate a recommendation to drop the index in question. The SQL Access Advisor will also review the index types. If it finds that an index should be a B-Tree, as opposed to a bitmap, it will make a recommendation to change the type of the index.

The SQL Access Advisor will also evaluate the workload for queries that would benefit from materialized views. In releases previous to Oracle8i, administrators used summary tables to increase SQL performance for data warehousing applications. A summary table stores the pre-computed results of data calculations, such as sums, counts, averages and data table joins. It is more efficient to access a pre-computed result instead of computing the desired result from detail data every time a query is executed. In Oracle8i and later releases, administrators are able to use materialized views to provide the same benefits as summary tables.

SQL Access Advisor Output
The output the advisor produces depends upon the extent of analysis performed. Administrators are able to choose from:

• Comprehensive - The SQL Access advisor reviews materialized views, materialized view logs and indexes. The advisor will assume that the SQL being analyzed is representative of the application's general workload that is being processed during the course of normal day-to-day operations.
• Limited - The SQL Access Advisor will assume that the workload consists of poorly performing SQL statements. The advisor will provide advice for improving the performance of that portion of the total workload.

The types of recommendations provided depend upon the extent of analysis performed:

For those running the SQL Access Advisor via command line, Oracle provides a series of data dictionary views that allow administrators to view the output generated from the SQL Tuning Advisor. The view that contains the actual recommendations is DBA_ADVISOR_RECOMMENDATIONS. Oracle also provides the GET_TASK_SCRIPT procedure that produces the output in a easy to read, graphical display. The display contains links to individual SQL statements which allows administrators to quickly see which SQL statements benefit from a recommendation.

Grid Control users are able to view the Summary Output page to view recommendations that are sorted by the benefit they provide. Clicking on the number on the far left side of the tabular output drills down into the specific recommendation.

Grid control's recommendation details page displays links for the specific objects that will be created if the recommendation is implemented. The bottom of the page displays the SQL statements that will be improved by the recommendation. Notice that the output DDL uses the table schema as default if none is specified. The page also displays the space that will be consumed if the object is created. Finally, users can hit the OK button to begin creating the recommended objects or save the DDL for execution at a later time. When you click on the materialized view link, 10G Enterprise Manager displays the DDL for the recommended object.

Summary
I think you'll find that the SQL Access Advisor provides administrators with a powerful performance analysis tool. The intent of this blog was to pique your curiosity on the tool and the benefits it provides. The Oracle documentation contains a wealth of knowledge to continue your education.