Dans l’exemple qui suit, nous allons illustrer deux possibilités offertes par le SQL Tuning Advisor. Il donne ci-dessous des conseils qui concernent la manière dont l’ordre SQL est écrit.

Schema d’exemple
Dans un premier temps, nous allons créer une table demo#6 avec le script ci-dessous :

create table demo#6 (col1 number primary key, col2 number,
col3 number, col4 number, col5 number, col6 number,
col7 varchar2(4000));

begin
for j in 1..100000 loop
insert into demo#6 values (i, i, i, i, i, 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#6’, –
cascade=>true, method_opt=>’FOR ALL COLUMNS SIZE 254′);

SQL*Tuning Advisor #1
Nous allons lancer le SQL Tuning Advisor avec la requête « select * from demo#6 where col1=1 union select * from demo#6 where col1=2 ». Le Tuning Advisor est capable de donner des conseils à propos de la syntaxe SQL comme vous pouvez le constater.

exec dbms_sqltune.drop_tuning_task( task_name => ‘DEMO#6’ );
variable gg varchar2(100)
exec :gg := DBMS_SQLTUNE.CREATE_TUNING_TASK( –
sql_text => ‘select * from demo#6 where col1=1 union select * from demo#6 where col1=2’,-
user_name => ‘SYSTEM’, –
scope => ‘COMPREHENSIVE’, –
time_limit => 30, –
task_name => ‘DEMO#6’, –
description => ‘Tuning d »un accès simple à la table DEMO#6’);

exec dbms_sqltune.execute_tuning_task( task_name => ‘DEMO#6’ );

set long 10000
set longchunksize 10000
set lines 100
select dbms_sqltune.report_tuning_task(‘DEMO#6’)
from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘DEMO#6’)
————————————————————————–
GENERAL INFORMATION SECTION
————————————————————————–
Tuning Task Name : DEMO#6
Tuning Task Owner : SYSTEM
Scope : COMPREHENSIVE
Time Limit(seconds) : 30
Completion Status : COMPLETED
Started at : 02/04/2007 17:56:53
Completed at : 02/04/2007 17:56:55
Number of SQL Restructure Findings: 1

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘DEMO#6’)
————————————————————————–
————————————————————————–
Schema Name: SYSTEM
SQL ID : 4am69yrt6d6y3
SQL Text : select * from demo#6 where col1=1 union select * from demo#6
where col1=2

————————————————————————–
FINDINGS SECTION (1 finding)
————————————————————————–

1- Restructure SQL finding (see plan 1 in explain plans section)

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘DEMO#6’)
————————————————————————–
—————————————————————-
Une opération « UNION » coûteuse a été trouvée à l’ID de ligne 1 du plan
d’exécution.

Recommendation
————–
– Si les doubles sont autorisés ou que l’unicité est garantie, envisagez
l’utilisation de « UNION ALL » à la place de « UNION ».

Rationale
———

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘DEMO#6’)
————————————————————————–
« UNION » est une opération coûteuse et bloquante car elle exige
l’élimination des lignes en double. « UNION ALL » est un choix moins
coûteux, à condition que les doubles soient autorisés ou que l’unicité
soit garantie.

————————————————————————–
EXPLAIN PLANS SECTION
————————————————————————–
[…]

SQL*Tuning Advisor #2
Nous allons lancer le SQL Tuning Advisor avec la requête « select * from demo#6 where col2=1 ». Le Tuning Advisor est capable de donner des conseils à propos de l’indexation comme vous le voyez ci-dessous.

exec dbms_sqltune.drop_tuning_task( task_name => ‘DEMO#6b’ );
variable gg varchar2(100)
exec :gg := DBMS_SQLTUNE.CREATE_TUNING_TASK( –
sql_text => ‘select * from demo#6 where col2=1’,-
user_name => ‘SYSTEM’, –
scope => ‘COMPREHENSIVE’, –
time_limit => 30, –
task_name => ‘DEMO#6b’, –
description => ‘Tuning d »un accès simple à la table DEMO#6b’);

exec dbms_sqltune.execute_tuning_task( task_name => ‘DEMO#6b’ );

set long 10000
set longchunksize 10000
set lines 100
select dbms_sqltune.report_tuning_task(‘DEMO#6b’)
from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘DEMO#6B’)
——————————————————————————
GENERAL INFORMATION SECTION
——————————————————————————
Tuning Task Name : DEMO#6b
Tuning Task Owner : SYSTEM
Scope : COMPREHENSIVE
Time Limit(seconds) : 30
Completion Status : COMPLETED
Started at : 02/04/2007 18:12:42
Completed at : 02/04/2007 18:12:42
Number of Index Findings : 1

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘DEMO#6B’)
——————————————————————————
——————————————————————————
Schema Name: SYSTEM
SQL ID : 5mjr2d9h58tux
SQL Text : select * from demo#6 where col2=1

——————————————————————————
FINDINGS SECTION (1 finding)
——————————————————————————

1- Index Finding (see explain plans section below)
————————————————–

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘DEMO#6B’)
——————————————————————————
Le plan d’exécution de cette instruction peut Ûtre amélioré en créant un ou
plusieurs index.

Recommendation (estimated benefit: 100%)
—————————————-
– Envisagez d’exécuter Access Advisor pour améliorer la conception du
schéma physique ou de créer l’index recommandé.
create index SYSTEM.IDX$$_002A0001 on SYSTEM.DEMO#6(‘COL2’);

Rationale
———

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘DEMO#6B’)
——————————————————————————
La création des index recommandés améliore de faþon considérable le plan
d’exécution de cette instruction. Il pourrait cependant être préférable
d’exécuter « Access Advisor » en utilisant une charge globale SQL
représentative contrairement à une seule instruction. Ceci permettra
d’obtenir des recommandations d’index complètes prenant en compte le coût
de maintenance des index et de la consommation d’espace supplémentaire.

——————————————————————————
EXPLAIN PLANS SECTION
——————————————————————————
[…]

Avec ce qui précède, voici deux exemples de plus des recommandations du SQL Tuning Advisor (et presque complètement en français…)

GarK!