PB Lock sur delete dans une table
Hello,
j'utilise un outil qui s'appelle DBUnit pour nettoyer les données d'une base DB2.
J'ai un problème quand il essaye de faire un delete from UNETABLE ou UNETABLE est une table qui contient environ 400 000 lignes.
Y'a plein de locks qui sont générés de type Row et exclusive et c'est très lent (j'ai laissé tourner le delete pendant 3 heures et c'était pas fini)
Evidemment dans ma boite les DBA sont pas bons et ils ont pas le temps de regarder.
Je tente de monitorer la base avec db2mon et voila ce que donne le snapshot du DATABASE MANAGER (xml)
Code:
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
|
<snapshot time="16/05/2008 09:49:31" type="db and dbm" node="TCP9E3F1" db="BE1D_ITC"><dbm>
<group name="General">
<param name="Committed private Memory (Bytes)" value="8110080"/>
<param name="DB2START timestamp" value="13/05/2008 08:45:24"/>
<param name="Database manager status" value="Active"/>
<param name="Last reset timestamp" value=""/>
<param name="Number of nodes" value="1"/>
<param name="Product name" value="DB2 v8.1.0.136"/>
<param name="Service level" value="s070720 (MI00188)"/>
</group>
<group name="Sort">
<param name="Piped sorts accepted" value="0"/>
<param name="Piped sorts requested" value="0"/>
<param name="Post threshold sorts" value="0"/>
<param name="Total private sort heap allocated" value="10299"/>
<param name="Total sort heap allocated" value="0"/>
</group>
<group name="Hash">
<param name="Hash joins after heap threshold exceeded" value="0"/>
</group>
<group name="Switch">
<param name="N0. BUFFERPOOL" value="0, Time = "/>
<param name="N0. LOCK" value="1, Time = 16/05/2008 09:01:26"/>
<param name="N0. SORT" value="0, Time = "/>
<param name="N0. STATEMENT" value="1, Time = 16/05/2008 09:01:26"/>
<param name="N0. TABLE" value="0, Time = "/>
<param name="N0. TIMESTAMP" value="1, Time = 13/05/2008 08:45:24"/>
<param name="N0. UOW" value="0, Time = "/>
</group>
<group name="Connection">
<param name="Active local databases" value="1"/>
<param name="Local connections" value="0"/>
<param name="Local connections executing in db manager" value="0"/>
<param name="Remote connections executing in db manager" value="1"/>
<param name="Remote connections to db manager" value="2"/>
</group>
<group name="Agent">
<param name="Agents assigned from pool" value="3923"/>
<param name="Agents created from empty pool" value="45"/>
<param name="Agents registered" value="44"/>
<param name="Agents stolen from another application" value="0"/>
<param name="Agents waiting for a token" value="0"/>
<param name="High water mark for agents registered" value="44"/>
<param name="High water mark for agents waiting for a token" value="0"/>
<param name="Idle agents" value="40"/>
<param name="Max agents overflow " value="0"/>
<param name="Maximum coordinating agents" value="43"/>
</group>
<group name="Gateway">
<param name="Current number of gateway connections" value="0"/>
<param name="Gateway connection pool agents stolen" value="0"/>
<param name="Gateway connections waiting for client request" value="0"/>
<param name="Gateway connections waiting for host reply" value="0"/>
<param name="Total number of gateway connections" value="0"/>
</group>
<group name="Memory Pool">
<param name="Database Monitor" value="147456--147456--376832"/>
<param name="Other Memory" value="1409024--1638400--11943936"/>
</group>
</dbm>
<db>
<group name="General">
<param name="Database OS" value="Linux"/>
<param name="Database catalog network node name" value=""/>
<param name="Database catalog node number" value="0"/>
<param name="Database name" value="BE1D_ITC"/>
<param name="Database path" value="/appli/db2/BE1D_ITC/db2inst3/NODE0000/SQL00001/"/>
<param name="Database status" value="0"/>
<param name="First database connect timestamp" value="14/05/2008 17:37:46"/>
<param name="Input database alias" value="BE1D_ITC"/>
<param name="Last backup timestamp" value=""/>
<param name="Last reset timestamp" value=""/>
<param name="Location of database" value="Remote"/>
</group>
<group name="Calculated">
<param name="%, Application section hit ratio" value="99,65"/>
<param name="%, Buffer pool hit ratio" value="N/A"/>
<param name="%, Buffer pool index hit ratio" value="N/A"/>
<param name="%, Catalog cache hit ratio" value="98,13"/>
<param name="%, Package cache hit ratio" value="95,72"/>
</group>
<group name="Application">
<param name="Application connects" value="2158"/>
<param name="Applications connected currently" value="2"/>
<param name="Appls. executing in db manager currently" value="1"/>
<param name="Elapsed execution time" value="0,009766"/>
<param name="High water mark for connections" value="41"/>
<param name="Indoubt transactions" value="0"/>
<param name="Secondary connects total" value="0"/>
</group>
<group name="Lock">
<param name="Deadlocks detected" value="0"/>
<param name="Exclusive lock escalations" value="0"/>
<param name="Lock escalations" value="5"/>
<param name="Lock list memory in use (Bytes)" value="1924720"/>
<param name="Lock timeouts" value="1"/>
<param name="Lock waits" value="0"/>
<param name="Locks currently held" value="24041"/>
<param name="Time database waited on locks (ms)" value="0"/>
</group>
<group name="Sort">
<param name="Sorts currently active" value="0"/>
<param name="Total shared sort heap allocated" value="0"/>
<param name="Total sort heap allocated" value="0"/>
<param name="Total sort overflow" value="3"/>
<param name="Total sort time (ms)" value="0"/>
<param name="Total sorts" value="114"/>
</group>
<group name="Statement">
<param name="Commit statements attempted" value="11255"/>
<param name="DDL statements executed" value="113"/>
<param name="Dynamic statements attempted" value="425337"/>
<param name="Failed statement operations" value="38"/>
<param name="Rollback statements attempted" value="58"/>
<param name="Select SQL statements executed" value="10750"/>
<param name="Static statements attempted" value="13297"/>
<param name="Update/Insert/Delete statements executed" value="378548"/>
</group>
<group name="Rows">
<param name="Binds/precompiles attempted" value="0"/>
<param name="Internal automatic rebinds" value="0"/>
<param name="Internal commits" value="2158"/>
<param name="Internal rollbacks" value="3"/>
<param name="Internal rollbacks due to deadlock" value="2"/>
<param name="Internal rows deleted" value="0"/>
<param name="Internal rows inserted" value="0"/>
<param name="Internal rows updated" value="0"/>
<param name="Rows deleted" value="56733"/>
<param name="Rows inserted" value="373040"/>
<param name="Rows read" value="4333537"/>
<param name="Rows selected" value="845936"/>
<param name="Rows updated" value="24897"/>
</group>
<group name="Buffer pool">
<param name="Asynchronous data read requests" value="0"/>
<param name="Asynchronous index read requests" value="0"/>
<param name="Asynchronous pool data page reads" value="0"/>
<param name="Asynchronous pool data page writes" value="0"/>
<param name="Asynchronous pool index page reads" value="0"/>
<param name="Asynchronous pool index page writes" value="0"/>
<param name="Buffer pool data logical reads" value="0"/>
<param name="Buffer pool data physical reads" value="0"/>
<param name="Buffer pool data writes" value="0"/>
<param name="Buffer pool index logical reads" value="0"/>
<param name="Buffer pool index physical reads" value="0"/>
<param name="Buffer pool index writes" value="0"/>
<param name="Buffer pool temp data logical reads" value="0"/>
<param name="Buffer pool temp data physical reads" value="0"/>
<param name="Buffer pool temp index logical reads" value="0"/>
<param name="Buffer pool temp index physical reads" value="0"/>
<param name="Data pages copied from extended storage" value="0"/>
<param name="Data pages copied to extended storage" value="0"/>
<param name="Database files closed" value="0"/>
<param name="Direct read requests" value="0"/>
<param name="Direct reads" value="0"/>
<param name="Direct reads elapsed time (ms)" value="0"/>
<param name="Direct write requests" value="0"/>
<param name="Direct writes" value="0"/>
<param name="Direct writes elapsed time (ms)" value="0"/>
<param name="Dirty list threshold cleaner triggers" value="0"/>
<param name="Dirty page steal cleaner triggers" value="0"/>
<param name="Index pages copied from extended storage" value="0"/>
<param name="Index pages copied to extended storage" value="0"/>
<param name="LSN Gap cleaner triggers" value="0"/>
<param name="No victim buffers available" value="0"/>
<param name="Shared sort heap high water mark" value="0"/>
<param name="Time waited for prefetch (ms)" value="0"/>
<param name="Total buffer pool read time (ms)" value="0"/>
<param name="Total buffer pool write time (ms)" value="0"/>
<param name="Total elapsed asynchronous read time" value="0"/>
<param name="Total elapsed asynchronous write time" value="0"/>
<param name="Unread prefetch pages" value="0"/>
</group>
<group name="Log">
<param name="Appl id holding the oldest transaction" value="224"/>
<param name="Bytes of log that are held up by minbuf" value="49196548"/>
<param name="Bytes of log that need to be redone during recovery" value="49196548"/>
<param name="High water mark for database heap" value="1462965"/>
<param name="Log data found in buffer" value="282366"/>
<param name="Log file archive" value="4294967295"/>
<param name="Log file number, current" value="1760"/>
<param name="Log file number, first" value="1758"/>
<param name="Log file number, last" value="1763"/>
<param name="Log pages read" value="5864"/>
<param name="Log pages written" value="31646"/>
<param name="Log read time (sec.ns)" value="0,034408"/>
<param name="Log space available" value="-1"/>
<param name="Log space used" value="32855918"/>
<param name="Log write time (sec.ns)" value="109,286904"/>
<param name="Maximum secondary log space used" value="0"/>
<param name="Maximum total log space used" value="32855918"/>
<param name="Number log buffer full" value="0"/>
<param name="Number of secondary logs allocated" value="0"/>
<param name="Number partial page log IOs" value="5327"/>
<param name="Number read log IOs" value="733"/>
<param name="Number write log IOs" value="17857"/>
</group>
<group name="Package cache">
<param name="Application section lookups" value="425397"/>
<param name="Application sections inserted" value="1475"/>
<param name="Package cache high water mark (Bytes) " value="2457706"/>
<param name="Package cache inserts" value="891"/>
<param name="Package cache lookups" value="20839"/>
<param name="Package cache overflows" value="0"/>
</group>
<group name="Catalog cache">
<param name="Catalog cache heap full" value="0"/>
<param name="Catalog cache high water mark" value="0"/>
<param name="Catalog cache inserts" value="203"/>
<param name="Catalog cache lookups" value="10850"/>
<param name="Catalog cache overflows" value="0"/>
</group>
<group name="Hash">
<param name="Hash join loops" value="0"/>
<param name="Hash join number" value="27"/>
<param name="Hash join overflows" value="0"/>
<param name="Hash joins (small) overflows" value="0"/>
</group>
<group name="Workspace">
<param name="Corresponding private overflows" value="0"/>
<param name="Corresponding shared overflows" value="0"/>
<param name="Private high water mark" value="385176"/>
<param name="Shared high water mark" value="0"/>
<param name="Total private section inserts" value="1475"/>
<param name="Total private section lookups" value="16646"/>
<param name="Total shared section inserts" value="0"/>
<param name="Total shared section lookups" value="0"/>
</group>
<group name="Agent">
<param name="Agents associated with applications" value="2"/>
<param name="Agents currently waiting on locks" value="0"/>
<param name="Maximum agents associated with applications" value="41"/>
<param name="Maximum coordinating agents" value="41"/>
</group>
<group name="Memory Pool">
<param name="Backup/Restore/Util" value="16384--32768--20496384"/>
<param name="Buffer Pool" value="147456--147456--4186963968"/>
<param name="Buffer Pool" value="168869888--168869888--4186963968"/>
<param name="Buffer Pool" value="212992--212992--4186963968"/>
<param name="Buffer Pool" value="344064--344064--4186963968"/>
<param name="Buffer Pool" value="606208--606208--4186963968"/>
<param name="Catalog Cache" value="1376256--1376256--4186963968"/>
<param name="Database" value="11911168--11911168--21774336"/>
<param name="Lock Manager" value="21200896--21200896--21217280"/>
<param name="Other Memory" value="0--0--12353536"/>
<param name="Package Cache" value="2621440--2621440--4186963968"/>
</group>
<group name="Ungrouped">
<param name="849" value="0"/>
<param name="850" value="0"/>
</group>
</db>
</snapshot> |
Est ce normal qu'il y ait autant de locks quand on fait le delete sur la table ?
Est ce qu'il faut regarder la taille des logs ?
Merci de votre aide car je ne suis pas assez expérimenté en DB2.
