AAS und andere Key Performance Indikatoren

Veröffentlicht von

4

Das folgende SQL berechnet einige Key performance Indicators einer DB – auf Basis der AWR History Views. Speziell wird hier für Jeden Snapshot von AWR die zugehörige Elapsed Time, DB Time, CPU Time und die AAS = Average Active Sessions berechnet. AAS eignet sich sehr als erster Indikator die Performance der Datenbank für einzelne Snapshots schnell auszuschliessen und auch schnell Kandidaten zu finden, die auf einen Engpass innerhalb der DB hinweisen.

Die AAS berechnet sich als DB_TIME / Elapsed_Time.
Ist diese deutlich kleiner 1 (nahe 0), kann man einen Engpass auf DB – Ebene ausschliessen. Liegt diese im Bereich von 1 bis Anzahl Cores, können einzelne Statements oder Session Probleme mit der Performance haben. Die Gesamtperformance liegt aber noch im vertretbaren Bereich. Je näher die AAS sich dem Wert von NUM_CORES nähert oder diesen sogar überschreitet, um zu sicherer ist von einem Engpass auf DB Seite auszugehen.

Daneben werden noch eine Reihe andere KPI (Key performance Indikatoren) berechnet, um gleich ein besseren Überblick zu haben. So wird neben der DB – Time auch die CPU Time berechnet, um zu sehen, wieviel der Zeit in DB Time auf Warten oder auf Nutzung der CPU fällt. Je kleiner die CPU Time neben hoher DB Time, um so höher das Potential an Tuningmaßnahmen zur Steigerung der Gesamtperformance der DB.

Desweitern stellt die COMMIT Time als Durchschnitt der Wartezeit des Wait-Events „log file sync“ pro Snapshot ein Indikator für durchschnittliche Dauer eines COMMIT. Diese Zeit kann durch Trigger oder andere synchrone Operationen wie z.B. der Refresh einer Mat. View (bei REFRESH on COMMIT) deutlich mit den Anzahl der DML Operationen ansteigen.

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

