Option 1:


SQL> set long 65536SQL> set longchunksize 65536SQL> set linesize 100 lines 1000 pages 0 SQL> @?/rdbms/admin/sqltrpt.sql Input SQL_ID

Option 2: (Using DBMS_SQLTUNE package)

DECLARE l_sql_tune_task_id VARCHAR2(100);BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => 'd969r1dvu7rg4', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 100, task_name => 'd969r1dvu7rg4_tuning_task1', description => 'Tuning task1 for statement n'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);END;/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'd969r1dvu7rg4_tuning_task1');
set long 65536set longchunksize 65536set linesize 100 lines 1000 pages 0select dbms_sqltune.report_tuning_task('d969r1dvu7rg4_tuning_task1') from dual;

Option 3: (Run SQLtuning advisor based on AWR report snapshot)


DECLARE l_sql_tune_task_id VARCHAR2(100);BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => 'd969r1dvu7rg4', begin_snap => 21386, -- AWR begin snap id end_snap => 21391, -- AWR end snap id scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 100, task_name => 'd969r1dvu7rg4_tuning_task1', description => 'Tuning task1 for statement n'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);END;/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'd969r1dvu7rg4_tuning_task1');
set long 65536set longchunksize 65536set linesize 100 lines 1000 pages 0select dbms_sqltune.report_tuning_task('d969r1dvu7rg4_tuning_task1') from dual;