Проблема найдена. Всем спасибо за участие в ее решении.
На фоне явных проблем с железом и попыток исключить эту составляющую нами не прекращались поиски проблем в самой программе. Найден запрос, который валит сервер:
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.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
SELECT Extent3.css_id AS css_id, Extent2.rehab_service_id AS
rehab_service_id, Extent8.rehab_group_id AS rehab_group_id,
Extent9.rehab_block_id AS rehab_block_id, Extent1.ipr_service_fact_id AS
ipr_service_fact_id, Extent1.performed_by AS performed_by, Extent3.fam
AS fam, Extent3.name AS name, Extent3.fname AS fname, Extent3.phone
AS phone, Extent4.css_name AS css_name, Extent5.department AS
department, Extent6.village AS village, Extent2.ipr_service_plan_id AS
ipr_service_plan_id, CAST('t' AS boolean) AS C1, Extent7.rehab_service
AS rehab_service, CAST(Extent1.service_date AS datetime year to
fraction(5)) AS C2, Extent8.rehab_group AS rehab_group,
Extent9.rehab_block AS rehab_block, Extent1.assistance AS assistance,
Extent10.appointment AS appointment FROM
"informix".sop_ipr_service_fact AS Extent1 INNER JOIN
"informix".sop_ipr_service_plan AS Extent2 ON Extent1.ipr_service_plan_id
= Extent2.ipr_service_plan_id INNER JOIN "informix".cs_user_ AS Extent3
ON Extent1.performed_by = Extent3.user_id INNER JOIN "informix".css_ AS
Extent4 ON Extent3.css_id = Extent4.css_id INNER JOIN
"informix".department AS Extent5 ON Extent4.dept_id = Extent5.dept_id
INNER JOIN "informix".village AS Extent6 ON Extent4.vill_id =
Extent6.vill_id INNER JOIN (SELECT
sop_d_rehab_service.rehab_service_id AS rehab_service_id,
sop_d_rehab_service.rehab_group_id AS rehab_group_id,
sop_d_rehab_service.rehab_service AS rehab_service,
sop_d_rehab_service.ordering AS ordering FROM
"informix".sop_d_rehab_service AS sop_d_rehab_service) AS Extent7 ON
Extent2.rehab_service_id = Extent7.rehab_service_id INNER JOIN (SELECT
sop_d_rehab_group.rehab_group_id AS rehab_group_id,
sop_d_rehab_group.rehab_block_id AS rehab_block_id,
sop_d_rehab_group.rehab_group AS rehab_group,
sop_d_rehab_group.ordering AS ordering FROM
"informix".sop_d_rehab_group AS sop_d_rehab_group) AS Extent8 ON
Extent7.rehab_group_id = Extent8.rehab_group_id INNER JOIN (SELECT
sop_d_rehab_block.rehab_block_id AS rehab_block_id,
sop_d_rehab_block.rehab_block AS rehab_block,
sop_d_rehab_block.ordering AS ordering FROM
"informix".sop_d_rehab_block AS sop_d_rehab_block) AS Extent9 ON
Extent8.rehab_block_id = Extent9.rehab_block_id INNER JOIN (SELECT
cs_d_appointment.appointment_id AS appointment_id,
cs_d_appointment.appointment AS appointment FROM
"informix".cs_d_appointment AS cs_d_appointment) AS Extent10 ON
Extent3.appointment_dict_id = Extent10.appointment_id INNER JOIN (SELECT
UnionAll34.C1 AS C1 FROM (SELECT UnionAll33.C1 AS C1 FROM
(SELECT UnionAll32.C1 AS C1 FROM (SELECT UnionAll31.C1 AS
C1 FROM (SELECT UnionAll30.C1 AS C1 FROM (SELECT
UnionAll29.C1 AS C1 FROM (SELECT UnionAll28.C1 AS C1
FROM (SELECT UnionAll27.C1 AS C1 FROM (SELECT
UnionAll26.C1 AS C1 FROM (SELECT
UnionAll25.C1 AS C1 FROM (SELECT UnionAll24.C1 AS
C1 FROM (SELECT UnionAll23.C1 AS C1
FROM (SELECT UnionAll22.C1 AS C1 FROM
(SELECT UnionAll21.C1 AS C1 FROM (SELECT
UnionAll20.C1 AS C1 FROM (SELECT
UnionAll19.C1 AS C1 FROM (SELECT
UnionAll18.C1 AS C1 FROM (SELECT
UnionAll17.C1 AS C1 FROM (SELECT
UnionAll16.C1 AS C1 FROM (SELECT
UnionAll15.C1 AS C1 FROM (SELECT
UnionAll14.C1 AS C1 FROM (SELECT
UnionAll13.C1 AS C1 FROM (SELECT
UnionAll12.C1 AS C1 FROM (SELECT
UnionAll11.C1 AS C1 FROM
(SELECT UnionAll10.C1 AS C1
FROM (SELECT UnionAll9.C1 AS C1
FROM (SELECT
UnionAll8.C1 AS C1 FROM (SELECT
UnionAll7.C1 AS C1 FROM
(SELECT UnionAll6.C1 AS C1
FROM (SELECT UnionAll5.C1 AS
C1 FROM (SELECT
UnionAll4.C1 AS C1 FROM (SELECT
UnionAll3.C1 AS C1
FROM (SELECT UnionAll2.C1 AS C1
FROM (SELECT
UnionAll1.C1 AS C1 FROM
(SELECT 340000002048 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM
INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable1
UNION ALL SELECT
340000002054 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE
TABID=1 ) AS SingleRowTable2) AS UnionAll1
UNION ALL SELECT
340000002059 AS C1 FROM
( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS
SingleRowTable3) AS UnionAll2 UNION ALL
SELECT
340000002063 AS C1 FROM ( SELECT CAST(1
AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable4)
AS UnionAll3 UNION ALL
SELECT 340000002089 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM
INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable5) AS UnionAll4
UNION ALL SELECT
340000002171 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE
TABID=1 ) AS SingleRowTable6) AS UnionAll5
UNION ALL SELECT
340000002146 AS C1 FROM ( SELECT
CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS
SingleRowTable7) AS UnionAll6 UNION ALL
SELECT 340000002153
AS C1 FROM ( SELECT CAST(1 AS int) AS X
FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable8) AS UnionAll7
UNION ALL SELECT
340000002604 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 )
AS SingleRowTable9) AS UnionAll8 UNION ALL
SELECT 340000002605 AS
C1 FROM ( SELECT CAST(1 AS int) AS X FROM
INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable10) AS UnionAll9
UNION ALL SELECT
340000017960 AS C1 FROM (
SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS
SingleRowTable11) AS UnionAll10 UNION ALL
SELECT 340000002151 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM
INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable12) AS UnionAll11
UNION ALL SELECT
340000001062 AS C1 FROM ( SELECT
CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS
SingleRowTable13) AS UnionAll12 UNION ALL
SELECT 340000002607 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES
WHERE TABID=1 ) AS SingleRowTable14) AS UnionAll13
UNION ALL SELECT
340000017953 AS C1 FROM ( SELECT CAST(1 AS int) AS
X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable15) AS
UnionAll14 UNION ALL SELECT
340000017956 AS C1 FROM ( SELECT
CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS
SingleRowTable16) AS UnionAll15 UNION ALL
SELECT 340000002589 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1
) AS SingleRowTable17) AS UnionAll16 UNION ALL
SELECT 340000002091 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE
TABID=1 ) AS SingleRowTable18) AS UnionAll17 UNION ALL
SELECT 340000002096 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE
TABID=1 ) AS SingleRowTable19) AS UnionAll18 UNION ALL
SELECT 340000002099 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE
TABID=1 ) AS SingleRowTable20) AS UnionAll19 UNION ALL
SELECT 340000002175 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 )
AS SingleRowTable21) AS UnionAll20 UNION ALL
SELECT 340000017952 AS C1 FROM ( SELECT
CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS
SingleRowTable22) AS UnionAll21 UNION ALL
SELECT 340000017957 AS C1 FROM ( SELECT
CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS
SingleRowTable23) AS UnionAll22 UNION ALL
SELECT 340000017955 AS C1 FROM ( SELECT
CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS
SingleRowTable24) AS UnionAll23 UNION ALL SELECT
340000002053 AS C1 FROM ( SELECT CAST(1 AS int)
AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable25) AS
UnionAll24 UNION ALL SELECT
340000002056 AS C1 FROM ( SELECT CAST(1 AS int) AS X FROM
INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable26) AS UnionAll25
UNION ALL SELECT 340000002147 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 )
AS SingleRowTable27) AS UnionAll26 UNION ALL SELECT
340000002150 AS C1 FROM ( SELECT CAST(1 AS int) AS X
FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable28) AS UnionAll27
UNION ALL SELECT 340000011667 AS C1
FROM ( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 )
AS SingleRowTable29) AS UnionAll28 UNION ALL SELECT
340000011636 AS C1 FROM ( SELECT CAST(1 AS int) AS X FROM
INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable30) AS UnionAll29
UNION ALL SELECT 340000017958 AS C1 FROM ( SELECT
CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS
SingleRowTable31) AS UnionAll30 UNION ALL SELECT
340000017959 AS C1 FROM ( SELECT CAST(1 AS int) AS X FROM
INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable32) AS UnionAll31
UNION ALL SELECT 340000020810 AS C1 FROM ( SELECT CAST(1
AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable33)
AS UnionAll32 UNION ALL SELECT 340000000482 AS C1 FROM (
SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE TABID=1 ) AS
SingleRowTable34) AS UnionAll33 UNION ALL SELECT 340000017961 AS
C1 FROM ( SELECT CAST(1 AS int) AS X FROM INFORMIX.SYSTABLES WHERE
TABID=1 ) AS SingleRowTable35) AS UnionAll34 UNION ALL SELECT
340000002061 AS C1 FROM ( SELECT CAST(1 AS int) AS X FROM
INFORMIX.SYSTABLES WHERE TABID=1 ) AS SingleRowTable36) AS UnionAll35 ON
Extent1.ipr_service_plan_id = UnionAll35.C1 WHERE
((Extent1.ipr_service_fact_id = 0) AND (0 > 0)) OR (0 = 0)
Этот запрос сгенерирован ORM EntityFramework из linq
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
var res =
(
from f in entitiesFactory.sopDbContext.sop_ipr_service_fact
join p in entitiesFactory.sopDbContext.sop_ipr_service_plan on f.ipr_service_plan_id equals p.ipr_service_plan_id
join u in entitiesFactory.sopDbContext.cs_user_ on f.performed_by equals u.user_id
join css in entitiesFactory.sopDbContext.css_ on u.css_id equals css.css_id
join dep in entitiesFactory.sopDbContext.department on css.dept_id equals dep.dept_id
join vill in entitiesFactory.sopDbContext.village on css.vill_id equals vill.vill_id
join service_ in entitiesFactory.sopDbContext.sop_d_rehab_service on p.rehab_service_id equals service_.rehab_service_id
join group_ in entitiesFactory.sopDbContext.sop_d_rehab_group on service_.rehab_group_id equals group_.rehab_group_id
join block_ in entitiesFactory.sopDbContext.sop_d_rehab_block on group_.rehab_block_id equals block_.rehab_block_id
join appointment in entitiesFactory.sopDbContext.cs_d_appointment on u.appointment_dict_id equals appointment.appointment_id
join pln in plans on f.ipr_service_plan_id equals pln.Value
where
(f.ipr_service_fact_id == 0 && request.keyID > 0 || request.keyID == 0)
Помимо объектов из контекста базы данных в join соединении участвует список ключей plans.
ORM его преобразовала в множество подзапросов к systables.
Причем чем больше мощность множества, тем больше подзапросов к systables.
После этого запроса сервер останавливается 10 минут. Умирает сразу.
Чекпоинт попал под внимание, тк он выполняется часто и первым попадал под зависание.
Свою часть темы закрываю.
За эти дни я и мои коллеги узнали что-то новое за более чем 15 лет работы с информиксом.
Было интересно. Еще раз спасибо!
ps. и все таки странно, что informix позволил пользовательскому запросу так глубоко парализовать работу субд, что даже подключиться к БД в новой сессии невозможно.
