Discussion :

[bstages2000]

Bonjour,

J'ai un soucis avec quelques requête sql qui se plante au bout d'un moment.
voici l'erreur qui est sorti :

ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small :

voici le requete :

Code :

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

select * from COMPTES_PRESENT where cpt_id in (select cpt_id from COMPTES_PRESENT where cpt_id not in (select distinct cpt_id from COMPTES));

Questions
1 - je pense que la requête ci-dessus n'est pas optimale ? on peut l'optimiser avec celle ci :

Code :

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

select cpt_id from COMPTES_PRESENT where cpt_id not in (select distinct cpt_id from COMPTES) ;

2- On m'a parlé de l'utilisation de curseur pour regler ce probleme, mais comment faire ?

3 - y'a til un parametrage spécific oracle à modifier afin de regler ce probleme, je pense au rollback segment et à undo_retention ?

Merci de m'aider

[mnitu]

...
1 - je pense que la requête ci-dessus n'est pas optimale ? on peut l'optimiser avec celle ci :

select cpt_id from COMPTES_PRESENT where cpt_id not in (select distinct cpt_id from COMPTES) ;

Oui. Est-ce que il y a un index sur cpt_id dans la table comptes ?

2- On m'a parlé de l'utilisation de curseur pour regler ce probleme, mais comment faire ?

Probablement pour travailler par lot. Peut être c’est une solution peut être non. Ca dépende de ce que l’application fait donc, il faut plus de détails

3 - y'a til un parametrage spécific oracle à modifier afin de regler ce probleme, je pense au rollback segment et à undo_retention ?

...

La taille des segments de rollback peut être augmenté, etc. Il se peut que vous en ayez plusieurs mais qu’ils ne soient pas de la même taille, etc. Il vous faut travailler avec votre DBA.
Mais avant de la faire il faut comprendre le traitement etc.

[orafrance]

le UNDO_RETENTION peut-être augmenté aussi

[bstages2000]

- Il ya bien un index sur le champ cpt_id dans la table compte et compte_present.

ce script est appélé par l'intermediaire d'une tache $U qui se fait à la demande, donc pas de traitement par lot.

le but du script est de vérifier qu'il nyait pas d'incohérence entre la table compte_present et compte, auquel cas, les incohérences sont supprimées.

je ne sais pas de quelle façon je peux optimiser cette requête afin de ne plus avoir des erreurs.

[Pomalaix]

Bonjour

Comme on a eu l'occasion de l'expliquer à diverses reprises, l'erreur ORA-01555 indique que des données d'annulation, nécessaires pour fournir un résultat consistant à votre SELECT de longue durée, ont été supprimées du segment d'annulation parce que de nouvelles transactions avaient besoin d'espace.

Les pistes de solution sont les suivantes :
- améliorer la requête pour qu'elle s'exécute plus rapidement, et soit donc moins sujette à ce phénomène
- agrandir le tablespace UNDO, de sorte que l'espace n'y soit pas réclamé trop rapidement
- augmenter le paramètre UNDO_RETENTION, mais comme cette valeur n'est pas garantie, ça risque de ne servir à rien
- ne pas faire plus de COMMIT que la logique de vos transactions applicatives ne l'exige, car ce sont les COMMIT qui rendent libérables les entrées UNDO dont votre SELECT a pourtant besoin

[McM]

C'est pas du tout optimisé en effet !
Le premier select ne sert à rien, et pour le troisième, mieux vaut préférer le NOT EXISTS (en règle générale)

Code :

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

1
2
3
4
5
6
SELECT * 
FROM COMPTES_PRESENT 
WHERE cpt_id IN (	SELECT cpt_id 
			FROM COMPTES_PRESENT 
                  WHERE cpt_id NOT IN (SELECT DISTINCT cpt_id FROM COMPTES)
                  )

=>

Code :

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

1
2
3
SELECT * 
FROM COMPTES_PRESENT a
WHERE NOT EXISTS (SELECT 1 FROM COMPTES b WHERE a.cpt_id = b.cpt_id)