WITH 
  BASISDATA AS (
    SELECT S.SNAP_ID                                    AS SNAP_ID
        , S.DBID                                        AS DBID
        , S.INSTANCE_NUMBER                             AS INST_ID
        , S.BEGIN_INTERVAL_TIME                         AS STARTTIME
        , S.END_INTERVAL_TIME                           AS ENDTIME
        , round(t.value/1000000/60, 2)                  AS TVALUE
        , CASE WHEN t.stat_name = 'DB time' THEN 'DB'
               WHEN t.stat_name = 'DB CPU'  THEN 'CPU'
               WHEN t.stat_name = 'PL/SQL execution elapsed time'  THEN 'PL/SQL'
               WHEN t.stat_name = 'RMAN cpu time (backup/restore)'  THEN 'RMAN'
               WHEN t.stat_name = 'sql execute elapsed time'  THEN 'SQL' 
                                                   END  AS TTYPE
        , oc.value                                      AS CORES
        , round( oi.value / (oi.value+ob.value) , 2)    AS IDLE
    FROM dba_hist_osstat oc
        , dba_hist_osstat oi
        , dba_hist_osstat ob
        , (SELECT * 
           FROM dba_hist_snapshot s
           WHERE S.BEGIN_INTERVAL_TIME >= trunc(SYSTIMESTAMP-7) ) s
        , dba_hist_sys_time_model t
    WHERE OC.STAT_NAME='NUM_CPUS'
    AND OC.DBID = S.DBID
    AND OC.SNAP_ID = S.SNAP_ID
    AND OC.INSTANCE_NUMBER = S.INSTANCE_NUMBER
    AND OI.STAT_NAME = 'IDLE_TIME'
    AND OI.DBID = S.DBID
    AND OI.SNAP_ID = S.SNAP_ID
    AND OI.INSTANCE_NUMBER = S.INSTANCE_NUMBER
    AND OB.STAT_NAME = 'BUSY_TIME'
    AND OB.DBID = S.DBID
    AND OB.SNAP_ID = S.SNAP_ID
    AND OB.INSTANCE_NUMBER = S.INSTANCE_NUMBER
    AND oi.value + ob.value > 0
    AND T.DBID = S.DBID
    AND T.SNAP_ID = S.SNAP_ID
    AND T.INSTANCE_NUMBER = S.INSTANCE_NUMBER
    AND t.stat_name IN ('DB time', 'DB CPU'
                        , 'PL/SQL execution elapsed time'
                        , 'sql execute elapsed time'
                        , 'RMAN cpu time (backup/restore)')
  ) , 
  COMMITTM AS (
    SELECT  se.DBID                                          AS DBID
        , se.SNAP_ID                                         AS SNAP_ID
        , se.INSTANCE_NUMBER                                 AS INST_ID
        , round( SUM(se.TIME_WAITED_MICRO)/1000/1000/60,2 )  AS COMMIT_TIME
    FROM    dba_hist_system_event se
    WHERE   se.EVENT_NAME = 'log file sync'
    GROUP BY  se.DBID
        , se.SNAP_ID
        , se.INSTANCE_NUMBER
    ) ,
  TXNUM AS (
    SELECT  ss.DBID                         AS DBID
        , ss.SNAP_ID                        AS SNAP_ID
        , ss.INSTANCE_NUMBER                AS INST_ID
        , SUM(ss.VALUE)                     AS TXNUMS
    FROM    dba_hist_sysstat ss
    WHERE   ss.stat_name IN ('user commits', 'user rollbacks')
    GROUP BY  ss.DBID
        , ss.SNAP_ID
        , ss.INSTANCE_NUMBER
    ) ,
  WAITTM AS (
    SELECT  se.DBID                                          AS DBID
        , se.SNAP_ID                                         AS SNAP_ID
        , se.INSTANCE_NUMBER                                 AS INST_ID
        , round( SUM(se.TIME_WAITED_MICRO)/1000/1000/60,2 )  AS WAIT_TIME
    FROM    dba_hist_system_event se
    WHERE   se.wait_class IN ('User I/O'
                            , 'System I/O'
                            , 'Commit'
                            , 'Administrative')
    GROUP BY  se.DBID
        , se.SNAP_ID
        , se.INSTANCE_NUMBER
    ) ,
  ADDVAL AS (
    SELECT  st.DBID                          AS DBID
        , st.SNAP_ID                         AS SNAP_ID
        , st.INSTANCE_NUMBER                 AS INST_ID
        , round( avg( CASE WHEN st.metric_name LIKE 'Redo Generated Per Sec' 
                    THEN st.value END),2 )   AS REDO
        , round( avg( CASE WHEN st.metric_name LIKE 'Average Active Sessions' 
                   THEN st.value END),2 )    AS AAS
        , round( avg( CASE WHEN st.metric_name LIKE 'Logical Reads Per Txn' 
                   THEN st.value END),2 )    AS LOGREADS_TXN
        , round( avg( CASE WHEN st.metric_name LIKE 'Physical Reads Per Txn' 
                   THEN st.value END),2 )    AS READS_TXN
        , round( avg( CASE WHEN st.metric_name LIKE 'Physical Reads Per Sec' 
                   THEN st.value END),2 )    AS READS_SEC
        , round( avg( CASE WHEN st.metric_name LIKE 'Physical Writes Per Sec' 
                   THEN st.value END),2 )    AS WRITES_SEC
        , round( avg( CASE WHEN st.metric_name LIKE 'SQL Service Response Time' 
                   THEN st.value END),2 )    AS RESP_SQL_SERVTIME
        , round( avg( CASE WHEN st.metric_name LIKE 'Response Time Per Txn' 
                   THEN st.value END),2 )    AS RESP_TIME_TXN
        , round( avg( CASE WHEN st.metric_name LIKE 'Executions Per Sec' 
                   THEN st.value END),2 )    AS EXECS_PER_SEC
        , round( avg( CASE WHEN st.metric_name LIKE 'User Calls Per Sec' 
                   THEN st.value END),2 )    AS CALLS_PER_SEC
        FROM    dba_hist_sysmetric_history st
    GROUP BY  st.DBID
        , st.SNAP_ID
        , st.INSTANCE_NUMBER
    ) ,
  DATA AS (
    SELECT d1.INST_ID                    AS INST_ID
        , d1.STARTTIME                   AS STARTTIME
        , d1.ENDTIME                     AS ENDTIME
        , d1.TVALUE                      AS DB_TIME
        , d2.TVALUE                      AS CPU_TIME
        , d3.TVALUE                      AS PLSQL_TIME
        , d4.TVALUE                      AS RMAN_TIME
        , d5.TVALUE                      AS SQL_TIME
        , d1.cores                       AS CORES
        , d1.idle                        AS IDLE
        , w.WAIT_TIME                    AS WAIT_TIME
        , c.COMMIT_TIME                  AS COMMIT_TIME
        , t.TXNUMS                       AS TX_NUMS
        , a.REDO                         AS REDO
        , a.AAS                          AS AAS
        , a.LOGREADS_TXN                 AS LREADS_TXN
        , a.READS_TXN                    AS READS_TXN
        , a.READS_SEC                    AS READS_SEC
        , a.WRITES_SEC                   AS WRITES_SEC
        , a.RESP_SQL_SERVTIME            AS RESP_SQL_SERVTIME
        , a.RESP_TIME_TXN                AS RESP_TIME_TXN
        , a.EXECS_PER_SEC                AS EXECS_PER_SEC
        , a.CALLS_PER_SEC                AS CALLS_PER_SEC
    FROM BASISDATA d1
        , BASISDATA d2
        , BASISDATA d3
        , BASISDATA d4
        , BASISDATA d5
        , WAITTM w
        , COMMITTM c
        , TXNUM t
        , ADDVAL a
    WHERE (d1.DBID      = d2.DBID)
    AND   (d1.SNAP_ID   = d2.SNAP_ID)
    AND   (d1.INST_ID   = d2.INST_ID)
    AND   (d1.STARTTIME = d2.STARTTIME)
    AND   (d1.DBID      = d3.DBID)
    AND   (d1.SNAP_ID   = d3.SNAP_ID)
    AND   (d1.INST_ID   = d3.INST_ID)
    AND   (d1.STARTTIME = d3.STARTTIME)
    AND   (d1.DBID      = d4.DBID)
    AND   (d1.SNAP_ID   = d4.SNAP_ID)
    AND   (d1.INST_ID   = d4.INST_ID)
    AND   (d1.STARTTIME = d4.STARTTIME)
    AND   (d1.DBID      = d5.DBID)
    AND   (d1.SNAP_ID   = d5.SNAP_ID)
    AND   (d1.INST_ID   = d5.INST_ID)
    AND   (d1.STARTTIME = d5.STARTTIME)
    AND   (d1.DBID      = w.DBID)
    AND   (d1.SNAP_ID   = w.SNAP_ID)
    AND   (d1.INST_ID   = w.INST_ID)
    AND   (d1.DBID      = c.DBID)
    AND   (d1.SNAP_ID   = c.SNAP_ID)
    AND   (d1.INST_ID   = c.INST_ID)
    AND   (d1.DBID      = t.DBID)
    AND   (d1.SNAP_ID   = t.SNAP_ID)
    AND   (d1.INST_ID   = t.INST_ID)
    AND   (d1.DBID      = a.DBID)
    AND   (d1.SNAP_ID   = a.SNAP_ID)
    AND   (d1.INST_ID   = a.INST_ID)
    AND   (d1.TTYPE = 'DB')
    AND   (d2.TTYPE = 'CPU')
    AND   (d3.TTYPE = 'PL/SQL')
    AND   (d4.TTYPE = 'RMAN')
    AND   (d5.TTYPE = 'SQL')
  ) , 
  DIFF AS (
    SELECT inst_id
        , starttime
        , CAST(starttime AS DATE)                       AS sdate
        , endtime
        , CAST(endtime AS DATE)                         AS edate
        , trunc( CAST(endtime AS DATE)-CAST(starttime AS DATE)) * 24 * 60  
             + trunc( MOD( 
                (CAST(endtime AS DATE)-CAST(starttime AS DATE))*24, 24 ) ) * 60
             + trunc( MOD( 
                (CAST(endtime AS DATE)-CAST(starttime AS DATE))*24*60, 60 ) ) 
                                                        AS DURATION
        , cores
        , IDLE                                          AS IDLE
        , db_time                                       AS db_time_2
        , Lag(db_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS db_time_1  
        , db_time - Lag(db_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS DBTIME
        , cpu_time                                      AS cpu_time_2
        , Lag(cpu_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS cpu_time_1  
        , cpu_time - Lag(cpu_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS CPUTIME
        , plsql_time                                    AS plsql_time_2
        , Lag(plsql_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS plsql_time_1  
        , plsql_time - Lag(plsql_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS PLSQLTIME             
        , rman_time                                     AS rman_time_2
        , Lag(rman_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS rman_time_1  
        , rman_time - Lag(rman_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS RMANTIME    
 
        , sql_time                                      AS sql_time_2
        , Lag(sql_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS sql_time_1  
        , sql_time - Lag(sql_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS SQLTIME
 
        , wait_time                                     AS wait_time_2
        , Lag(wait_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS wait_time_1  
        , wait_time - Lag(wait_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS WAITTIME               
        , commit_time                                   AS commit_time_2
        , Lag(commit_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS commit_time_1  
        , commit_time - Lag(commit_time, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS COMMITTIME
        , tx_nums                                       AS txnum_2
        , Lag(tx_nums, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS txnum_1  
        , tx_nums - Lag(tx_nums, 1, 0) 
                OVER (ORDER BY inst_id, starttime)      AS TXNUMs
        , REDO                                          AS REDO
        , AAS                                           AS AAS
        , LREADS_TXN                                    AS LREADS_TXN
        , READS_TXN                                     AS READS_TXN
        , READS_SEC                                     AS READS_SEC
        , WRITES_SEC                                    AS WRITES_SEC
        , RESP_SQL_SERVTIME                             AS RESP_SQL_SERVTIME
        , RESP_TIME_TXN                                 AS RESP_TIME_TXN
        , EXECS_PER_SEC                                 AS EXECS_PER_SEC
        , CALLS_PER_SEC                                 AS CALLS_PER_SEC
    FROM DATA 
), VAL AS (
    SELECT inst_id
        , starttime
        , sdate
        , endtime
        , DURATION
        , IDLE
        , DBTIME
        , CPUTIME
        , PLSQLTIME
        , RMANTIME
        , SQLTIME
        , WAITTIME
        , COMMITTIME
        , TXNUMS
        , round ( DBTIME / Duration, 2)               AS AAS2
        , cores
        , REDO                                        AS REDO
        , AAS                                         AS AAS
        , LREADS_TXN                                  AS LREADS_TXN
        , READS_TXN                                   AS READS_TXN
        , READS_SEC                                   AS READS_SEC
        , WRITES_SEC                                  AS WRITES_SEC
        , round(RESP_SQL_SERVTIME / 100,2)            AS RESP_SQL_SERVTIME
        , round(RESP_TIME_TXN / 100,2)                AS RESP_TIME_TXN
        , EXECS_PER_SEC                               AS EXECS_PER_SEC
        , CALLS_PER_SEC                               AS CALLS_PER_SEC
    FROM DIFF
    WHERE db_time_1 > 0
    AND   db_time_2 - db_time_1 > 0
    AND   cpu_time_1 > 0
    AND   cpu_time_2 - cpu_time_1 > 0
  )
SELECT inst_id
    , sdate                                       AS "Startdate"
    , duration                                    AS "Ela(min)"
    , Cores
    , idle
    , AAS                                         AS AAS
    , CASE WHEN aas > 0.5                        THEN '1 - GREEN'
           WHEN aas >= 0.5     AND aas <= 1      THEN '2 - green'
           WHEN aas > 1        AND aas < cores/2 THEN '3 - yellow'
           WHEN aas >= cores/2 AND aas <= cores  THEN '4 - YELLOW'
           ELSE '6 - RED' END                     AS "PerfInd"
    , dbtime                                      AS "DB(min)"
    , cputime                                     AS "CPU(min)"
    , committime                                  AS "COMMIT(min)"
    , TXNUMS                                      AS "Num TX"
    , plsqltime                                   AS "PL/SQL(min)"
    , sqltime                                     AS "SQL(min)"
    , rmantime                                    AS "RMAN(min)"
    , waittime                                    AS "WAIT(min)"
    , REDO                                        AS "REDO (B/s)"
    , LREADS_TXN                                  AS "LOG.READS/Tx"
    , READS_TXN                                   AS "PHY.READS/Tx"
    , READS_SEC                                   AS "PHY.READS/s"
    , WRITES_SEC                                  AS "PHY.WRITES/s"
    , RESP_SQL_SERVTIME                           AS "SQL RESP/Call(s)"
    , RESP_TIME_TXN                               AS "RESPTIME/Tx(s)"
    , EXECS_PER_SEC                               AS "EXECS(s)"
    , CALLS_PER_SEC                               AS "CALLS(s)"
FROM VAL
ORDER BY 2 DESC,1
;

4 thoughts on “AAS und andere Key Performance Indikatoren”

  1. Thanks but this is what we are trying to understand

    we want to find out the concurrent sessions for a snap id (avg sessions)
    Since we have a value of 2.4 so still its tough to see what is 2.4 value refering to
    Total sessions – 1300 so at that particular snap do we have 2.4% of 1300 or something else – if you can confirm that would be great.

    Antworten

  2. What is AAS here i have a value of 2.4 does that mean 2.4% of total sessions , how do i interprest the concurrent sessions at that snap time

    Antworten

    • Hi Vinary,

      as described in Section two, AAS = DB_TIME / Elapsed_Time.
      If the Value is euql or larger than 1 you can / will have some session having issues where the performance. AAS should be low, e.g less than 0.2

      HTH

      regards
      Rainer

      Antworten

Schreibe einen Kommentar

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

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.