Dans l’exemple qui suit, nous allons illustrer que le SQL Tuning Advisor ne descend pas dans le détail de comment les statistiques sont capturées. Cet exemple a été réalisé avec Oracle 10.2.0.3 sur Windows.

Dans un premier temps, nous allons créer une table demo#3 avec le script ci-dessous :

• La table demo#3 a des statistiques a jour puisque prise après le remplissage de la table. En revanche la stratégie par défaut ne prend pas d’histogramme sur les colonnes à moins qu’elles aient été préalablement utilisées dans une clause WHERE.
  
• Les données de la colonne COL1 sont indexées. Elles sont distribuées de manière non homogène puisque la valeur 1 représente en fréquence la moitié des valeurs de la colonne.

SQL> create table demo#3 (id number primary key,
col1 number,
col2 varchar2(4000));

create index demo#3_col1_idx on demo#3(col1);

begin
for i in 1..50000 loop
insert into demo#3 values (i,1,
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’);

insert into demo#3 values (50000+i,1+i,
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’);
end loop;
commit;
end;
/
exec dbms_stats.gather_table_stats(USER, ‘DEMO#3’ –
, cascade=>true, method_opt=>’FOR ALL COLUMNS SIZE AUTO’);

SQL*Tuning Advisor
Nous allons utiliser le SQL Tuning Advisor pour voir si la requête « select count(col2) from demo#3 where col1=1 » peut être améliorée.

SQL> variable gg varchar2(100)
SQL> exec :gg := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
    sql_text    => 'select count(col2) from demo#3 where col1=1', -
    user_name   => 'SYSTEM', -
    scope       => 'COMPREHENSIVE', -
    time_limit  => 30, -
    task_name   => 'DEMO#3', -
    description => 'Tuning d''un accès simple à la table DEMO#3');
SQL> exec dbms_sqltune.execute_tuning_task( task_name => 'DEMO#3' );

Pour affichez le résultat du SQL Tuning Advisor, tapez :

SQL > set long 10000
SQL > set longchunksize 10000
SQL > set lines 100
SQL > select dbms_sqltune.report_tuning_task('DEMO#3')
   from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#3')
----------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------------
Tuning Task Name   : DEMO#3
Tuning Task Owner  : SYSTEM
Scope              : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status  : COMPLETED
Started at         : 02/03/2007 16:18:25
Completed at       : 02/03/2007 16:18:27

----------------------------------------------------------
Schema Name: SYSTEM
SQL ID     : 2s4vfqtcpcw5v
SQL Text   : select count(col2) from demo#3 where col1=1

----------------------------------------------------------
There are no recommendations to improve the statement.

----------------------------------------------------------
SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK('DEMO#3');

Améliorer les plans grâce à la stratégie par défaut de prise de statistiques

COUNT(COL2)
-----------
 50000


Plan d'exécution
----------------------------------------------------------
Plan hash value: 559471190

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |  1005 |  3149   (1)| 00:00:38 |
|   1 |  SORT AGGREGATE    |        |     1 |  1005 |            |          |
|*  2 |   TABLE ACCESS FULL| DEMO#2 | 49606 |    47M|  3149   (1)| 00:00:38 |
-----------------------------------------------------------------------------

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

2 - filter("COL1"=1)


Statistiques
----------------------------------------------------------
     1  recursive calls
     0  db block gets
 14297  consistent gets
  7422  physical reads
     0  redo size
   422  bytes sent via SQL*Net to client
   396  bytes received via SQL*Net from client
     2  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
     1  rows processed

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0              1
              1          49999

GarK!