Oracle Optimiser une requête en ressources - Page 1

Vous n'aurez aucun mal à vous en rendre compte en lisant la suite, mais j'annonce quand même la couleur : je suis une quiche en Oracle ^^

Pour alimenter un outil de reporting, j'ai besoin de réaliser des extractions régulières depuis une base Oracle, en prenant les objets qui répondent à certains critères (entre autres : qui ont été modifiés au cours des X derniers jours, X étant généralement égal à 3). L'instance Oracle se trouve sur un serveur en production sur lequel je ne suis même pas censé installer un script crontabé, donc mon but est de minimiser les traitements à ce niveau.

J'ai donc un .sh qui balance ma requête à Oracle (via sqlplus, le client en ligne de commande par défaut), récupère le résultat brut dans un fichier et l'envoie par SCP vers un serveur sur lequel je peux faire ce que je veux. Je cherche à obtenir un fichier dont chaque ligne est au format suivant :

champ1=valeur1;champ2=valeur2;champ3=valeur3;[...];

Du coup ma requête ressemble à ça :

CREATE OR REPLACE FUNCTION escape(val IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
	RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(val, '#', '#25'), '=', '#3D'), ';', '#3B'), CHR(13), '#0D'), CHR(10), '#0A');
END;

/

CREATE OR REPLACE FUNCTION getField(key IN VARCHAR2, val IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
	IF val IS NULL THEN
		RETURN '';
	ELSE
		RETURN escape (key) || '=' || escape (val) || ';';
	END IF;
END;

/

SELECT
	getField ('champ1', champ1) ||
	getField ('champ2', champ2) ||
	getField ('champ3', champ3) ||
	[...]
FROM
	matable
WHERE
	mescritères;

La fonction "escape" me permet d'échapper les caractères ";", "=" et les retours à la ligne puisqu'ils ont une signification spéciale dans mon format de sortie. Ça marche plutôt pas mal, tant que la requête ne remonte pas trop de résultats. Mais de temps en temps j'ai besoin de faire une extraction totale de la base, et ça coince au niveau de la mémoire ("ORA-01652: unable to extend temp segment by 256 in tablespace TEMP").

Je pense que mes fonctions getField et escape, en plus du fait de concaténer toutes les chaines du résultat, provoquent une consommation de mémoire non négligeable. En plus certains champs sont des textes potentiellement très longs, donc j'ai du faire des versions de getField et escape qui utilisent le type "CLOB" au lieu de "VARCHAR2", et ça doit être encore pire au niveau mémoire.

Ma seule solution pour l'instant serait de découper la requête en plusieurs, en sélectionnant plusieurs fenêtres de temps, mais c'est un contournement pas très élégant. Y aurait-il une autre façon de m'y prendre qui bouffe moins de ressources ?

Quiche pawaaa

DECLARE PROCEDURE myproc IS 
CURSORS cur IS SELECT champ1, champ2, champ3 FROM matable WHERE moncul IS NOT NULL;
BEGIN

FOR row_cur IN cur LOOP

dbms_output.put_line('champ1=' || escape(row_cur.champ1) ||';'|| 'champ1=' || escape(row_cur.champ2) ||';'|| 'champ3=' || escape(row_cur.champ3) ||';');

END LOOP;

END ;

/

SQLPLUS> set serveroutput on
SQLPLUS> set feedback off
SQLPLUS> spool /tmp/proot.tmp
SQLPLUS> exec myproc
SQLPLUS> spool off

Ca devrait marcher sans consommer de memoire excessivement, enfin teste par toi meme.

y'a un problème avec dbms_output.put_line : je viens de voir sur le net que c'était limité à 255 caractères par ligne (j'en ai jusqu'à plusieurs dizaines de milliers).

sinon le fait d'utiliser un curseur ça améliore vraiment les perf ? juste par curiosité, tu sais pourquoi ?

parce qu'il fait pas de copie dans des tables temporaires mais cherche dynamiquement l'enregistrement suivant? (il doit faire juste un index kivabien, chais pas)

parce qu'un curseur est stocke dans la SGA et non dans la TEMP tablespace.

Pour la limitation de dbms_output.put_line, il va falloir chercher ailleurs en effet...

y'a aucun autre moyen pour "afficher" le contenu d'un curseur ? parceque sinon à part ça je viens de tester et effectivement ça a l'air de bien marcher

hmmm je suis pas un expert en developement pl/sql, mais cherche un peu sur le net, tu devrais trouver des documentations sur l'utilisations des curseurs comme par exemple http://ss64.com/oraplsql/

ca me fait penser que tu devrais penser a fermer ton curseur a la fin de la procedure, sinon c'est une autre erreur ORA que tu risques de rencontrer

(desole google me renvoit par defaut les pages de docs oracle en jap et j'ai la flemme d'aller chercher en anglais )

Pas besoin de close : le cuseur est utilisé dans un bloc local, donc il est fermé automatiquement.

Sinon pour l'instant, chou blanc pour afficher le curseur. D'après ce que j'ai lu il est impossible de modifier la limite de 255 caractères, et je n'ai pas les droits sur UTL_FILE qui m'aurais permis d'écrire mon résultat dans un fichier :/

Quand on exécute un simple "SELECT", il est par défaut envoyé vers la sortie standard, il n'y a pas moyen de copier ce comportement pour un curseur ?

hmm jete un oeil la dessus sinon : http://www.psoug.org/reference/dbms_lob.html

J'ai l'impression que ça propose des fonctions pour manipuler les {C,B}LOB, mais pas que ça propose de solution pour les afficher :/

Si tu as vraiment besoin de ces donnees pour le taff, demande a qu'on te donne des droits sur UTL_FILE, parce que je vois pas vraiment d'autre solution, a part cracher les donnees brutes avec spool et les manipuler apres avec sh/awk/sed...

ok, merci pour les conseils

Sinon dans le lien suivant ils proposent une astuce pour afficher plus de 255 caracteres :

http://hubpages.com/hub/Over-come-the-255-Charater-Limit-of-DBMS_OUTPUT

Par contre, evidemment, ca split en plusieurs lignes donc il te faudrait jeter ton resultat dans un temp file puis faire un sed "s/\n//g" pour supprimer les retours de ligne et enfin faire un append a ton log courant.

oui je suis tombé sur une solution comme ça, le souci c'est que reconstituer les lignes n'est pas évident, sachant que je n'ai rien pour déterminer où commence et termine une ligne (ceci dit je pourrais ajouter un tag au début et un tag à la fin, mais ça commence à faire vraiment bricolage :/)

ah ? tu supprimais pas deja les retours de ligne avec la function escape ?

si justement, je supprime les retours à la ligne dans les valeurs des champs pour être sûr que chaque enregistrement tienne exactement sur une ligne

bricolage : tu peux estimer que "champ1=" sera forcement en debut de ligne

non : en fait je n'affiche pas les champs dont la valeur est NULL, donc une ligne ne commence pas forcément par "champ1" ^^

Tu ajoutes un code de 512 caractères comme signal de fin de ligne comme ça y a moins de chances de te planter

Pour ta fonction escape, t'aurait intérêt à utiliser REGEXP_REPLACE et tout faire (tes cinq replace) en une fois

liquid (./2) :
set serveroutput on

set serveroutput on size 1000000

(et tu peux mettre une valeur de buffer plus grande si tu es en oracle 10g (ou 10g release2, je ne sais plus))

je peux pas trop utiliser regexp_replace, vu qu'il y a plusieurs chaines de remplacements et que cette fonction n'a pas l'air de le gérer :/

vince (./20) :
(et tu peux mettre une valeur de buffer plus grande si tu es en oracle 10g (ou 10g release2, je ne sais plus))

10g release 2

Au fait, pour information j'ai fini par obtenir les droits sur UTL_FILE donc j'en ai profité pour comparer les perfs sur une même requête écrite de deux façons différentes :

- select simple et récupération directe sur stdout : 1 heure 46 min 38 sec
- select avec curseur et écriture via UTL_FILE : 4 min 45 sec

C'est sans comparaison

(par contre même UTL_FILE a une limite de 32767 caractères par ligne qui me restreint un peu.. c'est gonflant ces limitations partout, surtout à des tailles aussi faibles :/)

L'écart est effectivement énorme. Mais il n'y a pas moyen d'optimiser l'écriture sur stdout en contrôlant le flush du flux au mieux ? Tu n'aurais plus la limitation de taille à ce moment là. Il en était question il y a quelques mois dans un topic de Thibaut (qui gueulait sur la ilbc je crois). Les écarts paraissaient énormes aussi, à moins que ce soient carrément les accès bdd qui fassent la différence.

Et juste par curiosité, c'est le fait de ne pas passer par stdout, ou le fait d'utiliser un curseur ?

(est-ce que c'est possible d'avoir curseur + stdout ? )

C'est le fait d'utiliser un curseur. Il est possible d'utiliser stdout en même temps qu'un curseur, mais comme expliqué sur ce topic comprendre "il n'est pas en write-only", comme d'hab c'est très limité (255 chars par ligne).

Ok (oui, mais ça n'expliquait pas exactement où était la chute de perf )

Je faisais juste référence à ta question entre parenthèses, qui n'aurait pas été là si tu avais lu le topic avant de poster

Relire les topics sur un iPhone, c'est parfois un peu chiant