Discussion :

[marieo]

Bonjour,

J'aimerais savoir comment faire pour optimiser le temps d'exécution d'une requête INSERT lancée depuis une BDD sur une autre base via un DB_LINK.

Voici ma requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
INSERT INTO ma_table_locale (champ1, champ2, champ3)
SELECT champ 1, champ2, champ3
FROM ma_table_distante@mon_dblink
WHERE EXISTS(select 1 from autre_table_locale where ma_table_locale.champ4=autre_table_locale.champ4);

Le temps d'exécution de la requête SELECT (sans la clause INSERT INTO) est de quelques minutes seulement alors que le temps d'exécution de la requête INSERT est de plusieurs heures.
J'avais une clé primaire sur "ma_table_locale" que j'ai inactivé en pensant gagner du temps d'exécution mais cela n'a pas fonctionné.

Auriez-vous des pistes à explorer pour réussir à optimiser le temps de traitement de cette requête ?
Quelles solutions pourraient être mises en place pour que l'INSERT ne prenne plus 3 heures ?


J'ai pensé à faire une extraction dans un fichier .txt du résultat de la requête SELECT pour ensuite faire un import via SQL LOADER mais n'ai pas encore testé car ça ne me paraît pas pertinent d'exporter des données Oracle pour ensuite les réimporter dans Oracle...


Merci beaucoup pour vos idées.
MarieO

[Ikebukuro]

Est-ce qu'il y a des triggers sur ta table ma_table_locale?
Via dbms_metadata.get_ddl, donne nous l'ordre ddl de création de la table, on aura peut-être des ifos.
Ta table ma_table_locale n'est pas sur un tablespace très lent ou avec des paramètres qui ralentisse le INSERT?

Quand le SELECT est rapide, c'est bien fait depuis la base où est ma_table_locale?

Il faut nous donner le plan d'exécution de l'INSERT via un EXPLAIN PLAN car tu n'as pas de bind variables.
Si on ne voit rien, il faudra faire un autotrace et une trace 10046 mais là ça prendra du temps.

[Waldar]

Faites le en deux étapes, une table de staging avec les colonnes 1 à 4, puis l'insert dans votre table avec le where exists.
Si le volume de la table distante est très important et que le volume de l'autre table locale est plus petit, ça peut valoir le coup d'envoyer l'autre table locale sur le serveur distant pour filtrer les résultats à importer.

[Mohamed.Houri]

Bonjour,

Lorsque vous avez un problème de performance pensez toujours à poster le plan d'exécution correspondant accompagné de la partie "Prédicat".

Mais il y a de fortes chances que votre situation soit un cas classique d'un insert/select via db link. En effet, dans un SELECT via dblink Oracle peut choisir le driving site, le local comme le distant. En revanche dans un insert/select le driving site est absolument le site où on insert. Du coup, il est fort probable que lors de votre insert Oracle a passé tout son temps à faire des allers-retours entre le site local et distant ligne par ligne (je ramène une ligne je vérifie l'existence, je ramène la deuxième je vérifie son existence ainsi de suite...). Cela est naturellement visible dans un plan d'execution particulièrement dans un rapport SQL Monitor.

Une solution parmi d’autres dans ce cas serait d’utiliser une ‘’pipelined function’’ pour rapatrier les données ou faire un block pl/sql suivi d’un insert avec un bulk collect comme expliqué dans l’article suivant qui date de presque 10 ans déjà

https://hourim.wordpress.com/2012/02...ct-via-dblink/


Bien Cordialement
Mohamed Houri

[Ikebukuro]

Un Autotrace permet aussi de voir ce genre de chose, on aurait vu un nombre impressionnant de "SQL*Net roundtrips to/from client".

[marieo]

Une solution parmi d’autres dans ce cas serait d’utiliser une ‘’pipelined function’’ pour rapatrier les données ou faire un block pl/sql suivi d’un insert avec un bulk collect comme expliqué dans l’article suivant qui date de presque 10 ans déjà

https://hourim.wordpress.com/2012/02...ct-via-dblink/

Bonjour,

Je viens de tester la solution de création de type pour stocker les données avant de les insérer via un bulk collect selon le post indiqué dans votre message.

J'ai adapté le script pour qu'il corresponde à la structure de mes bases de données mai j'ai un message d'erreur
- concernant la ligne "FETCH....." : "expression DISTANT_TYP_TAB inappropriée à gauche d'une instruction d'affectation"
- concernant la ligne "FOR i in ....." : "l'identificateur DISTANT_TYP_TAB.COUNT doit être déclaré".

Pourriez-vous m'aider à résoudre ces erreurs de fonctionnement ?

Je vous remercie.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
CREATE or replace force type schema_local.distant_typ AS object (
 champ1 number(10,0),
 champ2 varchar2(20),
 champ3 number(10,0),
 champ4 number(10,0));
/
 
CREATE or replace force type schema_local.distant_typ_tab AS TABLE of distant_typ;
/
 
DECLARE 
 CURSOR get_data_from_db IS
 SELECT champ1, champ2, champ3, champ4 
 FROM schema_distant.ma_table_distante@mon_dblink
 WHERE EXISTS(select 1 from schema_local.autre_table_locale where ma_table_distante.champ4=autre_table_locale.champ4);
BEGIN
 OPEN get_data_from_db;
 LOOP
  FETCH get_data_from_db BULK collect INTO schema_local.distant_typ_tab LIMIT 1000;
  FOR i in 1..distant_typ_tab.COUNT
   LOOP
     INSERT /* append */ INTO schema_local.table_locale
      (champ1, champ2, champ3, champ4)
     SELECT champ 1, champ2, champ3, champ4
     FROM table (distant_typ_tab);
     EXIT WHEN get_data_from_db%notfound;
   END LOOP;
 END LOOP;
 COMMIT;
END;
/

[JeitEmgie]

DECLARE

FETCH get_data_from_db BULK collect INTO schema_local.distant_typ_tab LIMIT 1000;

END;
/
[/CODE]

La destination du INTO doit être une VARIABLE pas un TYPE: déclarer une variable de ce type dans le DECLARE
cela réglera aussi l'autre problème (en changeant par VAR_OF_TYPE_DISTANT_TYP_TAB.COUNT)