SQL Tuning Advisor steps and details

Dear DBA-Mates,

Hope you all are Safe and doing well!!! As we all know about corona virus, so be-careful and safe.

Yes… Long time haven’t written anything but if you ask me I was also lost in this world and lots of things came up and down and that’s called life for all…

It’s ok… Let’s start the topic now which is very important and DBA’s should know it. You all may heard about SQL Tuning but very few people only do it.

It is very important as whenever developer/functional raise a concerns about the request or program is taking time to complete then we can do the SQLT which helps a lots and resolve the issue…

Please find the below steps:

1. Create Tuning Task:

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => '8b98n39ax64uj',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 300,

                          task_name   => '1q7d0p8qnpv51_tuning_task11',

                          description => 'Tuning task1 for statement 1q7d0p8qnpv51');

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

END;

/

 

2. Execute Tuning task:

 

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

 

3. Get the Tuning advisor report:

set longchunksize 65536

SET LONG 100000;

SET PAGESIZE 50000

SET LINESIZE 200

select dbms_sqltune.report_tuning_task('1q7d0p8qnpv51_tuning_task11') from dual;

This output can be spool also, which we can give to developer team to verify and then we can execute it.

This output will give advice and sql tune script by which running it will run fast and get it complete.

Some useful links:

How to change database DB name using NID utility

Stuck Thread in WebLogic Error and solution

What is PDB and CDB in oracle 12c

ORA-01110 data file 1: system01.dbf recovery