Jonas Mason, Senior Consultant

In the last of my blogs on Diagnostics and Tuning Pack features, I will be covering the Access Advisor, which can be executed against a SQL Tuning Set. Recommendations by this tool present the DBA with tuning options for consideration. Recommendations include:

• Indexes
• Materialized Views
• Partitioning

The Access Advisor considers possible access paths that would yield performance improvements for the SQL statements analyzed. It also validates existing access paths in your database, which can be helpful if you are looking to conserve storage by eliminating certain access paths, such as indexes.

Recommendations made by the Access Advisor typically require DDL in order to be implemented, which will consume additional storage. Therefore, execution time improvements for selects have to be balanced against index maintenance overhead and this additional storage consumption. The DBA needs to evaluate whether the table has frequent or infrequent DML such as deletes, inserts, and updates when considering whether or not to implement recommendations from the Access Advisor.

Recommendations by the Access Advisor should never be implemented without peer-reviewed consideration subject to QA and change control. Also keep in mind that many Advisor recommendations should never be implemented. While the goal of this blog is to provide the basic steps associated with executing the Access Advisor, experienced DBAs and Developers with in-house business knowledge are certainly critical to evaluating any recommendations made by Diagnostic and Tuning Pack tools.

Access Advisor

The Access Advisor attempts to tune SQL statements by analyzing existing and non-existent access paths, such as index, materialized views, and partitioning. The Access Advisor is run against a SQL Tuning Set that has been created and populated with SQL statistics; see Figure 1 below.

Figure 1: Navigate to SQL Access Advisor

Figure 2: Set Access Advisor Initial Options

In this case, we select the SQL Tuning set created over a 24-hour period to be analyzed:

Figure 3: Define Source of SQL Statements for Access Advisor

We want a comprehensive analysis of this entire SQL Tuning Set, so we will include indexes, materialized views, and partitioning. We will also perform a comprehensive review.

Figure 4: Specify Recommendation Actions to be Evaluated

The next screen, as shown in Figure 5 below, allows us to either schedule this Access Advisor analysis job immediately, or later (during off peak periods). If the SQL Tuning Set contains a significant number of distinct SQL, some long running load will be introduced on the server. If you have any doubts schedule during off-peak hours to avoid impacting application users.

Figure 5: Schedule Access Advisor Job

Select Next and review your Access Advisor configuration settings prior to submitting the job (see Figure 6):

Figure 6: Review Access Advisor Options Prior to Submitting

After selecting submit, you should see the advisor task running, as shown in Figure 7:

Figure 7: Access Advisor Confirmation

To view the results of this Access Advisor task, navigate to Performance -> Advisors Home; see Figure 8:

Figure 8: Access Advisor Results

On the Advisors Home page, we can see both the SQL Tuning Advisor and Access Advisor jobs that have been run, including the job we set up above, which is still running. See Figure 9 below for details.

Figure 9: Review Access Advisor Job Run Status

When we drill into the results of this job, we can see a review of potential cost savings in disk I/0, as well as the extent to which SQL statement performance will improve. This is based on the possible implementation of new indexes, materialized views, or partitions; see Figure 10 for more information.

Figure 10: Access Advisor Implementation Potential Cost Savings

If we select the Recommendations tab, per Figure 11, we can see a little more detail on the recommendations:

Figure 11: Access Advisor Recommendations and Potential Cost Savings

Recommendations that include a kind of DDL activity, such as create index or create materialized view, should result in a cost improvement, per Recommendation ID. While this is useful, the SQL Statements tab directly lists which statements will benefit, and by what percentage and cost. Obviously, implementing DDL, as recommended by the Access Advisor, should only be undertaken where a significant reduction in CPU, disk I/O, and/or memory is possible. While these resource considerations are important, the ultimate benefit to the end user is paramount. How much faster does the SQL execute? Is this SQL executed often enough to have a measurable impact when tuned, or is it only run once a month during a batch process?

Answers to the questions above are available in the SQL Statements tab of this view, as listed below in Figure 12:

Figure 12: Access Advisor Cost Improvement Breakdown per SQL Statement

If we drill down on Recommendation 1, we can see what is being recommended:

Figure 13: Access Advisor Index Creation Specifics

Next, we can specify the tablespace we want the new indexes created in. We can also view the DDL output and copy and paste this content into a standardized release script and change control process. Finally, we view the explain plan differences and test actual execution time variation in a non-production environment after implementing the DDL.

What we see in Figure 13 above is the recommendation to create five BTREE and BITMAP indexes on the tables TRANSFER_COURSE_SYLLABL_TBL and TRANSFER_COURSE_TBL. Not viewable in the screenshot above is the column that specifies the storage required to create indexes. In the case above, 15 MB is required to implement these indexes. While our selects are substantially improved with the implementation of these indexes, index maintenance overhead associated with DML must also be considered. In this scenario, the DBA should review table, index, and trigger characteristics for the TRANSFER_COURSE_SYLLABL_TBL and TRANSFER_COURSE_TBL tables prior to proceeding. Does DML occur in a batch job with one session during off peak hours, or from many sessions spread across a RAC cluster? Answers to these questions will help inform your decision.

Below, in Figure 14, is a list of a few of the 240 SQL statements that would benefit from the indexes suggested above as well as the executions counts of all of these statements during the 24 hour period the SQL Tuning Set captured activity.

Figure 14: SQL Statement Cost Improvement

Conclusion

Diagnostic and Tuning Pack tools, such as the Access Advisor, provide the DBA with an initial access path assessment of SQL contained in a SQL Tuning Set. Using available server CPU cycles to analyze SQL statistics presents the DBA with many initial tuning options, and he or she adds value by evaluating this tuning assessment and presenting his or her findings to peers and managers for consideration.