SQL Tuning Advisor - Bright DBA

How to run SQL Tuning Advisor Manually

SQL Tuning Advisor is SQL diagnostic software in the Oracle Database Tuning Pack. SQL Tuning Advisor is a mechanism for resolving problems related to suboptimally performing SQL statements. Use SQL Tuning Advisor to obtain recommendations for improving performance of high-load SQL statements, and prevent regressions by only executing optimal plans.

Tuning recommendations include:

Collection of object statistics
Creation of indexes
Rewriting SQL statements
Creation of SQL profiles
Creation of SQL plan baselines

1. Simulate the issue
2. Get the SQLID for running SQL
3. Run the SQL Advisor Manually
I. Ceate a tuning task
II. Check the status
III. Execute your SQL Tuning task
IV. Check the status again
4. Review the recommendations by SQL Tuning Advisor
5. Implement the Recommendations
6. Drop_tuning_task
7. Verify

Step 1: Simulate the issue.

SQL> grant dba to sh;
SQL> connect sh/sh
Connected.
SQL> drop index sales_time_bix;

Index dropped.

SQL> drop index sales_time_idx;
drop index sales_time_idx
           *
ERROR at line 1:
ORA-01418: specified index does not exist


SQL> create index sales_time_idx on sales(time_id) compute statistics;

Index created.

SQL>

SQL> DECLARE
 n number;
BEGIN
for i in 1..1000 loop
 select /*+ ORDERED USE_NL(c) FULL(c) FULL(s)*/ count(*) into n
 from sales s, customers c
 where c.cust_id = s.cust_id and CUST_FIRST_NAME='Dina'
 order by time_id;
 DBMS_LOCK.SLEEP(1);
end loop;
END;
/  2    3    4    5    6    7    8    9   10   11   12

Step 2: Get the SQLID for running SQL

SQL> select sid, serial#, sql_id, event from v$session where username='SH'
  2  /

       SID    SERIAL# SQL_ID        EVENT
---------- ---------- ------------- ----------------------------------------------------------------
        29         27               SQL*Net message from client
        33         21 1gyx4vasq9yv0 SQL*Net message from client
        49          3 5mxdwvuf9j3vp direct path read

SQL>

3: Run the SQL Advisor Manually

I. Create a tuning task


SET serveroutput ON
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '&&my_sql_id',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sql_tuning_task_&&my_sql_id',
                          description => 'Tuning task for statement &&my_sql_id.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

output:

SQL> DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
  2    3  BEGIN
  4    l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
  5                            sql_id      => '&&my_sql_id',
  6                            scope       => DBMS_SQLTUNE.scope_comprehensive,
  7                            time_limit  => 60,
  8                            task_name   => 'sql_tuning_task_&&my_sql_id',
  9                            description => 'Tuning task for statement &&my_sql_id.');
 10    DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 11  END;
/ 12
Enter value for my_sql_id: 5mxdwvuf9j3vp
old   5:                           sql_id      => '&&my_sql_id',
new   5:                           sql_id      => '5mxdwvuf9j3vp',
old   8:                           task_name   => 'sql_tuning_task_&&my_sql_id',
new   8:                           task_name   => 'sql_tuning_task_5mxdwvuf9j3vp',
old   9:                           description => 'Tuning task for statement &&my_sql_id.');
new   9:                           description => 'Tuning task for statement 5mxdwvuf9j3vp');

PL/SQL procedure successfully completed.

SQL>

II. Check the status

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

output:

SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';
old   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'
new   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_5mxdwvuf9j3vp'

TASK_NAME                      STATUS
------------------------------ -----------
sql_tuning_task_5mxdwvuf9j3vp  INITIAL

SQL>

III. Execute your SQL Tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');

output:

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');

PL/SQL procedure successfully completed.

SQL>

IV. Check the status again

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

output:

SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';
old   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'
new   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_5mxdwvuf9j3vp'

TASK_NAME                      STATUS
------------------------------ -----------
sql_tuning_task_5mxdwvuf9j3vp  COMPLETED

SQL>

Step 4: Review the recommendations by SQL Tuning Advisor

SET LINES 150
SET pages 50000
SET long 5000000
SET longc 5000000
 
SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;

output:

SQL> SET LINES 150
SET pages 50000
SET long 5000000
SET longc 5000000

SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;
SQL> SQL> SQL> SQL> SQL> old   1: SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual
new   1: SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_5mxdwvuf9j3vp') AS recommendations FROM dual

RECOMMENDATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : sql_tuning_task_5mxdwvuf9j3vp
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 07/20/2016 22:05:52
Completed at       : 07/20/2016 22:06:25

-------------------------------------------------------------------------------
Schema Name: SH
SQL ID     : 5mxdwvuf9j3vp
SQL Text   : SELECT /*+ ORDERED USE_NL(c) FULL(c) FULL(s)*/ COUNT(*) FROM
             SALES S, CUSTOMERS C WHERE C.CUST_ID = S.CUST_ID AND
             CUST_FIRST_NAME='Dina' ORDER BY TIME_ID

-------------------------------------------------------------------------------
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: 99.94%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'sql_tuning_task_5mxdwvuf9j3vp', task_owner => 'SYS', replace =>
            TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:             PARTIAL          COMPLETE
  Elapsed Time (s):           15.829308           .131064      99.17 %
  CPU Time (s):                 9.99348           .074863      99.25 %
  User I/O Time (s):                  0           .000694
  Buffer Gets:                  5320022              3093      99.94 %
  Physical Read Requests:             0                10
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0            884736
  Physical Write Bytes:               0                 0
  Rows Processed:                     0                 1
  Fetches:                            0                 1
  Executions:                         0                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 0 executions.
  2. Statistics for the SQL profile plan were averaged over 8 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 2043253752

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |    25 |   370M  (1)|999:59:59 |       |       |
|   1 |  SORT AGGREGATE       |           |     1 |    25 |            |          |       |       |
|   2 |   NESTED LOOPS        |           |   982 | 24550 |   370M  (1)|999:59:59 |       |       |
|   3 |    PARTITION RANGE ALL|           |   918K|    11M|   527   (2)| 00:00:07 |     1 |    28 |
|   4 |     TABLE ACCESS FULL | SALES     |   918K|    11M|   527   (2)| 00:00:07 |     1 |    28 |
|*  5 |    TABLE ACCESS FULL  | CUSTOMERS |     1 |    12 |   404   (1)| 00:00:05 |       |       |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("CUST_FIRST_NAME"='Dina' AND "C"."CUST_ID"="S"."CUST_ID")

2- Using SQL Profile
--------------------
Plan hash value: 3219640484

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |    25 |   937   (2)| 00:00:12 |       |       |
|   1 |  SORT AGGREGATE       |           |     1 |    25 |            |          |       |       |
|*  2 |   HASH JOIN           |           |   982 | 24550 |   937   (2)| 00:00:12 |       |       |
|*  3 |    TABLE ACCESS FULL  | CUSTOMERS |    43 |   516 |   405   (1)| 00:00:05 |       |       |
|   4 |    PARTITION RANGE ALL|           |   918K|    11M|   527   (2)| 00:00:07 |     1 |    28 |
|   5 |     TABLE ACCESS FULL | SALES     |   918K|    11M|   527   (2)| 00:00:07 |     1 |    28 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."CUST_ID"="S"."CUST_ID")
   3 - filter("CUST_FIRST_NAME"='Dina')

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


SQL>

Step 5: Implement the Recommendations
Note: Implement only after you satisfy the recommendations

execute dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_task_5mxdwvuf9j3vp', task_owner => 'SYS', replace => TRUE);

output:

SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_task_5mxdwvuf9j3vp', task_owner => 'SYS', replace => TRUE);

PL/SQL procedure successfully completed.

SQL>

Step 6: Drop_tuning_task
After review recommendations, we can remove the task by drop_tuning_task

BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
END;
/

output:

SQL> BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
END;
/  2    3    4
old   2:   DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
new   2:   DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_5mxdwvuf9j3vp');

PL/SQL procedure successfully completed.

SQL>

Step 7: Verify

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

output:

SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';
old   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'
new   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_5mxdwvuf9j3vp'

no rows selected

SQL>

Reference:

http://kb.initso.at/2011/05/oracle-howto-run-the-sql-tuning-advisor-without-enterprise-manager-db-console-or-grid-control/

OR ELSE YOU CAN TRY LIKE BELOW WAY

Step1: Create task

variable stmt_task VARCHAR2(64);
variable sts_task VARCHAR2(64);

EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
sql_id => '&sql_id', -       
task_name => 'sql_tuning_task1',-
description  => 'Tune my query');

Step2: Execute task

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('sql_tuning_task1');

Step3: Review task

SET LONG 1000000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_tuning_task1') from DUAL;

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.