Oracle® Database Concepts 11g Release 1 (11.1)
Oracle® Database Performance Tuning Guide 11g Release 1 (11.1)
Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1)
Oracle® Database Reference 11g Release 1 (11.1)
Oracle® Database SQL Language Reference 11g Release 1 (11.1)
The objective from the 11G OCM Upgrade Exam is:
Use SQL Access Advisor
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.
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.
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.