Start- und Endezeitpunkt von Testintervallen

Veröffentlicht von

2

Mit Hilfe des folgenden SQL Fragments lassen sich gut die jeweiligen Anfangs- und Ende SNAP_ID aus den AWR Views DBA_HIST_SNAPSHOT auslesen, um dann mit diesen andere Views zu joinen. Auf diese Weise lassen sich leicht einzelne Werte eines AWR Reports berechnen – jeweils passend zu den spezifizierten Testzeiträumen:

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

WITH
  SNAPID AS (
    SELECT /*RH*/
        s.SNAP_ID                   AS SNAP_ID
        , s.DBID                    AS DBID
        , s.instance_number         AS inst_id
        , s.endtime                 AS SNAP_TIME
        , trunc(s.STIME )           AS TESTDATE
        , MIN(s.SNAP_ID)            AS MIN_SNAP_ID
    FROM   (SELECT s.snap_id
                   , s.dbid
                   , S.INSTANCE_NUMBER
                   , to_char(S.END_INTERVAL_TIME,
                                'YYYY-MM-DD HH24:MI')   AS ENDTIME
                   , s.END_INTERVAL_TIME                AS STIME
            FROM   dba_hist_snapshot s ) s
    WHERE (s.STARTTIME >= '2014-04-29 12:00' AND s.ENDTIME <= '2014-04-29 16:40')
      OR  (s.STARTTIME >= '2014-05-05 13:20' AND s.ENDTIME <= '2014-05-05 20:40')
      OR  (s.STARTTIME >= '2014-05-07 12:00' AND s.ENDTIME <= '2014-05-07 14:20')
      OR  (s.STARTTIME >= '2014-05-09 14:40' AND s.ENDTIME <= '2014-05-09 22:20')
      OR  (s.STARTTIME >= '2014-05-10 14:20' AND s.ENDTIME <= '2014-05-11 07:20')
    GROUP BY  s.SNAP_ID
            , s.DBID
            , s.instance_number
            , s.endtime
            , trunc( STIME )
    ORDER BY s.snap_id DESC
    ) ,
  SNAPDATES AS (
     SELECT /*RH*/
         s.snap_time
         , s.testdate
         , s.snap_id
         , s.dbid
         , s.MIN_SNAP_ID
         , s.snap_id - LAG(s.snap_id, 1, 0) OVER (ORDER BY s.snap_id)   AS DIFF_SNAP_ID
         , s.snap_id - LEAD(s.snap_id, 1, 0) OVER (ORDER BY s.snap_id)  AS DIFF_SNAP_ID_2
     FROM (SELECT DISTINCT
                     s.snap_id
                     , s.snap_time
                     , s.testdate
                     , s.dbid
                     , s.MIN_SNAP_ID
           FROM SNAPID s ) s
     ) ,
   SNAPSTARTEND AS (
     SELECT /*RH*/
            s.RNUM                                           AS TESTNUM
            , s.TESTDATE                                     AS TESTDATE
            , s.SNAP_STARTID                                 AS SNAP_STARTID
            , e.SNAP_ENDID                                   AS SNAP_ENDID
            , s.DBID                                         AS DBID
            , i.INST_ID                                      AS INST_ID
            , s.MIN_SNAP_ID                                  AS MIN_SNAP_ID
            , s.SNAP_STARTTIME                               AS SNAP_STARTTIME
            , e.SNAP_ENDTIME                                 AS SNAP_ENDTIME
            , s.SDATE                                        AS STARTDATE
            , e.EDATE                                        AS ENDDATE
            , trunc( e.EDATE-s.SDATE) * 24 * 60
                  + trunc( MOD(
                     (e.EDATE-s.SDATE)*24, 24 ) ) * 60
                  + trunc( MOD(
                     (e.EDATE-s.SDATE)*24*60, 60 ) )         AS DURATION
            , trunc( e.EDATE-s.SDATE)                        AS DUR_DAYS
            , trunc( MOD( (e.EDATE-s.SDATE)*24, 24 ) )       AS DUR_HOURS
            , trunc( MOD( (e.EDATE-s.SDATE)*24*60, 60 ) )    AS DUR_MINS
     FROM (SELECT s.SNAP_TIME                     AS SNAP_STARTTIME
                  , to_date( s.SNAP_TIME,
                          'YYYY-MM-DD HH24:MI')   AS SDATE
                  , s.TESTDATE                    AS TESTDATE
                  , s.SNAP_ID                     AS SNAP_STARTID
                  , s.DBID                        AS DBID
                  , s.MIN_SNAP_ID                 AS MIN_SNAP_ID
                  , rownum                        AS RNUM
             FROM (SELECT s.SNAP_TIME
                          , s.TESTDATE
                          , s.SNAP_ID
                          , s.DBID
                          , MIN_SNAP_ID
                   FROM  SNAPDATES s
                   WHERE (s.DIFF_SNAP_ID > 1)) s) s ,
          (SELECT e.SNAP_TIME   AS SNAP_ENDTIME
                  , to_date( e.SNAP_TIME,
                            'YYYY-MM-DD HH24:MI')   AS EDATE
                  , e.SNAP_ID                       AS SNAP_ENDID
                  , rownum                          AS RNUM
           FROM (SELECT s.SNAP_TIME
                        , s.SNAP_ID
                 FROM(SELECT s.SNAP_TIME
                             , s.SNAP_ID
                             , s.DIFF_SNAP_ID_2
                      FROM  SNAPDATES s) s
                      WHERE (s.DIFF_SNAP_ID_2 <> -1)) e) e,
          (SELECT DISTINCT
                 I.INST_ID
           FROM gv$instance i) i
    WHERE (s.rnum = e.rnum)
    ORDER BY s.RNUM
            , i.INST_ID
    )

