Optimisation sql suite erreur de type ORA-01555: snapshot too old

Bonjour,

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

Citation:

---

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

---

voici le requete :

Code:

---

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:

---

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

Citation:

---

Envoyé par bstages2000

...
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 ?

Citation:

---

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

Citation:

---

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.

le UNDO_RETENTION peut-être augmenté aussi ;)

- 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.

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

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:

---

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:

---

1 2 3

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

---

Citation:

---

Envoyé par 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)

[

---

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

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

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. :mouarf:

Citation:

---

Envoyé par McM

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

---

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...

Citation:

---

Envoyé par bstages2000

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!

Citation:

---

Envoyé par 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. :mouarf:

---

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:

---

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.

Citation:

---

Envoyé par skuatamad

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:

---

1 2 3

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

---