IBM Maximo: SQL Queries: Difference between revisions

From Wiki
Line 165: Line 165:
= Escalacoes=
= Escalacoes=


== Historico ==
== Arquivamento==
 
Queries uteis para arquivamento/exclusao de registros em tabelas
 
  select to_char(statusdate, 'YYYY') as YEAR1, count(1) as CONT
  select to_char(statusdate, 'YYYY') as YEAR1, count(1) as CONT
  from escstatus  
  from escstatus  

Revision as of 20:45, 14 December 2021


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

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)))

Historico

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