Discussion :

[dude666]

Bonjour,

Je rencontre une difficulté sur un projet que j'ai pris en main récemment.

Le contexte

Un ordonnanceur lance une procédure stockée A qui lance elle même une centaine de proc stock (appeler Bx par la suite). L’exécution de ces procédures est donc toujours dans la même session Oracle.

Au bout de 3 heures de traitement, une erreur ORA-01652: unable to extend temp segment by 256 in tablespace est remontée. Logique, comme tout se passe dans la même session, l'espace temporaire n'est jamais libéré (si j'ai bien suivi...)

La solution propre serait de faire porter l'appelle de toutes ces procédures par l'ordonnanceur , mais malheureusement ce n'est pas possible pour tout un tas de raison (cout, délai, dispo des equipes ...)


Mon idée de contournement

lancer les procédures Bx dans leurs propres session Oracle, ouverte et clôturer par A (je sais, c'est pas génial ...)

Savez vous si cela est possible dans du code PL/SQL (rien vu sur google pour le moment, cas 1 je cherche mal, cas 2 : ça sent vraiment le caca ) ?

merci d'avance pour votre aide

[mnitu]

...

Au bout de 3 heures de traitement, une erreur ORA-01652: unable to extend temp segment by 256 in tablespace est remontée. Logique, comme tout se passe dans la même session, l'espace temporaire n'est jamais libéré (si j'ai bien suivi...)
...

Ca sent le produit cartésien suite à une jointure mal faite dans une requête SQL. Tracez le traitement pour voir où ça se passe!

[dude666]

Malheureusement non. on a tracé toutes les requetes, et aucune ne fait de produit cartésien.

les traitements les plus consommateurs de l'espace temporaire sont des requêtes du type create table as select, et la réecriture de ces parties n'est pas prévues à court termes

[mnitu]

Et vous avez trouver la requête qui plante ? Autre question vous est en autoextend sur le tablespace Temp et quelle est sa taille ?

[dude666]

oui, la requête qui plante est une toute petite requete (<1000 lignes) avec un utilisation du temp < 1Mo.
Le problème est que ses petites copines appelées avant elle ont bouffé tout le tablespace temp.

Sur les TBS temp, on est en autoextent, avec deux datafiles de 10 go (donc 20 en tout ...)

[mnitu]

A ma compréhension: l'exécution d'une requête a besoin d'espace temporaire pour une raison ou autre; par conséquence au fur et mesure de son exécution elle en prends. Mais une fois fini l'exécution de la requête en question l'espace est libéré. Donc l'espace ne reste pas alloué en attendant la fin de la transaction.

[dude666]

Apparemment, dixit nos DBA, l'espace temporaire utilisé par les requêtes est libéré après la clôture de la session oracle. ce qui explique l'augmentation sans fin de l'espace temporaire utilisé sur notre traitement batch de 8 heures.

Nous avons contourné le problème :
Le programme actuel créer un grand nombre de table avec la commande create table as select. Or, ce que je ne savais pas mais qui est enfin de compte assez logique, c'est que ce type de commande utilise l'espace temporaire pour stocker le résultat de la requête avant la création de la table, d’où une surconsommation même si les requêtes sont performantes.

On a donc juste remplacé ces create table as select par des insert into simple et ca marche beaucoup mieux (et c'est plus propre !)

Merci pour votre aide, je passe le post à résolu.

[mnitu]

Apparemment, dixit nos DBA, l'espace temporaire utilisé par les requêtes est libéré après la clôture de la session oracle. ce qui explique l'augmentation sans fin de l'espace temporaire utilisé sur notre traitement batch de 8 heures.
....

Envoyer votre DBA faire une formation.
Oracle Concepts

Oracle Database allocates temporary segments for queries as needed during a user session and drops them when the query completes.

[mnitu]

...
Le programme actuel créer un grand nombre de table avec la commande create table as select. Or, ce que je ne savais pas mais qui est enfin de compte assez logique, c'est que ce type de commande utilise l'espace temporaire pour stocker le résultat de la requête avant la création de la table, d’où une surconsommation même si les requêtes sont performantes.
...

Donc vous pensez que n'importe quelle instruction de type create table as select "utilise de l'espace temporaire pour stocker le résultat de la requête avant la création de la table..."
Eh bon si vous allez investiguer un peu vous allez finir par vous rendre compte que c'est faux en dépit d'avoir un air "assez logique".