11G OCM Use SQL Tuning Advisor

References

Oracle® Database Concepts 11g Release 1 (11.1)

• Chapter 14, "Manageability"
  • Application and SQL Tuning

Oracle® Database Performance Tuning Guide 11g Release 1 (11.1)

• Chapter 17, "Automatic SQL Tuning"
  • SQL Tuning Advisor
  • Reactive Tuning Using the SQL Tuning Advisor
    • Running the SQL Tuning Advisor
      • Creating a SQL Tuning Task
      • Executing a SQL Tuning Task
      • Checking the Status of a SQL Tuning Task
      • Checking the Progress of the SQL Tuning Advisor
      • Displaying the Results of a SQL Tuning Task
  • SQL Tuning Sets

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

• Chapter 125, "DBMS_SQLTUNE"
  • CREATE_SQLSET
  • CREATE_TUNING_TASK
  • EXECUTE_TUNING_TASK
  • LOAD_SQLSET
  • SELECT_CURSOR_CACHE

Oracle® Database Reference 11g Release 1 (11.1)

• Part II Static Data Dictionary Views
  • USER_ADVISOR_TASKS
• Part III Dynamic Performance Views
  • V$ADVISOR_PROGRESS

Overview

The objective from the 11G OCM Upgrade Exam is:

Use SQL Tuning Advisor

Reading Notes

SQL Tuning Advisor

Application and SQL Tuning says that:

After identifying the top resource-consuming SQL statements, Oracle Database can automatically analyze them and recommend solutions using the Automatic SQL Tuning Advisor. Automatic SQL Tuning is exposed with an advisor, called the SQL Tuning Advisor. The SQL Tuning Advisor takes one or more SQL statements as input and produces well-tuned plans along with tuning advice. You do not need to do anything other than invoke the SQL Tuning Advisor.

SQL Tuning Advisor is described thus:

The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

SQL Tuning Sets

SQL Tuning Sets are described thus:

A SQL Tuning Set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. The SQL statements can be loaded into a SQL Tuning Set from different SQL sources, such as the Automatic Workload Repository, the cursor cache, or custom SQL provided by the user. An STS includes:

• A set of SQL statements
• Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment
• Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type
• Associated execution plans and row source statistics for each SQL statement (optional)

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.

SQL Tuning Set

I saved this SQL into a SQL Tuning Set called OCM11G:

EXEC DBMS_SQLTUNE.CREATE_SQLSET( 'OCM11G' )

DECLARE

cur DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN

OPEN cur FOR

SELECT VALUE(P)

FROM table(

DBMS_SQLTUNE.SELECT_CURSOR_CACHE(

'sql_id = ''dgdfqhj9ua2kv''')) P;

DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'OCM11G',

populate_cursor => cur);

END;

/

Run the SQL Tuning Advisor

Creating a SQL Tuning Task

Followed the procedure in Creating a SQL Tuning Task:

VARIABLE task_name VARCHAR2(30)

EXEC :task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sqlset_name => 'OCM11G' );

PRINT task_name

The output is:

Executing a SQL Tuning Task

Followed the procedure in Executing a SQL Tuning Task:

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TASK_296' );

Checking the Status of a SQL Tuning Task

Followed the procedure in Checking the Status of a SQL Tuning Task:

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'TASK_296';

The output is:

Checking the Progress of the SQL Tuning Advisor

Followed the procedure in Checking the Progress of the SQL Tuning Advisor:

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'SYS' AND task_name = 'TASK_296';

The result is:

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'SYS' AND task_name = 'TASK_296'

*

ERROR at line 1:

ORA-00904: "TASK_NAME": invalid identifier

Also, USER_NAME is an invalid identifier—it should be USERNAME.

Displaying the Results of a SQL Tuning Task

Followed the procedure in Displaying the Results of a SQL Tuning Task:

SET LONG 1000

SET LONGCHUNKSIZE 1000

SET LINESIZE 100

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'TASK_296')

FROM DUAL;

And the result is: