Pickleball spielen

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
Connected.
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);
    begin
       tempstring := 'select * from adam.test where lower(w)=''uwe''';
       task_id := dbms_sqltune.create_tuning_task(sql_text => tempstring, task_name=>'SQLTUNE1');
       dbms_sqltune.execute_tuning_task('SQLTUNE1');
    end;
    /

PL/SQL procedure successfully completed.

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE1')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
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

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

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE1')
-------------------------------------------------------------------------------
  Table "ADAM"."TEST" was not analyzed.

  Recommendation
  --------------
  - 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');

  Rationale
  ---------

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE1')
-------------------------------------------------------------------------------
    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
  indices.

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

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

  Rationale
  ---------
    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.

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE1')
--------------------------------------------------------------------------------
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".

  Recommendation
  --------------
  - 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 🙂