Install Oracle Database 11g.

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

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

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.

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

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.

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.

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

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.

On the Review page, click Submit.

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.

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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.