ORA-01652: impossible d'étendre le segment temporaire de 128 dans le tablespace TEMP

**justinedr71** · 08/08/2011, 09h51

Bonjour,

Je travaille sous Talend Open Studio version 4.1.2 avec une base de données Oracle 10g.

J'effectue un job qui me permet dans un premier temps de récupérer tous les différents libellés de garantie présents sur une base client. A part de cet ensemble de libellés, je créé dynamiquement une requête que j’exécute ensuite.

Ma requête à la forme suivante :

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
 
"select pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pas_nb_jours_prorata, pri_type_risque, 
bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, 
bpp_pp_handicape, in_code_etabl, in_type," + globalMap.get("Key1") + "from ( select pol_numpol, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, 
sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, ccp_libelle, gad_prime_nette, 
pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, 
mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type
from f_polices, f_version_police, f_mouvement, f_sit_objet_risque, f_garantie_dyn, f_produitass, f_prd_risque, 
f_sit_assure, f_p_c_assure, f_p_personne, t_sit_maritale, f_intermediaire, f_tarif_ass, t_intercalaire, 
f_tarif_gar, t_cdecpt
where gad_ptrsorid = sor_ident (+)
and sor_ptrpolid = pol_ident (+)
and sor_ident = mvt_ptrsuivantid (+)
and mvt_ptrverid = ver_ident (+)
and pol_ptrpasid = pas_ident (+)
and pas_ident = pri_ptrpasident (+)
and sor_ident = sar_ptrsorid (+)
and sar_ptrassid = bpass_ident (+)
and bpass_ptrbppident = bpp_ident (+)
and bpp_ptrmarid = mar_ident (+) 
and pol_ptrinid = in_ident (+)  
and sor_datetarif = tas_dateffet (+) 
and tas_codeintercalaire = ta_code (+)
and ta_cle = tga_cletarif (+)
and tga_code_compta = ccp_code (+)
and (pol_datresil is null or pol_datresil >= (sysdate - to_yminterval('03-00')))
group by pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, gad_prime_nette, ccp_libelle, 
pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, 
mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type )
group by pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, 
sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pas_nb_jours_prorata, pri_type_risque, 
bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, 
bpp_pp_handicape, in_code_etabl, in_type
order by pol_numpol asc, ver_compteur asc"

Au moment de l'exécution, Key1 contient la valeur :

Code :

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

1
2
 
 decode( ccp_libelle, 'Temporaire décès à PU' , gad_prime_nette, 0 ) || ';' || decode( ccp_libelle, 'Temporaire décès à PP' , gad_prime_nette, 0 ) || ';' || decode( ccp_libelle, 'Rentes à PU' , gad_prime_nette, 0 ) || ';' || decode( ccp_libelle, 'ASSISTANCE - FRAIS MEDICAUX A L ETRANGER' , gad_prime_nette, 0 )

Ici je n'ai mis que 4 libellés, mais bien entendu, la requête est aussi longue que le nombre de libellés présents dans la base. Dans mon cas il y en à un peu moins de 200.

Le problème est qu'à l'exécution, le job bloque un moment sur le tOracleInput, et après un peu plus de 5h d'exécution, me pose l'erreur suivante :

ORA-01652: impossible d'étendre le segment temporaire de 128 dans le tablespace TEMP

Seulement dans ma base, j'ai sur TEMP, deux filename, sur deux disques différents, chacun faisant 32 Gb, en auto extend avec un next extend de 640 Kb ainsi qu'un max size/max blocks unlimited.

Si dans ma requête je place un
"and rownum < 200"
Cela fonctionne, le problème doit donc vraiment venir de la mise en mémoire par le tOracleInput, non? Comment je pourrai faire pour augmenter ce tablespace, sachant que je pensais déjà l'avoir fait mais apparemment ça ne fonctionne pas...

Merci pour votre aide.

Merci pour votre aide.

**McM** · 08/08/2011, 10h12

Le temp est utilisé pour les tris (order by, group by).
Es-tu sur que ta requête est correcte, c'est à dire pas de merge join cartesien ?
Elle me gêne un peu.
Pourquoi faire un group by dans la sous requête, alors qu'il n'y a aucune fonction de groupement (max, count, sum, etc..) ? Généralement, ça cache un problème soit de requête soit de modèle de données.
Dernière chose : Je n'arrive pas à comprendre le decode sur les libellés. La concaténation n'est pas une focntion de groupe.

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
SELECT pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation,
	sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, 
      pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code,
      mar_libelle, bpp_pp_nb_enfants,  bpp_pp_handicape, in_code_etabl, in_type,
 
	DECODE( ccp_libelle, 'Temporaire décès à PU' , gad_prime_nette, 0 ) 
