Dernièrement, nous avons migrés certaines bases de données à la version 10.2.0.4 et un utilisateur m'a mentionné que le temps d'exécution de l'une de ses requêtes nécessitait plus de 15 secondes. Auparavant, elle se terminait en moins d'une seconde... Ouch!
Après avoir vérifié le plan d'exécution de la requête, j'ai remarqué qu'à l'étape qui accède à une vue sur une autre base de données, nécessitait beaucoup de temps. J'ai pu observé ce temps en utilisant mon script d'analyse de performance de requête. Le voici :
-- À désactiver sinon ne fonctionnera pas
set serveroutput off
ALTER SESSION SET STATISTICS_LEVEL = ALL;
-- OU Ajouter le hint /*+ gather_plan_statistics */ dans la requete
-- ----------------------------------------------
-- Requête à optimiser
-- ----------------------------------------------
Select * from dual;
-- ****************
-- Plan d'execution
-- ****************
Prompt -- ==============================================================
Prompt -- Starts : nombre de fois que l'etape est executee
Prompt -- E-Rows : nombre de lignes que l'optimisateur estime retourner
Prompt -- E-Time : temps prevu par l'optimisateur pour accomplir l'étape
Prompt -- A-Rows : nombre de lignes retournees par la requete
Prompt -- A-Time : temps ecoule pour chaque etape
Prompt -- Buffers : nombre d'operations au buffer cache
Prompt -- E-Rows et A-Rows doivent etre egaux.
Prompt -- ==============================================================
--
-- 'All' : Affiche la section "Query block/Object Alias" , information sur les Predicate, et projection de colonne
-- après le plan.
-- 'Advanced': Comme l'option 'All', mais inclut l'infornation sur les
"Outline".
-- 'Outline' : Affiche seulement l'information "Outline" et l'information
sur les predicate après le plan.
--
-- Exemples :
-- dbms_xplan.display_cursor(null, null,'ADVANCED +ALLSTATS LAST
+MEMSTATS LAST')
-- dbms_xplan.display_cursor(null, null,'ADVANCED +MEMSTATS LAST')
-- dbms_xplan.display_cursor(null, null,'BASIC IOSTATS -ROWS LAST')
-- dbms_xplan.display_cursor(null, null,'ALLSTATS LAST')
--
select * from table(
dbms_xplan.display_cursor(null, null, 'ADVANCED +ALLSTATS LAST'));
Suite à l'exécution, le temps sous la colonne "A-Time" lors de l'accès à la table distante m'a clairement indiqué que c'est à cet endroit que la requête perd son temps.
Il n'y a pas 56 façons d'optimiser des requêtes qui mette en jointure des tables sur différentes instances alors, j'ai opté rapidement à utiliser le hint "Driving_Site"
À l'occasion, ramener la table distante sous la forme d'un énoncé SQL au niveau du SELECT comme si c'était une colonne, peut être très bénéfique. Par contre, il faut que cette solution s'y prête tel que dans un cas où la requête est exécutée à quelques reprises et non des milliers de fois.
