IBM Maximo: SQL Queries: Difference between revisions
Line 109: | Line 109: | ||
= Workflow = | = Workflow = | ||
Migrado para [[IBM Maximo: SQL Queries - WORKFLOW]] | |||
= Escalacoes= | = Escalacoes= |
Revision as of 16:13, 20 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
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