[fatsora]

C'est pas du tout optimisé en effet !
Le premier select ne sert à rien, et pour le troisième, mieux vaut préférer le NOT EXISTS (en règle générale)

[

Ceci est en "regle générale une idée recue"

Il faut voir a l'autotrace, explain plan ...

[McM]

Je savais que j'allais me prendre des réponses malgré le "en règle générale".
Et bien soit.. je ne me fie qu'à mon expérience, et en règle générale sur mes bases plus de 80% des requêtes NOT IN sont optimisées en NOT EXISTS.

De toute façon, un NOT IN (select DISTINCT, ben moi j'aime pas du tout.

[skuatamad]

De toute façon, un NOT IN (select DISTINCT, ben moi j'aime pas du tout.

Surtout si la colonne qui suit le DISTINCT n'est pas NOT NULL, et que dans la sous requete le IS NOT NULL n'est pas code.

Dans le cas present on peut supposer que cpt_id est la PK de la table compte, donc pas de soucis avec les nulls, mais a quoi peut bien servir le DISTINCT...

le but du script est de vérifier qu'il nyait pas d'incohérence entre la table compte_present et compte, auquel cas, les incohérences sont supprimées.

C'est quoi une incoherence? Pour moi ca ressemble a une FK.

PS:desole pour les accents : qwerty power!

[NGasparotto]

Je savais que j'allais me prendre des réponses malgré le "en règle générale".
Et bien soit.. je ne me fie qu'à mon expérience, et en règle générale sur mes bases plus de 80% des requêtes NOT IN sont optimisées en NOT EXISTS.

De toute façon, un NOT IN (select DISTINCT, ben moi j'aime pas du tout.

Ah bon ? Et que fait tu des cas ou le resultat est different ? Tu "optimises" aussi ?

Exemple avec la fameuse et hyper connue table EMP. Obtenir le nombre de empno qui ne sont pas manager.
NOT IN ? NOT EXISTS ?

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
SQL> select empno,ename,mgr from emp;
 
     EMPNO ENAME             MGR
---------- ---------- ----------
      7369 SMITH            7902
      7499 ALLEN            7698
      7521 WARD             7698
      7566 JONES            7839
      7654 MARTIN           7698
      7698 BLAKE            7839
      7782 CLARK            7839
      7788 SCOTT            7566
      7839 KING
      7844 TURNER           7698
      7876 ADAMS            7788
      7900 JAMES            7698
      7902 FORD             7566
      7934 MILLER           7782
 
14 rows selected.
 
SQL> select count(*) from emp where empno not in (select mgr from emp);
 
  COUNT(*)
----------
         0
 
SQL> select count(*) from emp e1 where not exists (select null from emp e2 where e1.empno=e2.mgr);
 
  COUNT(*)
----------
         8
 
SQL>

Il s'agit juste d'un exemple pour dire que la transformation de l'un vers l'autre ne doit pas etre si systematique que tu le laisses entendre.

Nicolas.

[bstages2000]

Surtout si la colonne qui suit le DISTINCT n'est pas NOT NULL, et que dans la sous requete le IS NOT NULL n'est pas code.

Dans le cas present on peut supposer que cpt_id est la PK de la table compte, donc pas de soucis avec les nulls, mais a quoi peut bien servir le DISTINCT...


C'est quoi une incoherence? Pour moi ca ressemble a une FK.

PS:desole pour les accents : qwerty power!

l'incohérence c'est le fait d'avoir des enregistrement présent dans la table COMPTES_PRESENT et qui ne serait pas présent dans la table COMPTES.
le but étant de supprimer ces enregistrement.

je vais donc partir de la solution suivante,

Code sql :

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

1
2
3
 SELECT * 
FROM COMPTES_PRESENT a
WHERE NOT EXISTS (SELECT 1 FROM COMPTES b WHERE a.cpt_id = b.cpt_id)