How To Run SQL Tuning Advisor manually in Oracle 19c?

The SQL Tuning Advisor analyzes high-volume SQL statements and offers tuning recommendations. It takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements.

It can run against any given SQL statement. The SQL Tuning Advisor provides advice in the form of precise SQL actions for tuning the SQL statements along with their expected performance benefits.

The recommendation or advice provided relates to the collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

In this below tutorial I am going to explain how to run sql tuning advisor against sql_id. 

In my case sql id is – g0t052az3rx44

1. Create Tuning Task

[code] SQL> DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => 'g0t052az3rx44', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 1800, task_name => 'g0t052az3rx44_tuning_task19', description => 'Tuning task for statement g0t052az3rx44'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / PL/SQL procedure successfully completed. [/code]

2. Execute Tuning task and/or run the advisor
[code] EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'g0t052az3rx44_tuning_task19'); SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='g0t052az3rx44_tuning_task19'; TASK_NAME STATUS ------------------------------ ----------- g0t052az3rx44_tuning_task19 COMPLETED [/code]

3. Once the Advisor task is completed. Generate report using command below
[code] SQL> set long 65536 set longchunksize 65536 set linesize 100 select dbms_sqltune.report_tuning_task('g0t052az3rx44_tuning_task19') from dual; DBMS_SQLTUNE.REPORT_TUNING_TASK('G0T052AZ3RX44_TUNING_TASK19') ---------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : g0t052az3rx44_tuning_task19 Tuning Task Owner : SYS Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 05/17/2020 18:46:42 Completed at : 05/17/2020 18:46:43 DBMS_SQLTUNE.REPORT_TUNING_TASK('G0T052AZ3RX44_TUNING_TASK19') ---------------------------------------------------------------------------------------------------- Schema Name : SYS Container Name: CDB$ROOT SQL ID : g0t052az3rx44 SQL Text : select name,intcol#,segcol#,type#,length,nvl(precision#,0),dec ode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale ,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fi xedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0 ), nvl(evaledition#,1),nvl(unusablebefore#,0),nvl(unusablebegi nning#,0), case when (type# in (1,8,9,96,112)) then nvl(collid, 16382) else 0 end case, nvl(collintcol#,0), nvl(acdrrescol#, 0), nvl(spare7, 0), nvl(spare9, 0), nvl(spare10, 0) from col$ where obj#=:1 order by intcol# Bind Variables: : 1 - (NUMBER):245 ------------------------------------------------------------------------------- There are no recommendations to improve the statement. ------------------------------------------------------------------------------- [/code]
4. Analyze the report and implement the solution if necessary