Durante uma análise de performance, uma prática comum é comparar dois ou mais relatórios AWR de períodos diferentes, geralmente um do período considerado “bom” e outro do do período considerado “ruim”. Porém, quando ainda não se tem essa definição de quando era bom e quando começou ficar ruim, uma alternativa é olhar para o comportamento do banco de forma mais abrangente, na tentativa de identificar algum padrão.
Recentemente comecei a montar algumas queries para consultar métricas direto do AWR e retornar um resultado em formato amigável, que permita ter uma visão clara da variação do comportamento durante o tempo.
Neste post disponibilo uma query para consultar especificar o DB Time, mas que pode ser adaptada para consultar outras métricas da DBA_HIST_SYS_TIME_MODEL (por exemplo, tenho uma adaptada para consultar CPU Time).
Script dbtime.sql disponível aqui.
Query:
/*
Script para gerar uma matriz com o DB Time da instancia por dia e hora
Atualizado aqui: https://github.com/maiconcarneiro/mcsqlkit/blob/main/dbtime.sql
Sintaxe: SQL>@dbtime <Qtd. Dias> <Inst ID> (Onde Inst ID = 0 soma todas as instancias do cluster)
Exemplo: SQL>@dbtime 30 1
Maicon Carneiro | Salvador-BA, 11/11/2022
*/
set verify off
set feedback off
alter session set nls_date_format='dd/mm Dy';
set sqlformat
set pages 999 lines 400
col snap_date heading "Date" format a10
define COL_NUM_FORMAT='99,999' -- define the format used in numeric columns
col h0 format &&COL_NUM_FORMAT
col h1 format &&COL_NUM_FORMAT
col h2 format &&COL_NUM_FORMAT
col h3 format &&COL_NUM_FORMAT
col h4 format &&COL_NUM_FORMAT
col h5 format &&COL_NUM_FORMAT
col h6 format &&COL_NUM_FORMAT
col h7 format &&COL_NUM_FORMAT
col h8 format &&COL_NUM_FORMAT
col h9 format &&COL_NUM_FORMAT
col h10 format &&COL_NUM_FORMAT
col h11 format &&COL_NUM_FORMAT
col h12 format &&COL_NUM_FORMAT
col h13 format &&COL_NUM_FORMAT
col h14 format &&COL_NUM_FORMAT
col h15 format &&COL_NUM_FORMAT
col h16 format &&COL_NUM_FORMAT
col h17 format &&COL_NUM_FORMAT
col h18 format &&COL_NUM_FORMAT
col h19 format &&COL_NUM_FORMAT
col h20 format &&COL_NUM_FORMAT
col h21 format &&COL_NUM_FORMAT
col h22 format &&COL_NUM_FORMAT
col h23 format &&COL_NUM_FORMAT
set feedback ON
-- obtem o nome da instancia
column NODE new_value VNODE
SET termout off
SELECT CASE WHEN &2 = 0 THEN 'Cluster' ELSE instance_name || ' / ' || host_name END AS NODE FROM GV$INSTANCE WHERE (&2 = 0 or inst_id = &2);
SET termout ON
-- resumo do relatorio
PROMP
PROMP Metrica...: DB Time
PROMP Qt. Dias..: &1
PROMP Instance..: &VNODE
PROMP
PROMP Valores negativos aparecem em casos de restart da instancia
PROMP
-- query
with awr as (
select snap_id,
begin_snap,
--round( (value - LAG(value, 1, value) OVER (ORDER BY snap_id)) /60 ,2) AS dbtime_diff
round( (value - LAG(value, 1, value) OVER (PARTITION BY startup_time, instance_number ORDER BY snap_id)) /60 ,2) AS dbtime_diff
from (
select min(s.startup_time) as startup_time,
s.instance_number,
s.snap_id,
min(s.begin_interval_time) as begin_snap,
sum(m.value)/1000/1000 as value
from dba_hist_snapshot s
join dba_hist_sys_time_model m on (s.snap_id = m.snap_id and s.dbid = m.dbid and s.instance_number = m.instance_number)
where 1=1
and m.stat_name = 'DB time'
and s.begin_interval_time >= trunc(sysdate)-&1
and (&2 = 0 or s.instance_number = &2)
group by s.snap_id, s.instance_number
)
)
SELECT TRUNC(begin_snap) snap_date,
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '00', dbtime_diff, 0)) "h0",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '01', dbtime_diff, 0)) "h1",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '02', dbtime_diff, 0)) "h2",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '03', dbtime_diff, 0)) "h3",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '04', dbtime_diff, 0)) "h4",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '05', dbtime_diff, 0)) "h5",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '06', dbtime_diff, 0)) "h6",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '07', dbtime_diff, 0)) "h7",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '08', dbtime_diff, 0)) "h8",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '09', dbtime_diff, 0)) "h9",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '10', dbtime_diff, 0)) "h10",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '11', dbtime_diff, 0)) "h11",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '12', dbtime_diff, 0)) "h12",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '13', dbtime_diff, 0)) "h13",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '14', dbtime_diff, 0)) "h14",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '15', dbtime_diff, 0)) "h15",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '16', dbtime_diff, 0)) "h16",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '17', dbtime_diff, 0)) "h17",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '18', dbtime_diff, 0)) "h18",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '19', dbtime_diff, 0)) "h19",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '20', dbtime_diff, 0)) "h20",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '21', dbtime_diff, 0)) "h21",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '22', dbtime_diff, 0)) "h22",
SUM (DECODE (TO_CHAR (begin_snap, 'hh24'), '23', dbtime_diff, 0)) "h23"
FROM awr
GROUP BY TRUNC(begin_snap)
order by 1;
Como usar
O público alvo (além de mim mesmo) é para quem costumar trabalhar com SQLPLUS ou SQLcl, onde a forma de executar o script foi otimizada para esse uso. Pra ter uma melhor experiência, crie um arquivo dbtime.sql, assim é possível executar a query repetidamente alternando os filtros do período e instância.
Exemplos
Consultando o DB Time dos últimos 30 dias para a instância 1:
SQL> @dbtime 30 1
Consultando o DB Time dos últimos 45 dias para todo o cluster (considera inst_id=0 no filtro):
SQL> @dbtime 45 0
Exemplo do resultado (valores em minutos):
Metrica...: DB Time
Qt. Dias..: 30
Instance..: orcl / lab01.dibiei.com
Valores negativos aparecem em casos de restart da instancia
Date h0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h22 h23
---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
18/10 Tue 102 195 332 307 193 187 70 34 19 19 69 34 6 14 7 47 11 13 86 121 282 54 284 57
19/10 Wed 109 203 353 288 157 224 69 19 46 48 17 49 12 77 47 5 21 13 85 121 264 110 285 60
20/10 Thu 123 214 305 275 180 170 59 18 1 4 23 24 2 15 6 45 18 25 81 124 284 67 302 55
21/10 Fri 107 206 347 301 155 232 69 33 10 13 15 29 2 4 96 21 16 17 121 124 303 70 291 60
22/10 Sat 114 238 280 277 201 191 178 29 0 2 9 439 158 1 4 2 4 16 83 127 258 74 118 49
23/10 Sun 23 5 0 30 3 0 36 0 0 1 17 0 1 1 2 2 4 4 39 4 5 7 18 17
24/10 Mon 70 189 347 286 172 250 73 54 1 3 22 7 6 5 6 29 18 25 100 142 272 114 225 26
25/10 Tue 83 214 369 290 207 128 95 14 4 17 62 48 4 13 20 33 20 16 85 132 249 45 209 54
26/10 Wed 93 212 294 306 168 104 39 0 14 5 4 8 3 11 145 39 9 16 78 133 232 103 269 52
27/10 Thu 93 209 281 264 139 96 33 0 15 4 26 9 23 5 33 7 45 16 82 132 253 93 274 59
28/10 Fri 112 156 313 256 136 93 30 0 2 2 1 0 1 1 3 2 4 17 83 135 237 103 217 58
29/10 Sat 74 214 284 294 170 99 140 0 0 2 8 451 142 1 5 2 4 16 80 134 240 98 98 47
30/10 Sun 2 6 0 44 10 1 30 0 0 1 15 0 1 1 2 2 4 4 38 3 3 10 12 16
31/10 Mon 92 270 283 273 126 95 25 1 11 19 58 49 21 44 37 25 93 34 80 148 221 111 197 43
01/11 Tue 88 212 330 272 172 106 35 22 7 24 33 11 17 6 105 11 46 85 84 139 267 85 308 58
02/11 Wed 98 204 384 251 162 117 42 21 2 2 1 0 1 1 1 2 4 18 81 138 228 82 193 60
03/11 Thu 123 216 274 266 109 106 20 21 5 25 15 36 6 11 3 7 94 22 81 138 240 101 237 59
04/11 Fri 94 230 322 252 132 106 24 21 3 20 11 3 53 2 41 34 20 83 124 146 228 46 167 27
05/11 Sat 71 212 347 264 148 113 135 22 0 2 15 446 152 1 4 2 4 21 80 140 157 6 39 3
06/11 Sun 4 4 0 26 1 0 13 0 0 1 14 0 1 1 2 2 4 4 37 3 3 6 16 27
07/11 Mon 86 233 297 251 126 103 33 24 7 53 152 81 25 77 54 99 82 86 79 141 221 130 232 65
08/11 Tue 107 181 398 259 152 118 54 24 1 12 12 33 6 14 29 120 81 21 76 138 253 87 192 61
09/11 Wed 79 198 351 269 130 107 31 25 2 24 87 63 30 23 15 22 5 14 66 143 177 82 124 60
10/11 Thu 123 335 499 380 199 141 65 23 4 3 16 12 1 5 20 91 63 21 56 154 205 102 151 27
11/11 Fri 131 231 480 357 236 148 67 48 34 13 9 47 18 10 24 53 53 13 67 73 182 177 263 97
12/11 Sat 71 111 252 438 250 105 193 74 58 1 26 385 215 62 11 2 4 4 69 73 138 234 134 65
13/11 Sun 47 37 1 10 18 1 43 1 0 0 21 1 0 1 1 2 4 4 42 4 4 5 16 4
14/11 Mon 24 63 259 413 336 126 88 61 88 61 63 61 60 61 61 62 64 65 129 136 198 469 233 168
15/11 Tue 128 160 324 447 406 226 255 91 52 11 1 1 0 1 1 2 4 4 65 75 135 360 145 92
16/11 Wed 65 110 280 386 230 133 112 67 53 102 75 69 70 63 110 89 51 19 140 152 192 284 233 50
17/11 Thu 68 75 289 315 204 138 89 25 17 57 35 8 1 49 46 65 24 6 65 71 132 199 0 0
31 rows selected.