Erstellen von Histogrammen über die Laufzeit von SQL Statements

Um einen ersten Überblick um die Laufzeit von SQL Statements, genauer gesagt über die Elapsed_time pro Execution, zu bekommen, nutze ich folgende kleines SQL Abfrage. Dadurch sind zum einen Ausreißer schnell ersichtlich und zum anderen bekommt man einer fremden DB schnell einen Überblick, welche DB User die größten Laufzeiten haben.
Es ist natürlich auch klar, dass dies nur einen Überblick verschafft. Wenn die RunTime (RT) = Elapsed_time / Executions hoch ist, wird in dieser Übersicht natürlich nicht sofort ersichtlich, wo die Zeit wirklich verbraucht wird. Einzig der durchschnittliche CPU Verbrauch gibt ein Indiz dafür, wieviel für die Wartezeit verbraucht wurde, z.B. auf IO Wait, oder Cluster Wait, oder …
Dieses muss dann in weiteren, detaillierten Abfragen genauer untersucht werden.

Auch habe ich hier bewusst auf die SQL_ID verzichtet, um bei gleichen SQL mit nur unterschiedlichen Werten (anstelle von Binds) nicht unzählig viele Zeilen zu bekommen. Deshalb habe ich stellvertretend dazu die ersten 20 Zeichen des SQL-Textes eingesetzt.