ein SQL der Form

1
2
3
4
5
6
7
8
9
10

WITH 
  (
  .
  . -- siehe oben
  . 
  ) ,
SELECT /*RH*/
     *
FROM SNAPSTARTEND s
;

liefert als Ausgabe dann folgendes:

AWR_STARTENDE

Um nun z.B. die DB Time als auch die SQL Time für die jeweiligen Testintervalle zu berechnen,
ist das SQL wie folgt zu erweitern:

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

WITH 
  (
  .
  . -- siehe oben
  . 
  ) ,
  SERVICETM AS (
    SELECT  st.DBID                                 AS DBID
        , st.SNAP_ID                                AS SNAP_ID
        , st.INSTANCE_NUMBER                        AS INST_ID
        , round( SUM( CASE WHEN st.stat_name LIKE 'DB CPU' 
                    THEN st.value END)/1000 )       AS CPU
        , round( SUM( CASE WHEN st.stat_name LIKE 'DB time' 
                   THEN st.value END)/1000 )        AS DB_TIME
        , round( SUM( CASE WHEN st.stat_name LIKE 'sql execute elapsed time' 
                   THEN st.value END)/1000 )        AS SQL_TIME
        FROM    dba_hist_sys_time_model st
    GROUP BY  st.DBID
        , st.SNAP_ID
        , st.INSTANCE_NUMBER
    ) ,
  DATA AS (
    SELECT SNAP_TIME
        , INST_ID
        , snap_id
        , DURATION
        , round(DB_TIME/1000/60,2)        AS DB_TIME
        , round(SQL_TIME/1000/60,2)       AS SQL_TIME
    FROM (SELECT  
            s.SNAP_STARTTIME              AS SNAP_TIME
            , s.SNAP_STARTID              AS SNAP_ID
            , s.INST_ID
            , s.MIN_SNAP_ID
            , s.DURATION
            , t2.DB_TIME - t1.DB_TIME     AS DB_TIME
            , t2.SQL_TIME - t1.SQL_TIME   AS SQL_TIME
        FROM  SERVICETM t1   
            , SERVICETM t2  
            , SNAPSTARTEND s
        WHERE  1=1
        AND    t1.snap_id = s.SNAP_STARTID
        AND    t1.dbid    = s.dbid
        AND    t1.inst_id = s.inst_id
        AND    t2.snap_id = s.SNAP_ENDID
        AND    t2.dbid    = s.dbid
        AND    t2.inst_id = s.inst_id
        ORDER BY s.SNAP_STARTID
        )
    ORDER BY SNAP_TIME DESC,
            INST_ID
    )
SELECT /*RH*/
     *
FROM DATA  s
;

2 thoughts on “Start- und Endezeitpunkt von Testintervallen”

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.