#Oracle SQL Tuning Advisor on the Command Line

This post will show a brief example how to use the SQL Tuning Advisor on the Command Line. The documentation is very verbose about it, but maybe you’ll find it a bit hard to dig through and prefer to start easy.

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
SQL> create table adam.test(n number, w varchar2(3));

Table created.

SQL> insert into adam.test values (1,'uwe');

1 row created.

SQL> commit;

Commit complete.

SQL> create index adam.idx on adam.test(w);

Index created.

The playing field contains nothing special, just an ordinary table and an ordinary index. Now let’s pretend that the following statement has a very long runtime and we don’t know why it does not use the index on the table:

SQL> select * from test where lower(w)='uwe';

         N W
---------- ---
         1 uwe

I will now ask the SQL Tuning Advisor about that ’slow‘ statement


SQL> declare
    tempstring  varchar2(300);
    task_id   varchar2(200);
       tempstring := 'select * from adam.test where lower(w)=''uwe''';
       task_id := dbms_sqltune.create_tuning_task(sql_text => tempstring, task_name=>'SQLTUNE1');

PL/SQL procedure successfully completed.

SQL> set long 5000
SQL> select dbms_sqltune.report_tuning_task('SQLTUNE1') from dual; 

Tuning Task Name   : SQLTUNE1
Tuning Task Owner  : ADAM
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 10/11/2013 08:46:19
Completed at       : 10/11/2013 08:46:21

Schema Name: ADAM
SQL ID     : 71vcnzazw9kvw
SQL Text   : select * from adam.test where lower(w)='uwe'


1- Statistics Finding

  Table "ADAM"."TEST" was not analyzed.

  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'ADAM', tabname =>
            'TEST', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');


    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

2- Index Finding (see explain plans section below)
  The execution plan of this statement can be improved by creating one or more

  Recommendation (estimated benefit: 66.52%)
  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.
    create index ADAM.IDX$$_000C0001 on ADAM.TEST(LOWER("W"),"N","W");

    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

3- Restructure SQL finding (see plan 1 in explain plans section)
  The predicate LOWER("TEST"."W")='uwe' used at line ID 1 of the execution
  plan contains an expression on indexed column "W". This expression prevents
  the optimizer from selecting indices on table "ADAM"."TEST".

  - Rewrite the predicate into an equivalent form to take advantage of
    indices. Alternatively, create a function-based index on the expression.

Pretty much to the point, don’t you agree? I removed the explain plan sections from the output; they show that without the recommended function based index, the statement needs to do a Full Table Scan .

That was a demonstration I did during my recent class Oracle Database 11g: Performance Tuning. Hope you find it useful 🙂

  1. #1 von Azhar Syed am Oktober 11, 2013 - 11:41

    As informative as ever ! Tx !

  2. #2 von Carlos Sierra am Oktober 11, 2013 - 12:49

    i always enjoy small and simple examples. thx for sharing with community

  3. #3 von Uwe Hesse am Oktober 11, 2013 - 13:05

    You’re welcome 🙂 Thank YOU for the nice feedback and especially for your contributions to the Oracle Community with SQLTXPLAIN (MOS Note 215187.1)

  4. #4 von Uwe Hesse am Oktober 11, 2013 - 13:06

    Azhar Syed, you’re welcome 🙂

  5. #5 von albertofrosi am Oktober 11, 2013 - 21:26

    Hi Uwe…very usefull as usual…but works only with Ee or SE also?

  6. #6 von Porus Homi Havewala am Oktober 12, 2013 - 12:01

    Alberto, this requires the EM Tuning pack license, and packs are only licensed for EE, not SE. So you cannot use it for SE.

    Uwe’s example also shows the importance of using Enterprise Manager. Why would anyone want to write PL/SQL like this, and spend their time? This is a simple example. Using the other nuances of tuning pack and diagnostics packs requires a lot of PL/SQL if using the command line. Better to use Enterprise Manager and save your time.

  7. #7 von Uwe Hesse am Oktober 13, 2013 - 17:16

    Thank you, Alberto, for the nice feedback and thank you, Porus, for answering!
    Obviously, you are very fond of using the Enterprise Manager 🙂
    It is not so very difficult to use the command line approach with the DBMS_SQLTUNE package as in the posting, though.

    Consider a scenario when you just want to get a recommendation for a more complex SQL statement. All you need to do is replace the tempstring above with that statement by just doing copy & paste from the posting. That would probably be faster than loading it into a SQL Tuning set and hand that over to the Enterprise Manager triggered SQL Tuning Advisor.

    It’s also an effort to crawl through various EM pages and clicking several buttons to get things done, you know 🙂

  8. #8 von Gaurav am März 11, 2015 - 13:37

    Nicely Explained, short and up to the point. Thanks for sharing.

  9. #9 von hari am Juni 29, 2015 - 15:11

    this is what people needed ,easy to know the concept and understand well

  10. #10 von sid am November 23, 2015 - 20:42

    Awesome ..very helpful

  11. #11 von Nazeem am Dezember 2, 2016 - 05:35

    Hi Uwe, short and sweet explanation. Looks like it accepts sqlid as well which helps avoid pasting huge sqls.

  12. #12 von Uwe Hesse am Dezember 2, 2016 - 10:00

    Nazeem, thank you for your nice words and the hint about the SQL_ID. Indeed, this works also:
    SQL> declare
    task_id varchar2(200);
    task_id := dbms_sqltune.create_tuning_task(sql_id => ’41tzurq3xc4vx‘, task_name=>’SQLTUNE1′);
    PL/SQL procedure successfully completed.

    Good point 🙂

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:


Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahre, wie deine Kommentardaten verarbeitet werden..