Improving Schema Design with SQL Access Advisor

Purpose

This tutorial describes how to use SQL Access Advisor to enhance your schema design.

Time to Complete

Approximately 40 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Using SQL Access Advisor
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Defining appropriate access structures to optimize SQL queries has always been a concern for an Oracle DBA. As a result, there have been many papers and scripts written as well as high-end tools developed to address the matter. In addition, with the development of partitioning and materialized view technology, deciding on access structures has become even more complex. As part of the manageability improvements in Oracle Database 10g and 11g, SQL Access Advisor has been introduced to address this very critical need.

SQL Access Advisor identifies and helps resolve performance problems relating to the execution of SQL statements by recommending which indexes, materialized views, materialized view logs, or partitions to create, drop, or retain. It can be run from Database Control or from the command line by using PL/SQL procedures.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g.

2.

Download and unzip the sqlaccadv.zip file into your working directory (that is, wkdir ) and navigate into your working directory.

Back to Topic List

Using SQL Access Advisor

SQL Access Advisor takes an actual workload as input, or the Advisor can derive a hypothetical workload from the schema. It then recommends the access structures for faster execution path. It provides the following advantages:

The following scenario illustrates the types of recommendations that can be made by SQL Access Advisor. The scenario also uses the SQL Performance Analyzer to prove that recommendations made by SQL Access Advisor are good.

Perform the following steps to understand the use of SQL Access Advisor:

1.

From a terminal session connected as user oracle; execute the sqlaccessadv_setup.sh script. This script generates the necessary data that is used throughout this OBE. In particular, it generates the SQL Tuning Set that is used to represent the workload you want to analyze.

./sqlaccessadv_setup.sh

 

2.

Connect to Enterprise Manager Database Control as user sh (password sh). Once on the Home page, click the Advisor Central link in the Related Links section.

 

3.

On the Advisor Central page, click the SQL Advisors link. Then, on the SQL Advisors page, click the SQL Access Advisor link.

 

4.

On the Initial Options page, select Inherit Options from a previously saved Task or Template, and then select SQLACCESS_WAREHOUSE template. Once done, click Continue.

 

5.

On the Workload Source page, select Use an existing SQL Tuning Set and enter SH.SQLSET_MY_SQLACCESS_WORKLOAD in the SQL Tuning Set field. This SQL Tuning Set was generated earlier. It represents a warehouse workload you want to analyze. Once done, click Next.

 

6.

On the Recommendation Options page, make sure all possible access structures are selected, and that Comprehensive is selected. Once done, click Next.

 

7.

On the Schedule page, enter MY_SQLACCESS_TASK in the Task Name field. Select the first Time Zone from the provided list (click the torch icon). Once done, click Next.

 

8.

On the Review page, click Submit.

 

9.

Back to the Advisor Central page, click Refresh. Once done, click the MY_SQLACCESS_TASK link in the Results table. The task should be in COMPLETED status.

 

10.

This takes you to the Results page. From this page, you can see the potential benefit of implementing the SQL Access Advisor recommendations on the workload. Click the Recommendation sub-tab.

 

11.

On the Recommendations sub-page, you can see the high-level overview of the recommendations. Basically, all possible types of recommendations were generated for this workload. Make sure all recommendations are selected, and click the Recommendation Details button. This takes you to the Details page where you can see more details about each of the recommendations, as well as the corresponding SQL statements from the workload that are affected by these recommendations. Click OK.

 

12.

Back to the Recommendations sub-page, click the Schedule Implementation button.

 

13.

On the Schedule Implementation page, a warning is displayed indicating that the wizard will not try to implement its recommendations because some of them are very important changes that should be looked at closely by the administrator. So, click the Show SQL button to look at the script you could use to implement all recommendations. In fact, you already created this script and you will use it later in this OBE. Once you reviewed the script, click Done. Back to the Schedule Implementation page, click Cancel.

 

14.

Click the Database tab on the top-right end corner and then the Software and Support tab. Once on the Software and Support page, click the SQL Performance Analyzer link. You now want to prove that implementing the recommendations will be beneficial.

 

15.

On the SQL Performance Analyzer page, click the Guided Workflow link.

 

16.

On the Guided Workflow page, click the Execute icon on the line corresponding to step 1. On the Create SQL Performance Analyzer Task, enter MY_SPA_TASK in the SQL Performance Analyzer Task Name field. Then, enter SH.SQLSET_MY_SQLACCESS_WORKLOAD in the SQL Tuning Set Name field. Once done, click Create.

 

17.

Back to the Guided Workflow page, click the Execute icon for step 2. On the Create Replay Trial page, enter MY_SQL_REPLAY_BEFORE in the Replay Trial Name field, and make sure you tick the Trial environment established check box. Then, click Submit.

 

18.

From your terminal session, connect as user sh (password sh) under SQL*Plus. In your SQL*Plus session execute the execute the implement.sql script. This script is a pre-created script corresponding to the recommendations previously generated by your SQL Access Advisor session.

@ implement.sql

 

19.

Back to your Guided Workflow Enterprise Manager page, click the Execute icon corresponding to step 3. On the Create Replay Trial page, enter MY_SQL_REPLAY_AFTER in the Replay Trial Name field. Make sure the Trial environment established check box is ticked, and click Submit.

 

20.

Back to your Guided Workflow Enterprise Manager page, click the Execute icon corresponding to step 4. On the Run Replay Trial Comparison page, make sure you create a comparison between MY_SQL_REPLAY_BEFORE and MY_SQL_REPLAY_AFTER. Click Submit.

 

21.

Back to your Guided Workflow Enterprise Manager page, click the Execute icon corresponding to step 5. On the SQL Performance Analyzer Task Result page, you can clearly see that the second trial is much faster than the original one.

 

Back to Topic Lis t

Summary

In this tutorial, you learned how to use SQL Access Advisor.

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

Left Curve
Popular Downloads
Right Curve
Untitled Document