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.
Autor: Leandro Lana
Trabalho com banco de dados Oracle desde 2006, já trabalhei com as plataformas 9i, 10G, 11G, 12C, 18C, 19C e 21(ainda em testes).
Trabalhando atualmente como consultor Oracle na MigraTI Soluções em TI como administrador de banco de dados Oracle, SQL-Server, MySQL e Postgresql.
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.
