Discussion :

[Zidmann]

Bonjour à vous tous,

Je vous écris car je débute actuellement sous une base de données Oracle. Je n'ai pas beaucoup d'expérience et j'aurais besoin de vos conseils.
Ceci est la toute première fois que j'écris un message pour demander de l'aide sur ce forum que je connais depuis peu, alors j'espère ne pas avoir fait d'erreur qui pourrait vous énerver.

Je travaille sous une base de données Oracle 10g et j'essaye d'accéler une procédure PL/SQL dans laquelle 3 requêtes SQL sont particulièrement gourmandes en terme de temps.

Concrètement, je crée une table temporaire tabA qui sera par la suite insérée dans deux autres tables tabB(totalement) et tabC(partiellement).
(1) Création de tabA : elle fait autour de 10 millions de lignes il me semble (après avoir été remplie) et ne possède ni index, ni partition. Il y a en revanche des conditions sur certaines colonnes pour que la valeur contenue soit non NULL
(2) Insertion de toutes les lignes de tabA dans tabB sans exception
(3) Insertion des lignes de tabA dans tabC selon une condition (WHERE) portant sur la valeur numérique d'un champ : la table tabA dispose de plusieurs champs dont un dénommé DOCUMENT_TYPE pouvant valoir 4 ou 6. Si la valeur est 4 la ligne est insérée dans tabB; sinon non.

Les tables tabB et tabC sont des tables déjà partitionnées sur d'autres champs et je n'ai pas le droit de modifier cette structure. Ces deux tables possèdent plusieurs clés secondaires et plusieurs indexs.

J'ai lu quelques posts et quelques articles. J'ai essayé d'utiliser un index B-Tree sur le champ DOCUMENT_TYPE mais je n'ai noté aucune accélération notable. J'en ai profité pour tester certaines méthodes pour insérer dans deux tables à la fois en une requête mais je n'ai pas eu de meilleurs temps d'exécution; la durée a d'ailleurs dans certains cas augmentée.


Avez-vous des pistes à me proposer pour pouvoir accélerer la durée totale d'exécution de ces 3 requêtes.

Merci d'avance de vos réponses.

[mnitu]

Il y a peu des détailles sur ce que vous faite en réalité et où ça coince pour pouvoir données des bons conseils.
Comme ça avez-vous déjà essayez le multi-table insert ?

[Zidmann]

Bonjour,

Je vous fait parvenir plus d'informations sur la structure des tables et sur ce que j'ai essayé de faire.
Je suis désolé si les noms ne sont pas explicites mais pour des raisons de confidentialité, j'ai été obligé de renommer les éléments.

En pièce jointe vous pourrez trouver en détail dans le fichier "InformationsTables.xls" les informations sur la structure des tables avec les champs, les indexs et le partitionnement dans le cas où cela vous serait utile.
Les 3 requêtes vous sont présentées dans le fichier "Requêtes.sql".

Pour accélérer la durée totale j'ai essayé :

- d'utiliser le INSERT ALL multiple TABLES mais le temps d'exécution était plus grand que celui obtenu par la somme du temps des deux requêtes.
De mémoire j'ai réalisé un :

Code :

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

1
2
3
4
5
6
INSERT ALL 
WHEN (DOCUMENT_TYPE=4) THEN INTO tabB (<liste de champs>)
WHEN 1=1 THEN INTO tabC (<liste de champs>)
         SELECT /*PARALLEL(TMP,4)*/
                    <liste de champs>
         FROM tabA TMP

- d'utiliser un hint append sur les deux requêtes d'insertion et sur la requête avec le INSERT ALL pour voir si l'insertion pouvait se passer plus vite. Mais à la place je n'ai pas eu de gain de temps parfois même j'ai eu un ralentissement.

[mnitu]

Et combien de temps dure le traitement ?
Pourriez-vous faire une trace SQL pour voir où le temps passe ?
Entre la création de la table temporaire et le chargement des autres tables il y a des autres traitements, requêtes qui sont exécutées ?

[Zidmann]

Bonjour,

Comme vous me l'avez demandé, je vous fais parvenir les traces d'exécution SQL de mes requêtes.
Veuillez m'excuser si je réponds aussi tardivement mais du fait du mercredi et jeudi fériés puis du pont, je n'ai pas pu obtenir ces informations plus tôt.

Pour vous répondre mnitu, il ya des requêtes entre la création de la table table1 et de la table2 qui utilisent table1 dans des opérations où l'on n'intéragit avec la table1 qu'avec des opérations de lecture (SELECT).
Il n'y a en revanche pas de requêtes entre la création de la table 2 et de la table3.

Merci d'avance de vos réponses et de vos commentaires.

Zidmann

[mnitu]

Votre principal événement d’attente est "db file sequential read", d’habitude associé à une lecture via l’index ce qui apparemment n’est pas votre cas. Prenez la première requête chercher les lignes concernant cet événement dans le fichier brut des traces et chercher les 3 paramètres p1, p2 et p3. A partir de leur valeur identifiez l’objet qui est concerné par ces lectures.

[Mohamed.Houri]

Vous insérez 3 millions de lignes en faisant 41,5 millions de lectures logiques (logical I/O). Ce qui veut dire 41,5/3 = 13 lectures logiques par insertion.

La différence entre Elapsed et CPU est de 1021 secondes représentant le temps d'attente de votre insert. Une bonne coïncidence avec le temps d'attente db file sequential read de 1027 secondes

