11G OCM Use SQL Access Advisor

References

Chapter 18, "SQL Access Advisor"
- Overview of the SQL Access Advisor
- Using the SQL Access Advisor
  - Setting Up Tasks and Templates
    - Creating Tasks
  - Working with Recommendations
    - Generating Recommendations
    - Viewing Recommendations

Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1)

Chapter 17, "DBMS_ADVISOR"

Oracle® Database Reference 11g Release 1 (11.1)

Part II Static Data Dictionary Views
- DBA_ADVISOR_RECOMMENDATIONS

Oracle® Database SQL Language Reference 11g Release 1 (11.1)

CREATE DIRECTORY

Oracle10g: Using SQLAccess Advisor (DBMS_ADVISOR) with the Automatic Workload Repository [ID 259188.1]

Overview

The objective from the 11G OCM Upgrade Exam is:

Use SQL Access Advisor

Reading Notes

SQL Access Advisor

Application and SQL Tuning says that:

The SQL Access Advisor can automatically analyze the schema design for a given workload and recommend indexes, function-based indexes, partitions, and materialized views to create, retain, or drop as appropriate for the workload. For single statement scenarios, the advisor only recommends adjustments that affect the current statement. For complete business workloads, the advisor makes recommendations after considering the impact on the entire workload.

Test Case

The test I am using is the SQL from Replay a captured workload which has an SQL ID of 'dgdfqhj9ua2kv'.

The test query is as follows:

SELECT products.prod_category, products.prod_subcategory, times.calendar_year, SUM( sales.quantity_sold ) AS total_quantity_sold, SUM( sales.amount_sold ) AS total_amount_sold FROM sales INNER JOIN products USING( prod_id ) INNER JOIN customers USING( cust_id ) INNER JOIN countries USING( country_id ) INNER JOIN channels USING( channel_id ) INNER JOIN times USING( time_id ) WHERE products.prod_valid = 'A' AND channels.channel_class = 'Direct' AND customers.cust_valid = 'A' AND countries.country_name = 'Australia' GROUP BY products.prod_category, products.prod_subcategory, times.calendar_year ORDER BY products.prod_category, products.prod_subcategory, times.calendar_year ;

This query is about a yearly summary of product categories and sub-categories sold directly to active customers in Australia.

This query was put into an SQL Tuning Set called SYS.OCM11G during the procedure outlined in Use SQL Tuning Advisor.

Procedure

Create a task

Followed the procedure in Create a task and in Creating Tasks:

EXEC DBMS_ADVISOR.CREATE_TASK( DBMS_ADVISOR.SQLACCESS_ADVISOR, 'OCM11G' )

Define the workload

Followed the procedure in Define the workload for adding the SQL Tuning SET, SYS.OCM11G, to the SQL Access Advisor task called 'OCM11G':

EXEC DBMS_ADVISOR.ADD_STS_REF( 'OCM11G', 'SYS', 'OCM11G' )

I want to check for all possible recommendations. I need to modify the task parameters as follows:

EXEC DBMS_ADVISOR.SET_TASK_PARAMETER( 'OCM11G', 'ANALYSIS_SCOPE', 'ALL' );

Generate the recommendations

Followed the procedure in Generate the recommendations and Generating Recommendations:

EXEC DBMS_ADVISOR.EXECUTE_TASK( 'OCM11G' );

View and implement the recommendations

Followed the procedure in View and implement the recommendations and went to Viewing Recommendations where I found the following comment:

Each recommendation generated by the SQL Access Advisor can be viewed using several catalog views, such as DBA_ADVISOR_RECOMMENDATIONS. However, it is easier to use the GET_TASK_SCRIPT procedure or use the SQL Access Advisor in Enterprise Manager, which graphically displays the recommendations and provides hyperlinks to quickly see which SQL statements benefit from a recommendation. Each recommendation produced by the SQL Access Advisor is linked to the SQL statement it benefits.

When Itry to use the GET_TASK_REPORT function as follows, I get an error message:

SET LONG 10000

SELECT DBMS_ADVISOR.GET_TASK_REPORT( 'OCM11G' ) FROM DUAL;

The result is:

ERROR:

ORA-13699: Advisor feature is not currently implemented.

ORA-06512: at "SYS.PRVT_ADVISOR", line 3122

ORA-06512: at "SYS.DBMS_ADVISOR", line 585

ORA-06512: at line 1

no rows selected

I followed the procedure in Oracle10g: Using SQLAccess Advisor (DBMS_ADVISOR) with the Automatic Workload Repository [ID 259188.1] to generate a script. First, I created a directory:

mkdir -p /u00/app/oracle/tuning/ocm11g

Then, I created a directory object called ADVISOR_RESULTS:

CREATE DIRECTORY advisor_results AS '/u00/app/oracle/tuning/ocm11g';

And, I create a script called ocm11g_sh_access.sql as follows:

exec DBMS_ADVISOR.CREATE_FILE( DBMS_ADVISOR.GET_TASK_SCRIPT('OCM11G'), 'ADVISOR_RESULTS', 'ocm11g_sh_access.sql');

The resulting script file is uploaded as ocm11g_sh_access.sql. None of the recommendations have been implemented.