1. Identify the SQL-ID for your SQL.

select s.sid, S.USERNAME, s.sql_id, p.spid FROM v$session s, v$process p WHERE s.paddr = p.addr and s.username='APPS' and s.sid='&1';

SID USERNAME SQL_ID SPID
---------- ------------------------------ ------------- ------------------------
266 APPS 1nc15w35wgbyf 8138

2. Input your SQL-ID in DBMS_SQLTUNE and create the tuning task.

sqlplus "/ as sysdba"
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '1nc15w35wgbyf',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '1nc15w35wgbyf_tuning_task',
description => 'Tuning task for statement 1nc15w35wgbyf');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/


3. Execute your tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '1nc15w35wgbyf_tuning_task');

4. Receive the tuning advise

SET PAGESIZE 24
Set LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('1nc15w35wgbyf_AWR_tuning_task') AS recommendations FROM dual;


The recommendations can be as below. Notice, although the plan 2 is better than recommendation plan 1, in actuality Plan 2 will lead into parallel execution of your SQL.
This will further result in more consumption of your database server resources. Hence as per the below recommendation, Plan 1 is better than plan 2.


-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------

2 potentially better execution plans were found for this statement. Choose
one of the following SQL profiles to implement.

Recommendation (estimated benefit: 98.22%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'V000005015237742110.2176398148', task_owner => 'APPS', replace
=> TRUE);

Recommendation (estimated benefit: 99.96%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name =>
'V000005015237742110.2176398148', task_owner => 'APPS', replace
=> TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

Executing this query parallel with DOP 64 will improve its response time
98.00% over the SQL profile plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 27.93% 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.