Discussion :

[dngaya]

Bonjour,
Nous avons une procedure stockée qui fonctionnenait il y a quelques jours , actuellement, lors du lancement du traitement, nous avons le message d'erreur ci-dessous sur oracle 10.2.0.3:

ORA-04030: out of process memory when trying to allocate 127000 bytes (QERGH hash-agg,kllcqas:kllsltba)

le traitement se plante sur lorsqu'il s'execute au niveau du bout de code suivant de la procedure stockée:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
 
------------------------------------------------------------------------------------------------
		--
		-- USE-CASE : un utilisateur charge une journée sur l'itération 1 sur une mauvaise ligne BIP
		--
		-- Entre l'itération 1 et l'itération 2, l'utilisateur a corrigé sa timesheet
		--
		-- Pour l'itération 2, le MERGE va insérer la bonne charge sur le bon code BIP
		--
		-- Le SELECT et la boucle FOR..LOOP ci dessous va rechercher dans l'historique
		-- (toute l'année courante SAUF l'itération courante) toutes les combinaisons possibles
		-- la clause NOT EXISTS permet d'enlever toutes les combinaisons de l'itération courante.
		-- Le BULK COLLECT INTO va stocker toutes les données en mémoire
		--
		-- La boucle FOR..LOOP va traiter toutes les combinaisons manquantes pour l'itération courante
		-- Elle va insérer les combinaisons manquantes tout en positionnant les consommés à zéro.
		--
		------------------------------------------------------------------------------------------------
		--
		-- 1. Traitement SELECT => Sauvegarde en mémoire des combinaisons manquantes
 
		dbms_output.put_line(to_char(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') ||
						 ' DEBUT Calcul des combinaisons manquantes...');
 
		SELECT t.bip_row
			 ,t.bip_step_type
			 ,t.bip_sub_task_type
			 ,t.bip_step_task_sub_task
			 ,t.bip_resource_id
			 ,t.bip_activity
			 ,t.bip_month --
		BULK COLLECT
		INTO   lst_bip_actuals_test
		FROM   sg_gims_bip_actuals_test t
		WHERE  NOT (t.act_year = n_year AND t.act_month = n_month AND
			   t.act_iter = n_iter)
		AND    NOT EXISTS
		 (SELECT s.bip_row
				    ,s.bip_step_type
				    ,s.bip_sub_task_type
				    ,s.bip_step_task_sub_task
				    ,s.bip_resource_id
				    ,s.bip_activity
				    ,s.bip_month
			   FROM   sg_gims_bip_actuals_test s
			   WHERE  s.act_year = n_year
			   AND    s.act_month = n_month
			   AND    s.act_iter = n_iter
			   AND    t.bip_row = s.bip_row
			   AND    t.bip_step_type = s.bip_step_type
			   AND    nvl(t.bip_sub_task_type, '#@#NULL#@#') =
					nvl(s.bip_sub_task_type, '#@#NULL#@#')
			   AND    t.bip_step_task_sub_task = s.bip_step_task_sub_task
			   AND    t.bip_resource_id = s.bip_resource_id
			   AND    t.bip_activity = s.bip_activity
			   AND    t.bip_month = s.bip_month
			   GROUP  BY s.bip_row
					  ,s.bip_step_type
					  ,s.bip_sub_task_type
					  ,s.bip_step_task_sub_task
					  ,s.bip_resource_id
					  ,s.bip_activity
					  ,s.bip_month)
		GROUP  BY t.bip_row
			    ,t.bip_step_type
			    ,t.bip_sub_task_type
			    ,t.bip_step_task_sub_task
			    ,t.bip_resource_id
			    ,t.bip_activity
			    ,t.bip_month;
		--
		n_sql_num := 150;
		-- Les données sont stockées dans la table mémoire lst_bip_actuals_test
		IF lst_bip_actuals_test.FIRST IS NOT NULL THEN
			-- Balayage de la table mémoire
			dbms_output.put_line(to_char(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') ||
							 ' ---- Combinaisons manquantes identifiées...');
			dbms_output.put_line(to_char(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') ||
							 ' ---- Il y a ' || lst_bip_actuals_test.COUNT ||
							 'combinaisons manquantes identifiées...');
			FOR i IN lst_bip_actuals_test.FIRST .. lst_bip_actuals_test.LAST LOOP
				/* -- DEBUG
                    dbms_output.put_line(i);
                    dbms_output.put('bip_row = ' || lst_bip_actuals_test(i).bip_row);
                    dbms_output.put(' bip_step_type = ' || lst_bip_actuals_test(i).bip_step_type);
                    dbms_output.put(' bip_sub_task_type = ' || lst_bip_actuals_test(i).bip_sub_task_type);
                    dbms_output.put(' bip_step_task_sub_task = ' || lst_bip_actuals_test(i).bip_step_task_sub_task);
                    dbms_output.put_line(' bip_resource_id = ' || lst_bip_actuals_test(i).bip_resource_id);
                    -- DEBUG */
				n_sql_num := 160;
				--

la table sg_gims_bip_actuals_test n'a que 225457 lignes.

Je constate que dans ce bout de code, le bulk collect n'a pas de limit.
Vu le message d'erreur, nous avons augmenté la pga à 800M et en plus le user qui lance le traitement a les paramètres au niveau system aix:
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 32768
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) 16384

malgré cela ,il y a toujours le message d'erreur.

Pouvez-vous me donner des pistes de reflexions, est ce que ce problème est provoqué par le bulk collect qui si je comprends bien selectionne toutes les lignes de la table et les met en memoire. comment se comporte le bulk collect dans ce cas du fait qu'il n'ya pas de limit ?
Si le problème peut - être resolu en mettant le limit, comment puis-je le faire dans cette sous requête ?

Merci beaucoup.

[Waldar]

Avant d'agir sur le BULK COLLECT, voyez-vous une différence de plan d'exécution avec cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SELECT DISTINCT
       t.bip_row,
       t.bip_step_type,
       t.bip_sub_task_type,
       t.bip_step_task_sub_task,
       t.bip_resource_id,
       t.bip_activity,
       t.bip_month
  FROM sg_gims_bip_actuals_test t
 WHERE t.act_year  <> n_year
    OR t.act_month <> n_month
    OR t.act_iter  <> n_iter
   AND NOT EXISTS
         (SELECT NULL
          FROM   sg_gims_bip_actuals_test s
          WHERE  s.act_year  = n_year
          AND    s.act_month = n_month
          AND    s.act_iter  = n_iter
          AND    t.bip_row = s.bip_row
          AND    t.bip_step_type = s.bip_step_type
          AND    (t.bip_sub_task_type = s.bip_sub_task_type
              OR (t.bip_sub_task_type IS NULL AND s.bip_sub_task_type IS NULL))
          AND    t.bip_step_task_sub_task = s.bip_step_task_sub_task
          AND    t.bip_resource_id = s.bip_resource_id
          AND    t.bip_activity = s.bip_activity
          AND    t.bip_month = s.bip_month);

[mnitu]

...
Je constate que dans ce bout de code, le bulk collect n'a pas de limit.
Vu le message d'erreur, nous avons augmenté la pga à 800M et en plus le user qui lance le traitement a les paramètres au niveau system aix:
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 32768
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) 16384

malgré cela ,il y a toujours le message d'erreur.

Pouvez-vous me donner des pistes de reflexions, est ce que ce problème est provoqué par le bulk collect qui si je comprends bien selectionne toutes les lignes de la table et les met en memoire. comment se comporte le bulk collect dans ce cas du fait qu'il n'ya pas de limit ?
Si le problème peut - être resolu en mettant le limit, comment puis-je le faire dans cette sous requête ?

Merci beaucoup.

A priori il y a pas mal des situations qui peuvent provoquer cette erreur, cherchez sur metalink ou google.
Faite un test simple en mettant une limite dans le select avec rownum < 1000. Si ça passe ré écrivez votre code en utilisant bulk collect avec limit. En général au delà de 5 000 enregistrements la gestion de la mémoire fait que bulk collect n’est plus assez rentable. Donc ça ne sert à rien de charger plus de 200 000 enregistrements en mémoire.