#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 by Azhar Syed on October 11, 2013 - 11:41

    As informative as ever ! Tx !

  2. #2 by Carlos Sierra on October 11, 2013 - 12:49

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

  3. #3 by Uwe Hesse on October 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 by Uwe Hesse on October 11, 2013 - 13:06

    Azhar Syed, you’re welcome 🙂

  5. #5 by albertofrosi on October 11, 2013 - 21:26

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

  6. #6 by Porus Homi Havewala on October 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 by Uwe Hesse on October 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 by Gaurav on March 11, 2015 - 13:37

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

  9. #9 by hari on June 29, 2015 - 15:11

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

  10. #10 by sid on November 23, 2015 - 20:42

    Awesome ..very helpful

  11. #11 by Nazeem on December 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 by Uwe Hesse on December 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 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: