Performance

(Oracle DB) Versão do Oracle

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

select banner from v$version where rownum < 2;

resultado:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

(Oracle DB) 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               | 28639|319467180|2022-02-10 00:50:25.000|
WORKORDER              | 65996 |226390056|2022-02-10 08:33:14.000|
...

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

(Oracle DB) 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|
--------+---+
09/02/22|171|
10/02/22|395|
11/02/22|218|
...

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

Migrado para IBM Maximo: SQL Queries - WORKFLOW

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

Dica de Delete Workflow History for Inactive Records

Ver também