améliorer perf d'une requête

Version imprimable

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

04/04/2011, 18h20
Waldar

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

la variable retour de ma fonction de type clob.
05/04/2011, 09h49
laurentschneider

tu as tenté un BULK COLLECT ?

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

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

05/04/2011, 11h22
ouinih

je ne comprends pas...
05/04/2011, 11h32
ojo77

Pouvez vous lancez la fonction avec une trace 10046 afin d'analyser ou le tempos est perdu et poster le tkprof ?
05/04/2011, 19h52
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/