||';'|| DECODE( ccp_libelle, 'Temporaire décès à PP' , gad_prime_nette, 0 ) 
||';'|| DECODE( ccp_libelle, 'Rentes à PU', gad_prime_nette, 0 ) 
||';'|| DECODE( ccp_libelle, 'ASSISTANCE - FRAIS MEDICAUX A L ETRANGER' , gad_prime_nette, 0 )
 
FROM 
( SELECT pol_numpol, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, 
	sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, ccp_libelle, gad_prime_nette, 
	pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, 
	mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type
FROM f_polices, f_version_police, f_mouvement, f_sit_objet_risque, f_garantie_dyn, f_produitass, f_prd_risque, 
      f_sit_assure, f_p_c_assure, f_p_personne, t_sit_maritale, f_intermediaire, f_tarif_ass, t_intercalaire, 
      f_tarif_gar, t_cdecpt
WHERE gad_ptrsorid = sor_ident (+)
AND sor_ptrpolid = pol_ident (+)
AND sor_ident = mvt_ptrsuivantid (+)
AND mvt_ptrverid = ver_ident (+)
AND pol_ptrpasid = pas_ident (+)
AND pas_ident = pri_ptrpasident (+)
AND sor_ident = sar_ptrsorid (+)
AND sar_ptrassid = bpass_ident (+)
AND bpass_ptrbppident = bpp_ident (+)
AND bpp_ptrmarid = mar_ident (+) 
AND pol_ptrinid = in_ident (+)  
AND sor_datetarif = tas_dateffet (+) 
AND tas_codeintercalaire = ta_code (+)
AND ta_cle = tga_cletarif (+)
AND tga_code_compta = ccp_code (+)
AND (pol_datresil IS NULL OR pol_datresil >= (SYSDATE - TO_YMINTERVAL('03-00')))
GROUP BY pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, 
		sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, 
  gad_prime_nette, ccp_libelle, 
        pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, 
        mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type 
)
GROUP BY pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, 
         sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, 
         pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, 
          mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type
ORDER BY pol_numpol ASC, ver_compteur ASC

**justinedr71** · 08/08/2011, 10h20

Bonjour,

Je pense que oui ma requête est correcte, comme elle s'exécute sans erreur lorsque j'ai moins de ligne (and rownum < 200). Je l'avais aussi faite tourner sous TOAD for Oracle avec un select count (distinct pol_numpol) afin de savoir combien de ligne elle retournait et cela avait fonctionné.

Le problème pourrait alors venir du group by ou du order by qui sont à la fin?

Dernière chose : Je n'arrive pas à comprendre le decode sur les libellés. La concaténation n'est pas une focntion de groupe.

Je ne comprend pas ce que vous voulez dire par la? Je concatène les DECODE car je souhaiterais avoir le résultat dans une seule et même chaîne de caractère. Avec des "0" si le libellé ne correspond pas à une garantie souscrite par la police d'assurance et le montant de la prime le cas contraire.

**McM** · 08/08/2011, 10h32

des decodes concaténés ne sont pas des fonctions de groupe. Tu ne peux pas regrouper des lignes dans une seule colonne avec des concaténations.

Code :

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

1
2
3
4
5
6
SELECT num, DECODE(col, 'a', 1, 0) ||':'|| DECODE(col, 'b',2,0)  AS lib
FROM (SELECT 1 num, 'a' col FROM dual UNION ALL SELECT 1 num, 'b' col FROM dual)
GROUP BY num
 
ORA-00979: not a GROUP BY expression
(et Toad te positionne sur le premier col)

par contre si tu n'as qu'une ligne, ça marche

Code :

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

1
2
3
4
5
6
SELECT num, DECODE(col, 'a', 1, 0) ||':'|| DECODE(col, 'b',2,0)  AS lib
FROM (SELECT 1 num, 'a' col FROM dual)
GROUP BY num
 
NUM	LIB
1	1:0

**Mohamed.Houri** · 08/08/2011, 10h47

Bonjour,

pourriez vous faire ceci

Code :

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

1
2
3
4
 
explain plan for select pol_numpol, pol_datresil ..... order by pol_numpol asc,ver_compteur asc;
 
select * from table(dbms_xplan.display);

Et postez ici l'explain plan obtenu accompagné de sa partie prédicate.

Postez aussi le résultat de la requête suivante

