Blog

le 04/02/2007
Thomas Bordet
Archives blog-arkzoyd @fr

Exemple d'ordre SQL #5 et #6 /+Anti-Pattern/

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!

Thomas Bordet

Voir tous ses articles

Blog

Exemple d'ordre SQL #5 et #6 /*+Anti-Pattern*/

Articles récents

Exemple d'ordre SQL #5 et #6 /+Anti-Pattern/