Steps to run SQL Tuning Advisor from Database.

 Dear Folks,

As we already know, we can run tuning advisor again SQL_ID in OEM and implement through it. But some times, we can't implement recommendations.

We have gone through same phase in OEM where we couldn't implement SQL profile through OEM. Hence we have decided to run from DB.

Here are the steps

19c

===

ONPROD [****@servername ~]$ export ORACLE_PDB_SID=OLBUI

NONPROD [****@servername ~]$ sqlplus / as sysdba

SQL> set long 1000000000

Col recommendations for a200

SQL> SQL> DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => '2jy3n3dtmntxm',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => '2jy3n3dtmntxm_tuning_task_1',

 2  3  4  5  6  7  8  9 description => 'Tuning task for statement 2jy3n3dtmntxm');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

10  11  12 DECLARE

*

ERROR at line 1:

ORA-13780: SQL statement does not exist.

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 257

ORA-06512: at "SYS.DBMS_SQLTUNE", line 771

ORA-06512: at line 4

>>If you receive above error.Use AWR snap IDS between SQL_ID Runs.

SQL> SELECT SNAP_ID FROM DBA_HIST_SQLSTAT

WHERE SQL_ID='2jy3n3dtmntxm'

ORDER BY SNAP_ID; 2  3

  SNAP_ID

----------

   3140

   3141

   3142

   3143

   3144

   3145

   3146

   3147

8 rows selected.

SQL> declare

l_sql_tune_task_id varchar2(100);

 2  3 begin

 4  l_sql_tune_task_id := dbms_sqltune.create_tuning_task (

 begin_snap => 3140,

 end_snap => 3147,

 sql_id => '2jy3n3dtmntxm',

 scope => dbms_sqltune.scope_comprehensive,

 time_limit => 10800,

 task_name => '2jy3n3dtmntxm_tuning_task',

 description => 'tuning_in_OLBUI');

dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

end;

/ 5  6  7  8  9  10  11  12  13  14

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '2jy3n3dtmntxm_tuning_task');

PL/SQL procedure successfully completed.

Once above done. Please execute following SQL query to get recommendations.

SET LONG 10000000;

SET PAGESIZE 100000000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('2jy3n3dtmntxm_tuning_task') AS recommendations FROM dual;

SET PAGESIZE 24

-------------------------------------------------------------------------------

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: 82.5%)

  -----------------------------------------

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name =>

    '2jy3n3dtmntxm_tuning_task', task_owner => 'SYS', replace =>

    TRUE);

Executed same in PROD and performance was improved.

SQL> show user

USER is "SYS"

SQL> exec dbms_sqltune.accept_sql_profile(task_name =>'2jy3n3dtmntxm_tuning_task', task_owner => 'SYS', replace =>TRUE);

PL/SQL procedure successfully completed.

After implemented above recommendation, Performance was improved for SQL. 

Thanks.