Code :

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

1
2
3
 
 
select * from v$version;

Bien Cordialement

Mohamed Houri

**justinedr71** · 08/08/2011, 10h53

Explain complete.

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 3045687361                                                     
                                                                                
--------------------------------------------------------------------------------
-----------------------------------------                                       
                                                                                
| Id  | Operation                                  | Name               | Rows  
| Bytes |TempSpc| Cost (%CPU)| Time     |                                       
                                                                                
--------------------------------------------------------------------------------
-----------------------------------------                                       
                                                                                
|   0 | SELECT STATEMENT                           |                    |  9629M
|   986G|       |  1441M  (1)|999:59:59 |                                       
                                                                                
|   1 |  SORT ORDER BY                             |                    |  9629M
|   986G|  2825G|  1441M  (1)|999:59:59 |                                       
                                                                                
|   2 |   HASH GROUP BY                            |                    |  9629M
|   986G|  2825G|  1441M  (1)|999:59:59 |                                       
                                                                                
|   3 |    VIEW                                    |                    |  9629M

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|   986G|       |   795M  (1)|999:59:59 |                                       
                                                                                
|   4 |     HASH GROUP BY                          |                    |  9629M
|  2546G|  5441G|   795M  (1)|999:59:59 |                                       
                                                                                
|*  5 |      HASH JOIN RIGHT OUTER                 |                    |  9629M
|  2546G|       |   643K (21)| 02:08:40 |                                       
                                                                                
|   6 |       TABLE ACCESS FULL                    | T_CDECPT           |   183 
|  6405 |       |     2   (0)| 00:00:01 |                                       
                                                                                
|*  7 |       HASH JOIN RIGHT OUTER                |                    |  9629M
|  2233G|       |   581K (12)| 01:56:19 |                                       
                                                                                
|   8 |        TABLE ACCESS FULL                   | F_TARIF_GAR        |  2258 
| 20322 |       |    22   (0)| 00:00:01 |                                       
                                                                                
|*  9 |        HASH JOIN RIGHT OUTER               |                    |   379M
|    84G|       |   519K  (2)| 01:43:57 |                                       
                                                                                
|  10 |         TABLE ACCESS FULL                  | T_INTERCALAIRE     |    89 

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|   890 |       |     3   (0)| 00:00:01 |                                       
                                                                                
|* 11 |         HASH JOIN RIGHT OUTER              |                    |   379M
|    81G|       |   517K  (2)| 01:43:28 |                                       
                                                                                
|  12 |          TABLE ACCESS FULL                 | F_TARIF_ASS        |   100 
|  1200 |       |     2   (0)| 00:00:01 |                                       
                                                                                
|* 13 |          HASH JOIN RIGHT OUTER             |                    |    37M
|  7890M|       |   514K  (1)| 01:42:58 |                                       
                                                                                
|  14 |           TABLE ACCESS FULL                | F_PRD_RISQUE       |    91 
|   728 |       |     2   (0)| 00:00:01 |                                       
                                                                                
|* 15 |           HASH JOIN RIGHT OUTER            |                    |    12M
|  2505M|       |   514K  (1)| 01:42:55 |                                       
                                                                                
|  16 |            TABLE ACCESS FULL               | T_SIT_MARITALE     |     6 
|    78 |       |     3   (0)| 00:00:01 |                                       
                                                                                
|* 17 |            HASH JOIN RIGHT OUTER           |                    |    12M

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|  2350M|  2112K|   514K  (1)| 01:42:54 |                                       
                                                                                
|  18 |             TABLE ACCESS FULL              | F_P_PERSONNE       | 63393 
|  1361K|       |   582   (2)| 00:00:07 |                                       
                                                                                
|* 19 |             HASH JOIN RIGHT OUTER          |                    |    12M
|  2088M|  7216K|   403K  (1)| 01:20:37 |                                       
                                                                                
|  20 |              TABLE ACCESS FULL             | F_VERSION_POLICE   |   210K
|  4737K|       |  1171   (2)| 00:00:15 |                                       
                                                                                
|* 21 |              HASH JOIN RIGHT OUTER         |                    |    12M
|  1807M|    14M|   304K  (1)| 01:00:56 |                                       
                                                                                
|  22 |               TABLE ACCESS FULL            | F_MOUVEMENT        |   631K
|  7400K|       |  3217   (1)| 00:00:39 |                                       
                                                                                
|* 23 |               HASH JOIN RIGHT OUTER        |                    |    11M
|  1534M|       |   217K  (1)| 00:43:36 |                                       
                                                                                
