améliorer perf d'une requête

[ouinih]

Bonjour,

je travaille avec oracle 10g. j'ai des pb de perf sur une requête.
Voici ce que je fais :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
OPEN c_curseur FOR SELECT DISTINCT 
                         r.chp1 ||
                         ';'            ||
                         r.chp2         ||
                         ';'                
                    FROM tab1 r
                    WHERE chp1= 'toto';
 
LOOP 
        FETCH c_curseur
        INTO v_ligne;
        EXIT WHEN c_curseur%NOTFOUND;
 
        val_return := val_return || v_ligne || chr(10);
 
 
 END LOOP;

Le Select dure qq secondes mais la boucle dure vers les 25 min pour 7000 lignes traitées.

Comment je peux l'optimiser ?

merci

[Waldar]

Quel est l'intérêt de tout mettre dans une seule variable ?
Je veux dire, qu'en faites-vous par la suite ?

[ouinih]

la variable retour de ma fonction de type clob.

[laurentschneider]

tu as tenté un BULK COLLECT ?

[ouinih]

J'ai essayé :

Code :

1
2
3
4
5
6
7
8
9
10
 
LOOP 
        FETCH c_curseur BULK COLLECT
        INTO v_ligne LIMIT 100;
        EXIT WHEN c_curseur%NOTFOUND;
 
        val_return := val_return || v_ligne || chr(10);
 
 
 END LOOP;

mais j'ai le msg d'erreur :

Code :

1
2
 
PLS-00497: impossible de mélanger des clauses mono- et multi-lignes (BULK) da

v_ligne es tde type VARCHAR2(255)
...????

[ojo77]

Il doit manquer quelques infos

J'ai fait le test suivant

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
EXEC SYS.DBMS_RANDOM.SEED(0);
 
CREATE TABLE big_table (c1 NUMBER , c2 NUMBER , c3 VARCHAR2(10), c4 date );
 
INSERT /*+ APPEND */ INTO big_table
SELECT LEVEL c1,
       ROUND(SYS.DBMS_RANDOM.NORMAL) c2,
       SYS.DBMS_RANDOM.STRING('x',10) c3,
       sysdate+ROUND(SYS.DBMS_RANDOM.NORMAL) c4
  FROM DUAL
CONNECT BY LEVEL <= 1e6;
 
COMMIT;
 
 
CREATE OR REPLACE FUNCTION aggr RETURN clob IS
  ttext varchar2(32767);
  ret   clob;
begin
  ret:='col1;col2
';
  FOR c IN (SELECT c1
                 , c2 
                 , c3
            FROM big_table
			WHERE c2=0)
  loop
    ttext:=trim(to_char(c.c1))||';'||trim(to_char(c.c2))||';'||c.c3||';
';
    dbms_lob.writeappend(ret,length(ttext),ttext);
  end loop;
  RETURN ret;
end;
/

J'ai donc une table de 1 million de lignes.

Code :

1
2
3
4
5
6
7
SQL> SELECT count(*) FROM big_table WHERE c2=0;
 
  COUNT(*)
----------
    382462
 
EcoulÚ : 00 :00 :00.10

J'ai 382 462 lignes pour c2=0. Je lance ma fonction avec timing on et autotrace en mode traceonly :

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
SQL> SELECT aggr() FROM dual;
 
EcoulÚ : 00 :00 :10.53
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 1388734953
 
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
 
 
Statistiques
----------------------------------------------------------
       3864  recursive calls
    2314071  db block gets
     390696  consistent gets
          0  physical reads
          0  redo size
       1014  bytes sent via SQL*Net to client
        716  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

J'execute le tout en 10 secondes environ on est loin de 25 minutes pour 17 000 lignes ...

[ouinih]

je ne comprends pas...

[ojo77]

Pouvez vous lancez la fonction avec une trace 10046 afin d'analyser ou le tempos est perdu et poster le tkprof ?

[McM]

C'est la réaffectation du CLOB qui est super longue.
Suivant la taille de tes 2 champs, tu peux concatener dans un VARCHAR2(32768) et tous les X loop, tu concatènes la chaine au CLOB.
Ou faire du dbms_lob.append, tu peux te baser sur ce post : http://www.developpez.net/forums/d10...ess-decriture/