TUNING QUERY

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
madriano210
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Qua, 11 Set 2019 4:28 pm

Bom dia !
Estou com problemas na otimização de uma query.
Inicialmente, o custo da query era de 646780, consegui reduzir para 207443, ainda um custo alto.
Em ambas esta me retornando 'ORA 01652: unable to extend temp segment by 128 in tablespace TEMP_ANALYST'.

Inicialmente, a ideia é pegar o período inteiro, sem limitação de data. Gostaria de saber se tem alguma dica, ou a melhor maneira seria criar um relatório diário e popular uma segunda tabela, executando assim a query principal em cima desta tabela 2.

*O uso de HINTS está descartado, visto que por orientação de nosso DBA o consumo estava muito alto no servidor, então o melhor e a otimização das querys e diminuição do uso da CPU.

Seguem os códigos para análise:

01. Query inicial(custo: 646780):

Selecionar tudo

Select 5 ID,
       Count(1) QTY,
       0 AMOUNT,       
       nvl(Sum(DECODE(trunc(created_date),trunc(sysdate),0,1)),0) DAY_COUNT,
       0 DAY_AMOUNT,
       'TRANSACAO' Name,
       'TLOG TI Sustentacao Financeiro' ENVIROMENT,
       'SENTINELA' Segment,
       'AUTORIZACAO COM INFORMACAO ADICIONAL não ENCONTRADA (TOUT_AUTHORIZATION_NONFINANCIAL => TOUT_AUTHORIZATION)' COMMENTS,
       Sysdate CREATED_DATE,
       Sysdate LAST_UPDATE_TIME
  from wtbr_interface.tout_authorization_non_financ gd
 where not exists (select 1
                     from wtbr_interface.tout_authorization a
                    where a.organization_id = gd.organization_id
                      and a.authorization_id = gd.authorization_id);
02. Query otimizada (custo: 207443) :

Selecionar tudo

with periodo as(select '5'                   IDX,
                       min(nf.created_date)  DT_INICIO,
                       max(nf.created_date)        DT_FINAL
                  from wtbr_interface.tout_authorization_non_financ nf),
              
          gd as (select nf.*,
                   row_number() over(Partition By nf.organization_id, nf.authorization_id, nf.vehicle_id Order By nf.batch_id Desc) rowline,
                   (select 1
                         from wtbr_interface.tout_authorization a
                        where a.organization_id = nf.organization_id
                          and a.authorization_id = nf.authorization_id) qtde_transacoes
              from wtbr_interface.tout_authorization_non_financ nf
              join periodo pr on pr.idx = 5
             where nf.created_date between pr.DT_INICIO and pr.DT_FINAL)
                
Select 5 ID,
       Count(1) QTY,
       0 AMOUNT,       
       nvl(Sum(DECODE(trunc(created_date),trunc(sysdate),0,1)),0) DAY_COUNT,
       0 DAY_AMOUNT,
       'TRANSACAO' Name,
       'TLOG TI Sustentacao Financeiro' ENVIROMENT,
       'SENTINELA' Segment,
       'AUTORIZACAO COM INFORMACAO ADICIONAL não ENCONTRADA (TOUT_AUTHORIZATION_NONFINANCIAL => TOUT_AUTHORIZATION)' COMMENTS,
       Sysdate CREATED_DATE,
       Sysdate LAST_UPDATE_TIME
  from gd
 where gd.qtde_transacoes = 0
   and gd.rowline = 1;
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Estranho o DBA sugerir pra não usar HINTS...
Só por curiosidade, que tipo de HINTS ele sugere que não seja usado?
Algum específico? ou TODOS?

Outra coisa estranha é o erro:

Selecionar tudo

"ORA 01652: unable to extend temp segment by 128 in tablespace TEMP_ANALYST'."
Tipo, porque não extender a TEMP? (Suas tabelas são muito grandes?)

To vendo a sua query principal.
Algumas perguntas:

1. Quantas linhas tem a tabela wtbr_interface.tout_authorization_non_financ ?
2. Quantas linhas tem a a tabela wtbr_interface.tout_authorization ?
3. A tabela wtbr_interface.tout_authorization possui algum índice nos campos organization_id e authorization_id?

Só pra explicar:
Pra CADA LINHA da tabela wtbr_interface.tout_authorization_non_financ ele vai fazer uma consulta na outra, pra ver se não existe.

Eu não conheço as suas tabelas, mas fico pensando aqui se não tem como "evitar" essas segunda consulta.
Algo assim:

Selecionar tudo

Select 5 ID,
       Count(1) QTY,
       0 AMOUNT,       
       nvl(Sum(DECODE(trunc(created_date),trunc(sysdate),0,1)),0) DAY_COUNT,
       0 DAY_AMOUNT,
       'TRANSACAO' Name,
       'TLOG TI Sustentacao Financeiro' ENVIROMENT,
       'SENTINELA' Segment,
       'AUTORIZACAO COM INFORMACAO ADICIONAL não ENCONTRADA (TOUT_AUTHORIZATION_NONFINANCIAL => TOUT_AUTHORIZATION)' COMMENTS,
       Sysdate CREATED_DATE,
       Sysdate LAST_UPDATE_TIME
  from wtbr_interface.tout_authorization_non_financ gd
where (organization_id, authorization_id ) not in (select a.organization_id, a.authorization_id)
                                                   from wtbr_interface.tout_authorization a);
(PS: não sei a quantidade das suas tabelas, então essa opção acima pode ser furada).

Aguardamos mais informação!
:-o
madriano210
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Qua, 11 Set 2019 4:28 pm

Então, sobre os hints. Estávamos usando o parallel, porém o consumo total estava muito alto, causando problemas de performance no banco. E com a quantidade de dados que hoje compõem a tabela acaba que temos o problema com a temp de qualquer forma.

As tabelas são muito grandes, a non_financial tem mais de 72 milhões de registros e a authorization mais de 160 milhões.
Por conta desse grande volume de dados estou tendo o problema de espaço na temp.

Estou tentando convencê-los de usar uma tabela auxiliar e fazer o relatório diariamente, assim faço uma query apenas somando os valores diários. Acredito que só assim conseguiremos fazer o que querem, um relatório de tudo que há no sistema de forma rápida.
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Certo, o Parallel tem que ser usado com muita cautela. Correto.
E sobre o estouro da TEMP, é compreensível.

Acho que sua idéia é boa, dividir a tarefa e rodar diariamente.
Não sei se você tem como buscar tudo que alterou apenas no último dia.
(Se a tabela tem esse tipo de informação).

Realmente, não existe um comando mágico pra esse tipo de situação. As vezes até requer uma alteração na modelagem.
madriano210
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Qua, 11 Set 2019 4:28 pm

dr_gori escreveu:Certo, o Parallel tem que ser usado com muita cautela. Correto.
E sobre o estouro da TEMP, é compreensível.

Acho que sua idéia é boa, dividir a tarefa e rodar diariamente.
Não sei se você tem como buscar tudo que alterou apenas no último dia.
(Se a tabela tem esse tipo de informação).

Realmente, não existe um comando mágico pra esse tipo de situação. As vezes até requer uma alteração na modelagem.
Sim, possuímos um controle de versão para os registros, tanto com um batch_id, quanto com campos created_date e last_updated_time. A rotina diária acredito que seja a mais plausível, a não ser que deixemos o relatório como mensal, assim podemos fazer a query limitando ao mês de referência desejado.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 18 visitantes