DBA Hub

📋Steps in this guide1/12

How to run SQL Tuning Advisor Manually

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 … Continue reading SQL Tu

oracle clusteringintermediate
by OracleDba
12 views
1

Overview

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 Step 1: Simulate the issue.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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
2

Section 2

Step 2: Get the SQLID for running SQL 3: Run the SQL Advisor Manually I. Create a tuning task 3: Run the SQL Advisor Manually I. Create a tuning task

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
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

Section 3

output: II. Check the status

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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;
/

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>

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

Section 4

output: III. Execute your SQL Tuning task

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
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>

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

Section 5

IV. Check the status again output:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');

PL/SQL procedure successfully completed.

SQL>

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

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>
6

Section 6

Step 4: Review the recommendations by SQL Tuning Advisor output: Step 5: Implement the Recommendations Note: Implement only after you satisfy the recommendations

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
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> 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>
7

Section 7

Step 5: Implement the Recommendations output: Step 6: Drop_tuning_task After review recommendations, we can remove the task by drop_tuning_task Step 6: Drop_tuning_task

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
execute dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_task_5mxdwvuf9j3vp', task_owner => 'SYS', replace => TRUE);

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

PL/SQL procedure successfully completed.

SQL>
8

Section 8

output: Step 7: Verify

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
END;
/

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>

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

Section 9

output: 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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
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>
10

Section 10

OR ELSE YOU CAN TRY LIKE BELOW WAY Step1: Create task Step2: Execute task

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
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');
11

Section 11

Step3: Review task 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. Caution:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('sql_tuning_task1');

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

Section 12

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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!