Discussion :

[thx5959]

* Bonjour, *

Adaptive Server Enterprise/12.5.4/EBF 13381/P/Sun_svr4/OS 5.8/ase1254/2006/64-bit/FBO/Wed May 24 03:14:30 2006

Sur un Dataserver, pour une base donnée, des requêtes sont lancées en paralléle et de temps en temps, j'ai le message

Can't load source: Message: 316. Severity: 17. Cannot compile the query because the space required for the search arguments (32000 bytes) exceeds the maximum amount of memory that can be granted. Try reducing the number of search arguments.: An error occurred during Swaps_de_Performance mapping, alternative: 1.

J'ai augmenté le "procedure cache size" mais rien n'y fait.

j'ai pensé mais par encore fait, de augmenter 'memory per worker process'

Votre idée ?

[mpeppler]

Le memory per worker process n'est utile que si la/les requêtes sont exécutées en mode "parallele".

Pour le reste - elle a l'air de quoi, la requête qui cause le problème?

Michael

[thx5959]

La requête ressemble à celà :

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
select distinct 
V.sous_type_taux,V.call_put,A.categorie,A.INST_CD as code_pivot,V.devise, 
V.echeance_dt,V.emetteur_id,V.emission_dt,V.val_id,V.sous_jacent_id, 
A.lib_c,A.lib_l,V.mt_emission,V.nb_titre,V.nominal,V.px_exercice, 
V.quotite,A.sous_type,A.type,V.taux_fixe,V.type_interet,V.type_taux, 
V.ratio_conversion,V.nom_taux_variable,V.marche,V.VAL_ID as INST_ID, 
valo_invent.sensib,getdate() as dat_jour,V.flag_produit_epargne, 
V.parite_conversion,V.cpe,V.nominal_uni_futur,v.periodicite_paiement_id, 
V.valeur_tick,V.variation_min_cours,V.flag_emtn,V.MTG_ORIG_WAL,nxt_call_dt,nxt_put_dt 
FROM portail.dbo.REF_VAL V, (select * from VALO where VALO.STATUT_VL=100 AND VALO.FLAG_INVENT='O' 
AND VALO.VALO_TYPE='O' 
AND VALO.VALO_DT >= dateadd(mm,-3,getdate()) 
AND VALO.UPD_DT >= dateadd(dd,-35,getdate()) 
AND UPD_DT <= "09/08/2009 06:03:42 " 
) as VALO, 
(SELECT VALO_ID 
FROM VALO,(select max(VALIDATION_DT) as date_valo, PTF_ID from VALO 
WHERE VALO.STATUT_VL=100 AND VALO.FLAG_INVENT='O' and VALO.VALO_TYPE='O' 
AND VALO.VALO_DT >= dateadd(mm,-3,getdate()) 
AND VALO.UPD_DT >= dateadd(dd,-35,getdate()) 
AND UPD_DT <= "09/08/2009 06:03:42 " 
group by PTF_ID) max_date 
WHERE max_date.date_valo = VALO.VALIDATION_DT 
AND max_date.PTF_ID = VALO.PTF_ID 
AND VALO.STATUT_VL=100 AND VALO.FLAG_INVENT='O' 
AND VALO.VALO_TYPE='O' 
AND VALO.VALO_DT >= dateadd(mm,-3,getdate()) 
AND VALO.UPD_DT >= dateadd(dd,-35,getdate()) 
AND VALO.UPD_DT <= "09/08/2009 06:03:42 " 
) as VIEW_DERNIERE_DATE_VALO, 
 
portail.dbo.VALO_INVENT VALO_INVENT, REF_INST A 
WHERE VALO_INVENT.VALO_ID = VALO.VALO_ID 
AND VIEW_DERNIERE_DATE_VALO.VALO_ID = VALO.VALO_ID 
AND V.VAL_ID = VALO_INVENT.INST_ID 
AND V.VAL_ID=A.INST_ID 
AND ((VALO.VALO_ID IN (17752179,17753509,17753513,17753515,17753502,17753514,17753344,17753316,17753359,17753365,17753321,17753522,17753366,17753317,17753479,17753324,17751775,17752040,17750552,17753325,17753419,17753320,17753376,17753377,17753339,17753337,17753338,17753336,17753335,17753343,17753482,17753367,17753346,17753348,17753318,17753347,17753332,17753351,17753329,17753362,17753490,17753330,17753481,17753488,17753477,17753480,17753353,17753519,17753354,17753474,17753523,17753524,17753342,17753476,17753379,17753375,17753380,17753322,17753360,17753357,17753319,17753326,17753340,17753368,17753374,17753349,17753370,17753369,17753361,17753358,17753372,17753373,17753363,17753364,17753526,17753323,17753496,17753493,17753501,17753498,17753499,17753497,17753500,17753495,17753494,17753378,17753371,17753487,17753333,17753491,17753341,17753402,17753398,17753478,17753429,17753447,17753440,17753470,17753417,17753465,17753469,17753421,17753466,17753425,17753431,17753443,17753442,17753464,17753452,17753414,17753423,17753428,17753422,17753416,17753409,17753427,17753399,17753446,17753424,17753457,17753456,17753407,17753458,17753459,17753453,17753461,17753400,17753430,17753401,17753460,17753403,17753455,17753434,17753435,17753436,17753437,17753438,17753410,17753454,17753450,17753420,17753441,17753433,17753444,17753445,17753467,17753468,17753418,17753432,17753408,17753426,17753406,17753413,17753439,17753448,17753405,17753411,17753451,17753449,17753471,17753462,17753517,17753512,17753511,17753510,17753507,17753504,17753505,17753345,17753506,17753508,17753503,17753516,17753485,17753483,17753327,17753489,17753518,17753463,17753397,17753393,17753395,17753392,17753394,17753328) )) 
 
