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

Step 6
Once the tuning task has completed, the recommendations can be generated using the following:
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('RACPTbook_workshop_chapter7') AS recommendations FROM dual;
Based on the query and the current structure, the tuning optimizer can generate a very detailed list of
recommendations or no recommendations at all if no changes are required. In this specific example, the tuning
optimizer generated a list of recommendations.
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : RACPTbook_workshop_chapter7
Tuning Task Owner : RAPDWH
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 07/19/2010 18:32:07
Completed at : 07/19/2010 18:33:38
-------------------------------------------------------------------------------
Schema Name: RAPDWH
SQL ID : gbybkujt0w0y6
SQL Text : SELECT /*+ NOPARALLEL */ N_NAME,R_NAME,P_NAME,S_NAME,SUM(PS_SUPPL
YCOST) 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
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 90.15%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution for this statement.
execute dbms_sqltune.accept_sql_profile(task_name =>
'RACPTbook_workshop_chapter7', task_owner => 'RAPDWH', replace
=> TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);
Executing this query with (degree of parallelism) DOP 10 will improve its response time 90.16% over the
original plan. However, there is some cost in enabling parallel execution. It will increase the statement's resource
consumption by an estimated 1.58%, which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the response time of concurrent
statements might be negatively impacted if sufficient hardware capacity is not available.