11G OCM Use SQL Access Advisor

References

Overview

The objective from the 11G OCM Upgrade Exam is:

Use SQL Access Advisor

Reading Notes

SQL Access Advisor

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.