UNION 
 
select distinct 
V.sous_type_taux,V.call_put,A.categorie,A.INST_CD as code_pivot, 
V.devise,V.echeance_dt,V.emetteur_id,V.emission_dt, 
V.val_id,V.sous_jacent_id,A.lib_c,A.lib_l,V.mt_emission, 
V.nb_titre,V.nominal,V.px_exercice,V.quotite,A.sous_type, 
A.type,V.taux_fixe,V.type_interet,V.type_taux,V.ratio_conversion, 
V.nom_taux_variable,V.marche,V.VAL_ID as INST_ID,valo_invent.sensib, 
getdate() as dat_jour,V.flag_produit_epargne,V.parite_conversion,V.cpe, V.nominal_uni_futur,v.periodicite_paiement_id, 
null,null,V.flag_emtn,V.MTG_ORIG_WAL,nxt_call_dt,nxt_put_dt 
from REF_CONTRAT PARENT, REF_CONTRAT JAMBE, VALO_INVENT VALO_INVENT, REF_VAL V, REF_INST A, REF_INST P, 
(select * from VALO where VALO.STATUT_VL=100 AND VALO.FLAG_INVENT='O' 
AND VALO.VALO_TYPE='O' 
AND VALO.VALO_DT >= dateadd(mm,-3,getdate()) 
AND VALO.UPD_DT >= dateadd(dd,-14,getdate()) 
AND UPD_DT <= "09/08/2009 06:03:42 " 
) as VALO, 
 
(SELECT VALO_ID 
FROM VALO,(select max(VALIDATION_DT) as date_valo, PTF_ID from VALO 
WHERE VALO.STATUT_VL=100 AND VALO.FLAG_INVENT='O' and VALO.VALO_TYPE='O' 
AND VALO.VALO_DT >= dateadd(mm,-3,getdate()) 
AND VALO.UPD_DT >= dateadd(dd,-14,getdate()) 
AND UPD_DT <= "09/08/2009 06:03:42 " 
group by PTF_ID) max_date 
WHERE max_date.date_valo = VALO.VALIDATION_DT 
AND max_date.PTF_ID = VALO.PTF_ID 
AND VALO.STATUT_VL=100 AND VALO.FLAG_INVENT='O' 
AND VALO.VALO_TYPE='O' 
AND VALO.VALO_DT >= dateadd(mm,-3,getdate()) 
AND VALO.UPD_DT >= dateadd(dd,-14,getdate()) 
AND VALO.UPD_DT <= "09/08/2009 06:03:42 " 
) as VIEW_DERNIERE_DATE_VALO 
 
WHERE JAMBE.PARENT_ID = PARENT.CONTRAT_ID 
AND VIEW_DERNIERE_DATE_VALO.VALO_ID = VALO.VALO_ID 
AND VALO.VALO_ID = VALO_INVENT.VALO_ID 
AND parent.CONTRAT_ID = VALO_INVENT.inst_ID 
AND PARENT.CONTRAT_ID = P.INST_ID 
AND P.CATEGORIE = 'CESSTMP' 
AND JAMBE.SOUS_JACENT_ID = V.VAL_ID 
AND V.VAL_ID=A.INST_ID 
AND ((VALO.VALO_ID IN (17752179,17753509,17753513,17753515,17753502,17753514,17753344,17753316,17753359,17753365,17753321,17753522,17753366,17753317,17753479,17753324,17751775,17752040,17750552,17753325,17753419,17753320,17753376,17753377,17753339,17753337,17753338,17753336,17753335,17753343,17753482,17753367,17753346,17753348,17753318,17753347,17753332,17753351,17753329,17753362,17753490,17753330,17753481,17753488,17753477,17753480,17753353,17753519,17753354,17753474,17753523,17753524,17753342,17753476,17753379,17753375,17753380,17753322,17753360,17753357,17753319,17753326,17753340,17753368,17753374,17753349,17753370,17753369,17753361,17753358,17753372,17753373,17753363,17753364,17753526,17753323,17753496,17753493,17753501,17753498,17753499,17753497,17753500,17753495,17753494,17753378,17753371,17753487,17753333,17753491,17753341,17753402,17753398,17753478,17753429,17753447,17753440,17753470,17753417,17753465,17753469,17753421,17753466,17753425,17753431,17753443,17753442,17753464,17753452,17753414,17753423,17753428,17753422,17753416,17753409,17753427,17753399,17753446,17753424,17753457,17753456,17753407,17753458,17753459,17753453,17753461,17753400,17753430,17753401,17753460,17753403,17753455,17753434,17753435,17753436,17753437,17753438,17753410,17753454,17753450,17753420,17753441,17753433,17753444,17753445,17753467,17753468,17753418,17753432,17753408,17753426,17753406,17753413,17753439,17753448,17753405,17753411,17753451,17753449,17753471,17753462,17753517,17753512,17753511,17753510,17753507,17753504,17753505,17753345,17753506,17753508,17753503,17753516,17753485,17753483,17753327,17753489,17753518,17753463,17753397,17753393,17753395,17753392,17753394,17753328) ))