dbms_stats en dynamique

Version imprimable

Bonjour,
J'alimente un dataware house composé de plusieurs tables partitionnées. tous les jours quelques partitions sont tronquées et réalimentées.
Pour tout automatiser j'ai l'intention d'analyser ces partitions de façon dynamique dans une procédure.
Voici le code que j'utilise et qui helas ne marche pas.

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
35
36
37
38
CREATE OR REPLACE PROCEDURE "prc_AnalyseTablePartition" (pi_table_owner varchar2,
pi_cnom_table varchar2 , pi_cyear varchar2) is
 
CURSOR C_PARAM (p_table_owner varchar2,p_cnom_table varchar2 , p_cyear varchar2) is
select distinct('dbms_stats.GATHER_TABLE_STATS(OWNNAME => ''' || p_table_owner || ''',TABNAME => ''' || ai.table_name ||''',PARTNAME =>  ''' || partition_name || ''' , CASCADE => FALSE, METHOD_OPT => ''FOR ALL INDEXED COLUMNS'', GRANULARITY => ''PARTITION'');') cleTexte
from all_indexes ai,all_tab_partitions atp
where atp.table_name = p_cnom_table
and atp.table_owner = p_table_owner
and substr(partition_name,5,4) >= p_cyear
and atp.table_name = ai.table_name
and atp.table_owner = ai.table_owner
;
 
 
L_PARAM	C_PARAM%ROWTYPE;
 
v_select varchar2(3000);  
cursor1  integer;
v_text   varchar2(300);
 
begin
Open C_PARAM(pi_table_owner,pi_cnom_table, pi_cyear);
   While (1=1)
   Loop
     Fetch C_PARAM into L_PARAM;
     If (C_PARAM%FOUND)
        Then
	begin
	  cursor1 := dbms_sql.open_cursor;
	  dbms_sql.parse(cursor1, l_param.cleTexte, dbms_sql.native);
	  dbms_sql.close_cursor(cursor1); 	 
             End;		
         else
	exit;
         end if;		
    End Loop;	
    close C_PARAM;
end;

07/12/2006, 10h33
orafrance

Citation:

Envoyé par jj bzh

qui helas ne marche pas.

c'est vague...

à l'execution de cette procédure j'ai ces erreurs :

Code:

1
2
3
4
5
6
 
ORA-00928: missing SELECT keyword
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "DEVDW.prc_AnalyseTablePartition", line 30
ORA-06512: at line 11

07/12/2006, 10h53
orafrance

déjà le curseur me parait complétement dingue... tu boucles jusqu'à ne plus avoir de ligne... je te conseille de lire ce tuto : http://sheikyerbouti.developpez.com/...=Chap1#L1.2.13

et au lieu de DBMS_SQL, fait donc un EXECUTE IMMEDIATE : http://sheikyerbouti.developpez.com/...e=Chap1#L1.2.6
07/12/2006, 11h34
jj bzh
Citation:

déjà le curseur me parait complétement dingue...

c'est pas la boucle la + optimisé que je connaisse mais c'est mon dba qui l'a pondu ...
Dans le lien le cout de la boucle est de x et dans ma boucle c'est x+1. Ma boucle est donc + couteuse, mais c'est pas dramatique.

pour revenir à mon problème :
Quand j'execute le select du curseur sous SQL plus , j'obtient :
Code:

1 2 3 4 dbms_stats.GATHER_TABLE_STATS(OWNNAME => 'DEVDW', TABNAME => 'VENTE_SITE_DEPT',PARTNAME => 'PART200401' , CASCADE => FALSE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS', GRANULARITY => 'PARTITION');
Quand je reprend ce résultat et que je le lance toujours sous sql plus, cela fonctionne.

Quand je le fait avec une procedure, cela ne fonctionne plus , ni avec execute immediate ou dbms_sql.
Comment lancer un dbms_stats en dynamique ?
07/12/2006, 11h37
orafrance

Citation:

Envoyé par jj bzh

Dans le lien le cout de la boucle est de x et dans ma boucle c'est x+1. Ma boucle est donc + couteuse, mais c'est pas dramatique.

C'est pas un problème de perf mais de bonne pratique :?

Citation:

Envoyé par jj bzh

Quand je le fait avec une procedure, cela ne fonctionne plus , ni avec execute immediate ou dbms_sql.

il ne faut pas mettre le ; ;)

ton DBA a l'air d'être un champion du dév :mouarf:

EUREKA ! j'ai trouvé la solution était super simple (comme toujours qd on a trouvé !) :D

dbms_stats n'a pas besoin de execute immediate ou de dbms_sql pour etre lancé. on lui passe des parametres et puis c'est tout !
ce qui donne :

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
35
36
37
38
39
40
41
 
CREATE OR REPLACE PROCEDURE "prc_AnalyseTablePartition" (pi_table_owner varchar2,
pi_cnom_table varchar2 , pi_cyear varchar2) is
 
 
CURSOR C_PARAM (p_table_owner varchar2,p_cnom_table varchar2 , p_cyear varchar2) is
select table_owner , table_name ,partition_name
from all_tab_partitions
where table_name = p_cnom_table
and table_owner = p_table_owner
and substr(partition_name,5,4) >= p_cyear
;
 
 
L_PARAM	 C_PARAM%ROWTYPE;
 
v_select varchar2(3000);  
cursor1  integer;
v_text   varchar2(300);
 
begin
 Open C_PARAM(pi_table_owner,pi_cnom_table, pi_cyear);
 While (1=1)
  Loop
   Fetch C_PARAM into L_PARAM;
    If (C_PARAM%FOUND)
    Then
     begin			
      dbms_stats.gather_table_stats (OWNNAME => L_PARAM.table_owner,
      TABNAME => L_PARAM.table_name,
      PARTNAME =>  l_param.partition_name ,
      CASCADE => FALSE, 
      METHOD_OPT => 'FOR ALL INDEXED COLUMNS', 
      GRANULARITY => 'PARTITION');
     End;		
   else
    exit;
   end if;		
  End Loop;	
close C_PARAM;
end;