Performance

Maiores tabelas e atualização de estatísticas

Verificando as maiores tabelas e a atualização de estatísticas das tabelas.

select table_name, round((num_rows*avg_row_len)/1048576) tbsize, num_rows, last_analyzed
from all_tables
where owner='MAX'
and num_rows>0
order by num_rows desc

resultado

table_name   tbsize   num_rows    last_analyzed
WOSTATUS     27442    306121780   24-SET-21
WORKORDER    65996    95582941    24-SET-21
...

Mesmo que seu ambiente tenha pouca movimentação, atualizar as estatísticas é fundamental pra um funcionamento do ambiente com qualidade.

Atualização de estatísticas

Verificando a atualização de estatísticas das tabelas.

select TO_CHAR(last_analyzed) as last_a, count(1) as cnt
from all_tables
where owner='MAX'
and num_rows>0
group by TO_CHAR(last_analyzed)
order by TO_CHAR(last_analyzed)

resultado

last_a         cnt
17-NOV-21	148
18-NOV-21	569
26-NOV-20	217
...

Geral

Registros por Ano

select yList.year y,
(select count(*) from workorder where extract(year from reportdate)=yList.year) c_workorder,
(select count(*) from wfinstance where extract(year from starttime)=yList.year) c_wfinstance,
(select count(*) from ticket where extract(year from reportdate)=yList.year) c_ticket,
(select count(*) from po where extract(year from orderdate)=yList.year) c_po,
(select count(*) from matrectrans where extract(year from transdate)=yList.year) c_matrectrans,
(select count(*) from servrectrans where extract(year from transdate)=yList.year) c_servrectrans
from (select column_value as year FROM table(sys.odcivarchar2list('2020','2021'))) yList
group by yList.year
order by yList.year;

Relatórios

Listar os relatórios executados com maior tempo de duracao nos últimos 30 dias

select reportname, description, appname, lastrunduration, lastrundate
from report
where lastrunduration is not null
and lastrundate > sysdate - 30
order by lastrunduration desc

Listar os relatórios mais executados nos últimos 30 dias

select reportname, appname, sum(runtime)/1000 tot_exec_time, count(*) num_exec, round(avg(runtime)/1000,3) avg_exec_time
from reportusagelog
where startdate > sysdate - 30
group by reportname, appname
order by tot_exec_time desc

Listar os relatórios por hora

select extract(hour from (cast(startdate as timestamp))) exechour, round(sum(enddate-startdate)*24*60*60,3) exec_time
from reportusagelog
where startdate > sysdate - 30
group by extract(hour from (cast(startdate as timestamp)))
order by extract(hour from (cast(startdate as timestamp)))

Outros exemplos

IBM Maximo: Exportando um relatório (BIRT) do banco de dados do Maximo

Crontasks

Listar as crontasks executados com maior tempo de duracao nos últimos 30 dias

select crontaskname, instancename, servername, (endtime-starttime)*24*60*60 exec_time, starttime
from crontaskhistory
where activity='ACTION'
and starttime > sysdate - 30
order by exec_time desc

Listar as crontasks executados por hora

select extract(hour from (cast(starttime as timestamp))) exec_hour, round(sum(endtime-starttime)*24*60*60,3) exec_time
from crontaskhistory
where activity='ACTION'
and starttime > sysdate - 30
group by extract(hour from (cast(starttime as timestamp)))
order by extract(hour from (cast(starttime as timestamp)))

Workflow

Historico

select to_char(STARTTIME, 'YYYY') as YEAR1, count(1) as CONT
from wfinstance 
where active=0
and deletable=1
group by to_char(STARTTIME, 'YYYY')
order by to_char(STARTTIME, 'YYYY')

result:

YEAR1	CNT
2017	536611
2018	12263485
2019	17314752
2020	10712313
2021	20238022

Arquivamento

Queries uteis para arquivamento/exclusao de registros em tabelas

select count(1)                    
from wfinstance
where active=0 and deletable=1

select count(1)                    
from wfinstance
where active=0 and deletable=1
and starttime < sysdate -90

select count(1) from wftransaction 
where wfid in  
(select wfid
from wfinstance
where active=0 and deletable=1
and starttime < sysdate -90)

select count(1) from wfcallstack    
where wfid in  
(select wfid
from wfinstance
where active=0 and deletable=1
and starttime < sysdate -90)

Escalacoes

Arquivamento

Queries uteis para arquivamento/exclusao de registros em tabelas

select to_char(statusdate, 'YYYY') as YEAR1, count(1) as CONT
from escstatus 
group by to_char(statusdate, 'YYYY')
order by to_char(statusdate, 'YYYY')

2019	719.178
2020	773.078
2021	799.968

Login

Logins nos últimos 2 dias

select * from logintracking
where attemptresult='LOGIN'
and attemptdate > sysdate - 2

Listar os Logins por hora

select extract(hour from (cast(attemptdate as timestamp))) login_hour, count(*) num_login
from logintracking 
where attemptresult='LOGIN'
and attemptdate > sysdate - 10
group by extract(hour from (cast(attemptdate as timestamp)))
order by extract(hour from (cast(attemptdate as timestamp)))

Arquivamento

Queries uteis para arquivamento/exclusao de registros em tabelas

select to_char(attemptdate, 'YYYY') as YEAR1, count(1) as CONT
from logintracking 
group by to_char(attemptdate, 'YYYY')
order by to_char(attemptdate, 'YYYY')

2019	296.852
2020	205.320
2021	221.299

Ver também