db file sequential read concerne normalement des accès via index. Mais votre plan d'exécution ne montre que des full table scan. Il y a donc de fortes chances que ces db file sequential read (qui sont en fait des physical I/O suivis par des logical I/O) proviennent de l'undo tablespace afin de garantir une lecture consistante (read consistency).

Est-ce que la table TABLE1 est modifiée lorsque vous êtes en train d'insérer dans la table TABLE2?

[Zidmann]

Bonjour Mohamed.Houri,

Comment me conseillez-vous de m'y prendre exactement pour vérifier si la table TABLE1 est modifiée pendant l'insertion dans la table TABLE2 ?

[mnitu]

Y a-t-il des indexes sur la table TABLE 2 ?

[mnitu]

Je pense que ce n’est pas la lecture consistent mais la maintenance des indexes qui plombe les perfs.

[Zidmann]

Bonjour mnitu,

Oui, il y a des indexs sur la table TABLE2 et dans TABLE3 aussi, comme celà est indiqué dans le document "InformationsTables.xls".

[Zidmann]

Bonjour mnitu,

Dans ce cas, me conseilleriez-vous de désactiver les indexes avant l'insertion et de les reconstruire après l'insertion ?

Je voulais aussi vous demander si vous ne pensez pas si le partitionnement des tables peut aussi y être pour quelque chose.

[mnitu]

Pour l'instant ajoutez le hint /*+ APPEND */ pour faire un test en direct path load.

Code :

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

1
2
 
INSERT /*+ APPEND */ INTO TABLE2 ...

[Zidmann]

Bonjour,

Je n'ai actuellement pas accès à la bdd avec les trois tables mais je sais que j'avais déjà essayé d'utiliser le hint Append.

Durant mon test : j'avais crée une boucle de 3 itérations dans laquelle j'alternais les insertions avec et puis sans hint Append pour voir combien de temps chaque requête prenait de temps pour s'exécuter.. Entre deux insertions je réalisais bien sur un ROLLBACK.

Il est apparu qu'il n'y avait aucun gain.
Les durées étaient semblables à quelques secondes près

[mnitu]

Essayez alors de supprimer les indexes et les récréer après le chargement des tables.

[Zidmann]

Bonjour,

Je vais désactiver les indexes (DISABLE), faire l'insertion et reconstruire les indexs (REBUILD) plutôt. Je n'aurais les résultats que demain en revanche. Je vous les communiquerai dans un fichier Excel sur ce forum.

Merci d'avoir jeté un coup d'oeil à mon problème et à mes derniers posts.

[mnitu]

...
Je vais désactiver les indexes (DISABLE)...

Ça pourrait s'avérer un poil compliqué:

DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.

Mais il y en a unusable.

[Mohamed.Houri]

@Zidmann
Le hint /*+ append */ ne sert à rien si votre table destination possède des triggers ou des contraintes d'intégrités (FK). Il sera silencieusement ignoré.
Si vous partez de tables vides il est intéressant de mettre les indexes dans un statut 'unusable' et les reconstruire par la suite. Mais si le volume à insérer et très petit par rapport au volume de données existant, cela ne sert à rien de "disabler" vos indexes car vous allez perdre du temps lors de leur reconstruction.

Si vos tables sont par contre partitionnées, pensez alors à ''disabler'' uniquement les partitions des indexes locaux correspondant aux partitions de la table que vous allez toucher par votre insert. Ainsi, vous allez reconstruire uniquement les partitions touchées. Ce qui n’est pas le cas des indexes globaux.
Dans votre fichier trace je n'ai vu aucune instruction insert. Est-ce que vous êtes sûr que ce fichier correspond à un insert/select ou uniquement à la partie select.

[Zidmann]

Bonjour Mohamed.Houri,

Le problème justement c'est que je ne pars pas avec des tables d'arrivée vides.
TABLE 2 et TABLE3 sont très lourdes. TABLE3 sert d'ailleurs de table d'archivage qui contient l'historique de certaines informations. La seule table vide est la table que je créé : TABLE1.

Pour savoir le rapport entre le volume inséré et le volume total, il faudra attendre demain pour obtenir cette information.

Le partitionnement se fait sur un critère qui n'est pas un critère bateau comme la date, le mois, l'année, une couleur enfin des trucs de ce genre.
Lors de l'insertion, je pense que beaucoup de partitions sont concernées. Il faudra que je recherche cette information également.

Autre détail, il ne me semble pas que les indexes ne sont pas locaux mais tous globaux.

Pour ce qui est du fichier trace, je peux vous assurer qu'il s'agit bien de requêtes de la forme insert/select mais je vais quand même jetter un coup d'oeil demain pour en être sur.

[Zidmann]

Bonjour,

J'ai regardé combien de lignes étaient insérées et combien de partitions étaient touchées. Voici mes résultats :

Table2:
Nb de partitions sollicitées 3826
Nb de lignes insérées 3140868
Nb de lignes après insertion 43502548
Proportion des lignes insérées (en %) 7,219963

Table3:
Nb de partitions sollicitées 3553
Nb de lignes insérées 1567187
Nb de lignes après insertion 20038390
Proportion des lignes insérées (en %) 7,820923

Est-ce que ceci vaux le coût selon vous de détruire les indexes et de les re-créer par la suite ?

Merci d'avance de vos réponses.