|  24 |                TABLE ACCESS FULL           | F_PRODUITASS       |    30 

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|   210 |       |     2   (0)| 00:00:01 |                                       
                                                                                
|  25 |                NESTED LOOPS OUTER          |                    |    11M
|  1457M|       |   217K  (1)| 00:43:35 |                                       
                                                                                
|* 26 |                 FILTER                     |                    |       
|       |       |            |          |                                       
                                                                                
|* 27 |                  HASH JOIN RIGHT OUTER     |                    |    11M
|  1227M|  1912K|   217K  (1)| 00:43:35 |                                       
                                                                                
|  28 |                   TABLE ACCESS FULL        | F_POLICES          | 42459 
|  1409K|       |   846   (1)| 00:00:11 |                                       
                                                                                
|* 29 |                   HASH JOIN RIGHT OUTER    |                    |    11M
|   854M|  1168K|   167K  (2)| 00:33:35 |                                       
                                                                                
|  30 |                    TABLE ACCESS FULL       | F_P_C_ASSURE       | 49733 
|   582K|       |    98   (2)| 00:00:02 |                                       
                                                                                
|* 31 |                    HASH JOIN RIGHT OUTER   |                    |    11M

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|   719M|    13M|   125K  (2)| 00:25:05 |                                       
                                                                                
|  32 |                     TABLE ACCESS FULL      | F_SIT_ASSURE       |   573K
|  6715K|       |  3282   (1)| 00:00:40 |                                       
                                                                                
|* 33 |                     HASH JOIN RIGHT OUTER  |                    |    11M
|   588M|    31M| 85608   (2)| 00:17:08 |                                       
                                                                                
|  34 |                      TABLE ACCESS FULL     | F_SIT_OBJET_RISQUE |   585K
|    25M|       |  3326   (2)| 00:00:40 |                                       
                                                                                
|  35 |                      TABLE ACCESS FULL     | F_GARANTIE_DYN     |    11M
|    97M|       | 69254   (2)| 00:13:52 |                                       
                                                                                
|  36 |                 TABLE ACCESS BY INDEX ROWID| F_INTERMEDIAIRE    |     1 
|    21 |       |     1   (0)| 00:00:01 |                                       
                                                                                
