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

PL/SQL Oracle Discussion :

Commit après 1000 enregistrements


Sujet :

PL/SQL Oracle

  1. #1
    Membre du Club
    Commit après 1000 enregistrements
    Bonjour ,

    je fais des insertions des données qui proviennent d'une autre table via dblink.
    mails la table cible contient plusieurs milliers de lignes , et j'ai des problèmes de performances , je souhaiterais faire des commit après un certain nombre d’enregistrements insérés ,ci-dessous ma requête .


    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
     insert into ma_table
        (
    	champs_1,
    	champs_2,
    	champs_3,
    	champs_4,
    	champs_5,
    	champs_6,
    	champs_7,
    	champs_7
        )
        select * from la_table_cible@mon_dblink ;
     commit ;



    Toute aide sera la bienvenue , merci .

  2. #2
    Membre éprouvé
    Bonjour,

    Avant de pouvoir vous donner un conseil, j’aurai aimé connaitre comment êtes vous arrivé à savoir que "commiter" tous 1000 lignes améliorera la performance de votre select?

    Cary Millsap: a dit une chose très sensée: ''Why Guess when you can know?'' Pourquoi devinez lorsque vous pouvez savoir ?

    Aussi, dans ce genre de situations, il vaut mieux diagnostiquer correctement le problème de performance avant de lui apporter par la suite le remède qui lui sied parfaitement.

    Ceci dit j’ai quelques conseils à vous donner quand même:
    Si votre table d’insertion n’a ni trigger ni contrainte d’intégrité (FK) pensez alors à utiliser un ''direct path load'' en utilisant le hint /*+ append */.

    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
     
    INSERT /*+ append */ INTO ma_table
        (
    	champs_1,
    	champs_2,
    	champs_3,
    	champs_4,
    	champs_5,
    	champs_6,
    	champs_7,
    	champs_7
        )
    select champs_1,
    	champs_2,
    	champs_3,
    	champs_4,
    	champs_5,
    	champs_6,
    	champs_7,
    	champs_7 
      from la_table_cible@mon_dblink ;

    Cela ne génèrera pas d’UNDO sur la table et les indexes seront chargés avec un ''bulk collect''. Si certaines conditions supplémentaires sont vérifiées il n’y aura pas de Redo aussi :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    Table Mode    Insert Mode     ArchiveLog mode      result
    -----------   -------------   -----------------    ----------
    LOGGING       APPEND          ARCHIVE LOG          redo generated
    NOLOGGING     APPEND          ARCHIVE LOG          no redo
    LOGGING       no append       ""                   redo generated
    NOLOGGING     no append       ""                   redo generated
    LOGGING       APPEND          noarchive log mode   no redo
    NOLOGGING     APPEND          noarchive log mode   no redo
    LOGGING       no append       noarchive log mode   redo generated
    NOLOGGING     no append       noarchive log mode   redo generated


    11_QUESTION_ID:5280714813869" target="_blank">http://asktom.oracle.com/pls/asktom/...:5280714813869

    Attention ceci représente une solution valable lorsque vous ne faites pas de ''delete'' fréquents sur la table où lorsque vous insérez dans une table vide. En effet le ''direct path load'' insert directement au dessus du High Water Mark et n’utilise aucun espace libre existant dans les blocks de données.

    Si vous partez d’une table vide, ou si le volume de données à insérer est largement supérieur au volume de données actuel de la table, il vaut mieux alors mettre tous les indexes dans un statut ‘DISABLED’ et faire un ‘rebuild’ après la fin de l’insert.

    Si vous lisez le nouveau libre de Jonathan Lewis, vous réfléchiriez à plusieurs reprises avant d’utiliser un select *. Je vous conseille alors de remplacer le select * par les colonnes nécessaires à l’insert uniquement.

    Est-ce que votre table est partitionnée ? d'autres suggestions peuvent être envisagées dans ce cas aussi

    Enfin, comme je l’ai signalé au début, pensez à tracer votre insert/select via l’event 10046 afin de connaître réellement où se trouve l’origine du ralentissement de votre insert
    Bien Respectueusement
    www.hourim.wordpress.com

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

  3. #3
    Membre du Club
    Bonjour Mohamed ,


    Bravo, c'est ce qu'on appelle par une réponse remplie de bon sens .

    Effectivement je pensais que les insertions étaient stockées dans les UNDO, c'est pourquoi j'ai pensé qu'en effectuant des commit intermédiaires , j'aurais résolu mon problème mais apparemment non .. .

    Ceci dit mon contexte est celui ci:
    Ma table contient déjà des données (des millions de lignes) précédemment enregistrées, elle a aussi des contraintes de Primary key , de Unique Key mais pas de FK .

    Si je fais un
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    SELECT * FROM la_table_cible@mon_dblink ;
    pour récupérer mes données c'est parce que de l'autre côté ma table cible n'est qu'une vue qui contient les noms de colonnes des tables dont je désire récupérer les données .

    Parraport à vos conseils , je pense que la solution :
    Si vous partez d’une table vide, ou si le volume de données à insérer est largement supérieur au volume de données actuel de la table, il vaut mieux alors mettre tous les indexes dans un statut ‘DISABLED’ et faire un ‘rebuild’ après la fin de l’insert.
    serait adaptée à mon contexte .

    La table cibles sont partitionnée , ma table de destination (celle qui doit contenir les données extraites ) est aussi partitionnée .

    Merci toujours pour vos conseils si éclairés .

  4. #4
    Membre éprouvé
    Citation Envoyé par cornnery Voir le message

    La table cibles sont partitionnée , ma table de destination (celle qui doit contenir les données extraites ) est aussi partitionnée .
    Si les deux tables sont partitionnées des deux côtés, vous pouvez faire un insert/select par partition. Et dans ce cas il faudra uniquement "disabler" les indexes locaux correpondants à la partition p1 comme suit par exemple:

    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
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
     
    mhouri > alter index part_ind modify partition p1 unusable;
     
    Index altered.
     
    mhorui> select index_name, status
      2  from user_ind_partitions
      3  where status = 'UNUSABLE';
     
    INDEX_NAME                     STATUS                                                                                   
    ------------------------------ --------                                                                                 
    PART_IND                       UNUSABLE                                                                                 
     
    INSERT /*+ append */ INTO ma_table partition (p1)
        (
    	champs_1,
    	champs_2,
    	champs_3,
    	champs_4,
    	champs_5,
    	champs_6,
    	champs_7,
    	champs_7
        )
    SELECT champs_1,
    	champs_2,
    	champs_3,
    	champs_4,
    	champs_5,
    	champs_6,
    	champs_7,
    	champs_7 
     FROM la_table_cible@mon_dblink 
     WHERE cle_partition = :cle_qui_correspond a la partition p1
    ;
     
    mhouri> alter index part_ind rebuild partition p1;
     
    Index altered.
     
    mhouri> select index_name, status
      2  from user_ind_partitions
      3  where status = 'UNUSABLE';
     
    no rows selected


    Et ainsi de suite pour les autres partitions.
    Bien Respectueusement
    www.hourim.wordpress.com

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

  5. #5
    Membre du Club
    Hi Mohamed ,

    intéressant , sauf que dans mon cas la table n'a pas été construite avec des index locaux , mais avec un index global, dans ce cas dois je le "disabler" de façon globale et le reconstruire ? ça ne serait pas un traitement lourd pour une table contenant des millions de ligne ???

    Merci

  6. #6
    Membre éprouvé
    Citation Envoyé par cornnery Voir le message
    Hi Mohamed ,

    intéressant , sauf que dans mon cas la table n'a pas été construite avec des index locaux , mais avec un index global, dans ce cas dois je le "disabler" de façon globale et le reconstruire ? ça ne serait pas un traitement lourd pour une table contenant des millions de ligne ???

    Merci
    Ce que je vous ai suggéré a un sens pour les indexes locallement partitionnés car on ne ''disable'' que la partition vide (ou à insérer) et on refait le 'rebuild' après une fois cette partition chargée.

    Pour les indexes globaux, cela n'a pas de sens surtout lorsque le volume de données apporté par l'insert est largement inférieur au volume des données avant l'insert. En d'autres mots, si vous insérez 1 million de lignes dans une table qui en contient déjà 10 millions, vous devriez dans ce cas éviter de faire le ''disable'' des indexes avant insert; autrement vous auriez à faire un rebuild d'un index de 11 millions de lignes; ce qui n'est pas négligeable
    Bien Respectueusement
    www.hourim.wordpress.com

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

  7. #7
    Membre du Club
    Ok ,

    je teste tout ça et je vous fait un feed-back .

    Merci encore

  8. #8
    Membre du Club
    Hi Mohamed ,

    Je reviens encore avec mon problème de performance ,

    après avoir suivi vos conseils j'ai modifié mon script comme ci-dessous:

    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
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    CREATE OR REPLACE PROCEDURE facts.proc_fact_moc IS
       DynamicSql varchar2(10000);
       dateAndTime varchar(10000);
       msg        varchar(10000);
       p1   varchar(100);
       ecode NUMBER;
       emesg VARCHAR2(10000);
    BEGIN
      select 'D'||to_char(sysdate -2,'yyyymmdd')into p1 from dual;
      BEGIN
        ----first insertion 
    	msg := 'Start first insertion in FACT table';
        insert into facts_data_log values('v_fact_moc',sysdate,msg,0);
    	commit;
     
        DynamicSql:=' insert /*+ append */ into fact_moc partition ('||p1||') 
        (
    	msisdn,
    	trafic_date,
    	destination,
    	traffic_type,
    	vas_service,
    	account_id,
    	total_calls,
    	total_revenu_appel,
    	total_duree_appel,
    	total_volume,
    	apn_info,
    	extension_code,
    	service_class_id,
    	location_number,
    	service_offerings,
    	cell_identity,
    	country_code
        )
        select /*+ parallel(3) */ 
    	msisdn,
        trafic_date,
        destination,
        traffic_type,
        vas_service,
        account_id,
        total_calls,
        total_revenu_appel,
        total_duree_appel,
        total_volume,
        apn_info,
        extension_code,
        service_class_id,
        location_number,
        service_offerings,
        cell_identity,
        country_code
    	from v_fact_moc@DWS.cg.NET';
      execute immediate(DynamicSql);
      commit;
      msg := 'End of first insertion in FACT table';
      insert into facts_data_log values('v_fact_moc', sysdate, msg, 0);
      commit;
       EXCEPTION WHEN OTHERS THEN 
         ecode := SQLCODE;
         emesg := SQLERRM;
    	 ROLLBACK;
    	 msg :='An error has occured during the insertion in FACT_MOC from v_fact_moc_mtn ';
    	 msg := msg ||': '||TO_CHAR(ecode) || '-' || emesg ;
         insert into facts_data_log values('v_fact_moc',sysdate,msg,0);
         commit;              
     
      END ;
     END proc_fact_moc ;


    Hélas le script cours des heures et des heures sans s'arrêter , mais quand je lance la vue
    v_fact_moc
    directement sur ma cible , celle-ci ne prends qu'une dizaine de minutes .
    Auriez-vous d'autres conseils intéressants à me donner pour optimiser cette requête , merci .

  9. #9
    Membre éprouvé
    Bonjour

    Pourquoi utilisez-vous du SQL dynamique? Le SQL statique n'est pas possible?

    Puisque vous êtes encore en environement de TEST, faites ce qui suit

    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
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
     
    alter session set events '10046 trace name context forever, level 12';
     
     insert /*+ append */ into fact_moc partition (p1) 
        ( msisdn,
          trafic_date,
          destination,
          traffic_type,
          vas_service,
          account_id,
          total_calls,
          total_revenu_appel,
          total_duree_appel,
          total_volume,
          apn_info,
          extension_code,
          service_class_id,
          location_number,
          service_offerings,
          cell_identity,
          country_code
        )
        select 
             msisdn,
            trafic_date,
            destination,
            traffic_type,
            vas_service,
            account_id,
            total_calls,
            total_revenu_appel,
            total_duree_appel,
            total_volume,
            apn_info,
            extension_code,
            service_class_id,
            location_number,
            service_offerings,
            cell_identity,
            country_code
    from v_fact_moc@DWS.cg.NET
    where -- clause qui correspond à la partition p1
     
    alter session set events '10046 trace name context off';


    Attention à la where clause
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
     
    from v_fact_moc@DWS.cg.NET
    where -- clause qui correspond à la partition p1


    Il faudrait sélectionner uniquement les lignes qui peuvent être contenues dans la partition p1.

    Avec le fichier trace généré vous allez savoir où est épuisé le temps d'execution de l'insert/select
    Bien Respectueusement
    www.hourim.wordpress.com

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

  10. #10
    Membre du Club
    Hi Mohamed ,

    puis je savoir quelle est la conséquence en ajoutant la ligne ci-dessous
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    ALTER session SET events '10046 trace name context forever, level 12';
    ?

    Pensez-vous qu'il serait plus performant d'utiliser le sql normal (statique) au sql dynamique
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    execute immediate(DynamicSql);
    ?

    pour ce qui est de la restriction de la partition p1 , la vue
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    v_fact_moc@DWS.cg.NET
    fait déjà une restriction sur la partition où est hébergée la table cible .

    Merci

  11. #11
    Membre éprouvé
    puis je savoir quelle est la conséquence en ajoutant la ligne ci-dessous
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    ALTER session SET events '10046 trace name context forever, level 12';

    La conséquence c'est la génération d'un "trace file" dans le répertoire user_dump_test où est hébergée votre base de données. Ceci ralentira un peu votre insert/select mais permettra de savoir quelle partie du select/insert prend le plus de temps.

    Pensez-vous qu'il serait plus performant d'utiliser le sql normal (statique) au sql dynamique [code]execute immediate(DynamicSql);

    Il existe de bonnes habitudes en Oracle qu'il convient d'adopter aussi longtemps que cela est possible. Parmi ces bonnes habitudes c'est de n'utiliser le SQL dynamique que si le SQL statique n'est pas possible. L'explication se trouve dans ce site et dans d'autres également. Il suffit de chercher.

    Dans quel environnement faites vous votre insert/select?
    Bien Respectueusement
    www.hourim.wordpress.com

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

  12. #12
    Membre du Club
    Ok ,

    Je travaille sur
    RHEL4 64 bits , oracle 10.2.0.1.0
    .

    Pour ce qui du fichier trace généré dans user_dump_test je l'ai localisé et sachant que ma requête prends des heures et heures pour s'exécuter ,
    pensez-vous que le résultat provisoire qu'il a généré peut nous en dire long déjà sur les problèmes de performance que je rencontre ?

    Merci

  13. #13
    Membre éprouvé
    Citation Envoyé par cornnery Voir le message
    Ok ,

    Je travaille sur .

    Pour ce qui du fichier trace généré dans user_dump_test je l'ai localisé et sachant que ma requête prends des heures et heures pour s'exécuter ,
    pensez-vous que le résultat provisoire qu'il a généré peut nous en dire long déjà sur les problèmes de performance que je rencontre ?

    Merci
    Oui tout à fait
    Bien Respectueusement
    www.hourim.wordpress.com

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

  14. #14
    Membre du Club
    Hi Mohamed ,

    en pièce-jointe , le fichier trace "provisoire" généré .

    Merci

  15. #15
    Expert éminent sénior
    Voilà le profile de votre traitement qui vous indique où le temps passe.

  16. #16

  17. #17
    Membre éprouvé
    98% de votre temps est passé dans "[B]SQL*Net message from dblink [idle[/B]]" comme vous pouvez le constater via le fichier html attaché.

    Vous devriez limiter le volume de données transférées entre les deux bases de données. Il est vrai que nous ne savons pas qu'est ce qui se cache derrière

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
     
    FROM v_fact_moc@DWS.cg.NET


    Combien de lignes envisagez-vous d’insérer avec ce select?

    Il va falloir aller dans la base distante et faire un examen minutieux de ce select. Peut-être qu’il y a un moyen d’améliorer la performance du select qui se cache derrière ce qui semble être une vue.

    Essayez de limiter le select à une dizaine de lignes (where rownum<10) et voir si l’insert/select s’exécute quand même très vite ou pas juste pour confirmer que le volume de données échangé entre les deux bases représente le problème à traiter
    Bien Respectueusement
    www.hourim.wordpress.com

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

  18. #18
    Membre du Club
    @mnitu

    Merci pour le output en HTML .

    @Mohamed ,

    la vue renvoie en moyenne 1 million 800 mille lignes .

    Je teste la requête avec un
    rownum <=100
    .

    Et je vous fait le feed-back .

    Merci

  19. #19
    Membre émérite
    Salut !

    Ca parait un peu barbare comme procédé...
    C'est un fonctionnement de prod, où tu fais ça pour te créer de la donnée ?

    Peut être qu'exporter d'un côté, charger de l'autre serait plus efficace ?

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  20. #20
    Membre du Club
    Bonjour pacmann ,

    en effet au final c'est avec la prod que je dois faire cette manipulation,
    et je n'ai pas le droit de créer une table (même temporaire) .
    Si vous m'expliquez comment faire le dump d'une vue ... je suis preneur,
    car comme vous le constaterez je fais le lien avec une vue et non avec une table directement .