Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
OS Name: Microsoft Windows Server 2016 Standard
System Model: Virtual Machine
System Type: x64-based PC
Оперватива 8Gb
Встретил error ORA-04030. Перерыл документации и советы.
Прошу глянуть,на что мне обратить внимание,какие видны проблемы? Из trc я вижу что падение произошло на 371 сессии и 87 процессе,а именно SHAD(так ли это?) К слову,количество процессов SHAD сейчас 113 (правильно ли,должно ли так быть? Нужно ли обратить внимание?)
Посмотрел в логи,ниже приведу результаты(сперва 1й trace,затем 2й):
Буду благодарен помощи!
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
Trace file D:\APP\...\trace\xxx_14956.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Windows NT Version V6.2
CPU : 4 - type 8664, 4 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:1579M/8191M, Ph+PgF:68M/13240M
Instance name: xxx
Redo thread mounted by this instance: 1
Oracle process number: 87
Windows thread id: 14956, image: ORACLE.EXE (SHAD)
*** 2019-03-20 13:23:54.695
*** SESSION ID:(371.63160) 2019-03-20 13:23:54.695
*** CLIENT ID:() 2019-03-20 13:23:54.695
*** SERVICE NAME:(SYS$USERS) 2019-03-20 13:23:54.695
*** MODULE NAME:() 2019-03-20 13:23:54.695
*** CLIENT DRIVER:() 2019-03-20 13:23:54.695
*** ACTION NAME:() 2019-03-20 13:23:54.695
Incident 154732 created, dump file: D:\APP\...\incident\incdir_154732\xxx_14956_i154732.trc
ORA-04030: out of process memory when trying to allocate 262168 bytes (QERHJ hash-joi,HT buckets)
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.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
398.
399.
400.
401.
402.
403.
404.
405.
406.
407.
408.
409.
410.
411.
412.
Dump file D:\APP\...\incident\incdir_154732\xxx_14956_i154732.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Windows NT Version V6.2
CPU : 4 - type 8664, 4 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:1576M/8191M, Ph+PgF:65M/13240M
Instance name: xxx
Redo thread mounted by this instance: 1
Oracle process number: 87
Windows thread id: 14956, image: ORACLE.EXE (SHAD)
*** 2019-03-20 13:23:54.804
*** SESSION ID:(371.63160) 2019-03-20 13:23:54.804
*** CLIENT ID:() 2019-03-20 13:23:54.804
*** SERVICE NAME:(SYS$USERS) 2019-03-20 13:23:54.804
*** MODULE NAME:() 2019-03-20 13:23:54.804
*** CLIENT DRIVER:() 2019-03-20 13:23:54.804
*** ACTION NAME:() 2019-03-20 13:23:54.804
[TOC00000]
Jump to table of contents
Dump continued from file: D:\APP\...\trace\xxx_14956.trc
[TOC00001]
ORA-04030: out of process memory when trying to allocate 262168 bytes (QERHJ hash-joi,HT buckets)
[TOC00001-END]
[TOC00002]
========= Dump for incident 154732 (ORA 4030) ========
[TOC00003]
----- Beginning of Customized Incident Dump(s) -----
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
*** 2019-03-20 13:23:55.851
69% 7952 KB, 32 chunks: "kllcqas:kllsltba " SQL
QERHJ hash-joi ds=0000000024DD61F0 dsprt=0000000024DC0E60
10% 1182 KB, 51 chunks: "permanent memory " SQL
sort subheap ds=0000000024DCB840 dsprt=0000000024DC0E60
7% 785 KB, 42 chunks: "free memory "
top uga heap ds=0000000022367790 dsprt=0000000000000000
4% 512 KB, 8 chunks: "QERHJ Bit vector " SQL
QERHJ hash-joi ds=0000000024DD61F0 dsprt=0000000024DC0E60
2% 211 KB, 29 chunks: "permanent memory "
pga heap ds=0000000022361350 dsprt=0000000000000000
2% 187 KB, 1 chunk : "kllcqc:kllcqslt " SQL
QERHJ hash-joi ds=0000000024DD61F0 dsprt=0000000024DC0E60
1% 76 KB, 1 chunk : "Fixed Uga "
Fixed UGA heap ds=0000000024C5BC38 dsprt=0000000022361350
1% 71 KB, 8 chunks: "free memory "
pga heap ds=0000000022361350 dsprt=0000000000000000
1% 61 KB, 2 chunks: "free memory "
top call heap ds=0000000022367570 dsprt=0000000000000000
0% 55 KB, 30 chunks: "free memory "
session heap ds=0000000024DACF40 dsprt=0000000022367790
=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------
******************************************************
PRIVATE HEAP SUMMARY DUMP
11 MB total:
10 MB commented, 212 KB permanent
919 KB free (0 KB in empty extents),
11 MB, 1 heap: "session heap " 786 KB free held
------------------------------------------------------
Summary of subheaps at depth 1
10 MB total:
10 MB commented, 70 KB permanent
66 KB free (0 KB in empty extents),
10 MB, 1 heap: "kxs-heap-w " 55 KB free held
------------------------------------------------------
Summary of subheaps at depth 2
10 MB total:
10 MB commented, 23 KB permanent
8 KB free (0 KB in empty extents),
8691 KB, 1 heap: "QERHJ hash-joi "
1186 KB, 1 heap: "sort subheap "
------------------------------------------------------
Summary of subheaps at depth 3
10 MB total:
8666 KB commented, 1205 KB permanent
17 KB free (0 KB in empty extents),
7953 KB, 32 chunks: "kllcqas:kllsltba "
=========================================
REAL-FREE ALLOCATOR DUMP FOR THIS PROCESS
-----------------------------------------
Dump of Real-Free Memory Allocator Heap [0x24ce0000]
mag=0xfefe0001 flg=0x5000003 fds=0x0 blksz=65536
blkdstbl=0x24ce0018, iniblk=18432 maxblk=524288 numsegs=8
In-use num=68 siz=11534336, Freeable num=1 siz=65536, Free num=2 siz=3801088
==========================================
INSTANCE-WIDE PRIVATE MEMORY USAGE SUMMARY
------------------------------------------
Dumping Work Area Table (level=1)
=====================================
Global SGA Info
---------------
global target: 848 MB
auto target: 606 MB
max pga: 200 MB
pga limit: 0 MB
pga limit known: 0
pga limit errors: 0
pga inuse: 183 MB
pga alloc: 255 MB
pga freeable: 38 MB
pga freed: 109 MB
pga to free: 0
broker request: 0
pga auto: 10 MB
pga manual: 0 MB
pga alloc (max): 255 MB
pga auto (max): 10 MB
pga manual (max): 0 MB
# workareas : 1
# workareas(max): 7
================================
PER-PROCESS PRIVATE MEMORY USAGE
--------------------------------
Private memory usage per Oracle process
-------------------------
Top 10 processes:
-------------------------
(percentage is of 393 MB total allocated memory)
20% pid 36: 28 MB used of 80 MB allocated
19% pid 39: 69 MB used of 76 MB allocated
11% pid 70: 43 MB used of 45 MB allocated
6% pid 12: 6690 KB used of 23 MB allocated (16 MB freeable)
4% pid 9: 3207 KB used of 17 MB allocated (5120 KB freeable)
4% pid 73: 15 MB used of 15 MB allocated
3% pid 87: 11 MB used of 12 MB allocated (64 KB freeable) <= CURRENT PROC
2% pid 69: 1668 KB used of 8364 KB allocated (6016 KB freeable)
1% pid 93: 3260 KB used of 4068 KB allocated
1% pid 75: 3806 KB used of 3940 KB allocated
-------------------------
All processes:
-------------------------
(session detail when over 20 MB allocated)
pid 2: 786 KB used of 956 KB allocated
pid 3: 763 KB used of 932 KB allocated
pid 4: 759 KB used of 932 KB allocated
pid 5: 764 KB used of 876 KB allocated
pid 6: 763 KB used of 932 KB allocated
pid 7: 760 KB used of 932 KB allocated
pid 8: 1797 KB used of 2340 KB allocated (384 KB freeable)
pid 9: 3207 KB used of 17 MB allocated (5120 KB freeable)
pid 10: 759 KB used of 932 KB allocated
pid 11: 2218 KB used of 2707 KB allocated (256 KB freeable)
pid 12: 6690 KB used of 23 MB allocated (16 MB freeable)
------------------------------------
Begin session detail for pid 12
sid: 3 ser: 22071 audsid: 0 user: 0/SYS
flags: (0x51) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x409) -/-/INC
pid: 12 O/S info: user: SYSTEM, term: ORA1, ospid: 9500
image: ORACLE.EXE (DBW0)
current SQL: <none>
End session detail for pid 12
------------------------------------
pid 13: 934 KB used of 2276 KB allocated (832 KB freeable)
pid 14: 822 KB used of 1293 KB allocated
pid 15: 760 KB used of 932 KB allocated
pid 16: 809 KB used of 996 KB allocated
pid 17: 760 KB used of 932 KB allocated
pid 18: 934 KB used of 1508 KB allocated (256 KB freeable)
pid 19: 807 KB used of 932 KB allocated
pid 20: 763 KB used of 932 KB allocated
pid 21: 778 KB used of 932 KB allocated
pid 22: 1250 KB used of 1380 KB allocated
pid 23: 567 KB used of 676 KB allocated
pid 24: 983 KB used of 1188 KB allocated
pid 25: 664 KB used of 2596 KB allocated (1024 KB freeable)
pid 26: 768 KB used of 2148 KB allocated (1024 KB freeable)
pid 27: 704 KB used of 2084 KB allocated (1024 KB freeable)
pid 28: 759 KB used of 932 KB allocated
pid 29: 760 KB used of 932 KB allocated
pid 30: 760 KB used of 932 KB allocated
pid 31: 759 KB used of 932 KB allocated
pid 32: 760 KB used of 932 KB allocated
pid 33: 765 KB used of 1060 KB allocated
pid 34: 1062 KB used of 1956 KB allocated (576 KB freeable)
pid 35: 1613 KB used of 3044 KB allocated (896 KB freeable)
pid 36: 28 MB used of 80 MB allocated
------------------------------------
Begin session detail for pid 36
sid: 12 ser: 46344 audsid: 20040019 user: 103/ORX
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 36 O/S info: user: SYSTEM, term: ORA1, ospid: 8552
image: ORACLE.EXE (SHAD)
client details:
O/S info: user: orx, term: ORA2_R4, ospid: 200:3464
machine: XXX\ORA2_R4program: Writer.exe
application name: Writer.exe, hash value=622571254
current SQL:
INSERT INTO opc_teg_history(node_id, teg_id, read_time, value)
SELECT 32578983, 32578982, to_date('20.03.2019 13:23:42', 'dd.mm.yyyy hh24:mi:ss'), '0' FROM DUAL
UNION ALL
SELECT 32578985, 32578984, to_date('20.03.2019 13:23:42', 'dd.mm.yyyy hh24:mi:ss'), '0' FROM DUAL
UNION ALL
SELECT 32578987, 32578986, to_date('20.03.2019 13:23:42', 'dd.mm.yyyy hh24:mi:ss'), '5,87' FROM DUAL
UNION ALL
SELECT 32578989, 32578988, to_date('20.03.2019 13:23:42', 'dd.mm.yyyy hh24:mi:ss'), '5,9' FROM DUAL
UNION ALL
SELECT 32578991, 32578990, to_date('20.03.2019 13:23:42', 'dd.mm.yyyy hh24:mi:ss'), '5,87' FROM DUAL
UNION ALL
SELECT 32578993, 32578992, to_date('20.03.2019 13:23:42', 'dd.mm.yyyy hh24:mi:ss'), '0' FROM DUAL
UNION ALL
SELECT 32578995, 32578994, to_date('20.03.2019 13:23:42', 'dd.mm.yy
End session detail for pid 36
------------------------------------
pid 37: 1571 KB used of 2404 KB allocated (512 KB freeable)
pid 38: 844 KB used of 996 KB allocated
pid 39: 69 MB used of 76 MB allocated
------------------------------------
Begin session detail for pid 39
sid: 363 ser: 45452 audsid: 20040048 user: 103/ORX
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 39 O/S info: user: SYSTEM, term: ORA1, ospid: 13864
image: ORACLE.EXE (SHAD)
client details:
O/S info: user: orx, term: ORA2_R6, ospid: 3500:3292
machine: xxx\ORA2_R6program: Writer.exe
application name: WTO.Writer.exe, hash value=622571254
current SQL:
INSERT INTO opc_teg_history(node_id, teg_id, read_time, value)
SELECT 32554525, 32554524, to_date('20.03.2019 13:23:42', 'dd.mm.yyyy hh24:mi:ss'), '15,18' FROM DUAL
UNION ALL
SELECT 32554527, 32554526, to_date('20.03.2019 13:23:42', 'dd.mm.yyyy hh24:mi:ss'), '15,9' FROM DUAL
UNION ALL
SELECT 32546989, 32546988, to_date('20.03.2019 13:23:42', 'dd.mm.yyyy hh24:mi:ss'), '0' FROM DUAL
UNION ALL
SELECT 32553243, 32553242, to_date('20.03.2019 13:23:42', 'dd.mm.yyyy hh24:mi:ss'), '117,3' FROM DUAL
UNION ALL
SELECT 32553245, 32553244, to_date('20.03.2019 13:23:42', 'dd.mm.yyyy hh24:mi:ss'), '118,6' FROM DUAL
UNION ALL
SELECT 32553247, 32553246, to_date('20.03.2019 13:23:42', 'dd.mm.yyyy hh24:mi:ss'), '128,3' FROM DUAL
UNION ALL
SELECT 32553249, 32553248, to_date('20.03.2019 13:23:42'
End session detail for pid 39
------------------------------------
pid 40: 911 KB used of 1316 KB allocated
pid 41: 844 KB used of 996 KB allocated
pid 42: 844 KB used of 996 KB allocated
pid 43: 844 KB used of 996 KB allocated
pid 44: 844 KB used of 996 KB allocated
pid 45: 844 KB used of 996 KB allocated
pid 46: 844 KB used of 996 KB allocated
pid 47: 764 KB used of 932 KB allocated
pid 48: 1045 KB used of 1700 KB allocated (320 KB freeable)
pid 49: 885 KB used of 996 KB allocated
pid 50: 987 KB used of 1444 KB allocated (64 KB freeable)
pid 51: 1556 KB used of 3155 KB allocated (1024 KB freeable)
pid 52: 1138 KB used of 1811 KB allocated (448 KB freeable)
pid 53: 3432 KB used of 3556 KB allocated
pid 54: 915 KB used of 1124 KB allocated
pid 55: 915 KB used of 1124 KB allocated
pid 56: 554 KB used of 676 KB allocated
pid 57: 554 KB used of 676 KB allocated
pid 58: 554 KB used of 676 KB allocated
pid 59: 554 KB used of 676 KB allocated
pid 60: 554 KB used of 676 KB allocated
pid 61: 554 KB used of 676 KB allocated
pid 62: 554 KB used of 676 KB allocated
pid 63: 554 KB used of 676 KB allocated
pid 64: 554 KB used of 676 KB allocated
pid 65: 554 KB used of 676 KB allocated
pid 66: 554 KB used of 676 KB allocated
pid 67: 554 KB used of 676 KB allocated
pid 68: 554 KB used of 676 KB allocated
pid 69: 1668 KB used of 8364 KB allocated (6016 KB freeable)
pid 70: 43 MB used of 45 MB allocated
------------------------------------
Begin session detail for pid 70
sid: 249 ser: 44642 audsid: 20040036 user: 103/ORX
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 70 O/S info: user: SYSTEM, term: ORA1, ospid: 12984
image: ORACLE.EXE (SHAD)
client details:
O/S info: user: orx, term: ORA2_R2, ospid: 3248:3400
machine: xxx\ORA2_R2program: Writer.exe
application name: Writer.exe, hash value=622571254
current SQL:
INSERT INTO opc_teg_history(node_id, teg_id, read_time, value)
SELECT 32587818, 32587817, to_date('20.03.2019 13:23:43', 'dd.mm.yyyy hh24:mi:ss'), '29,29' FROM DUAL
UNION ALL
SELECT 32587820, 32587819, to_date('20.03.2019 13:23:43', 'dd.mm.yyyy hh24:mi:ss'), '6,34' FROM DUAL
UNION ALL
SELECT 32587822, 32587821, to_date('20.03.2019 13:23:43', 'dd.mm.yyyy hh24:mi:ss'), '13,91' FROM DUAL
UNION ALL
SELECT 32587824, 32587823, to_date('20.03.2019 13:23:43', 'dd.mm.yyyy hh24:mi:ss'), '0' FROM DUAL
UNION ALL
SELECT 32587826, 32587825, to_date('20.03.2019 13:23:43', 'dd.mm.yyyy hh24:mi:ss'), '10,62' FROM DUAL
UNION ALL
SELECT 32587828, 32587827, to_date('20.03.2019 13:23:43', 'dd.mm.yyyy hh24:mi:ss'), '6,96' FROM DUAL
UNION ALL
SELECT 32587830, 32587829, to_date('20.03.2019 13:23:43',
End session detail for pid 70
------------------------------------
pid 71: 1133 KB used of 2259 KB allocated (832 KB freeable)
pid 72: 3293 KB used of 3428 KB allocated
pid 73: 15 MB used of 15 MB allocated
pid 74: 915 KB used of 1124 KB allocated
pid 75: 3806 KB used of 3940 KB allocated
pid 76: 915 KB used of 1124 KB allocated
pid 77: 1227 KB used of 2259 KB allocated (896 KB freeable)
pid 78: 1719 KB used of 3492 KB allocated
pid 79: 915 KB used of 1124 KB allocated
pid 80: 915 KB used of 1124 KB allocated
pid 81: 915 KB used of 1124 KB allocated
pid 82: 971 KB used of 2579 KB allocated (896 KB freeable)
pid 83: 1711 KB used of 3091 KB allocated (896 KB freeable)
pid 84: 911 KB used of 1124 KB allocated
pid 85: 1571 KB used of 2468 KB allocated (384 KB freeable)
pid 86: 987 KB used of 1188 KB allocated
pid 87: 11 MB used of 12 MB allocated (64 KB freeable)
pid 88: 1438 KB used of 2195 KB allocated (576 KB freeable)
pid 89: 915 KB used of 1124 KB allocated
pid 90: 915 KB used of 1124 KB allocated
pid 91: 915 KB used of 1124 KB allocated
pid 92: 915 KB used of 1124 KB allocated
pid 93: 2328 KB used of 3620 KB allocated (1024 KB freeable)
======================================================
PRIVATE MEMORY USAGE FOR LARGEST PROCESS
------------------------------------------------------
Begin memory detail for largest PGA user, pid 36
PGA memory detail is busy and was not dumped.
Oracle pid 73 (OS pid 1508 ) may be dumping detail.
End of memory detail for pid 36
------------------------------------------------------
(if there was no memory detail for pid 36, it probably timed out and
may appear in trace file D:\APP\...\trace\xxx_8552.trc)
======================================================
ESTIMATED MEMORY USES FOR ALL PROCESSES
------------------------------------------------------
(from 0 snapshots out of 92 processes)
100% 393 MB, 92 processes: "unsnapshotted "
================
SWAP INFORMATION
----------------
----- End of Customized Incident Dump(s) -----
[TOC00003-END]
*** 2019-03-20 13:23:58.054
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00004]
----- Current SQL Statement for this session (sql_id=7yb27g5vxk9s5) -----
(select a.*, k.alarms_id fa_nokvit from alarms a, fa_nokvit k where (a.alm_nativetimelast between to_date('20.03.2019', 'DD.MM.YYYY HH24:MI:SS') and to_date('21.03.2019', 'DD.MM.YYYY HH24:MI:SS')) and k.alarms_id(+) = a.alarms_id)union (select a.*, k.alarms_id fa_nokvit from fa_messages a, fa_nokvit k where (a.alm_nativetimelast between to_date('20.03.2019', 'DD.MM.YYYY HH24:MI:SS') and to_date('21.03.2019', 'DD.MM.YYYY HH24:MI:SS')) and k.alarms_id(+) = a.alarms_id)
[TOC00004-END]
|