Wenn man die Abfrage auf ein paar Schema einschränken kann, wird natürlich die Ergebnismenge kleiner und aussagekräftiger, der erste Teile DATA des With-Clause dafür um so performanter:

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
WITH 
  DATA AS (SELECT Q.DBID
                 , Q.INSTANCE_NUMBER                                AS INST_ID
                 , Q.SNAP_ID                                        AS SNAP_ID
                 , S.BEGIN_INTERVAL_TIME                            AS STARTTIME
                 , Q.PARSING_SCHEMA_NAME                            AS USRNAME
                 , CASE WHEN T.COMMAND_TYPE = 1   THEN 'CRT.TAB'
                        WHEN T.COMMAND_TYPE = 2   THEN 'INSERT'
                        WHEN T.COMMAND_TYPE = 3   THEN 'SELECT'
                        WHEN T.COMMAND_TYPE = 6   THEN 'UPDATE'
                        WHEN T.COMMAND_TYPE = 7   THEN 'DELETE'
                        WHEN T.COMMAND_TYPE = 26  THEN 'LOCK TAB'
                        WHEN T.COMMAND_TYPE = 47  THEN 'PL/SQL'
                        WHEN T.COMMAND_TYPE = 48  THEN 'ISO.LVL'
                        WHEN T.COMMAND_TYPE = 170 THEN 'CALL'
                        WHEN T.COMMAND_TYPE = 189 THEN 'MERGE'
                        ELSE 'XX-'||to_char(T.COMMAND_TYPE) END     AS SQLTYPE
                 , UPPER( CAST(substr(T.SQL_TEXT,1, 40) 
                                            AS VARCHAR2(40) ) )     AS SQLTEXT
                 , round(Q.ELAPSED_TIME_DELTA/1000/Q.EXECUTIONS_DELTA) RT
                 , round(Q.CPU_TIME_DELTA/1000/Q.EXECUTIONS_DELTA)  AS CPU
                 , Q.EXECUTIONS_DELTA           AS EXECS
                 , Q.PARSE_CALLS_DELTA          AS PARSE_CALLS
                 , Q.ROWS_PROCESSED_DELTA       AS ROWS_PROCESSED
                 , Q.FETCHES_DELTA              AS FETCHES
           FROM  dba_hist_sqlstat q
                 , dba_hist_snapshot s
                 , dba_hist_sqltext t
           WHERE 1=1
           AND   Q.PARSING_SCHEMA_NAME NOT IN ('DBSNMP')
           AND   Q.PARSING_SCHEMA_NAME NOT LIKE 'SYS%'
           AND   Q.EXECUTIONS_DELTA     > 0
           AND   Q.DBID                 = S.DBID
           AND   Q.INSTANCE_NUMBER      = S.INSTANCE_NUMBER
           AND   Q.SNAP_ID              = S.SNAP_ID
           AND   Q.DBID                 = T.DBID
           AND   Q.SQL_ID               = T.SQL_ID
           AND   (S.BEGIN_INTERVAL_TIME >= trunc(SYSTIMESTAMP-7))
  ) ,
  D1 AS (SELECT DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
                , COUNT(*) H1
           FROM DATA
           WHERE RT <= 1
           GROUP BY DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
  ) ,
  D10 AS (SELECT DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
                , COUNT(*) H10
           FROM DATA
           WHERE RT <= 10
           AND   RT > 1
           GROUP BY DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
  ) ,
  D50 AS (SELECT DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
                , COUNT(*) H50
           FROM DATA
           WHERE RT <= 50
           AND   RT > 10
           GROUP BY DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
  ) ,
  D100 AS (SELECT DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
                , COUNT(*) H100
           FROM DATA
           WHERE RT <= 100
           AND   RT >  50
           GROUP BY DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
  ) ,
  D500 AS (SELECT DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
                , COUNT(*) H500
           FROM DATA
           WHERE RT <= 500
           AND   RT >  100 
           GROUP BY DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
  ) ,
  D1000 AS (SELECT DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
                , COUNT(*) H1000
           FROM DATA
           WHERE RT <= 1000
           AND   RT >  500
           GROUP BY DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
  ) ,
  D5000 AS (SELECT DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , SQLTEXT
                , USRNAME
                , COUNT(*) H5000
           FROM DATA
           WHERE RT <= 5000
           AND   RT >  1000
           GROUP BY DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
  ) ,
  D10000 AS (SELECT DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
                , COUNT(*) H10000
           FROM DATA
           WHERE RT <= 10000
           AND   RT >  5000
           GROUP BY DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
  ) ,
  D50000 AS (SELECT DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
                , COUNT(*) H50000
           FROM DATA
           WHERE RT <= 50000
           AND   RT >  10000
           GROUP BY DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
  ) ,
  DREST AS (SELECT DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
                , COUNT(*) HREST
           FROM DATA
           WHERE RT > 50000
           GROUP BY DBID
                , INST_ID
                , SNAP_ID
                , SQLTYPE
                , USRNAME
                , SQLTEXT
  ) ,
  DALL  AS (SELECT DBID
                , INST_ID
                , SNAP_ID
                , STARTTIME
                , SQLTYPE
                , USRNAME
                , SQLTEXT
                , round( AVG(PARSE_CALLS), 2)    AS AVG_PARSE
                , round( AVG(ROWS_PROCESSED), 2) AS AVG_ROWS
                , round( AVG(FETCHES), 2)        AS AVG_FETCH
                , round( AVG(CPU),2)             AS AVGCPU
                , round( AVG(RT), 2)             AS AVGRT
                , round( MIN(RT), 2)             AS MINRT
                , round( MAX(RT), 2)             AS MAXRT
                , SUM(EXECS)                     AS EXECS
                , COUNT(*)                       AS HALL
            FROM DATA
            GROUP BY DBID
                , INST_ID
                , SNAP_ID
                , STARTTIME
                , SQLTYPE
                , USRNAME
                , SQLTEXT
  )
SELECT
    da.INST_ID
    , da.snap_id
    , da.STARTTIME
    , da.USRNAME
    , da.SQLTYPE
    , da.SQLTEXT
    , da.HALL       "ALL"
    , da.Execs      "Execs"
    , da.AVG_PARSE  "AvgParses"
    , da.AVG_ROWS   "AvgRows"
    , da.AVG_FETCH  "AvgFetches"
    , da.avgcpu     "AvgCPU(ms)"
    , da.MINRT      "Min(ms)"
    , da.AVGRT      "Avg(ms)"
    , da.MAXRT      "Max(ms)"
    , H1            "<=1ms"
    , H10           "<=10ms"
    , H50           "<=50ms"
    , H100          "<=100ms"
    , H500          "<=500ms"
    , H1000         "<=1sec"
    , H5000         "<=5sec"
    , H10000        "<=10sec"
    , H50000        "<=50sec"
    , HREST         "Rest"
FROM DALL       da
    , D1        d1
    , D10       d2
    , D50       d3
    , D100      d4
    , D500      d5
    , D1000     d6
    , D5000     d7
    , D10000    d8
    , D50000    d9
    , DREST     d
