IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Optimiser un insert via un db_link


Sujet :

SQL Oracle

  1. #1
    Membre du Club
    Inscrit en
    mai 2006
    Messages
    115
    Détails du profil
    Informations forums :
    Inscription : mai 2006
    Messages : 115
    Points : 59
    Points
    59
    Par défaut Optimiser un insert via un db_link
    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

  2. #2
    Membre chevronné
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    avril 2013
    Messages
    1 732
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : avril 2013
    Messages : 1 732
    Points : 2 110
    Points
    2 110
    Par défaut
    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.
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  3. #3
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Consultant Teradata
    Inscrit en
    septembre 2008
    Messages
    8 188
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Consultant Teradata
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : septembre 2008
    Messages : 8 188
    Points : 17 070
    Points
    17 070
    Par défaut
    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.

  4. #4
    Membre éprouvé

    Homme Profil pro
    Inscrit en
    mars 2010
    Messages
    510
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : mars 2010
    Messages : 510
    Points : 1 296
    Points
    1 296
    Par défaut
    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
    Bien Respectueusement
    www.hourim.wordpress.com

    "Ce qui se conçoit bien s'énonce clairement"

  5. #5
    Membre chevronné
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    avril 2013
    Messages
    1 732
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : avril 2013
    Messages : 1 732
    Points : 2 110
    Points
    2 110
    Par défaut
    Un Autotrace permet aussi de voir ce genre de chose, on aurait vu un nombre impressionnant de "SQL*Net roundtrips to/from client".
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  6. #6
    Membre du Club
    Inscrit en
    mai 2006
    Messages
    115
    Détails du profil
    Informations forums :
    Inscription : mai 2006
    Messages : 115
    Points : 59
    Points
    59
    Par défaut
    Citation Envoyé par Mohamed.Houri Voir le message
    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;
    /

  7. #7
    Membre expert
    Homme Profil pro
    Inscrit en
    septembre 2006
    Messages
    2 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : septembre 2006
    Messages : 2 736
    Points : 3 924
    Points
    3 924
    Par défaut
    Citation Envoyé par marieo Voir le message
    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)

Discussions similaires

  1. optimisation requete insert ou update sous postgres
    Par peppena dans le forum PostgreSQL
    Réponses: 2
    Dernier message: 01/03/2007, 11h21
  2. [Hibernate 3] [HQL] Faire un Insert via HQL
    Par bouchette63 dans le forum Hibernate
    Réponses: 3
    Dernier message: 09/11/2006, 17h05
  3. PB d'insertion via formulaire
    Par xtiand4 dans le forum Langage
    Réponses: 5
    Dernier message: 23/06/2006, 09h45
  4. Optimiser un INSERT global
    Par niiccoo dans le forum Oracle
    Réponses: 25
    Dernier message: 22/05/2006, 14h25
  5. [Help] pb d'insertion via PHP ...
    Par romtrash dans le forum PostgreSQL
    Réponses: 3
    Dernier message: 19/04/2005, 11h47

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo