Outra forma visualizar os archives gerados por hora (pivot)

Após descobrir como verificar quantos arquivos estão sendo gerados por hora com o post do Maycon Tomiasi, resolvi postar para vocês uma forma diferente de pegar os históricos dos archives de sua instância.

Se acompanharem o oraclehome vocês verão que esta tecnica foi postada aqui ontem Pivot ou CrossTab Post de nosso adm Rafael Stoever.

Esta Forma pode ser de escolha de algumas pessoas para diagnostificar algum desvio padrão no comportamento dos archives.

set linesize 200
Column Total format 99999

SELECT  to_char(first_time, 'mm/dd') "Data",
        to_char(first_time, 'Dy') "Dia",
        count(1) "Total",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'00',1,0)),'999'),'   0',
'   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'00',1,0)),'999')) "00",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'01',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'01',1,0)),'99')) "01",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'02',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'02',1,0)),'99')) "02",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'03',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'03',1,0)),'99')) "03",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'04',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'04',1,0)),'99')) "04",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'05',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'05',1,0)),'99')) "05",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'06',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'06',1,0)),'99')) "06",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'07',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'07',1,0)),'99')) "07",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'08',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'08',1,0)),'99')) "08",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'09',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'09',1,0)),'99')) "09",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'10',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'10',1,0)),'99')) "10",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'11',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'11',1,0)),'99')) "11",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'12',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'12',1,0)),'99')) "12",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'13',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'13',1,0)),'99')) "13",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'14',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'14',1,0)),'99')) "14",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'15',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'15',1,0)),'99')) "15",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'16',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'16',1,0)),'99')) "16",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'17',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'17',1,0)),'99')) "17",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'18',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'18',1,0)),'99')) "18",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'19',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'19',1,0)),'99')) "19",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'20',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'20',1,0)),'99')) "20",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'21',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'21',1,0)),'99')) "21",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'22',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'22',1,0)),'99')) "22",
        decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'23',1,0)),'99'),
'   0','   ',to_char(SUM(decode(to_char(first_time, 'hh24'),'23',1,0)),'99')) "23"
FROM    sys.v_$log_history
where first_time between sysdate -20 and sysdate
group by to_char(first_time, 'mm/dd'), to_char(first_time, 'Dy')
order by 1 desc;

Não se assuste com o tamanho do script, ele apenas faz varios decodes para mostrar de uma forma amigável a informação na tela.

Sua saida é bem detalhada conforme abaixo.

Data  Dia  Total 00   01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23
----- --- ------ ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
11/08 Tue    394   24  29  24  24  24  24  24  30  29  42  28  28  24  24  16   0   0   0   0   0   0   0   0   0
11/07 Mon    613   24  29  24  24  24  24  24  31  24  29  42  24  24  24  24  24  25  24  24  24  24  24  25  24
11/06 Sun    585   24  30  24  24  24  24  24  24  24  24  24  24  24  24  24  24  24  24  24  24  24  24  25  26
11/05 Sat    582   24  29  23  24  24  24  23  24  24  24  26  24  24  24  24  24  24  24  24  24  25  24  24  24
11/04 Fri    624   24  30  24  24  24  24  24  30  24  24  24  24  24  24  24  24  24  24  40  44  24  24  24  24
11/03 Thu    617   24  29  24  24  24  24  24  31  24  24  29  24  27  24  42  26  24  24  24  24  24  24  25  24
11/02 Wed    591   26  27  26  27  24  24  24  24  24  26  24  24  24  24  24  24  24  24  24  24  24  24  25  26
11/01 Tue    637   24  29  24  24  24  27  42  29  26  25  29  24  24  26  27  40  24  24  24  24  24  24  25  24
10/31 Mon    628   25  28  24  24  24  24  24  30  25  28  24  24  24  24  34  43  24  26  24  24  26  24  25  26
10/30 Sun    584   24  28  24  24  24  26  24  24  24  24  24  24  24  24  24  24  24  25  24  24  24  24  24  25
10/29 Sat    585   24  27  24  24  24  24  24  24  24  24  28  24  24  24  24  24  24  24  24  24  25  24  25  24
10/28 Fri    618   26  27  24  24  24  24  24  31  24  24  37  34  24  24  24  28  24  26  24  24  24  24  25  24
10/27 Thu    609   24  27  24  24  24  24  24  31  24  28  25  28  37  24  24  24  24  24  24  24  24  24  24  25
10/26 Wed    609   24  27  24  24  24  25  24  30  25  29  38  24  24  24  24  24  24  24  24  24  24  25  24  26
10/25 Tue    612   25  28  24  24  24  24  24  30  27  27  41  24  24  24  24  24  24  24  24  24  24  25  24  25
10/24 Mon    607   26  28  24  24  24  25  24  32  28  32  26  24  24  24  25  24  24  24  24  24  24  25  24  24
10/23 Sun    582   25  28  24  24  24  24  24  24  24  24  24  24  25  24  24  24  24  24  24  24  24  24  24  24
10/22 Sat    583   24  28  24  23  24  25  24  24  24  24  26  24  24  24  24  24  24  24  24  24  25  24  24  24
10/21 Fri    610   24  27  24  24  24  24  24  31  26  24  30  28  24  25  24  26  29  26  24  24  24  24  24  26
10/20 Thu    410        0   0   0   0   0   0  11  24  24  29  26  24  30  24  24  24  24  24  24  24  24  24  26

Na primeira linha temos a informação de data e hora e nas inhas abaixo é a quantidade de cada hora respectiva de sua coluna.

Caso você observar que todo dia em determinado horário gera mais archives é mais fácil de identificar.

%name Outra forma visualizar os archives gerados por hora (pivot)

Autor: Leandro Lana

Trabalho com banco de dados Oracle desde 2006, já trabalhei com as plataformas 9i, 10G, 11G e 12C.

Trabalhando atualmente como consultor Oracle na MigraTI Soluções em TI como administrador de banco de dados Oracle, SQL-Server e DB2.

Contato: leandro.lana@migrati.com.br

Fone: (47) 9191-6052 / (47) 3328 0996

Certificações:

OCA 10G.

OCP 10G.

OCE Linux.

OCE RAC/Cluster.

MCP SQL-Server 2008.

MCITP SQL-Server 2008.

DB2 Fundamentals.