WHERE (da.DBID    = d1.DBID (+))
AND   (da.INST_ID = d1.INST_ID (+))
AND   (da.SNAP_ID = d1.SNAP_ID (+))
AND   (da.SQLTYPE = d1.SQLTYPE (+))
AND   (da.USRNAME = d1.USRNAME (+))
AND   (da.SQLTEXT = d1.SQLTEXT (+))
--
AND   (da.DBID    = d2.DBID (+))
AND   (da.INST_ID = d2.INST_ID (+))
AND   (da.SNAP_ID = d2.SNAP_ID (+))
AND   (da.SQLTYPE = d2.SQLTYPE (+))
AND   (da.USRNAME = d2.USRNAME (+))
AND   (da.SQLTEXT = d2.SQLTEXT (+))
--
AND   (da.DBID    = d3.DBID (+))
AND   (da.INST_ID = d3.INST_ID (+))
AND   (da.SNAP_ID = d3.SNAP_ID (+))
AND   (da.SQLTYPE = d3.SQLTYPE (+))
AND   (da.USRNAME = d3.USRNAME (+))
AND   (da.SQLTEXT = d3.SQLTEXT (+))
--
AND   (da.DBID    = d4.DBID (+))
AND   (da.INST_ID = d4.INST_ID (+))
AND   (da.SNAP_ID = d4.SNAP_ID (+))
AND   (da.SQLTYPE = d4.SQLTYPE (+))
AND   (da.USRNAME = d4.USRNAME (+))
AND   (da.SQLTEXT = d4.SQLTEXT (+))
--
AND   (da.DBID    = d5.DBID (+))
AND   (da.INST_ID = d5.INST_ID (+))
AND   (da.SNAP_ID = d5.SNAP_ID (+))
AND   (da.SQLTYPE = d5.SQLTYPE (+))
AND   (da.USRNAME = d5.USRNAME (+))
AND   (da.SQLTEXT = d5.SQLTEXT (+))
--
AND   (da.DBID    = d6.DBID (+))
AND   (da.INST_ID = d6.INST_ID (+))
AND   (da.SNAP_ID = d6.SNAP_ID (+))
AND   (da.SQLTYPE = d6.SQLTYPE (+))
AND   (da.USRNAME = d6.USRNAME (+))
AND   (da.SQLTEXT = d6.SQLTEXT (+))
--
AND   (da.DBID    = d7.DBID (+))
AND   (da.INST_ID = d7.INST_ID (+))
AND   (da.SNAP_ID = d7.SNAP_ID (+))
AND   (da.SQLTYPE = d7.SQLTYPE (+))
AND   (da.USRNAME = d7.USRNAME (+))
AND   (da.SQLTEXT = d7.SQLTEXT (+))
--
AND   (da.DBID    = d8.DBID (+))
AND   (da.INST_ID = d8.INST_ID (+))
AND   (da.SNAP_ID = d8.SNAP_ID (+))
AND   (da.SQLTYPE = d8.SQLTYPE (+))
AND   (da.USRNAME = d8.USRNAME (+))
AND   (da.SQLTEXT = d8.SQLTEXT (+))
--
AND   (da.DBID    = d9.DBID (+))
AND   (da.INST_ID = d9.INST_ID (+))
AND   (da.SNAP_ID = d9.SNAP_ID (+))
AND   (da.SQLTYPE = d9.SQLTYPE (+))
AND   (da.USRNAME = d9.USRNAME (+))
AND   (da.SQLTEXT = d9.SQLTEXT (+))
--
AND   (da.DBID    = d.DBID (+))
AND   (da.INST_ID = d.INST_ID (+))
AND   (da.SNAP_ID = d.SNAP_ID (+))
AND   (da.SQLTYPE = d.SQLTYPE (+))
AND   (da.USRNAME = d.USRNAME (+))
AND   (da.SQLTEXT = d.SQLTEXT (+))
--
ORDER BY da.STARTTIME DESC 
    , da.INST_ID
    , da.USRNAME
    , da.SQLTYPE
    , da.SQLTEXT
    , da.HALL

Ein mögliches Ergebnis kann dann so aussehen:
histogramm_sql_statement

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.