L’exemple ci-dessous illustre un cas où une application n’est pas développée de la meilleure manière. Vous pouvez probablement simplement trouver la raison pour laquelle ce qui arrive arrive. Si ce n’est pas le cas, attendez le prochain « Post » pour l’explication de comment utiliser AWR/ADDM et SQL Tuning Advisor dans ce cas.

Créer et alimenter une table
Dans un premier temps, il faut créer le modèle de données. Voici ci-dessous le script relatif à cette création. Les caractéristiques du modèle de données sont les suivantes :

• Une table nommée DEMO#1 contient 2 colonnes de type VARCHAR2 nommées respectivement ID et LIB. La colonne ID étant une clé primaire est forcément indexée.
• La table est remplie de 100.000 lignes. Chaque ligne est suffisamment grande pour que la taille de la table soit significative
• Les statistiques sur les objets (table & index) sont effectuées

create table demo#1 (
id varchar2(100) primary key,
lib varchar2(4000));

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

Requête et plan d’exécution
Voici l’ordre SQL généré par l’application :

set autotrace traceonly
select lib from demo where id=1;

Et son plan d’exécution. Qu’est-ce que vous remarquez ?

Plan d’exécution
———————————————————-
Plan hash value: 4000794843

—————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
—————————————————————
| 0 | SELECT STATEMENT | | 1 | 1006 | 3150 (1)|
|* 1 | TABLE ACCESS FULL|DEMO#1| 1 | 1006 | 3150 (1)|
—————————————————————

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

1 – filter(TO_NUMBER(« ID »)=1)

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

Script pour démontrer l’utilisation de AWR, ADDM et SQL Tuning Advisor
Maintenant que vous avez tout configuré et compris, vous pouvez garder une copie du script ci-dessous que vous pouvez utiliser pour utiliser AWR, ADDm et SQL Tuning Advisor.

set timing on
declare
gg varchar2(4000);
begin
for i in 1..10000 loop
select lib into gg
from demo#1
where id=1;
end loop;
end;
/

Conclusion
L’objectif de ce « Post » comme de ceux qui suivront et qui prendront la même forme sont d’illustrer le genre de problèmes que vous pouvez rencontrer. Dans ce cas, passer par un index pertinent peut améliorer les performances. Ne généralisez pas et tenez compte de l’ensemble de l’activité. L’ordre s’exécute sur ma machine en 0,28 seconde quand l’index n’existe pas… Fallait-il l’améliorer ?

GarK!