References
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)
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:
| TASK_NAME |
|---|
| TASK_296 |
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:
| STATUS |
|---|
| COMPLETED |
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:
| DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_296') |
|---|
|
GENERAL INFORMATION SECTION
------------------------------------------------------------------------------- Tuning Task Name : TASK_296 Tuning Task Owner : SYS Workload Type : SQL Tuning Set Scope : COMPREHENSIVE Time Limit(seconds) : 1800 Completion Status : COMPLETED Started at : 02/23/2012 03:49:06 Completed at : 02/23/2012 03:51:07 SQL Tuning Set (STS) Name : OCM11G SQL Tuning Set Owner : SYS Number of Statements in the STS : 1 ------------------------------------------------------------------------------- There are no recommendations to improve the statements in the workload. ------------------------------------------------------------------------------- |