SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 219

Step 2
If you examined the plan, you would notice that Oracle had performed a full scan on almost all the tables used in the
statement. Full scans are normally expensive based on the number of rows that the table contains. If the number of
rows are not high, probably a full scan would be more efficient compared to an indexed-based lookup. The first step
for using the SQL tuning advisor is to identify the SQL_ID for the statement to be tuned:
SQL> SELECT sql_id,
sql_text
FROM v$sql
WHERE sql_text LIKE '%SUM(PS_SUPPLYCOS%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
gbybkujt0w0y6
SELECT /*+ NOPARALLEL */ N_NAME,R_NAME,P_NAME,S_NAME,SUM(PS_SUPPLYCOST) FROM region, NATION,
SUPPLIER, PARTSUPP, PART WHERE PS_PARTKEY=P_PARTKEY AND PS_SUPPKEY = S_SUPPKEY AND S_NATIONKEY =
N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY GROUP BY N_NAME, R_NAME, P_NAME, S_NAME
Step 3
Using the SQL_ID from Step 2, we use the SQL advisor to create a tuning task:
SET SERVEROUTPUT ON
DECLARE
mv_sql_tune_task_id VARCHAR2(100);
BEGIN
mv_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => ' gbybkujt0w0y6',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'RACPTbook_workshop_chapter7',
description => 'Tuning task');
DBMS_OUTPUT.put_line('mv_sql_tune_task_id: ' || mv_sql_tune_task_id);
END;
/
Step 4
Once the tuning task is created, the task needs to be executed using the EXECUTE_TUNING_TASK procedure following:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'RACPTbook_workshop_chapter7');
Step 5
Verify the task status of the tuning task using the following query:
SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME LIKE 'RAC%';
TASK_NAME STATUS
------------------------------ -----------
RACPTbook_workshop_chapter7 COMPLETED