|* 37 |                  INDEX UNIQUE SCAN         | PK_IN_IDENT        |     1 
|       |       |     0   (0)| 00:00:01 |                                       
                                                                                
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
-----------------------------------------                                       
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   5 - access("TGA_CODE_COMPTA"="CCP_CODE"(+))                                  
   7 - access("TA_CLE"="TGA_CLETARIF"(+))                                       
   9 - access("TAS_CODEINTERCALAIRE"="TA_CODE"(+))                              
  11 - access("SOR_DATETARIF"="TAS_DATEFFET"(+))                                
  13 - access("PAS_IDENT"="PRI_PTRPASIDENT"(+))                                 
  15 - access("BPP_PTRMARID"="MAR_IDENT"(+))                                    
  17 - access("BPASS_PTRBPPIDENT"="BPP_IDENT"(+))                               
  19 - access("MVT_PTRVERID"="VER_IDENT"(+))                                    
  21 - access("SOR_IDENT"="MVT_PTRSUIVANTID"(+))                                
  23 - access("POL_PTRPASID"="PAS_IDENT"(+))                                    
  26 - filter("POL_DATRESIL" IS NULL OR "POL_DATRESIL">=SYSDATE@!-INTERVAL'+0000
00003-00' YEAR(9) TO MONTH)                                                     
                                                                                
  27 - access("SOR_PTRPOLID"="POL_IDENT"(+))                                    
  29 - access("SAR_PTRASSID"="BPASS_IDENT"(+))                                  

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
  31 - access("SOR_IDENT"="SAR_PTRSORID"(+))                                    
  33 - access("GAD_PTRSORID"="SOR_IDENT"(+))                                    
  37 - access("POL_PTRINID"="IN_IDENT"(+))                                      

64 rows selected.




BANNER                                                          
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production                          
CORE	10.2.0.1.0	Production                                      
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production         
NLSRTL Version 10.2.0.1.0 - Production                          

5 rows selected.

**Mohamed.Houri** · 08/08/2011, 11h06

Merci,

L'explain plan que vous avez posté est inexploitable à cause de l'indentation qui a été mal ajustée

Pourriez-vous s'il vous plaît refaire la même chose mais cette fois ci précédée de

Code :

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

1
2
 
set linesize 150;

Ceci permettra de bien ajuster l'indentation

Bien Cordialement

Mohamed Houri

**justinedr71** · 08/08/2011, 11h08

Mcm, en effet dans mon cas je n'ai pas qu'une seule ligne dans ma clause GROUP BY. Mais sur des petites quantités de données cela fonctionne quand même... Comment ça se fait?

J'ai modifié ma requête ainsi :

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
 
"select pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pas_nb_jours_prorata, pri_type_risque, 
bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, 
bpp_pp_handicape, in_code_etabl, in_type," + globalMap.get("Key1") + "from ( select pol_numpol, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, 
sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, ccp_libelle, gad_prime_nette, 
pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, 
mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type
from f_polices, f_version_police, f_mouvement, f_sit_objet_risque, f_garantie_dyn, f_produitass, f_prd_risque, 
f_sit_assure, f_p_c_assure, f_p_personne, t_sit_maritale, f_intermediaire, f_tarif_ass, t_intercalaire, 
f_tarif_gar, t_cdecpt
where gad_ptrsorid = sor_ident (+)
and sor_ptrpolid = pol_ident (+)
and sor_ident = mvt_ptrsuivantid (+)
and mvt_ptrverid = ver_ident (+)
and pol_ptrpasid = pas_ident (+)
and pas_ident = pri_ptrpasident (+)
and sor_ident = sar_ptrsorid (+)
and sar_ptrassid = bpass_ident (+)
and bpass_ptrbppident = bpp_ident (+)
and bpp_ptrmarid = mar_ident (+) 
and pol_ptrinid = in_ident (+)  
and sor_datetarif = tas_dateffet (+) 
and tas_codeintercalaire = ta_code (+)
and ta_cle = tga_cletarif (+)
and tga_code_compta = ccp_code (+)
and (pol_datresil is null or pol_datresil >= (sysdate - to_yminterval('03-00'))))
group by pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, 
sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pas_nb_jours_prorata, pri_type_risque, 
bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, 
bpp_pp_handicape, in_code_etabl, in_type"

J'ai lancé l'exécution, j'attend donc de voir... Qu'entendez-vous par :

Tu ne peux pas regrouper des lignes dans une seule colonne avec des concaténations

??
Parce qu'encore une fois, sur une petite quantité de données, cela fonctionne...

**justinedr71** · 08/08/2011, 11h16

Explain complete.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3045687361

-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9629M| 986G| | 1441M (1)|999:59:59 |
| 1 | SORT ORDER BY | | 9629M| 986G| 2825G| 1441M (1)|999:59:59 |
| 2 | HASH GROUP BY | | 9629M| 986G| 2825G| 1441M (1)|999:59:59 |
| 3 | VIEW | | 9629M| 986G| | 795M (1)|999:59:59 |
| 4 | HASH GROUP BY | | 9629M| 2546G| 5441G| 795M (1)|999:59:59 |
|* 5 | HASH JOIN RIGHT OUTER | | 9629M| 2546G| | 643K (21)| 02:08:40 |
| 6 | TABLE ACCESS FULL | T_CDECPT | 183 | 6405 | | 2 (0)| 00:00:01 |
|* 7 | HASH JOIN RIGHT OUTER | | 9629M| 2233G| | 581K (12)| 01:56:19 |
| 8 | TABLE ACCESS FULL | F_TARIF_GAR | 2258 | 20322 | | 22 (0)| 00:00:01 |
|* 9 | HASH JOIN RIGHT OUTER | | 379M| 84G| | 519K (2)| 01:43:57 |
| 10 | TABLE ACCESS FULL | T_INTERCALAIRE | 89 | 890 | | 3 (0)| 00:00:01 |
|* 11 | HASH JOIN RIGHT OUTER | | 379M| 81G| | 517K (2)| 01:43:28 |
| 12 | TABLE ACCESS FULL | F_TARIF_ASS | 100 | 1200 | | 2 (0)| 00:00:01 |
|* 13 | HASH JOIN RIGHT OUTER | | 37M| 7890M| | 514K (1)| 01:42:58 |
| 14 | TABLE ACCESS FULL | F_PRD_RISQUE | 91 | 728 | | 2 (0)| 00:00:01 |
|* 15 | HASH JOIN RIGHT OUTER | | 12M| 2505M| | 514K (1)| 01:42:55 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
| 16 | TABLE ACCESS FULL | T_SIT_MARITALE | 6 | 78 | | 3 (0)| 00:00:01 |
|* 17 | HASH JOIN RIGHT OUTER | | 12M| 2350M| 2112K| 514K (1)| 01:42:54 |
| 18 | TABLE ACCESS FULL | F_P_PERSONNE | 63393 | 1361K| | 582 (2)| 00:00:07 |
|* 19 | HASH JOIN RIGHT OUTER | | 12M| 2088M| 7216K| 403K (1)| 01:20:37 |
| 20 | TABLE ACCESS FULL | F_VERSION_POLICE | 210K| 4737K| | 1171 (2)| 00:00:15 |
|* 21 | HASH JOIN RIGHT OUTER | | 12M| 1807M| 14M| 304K (1)| 01:00:56 |
| 22 | TABLE ACCESS FULL | F_MOUVEMENT | 631K| 7400K| | 3217 (1)| 00:00:39 |
|* 23 | HASH JOIN RIGHT OUTER | | 11M| 1534M| | 217K (1)| 00:43:36 |
| 24 | TABLE ACCESS FULL | F_PRODUITASS | 30 | 210 | | 2 (0)| 00:00:01 |
| 25 | NESTED LOOPS OUTER | | 11M| 1457M| | 217K (1)| 00:43:35 |
|* 26 | FILTER | | | | | | |
|* 27 | HASH JOIN RIGHT OUTER | | 11M| 1227M| 1912K| 217K (1)| 00:43:35 |
| 28 | TABLE ACCESS FULL | F_POLICES | 42459 | 1409K| | 846 (1)| 00:00:11 |
|* 29 | HASH JOIN RIGHT OUTER | | 11M| 854M| 1168K| 167K (2)| 00:33:35 |
| 30 | TABLE ACCESS FULL | F_P_C_ASSURE | 49733 | 582K| | 98 (2)| 00:00:02 |
|* 31 | HASH JOIN RIGHT OUTER | | 11M| 719M| 13M| 125K (2)| 00:25:05 |
| 32 | TABLE ACCESS FULL | F_SIT_ASSURE | 573K| 6715K| | 3282 (1)| 00:00:40 |
|* 33 | HASH JOIN RIGHT OUTER | | 11M| 588M| 31M| 85608 (2)| 00:17:08 |
| 34 | TABLE ACCESS FULL | F_SIT_OBJET_RISQUE | 585K| 25M| | 3326 (2)| 00:00:40 |
| 35 | TABLE ACCESS FULL | F_GARANTIE_DYN | 11M| 97M| | 69254 (2)| 00:13:52 |
| 36 | TABLE ACCESS BY INDEX ROWID| F_INTERMEDIAIRE | 1 | 21 | | 1 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
|* 37 | INDEX UNIQUE SCAN | PK_IN_IDENT | 1 | | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("TGA_CODE_COMPTA"="CCP_CODE"(+))
7 - access("TA_CLE"="TGA_CLETARIF"(+))
9 - access("TAS_CODEINTERCALAIRE"="TA_CODE"(+))
11 - access("SOR_DATETARIF"="TAS_DATEFFET"(+))
13 - access("PAS_IDENT"="PRI_PTRPASIDENT"(+))
15 - access("BPP_PTRMARID"="MAR_IDENT"(+))
17 - access("BPASS_PTRBPPIDENT"="BPP_IDENT"(+))
19 - access("MVT_PTRVERID"="VER_IDENT"(+))
21 - access("SOR_IDENT"="MVT_PTRSUIVANTID"(+))
23 - access("POL_PTRPASID"="PAS_IDENT"(+))
26 - filter("POL_DATRESIL" IS NULL OR "POL_DATRESIL">=SYSDATE@!-INTERVAL'+000000003-00' YEAR(9) TO MONTH)
27 - access("SOR_PTRPOLID"="POL_IDENT"(+))
29 - access("SAR_PTRASSID"="BPASS_IDENT"(+))
31 - access("SOR_IDENT"="SAR_PTRSORID"(+))
33 - access("GAD_PTRSORID"="SOR_IDENT"(+))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
37 - access("POL_PTRINID"="IN_IDENT"(+))

64 rows selected.

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.

**Mohamed.Houri** · 08/08/2011, 11h38

Bon, j'ai formatté l'explain plan pour vous

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
 
 
PLAN_TABLE_OUTPUT                                                               
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3045687361                                                                                                                       
-------------------------------------------------------------------------------------------------------------------------                                                                                                                     
| Id  | Operation                                  | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                       
-------------------------------------------------------------------------------------------------------------------------                                                                                                                      
|   0 | SELECT STATEMENT                           |                    |  9629M|   986G|       |  1441M  (1)|999:59:59 |                                                                                                                       
|   1 |  SORT ORDER BY                             |                    |  9629M|   986G|  2825G|  1441M  (1)|999:59:59 |                                                                                                                       
|   2 |   HASH GROUP BY                            |                    |  9629M|   986G|  2825G|  1441M  (1)|999:59:59 |                                                                                                                       
|   3 |    VIEW                                    |                    |  9629M|   986G|       |   795M  (1)|999:59:59 |                                                                                                                       
|   4 |     HASH GROUP BY                          |                    |  9629M|  2546G|  5441G|   795M  (1)|999:59:59 |                                                                                                                       
|*  5 |      HASH JOIN RIGHT OUTER                 |                    |  9629M|  2546G|       |   643K (21)| 02:08:40 |                                                                                                                       
|   6 |       TABLE ACCESS FULL                    | T_CDECPT           |   183 |  6405 |       |     2   (0)| 00:00:01 |                                                                                                                       
|*  7 |       HASH JOIN RIGHT OUTER                |                    |  9629M|  2233G|       |   581K (12)| 01:56:19 |                                                                                                                       
|   8 |        TABLE ACCESS FULL                   | F_TARIF_GAR        |  2258 | 20322 |       |    22   (0)| 00:00:01 |                                                                                                                       
|*  9 |        HASH JOIN RIGHT OUTER               |                    |   379M|    84G|       |   519K  (2)| 01:43:57 |                                                                                                                       
|  10 |         TABLE ACCESS FULL                  | T_INTERCALAIRE     |    89 |   890 |       |     3   (0)| 00:00:01 |                                                                                                                       
|* 11 |         HASH JOIN RIGHT OUTER              |                    |   379M|    81G|       |   517K  (2)| 01:43:28 |                                                                                                                       
|  12 |          TABLE ACCESS FULL                 | F_TARIF_ASS        |   100 |  1200 |       |     2   (0)| 00:00:01 |                                                                                                                       
|* 13 |          HASH JOIN RIGHT OUTER             |                    |    37M|  7890M|       |   514K  (1)| 01:42:58 |                                                                                                                       
|  14 |           TABLE ACCESS FULL                | F_PRD_RISQUE       |    91 |   728 |       |     2   (0)| 00:00:01 |                                                                                                                       
|* 15 |           HASH JOIN RIGHT OUTER            |                    |    12M|  2505M|       |   514K  (1)| 01:42:55 |                                                                                                                       
|  16 |            TABLE ACCESS FULL               | T_SIT_MARITALE     |     6 |    78 |       |     3   (0)| 00:00:01 |                                                                                                                       
|* 17 |            HASH JOIN RIGHT OUTER           |                    |    12M|  2350M|  2112K|   514K  (1)| 01:42:54 |                                                                                                                       
|  18 |             TABLE ACCESS FULL              | F_P_PERSONNE       | 63393 |  1361K|       |   582   (2)| 00:00:07 |                                                                                                                       
|* 19 |             HASH JOIN RIGHT OUTER          |                    |    12M|  2088M|  7216K|   403K  (1)| 01:20:37 |                                                                                                                      
|  20 |              TABLE ACCESS FULL             | F_VERSION_POLICE   |   210K|  4737K|       |  1171   (2)| 00:00:15 |                                       
|* 21 |              HASH JOIN RIGHT OUTER         |                    |    12M|  1807M|    14M|   304K  (1)| 01:00:56 |                                       
|  22 |               TABLE ACCESS FULL            | F_MOUVEMENT        |   631K|  7400K|       |  3217   (1)| 00:00:39 |                                       
|* 23 |               HASH JOIN RIGHT OUTER        |                    |    11M|  1534M|       |   217K  (1)| 00:43:36 |                                                                                                                       
|  24 |                TABLE ACCESS FULL           | F_PRODUITASS       |    30 |   210 |       |     2   (0)| 00:00:01 |                                                                                                                       
|  25 |                NESTED LOOPS OUTER          |                    |    11M|  1457M|       |   217K  (1)| 00:43:35 |                                                                                                                       
|* 26 |                 FILTER                     |                    |       |       |       |            |          |                                                                                                                       
|* 27 |                  HASH JOIN RIGHT OUTER     |                    |    11M|  1227M|  1912K|   217K  (1)| 00:43:35 |                                                                                                                       
|  28 |                   TABLE ACCESS FULL        | F_POLICES          | 42459 |  1409K|       |   846   (1)| 00:00:11 |                                                                                                                       
|* 29 |                   HASH JOIN RIGHT OUTER    |                    |    11M|   854M|  1168K|   167K  (2)| 00:33:35 |                                                                                                                       
|  30 |                    TABLE ACCESS FULL       | F_P_C_ASSURE       | 49733 |   582K|       |    98   (2)| 00:00:02 |                                                                                                                       
|* 31 |                    HASH JOIN RIGHT OUTER   |                    |    11M|   719M|    13M|   125K  (2)| 00:25:05 |                                                                                                                       
|  32 |                     TABLE ACCESS FULL      | F_SIT_ASSURE       |   573K|  6715K|       |  3282   (1)| 00:00:40 |                                                                                                                    
|* 33 |                     HASH JOIN RIGHT OUTER  |                    |    11M|   588M|    31M| 85608   (2)| 00:17:08 |                                       
|  34 |                      TABLE ACCESS FULL     | F_SIT_OBJET_RISQUE |   585K|    25M|       |  3326   (2)| 00:00:40 |                                                                                                                       
|  35 |                      TABLE ACCESS FULL     | F_GARANTIE_DYN     |    11M|    97M|       | 69254   (2)| 00:13:52 |                                                                                                           
|  36 |                 TABLE ACCESS BY INDEX ROWID| F_INTERMEDIAIRE    |     1 |    21 |       |     1   (0)| 00:00:01 |                                                                                                                       
|* 37 |                  INDEX UNIQUE SCAN         | PK_IN_IDENT        |     1 |       |       |     0   (0)| 00:00:01 |                                                                                                                                                                                                      
 
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
 
   5 - access("TGA_CODE_COMPTA"="CCP_CODE"(+))                                  
   7 - access("TA_CLE"="TGA_CLETARIF"(+))                                       
   9 - access("TAS_CODEINTERCALAIRE"="TA_CODE"(+))                              
  11 - access("SOR_DATETARIF"="TAS_DATEFFET"(+))                                
  13 - access("PAS_IDENT"="PRI_PTRPASIDENT"(+))                                 
  15 - access("BPP_PTRMARID"="MAR_IDENT"(+))                                    
  17 - access("BPASS_PTRBPPIDENT"="BPP_IDENT"(+))                               
  19 - access("MVT_PTRVERID"="VER_IDENT"(+))                                    
  21 - access("SOR_IDENT"="MVT_PTRSUIVANTID"(+))                                
  23 - access("POL_PTRPASID"="PAS_IDENT"(+))                                    
  26 - filter("POL_DATRESIL" IS NULL OR "POL_DATRESIL">=SYSDATE@!-INTERVAL'+000000003-00' YEAR(9) TO MONTH)                                                                                                                                     
  27 - access("SOR_PTRPOLID"="POL_IDENT"(+))                                    
  29 - access("SAR_PTRASSID"="BPASS_IDENT"(+))                                  
  31 - access("SOR_IDENT"="SAR_PTRSORID"(+))                                    
  33 - access("GAD_PTRSORID"="SOR_IDENT"(+))                                    
  37 - access("POL_PTRINID"="IN_IDENT"(+))                                      
 
64 rows selected.

Il y a enormément de données (Rows) à manipuler par votre requête. A combien d'enregistrements vous vous attendez en retour ? Selon l'explain plan cela va être de l'ordre du 9,5 millions? Si ce nombre vous semble correct alors dans ce cas je me demande franchement à quoi cela sert t-il de sélectionner autant d'enregistrements!!!

Si par contre, le nombre est inexacte, alors soit les statistiques ne sont pas à jour ou soit votre requête n'est pas celle que vous esperez avoir écrite.

De plus, les deux premières opérations faites par votre requête sont

Code :

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

1
2
3
 
|  34 |                      TABLE ACCESS FULL     | F_SIT_OBJET_RISQUE |   585K|    25M|       |  3326   (2)| 00:00:40 |                                                                                                                       
|  35 |                      TABLE ACCESS FULL     | F_GARANTIE_DYN     |    11M|

Deux enormes FULL SCAN de tables sans prédicate?

Je ne peux même pas savoir si vraiment il y a absence de prédicate pour ces deux tables ou pas car vous n'avez pas mis des alias pour les tables afin de faire correspondre chaque where clause à sa table correspondante. Vous devriez commencer par faire des alias à vos tables et aux champs que vous sélectionnez

Bien Cordialement

Mohamed Houri

PS : à quelle table appartient le champ POL_DATRESIL?

ORA-01652: impossible d'étendre le segment temporaire de 128 dans le tablespace TEMP

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager