Vitor, eu enviei o plano de execução em anexo.
2015-07-08 9:33 GMT-03:00 Vitor Junior vitorj...@gmail.com [oracle_br] < oracle_br@yahoogrupos.com.br>: > > > Bom dia. > Orfeu, sem conhecer as tabelas envolvidas, plano de execução e pelo menos > um trace, suspeito que ninguém vai conseguir te ajudar aqui... hehehe > > > > > Att,/Regards, > > > Vitor Jr. > Infraestrutura / Infrastructure Team > Oracle 11g DBA Certified Professional - OCP > Oracle Certified Expert, Oracle Real Application Clusters 11g and Grid > Infrastructure Administrator - OCE > Oracle Database 11g Performance Tuning Certified Expert - OCE > Oracle Exadata 11g Certified Implementation Specialist > Oracle Certified Associate, MySQL 5 > mail, gtalk e msn: vitorj...@gmail.com > http://certificacaobd.com.br/ > skype: vjunior1981 > https://mybizcard.co/vitor.jr.385628 > > 2015-07-08 9:26 GMT-03:00 Orfeu Lima orfeu.l...@gmail.com [oracle_br] < > oracle_br@yahoogrupos.com.br>: > >> >> >> bom dia a todos. >> O select abaixo está executando com um tempo de 20s, e gostaria de pedir a >> ajuda para poder estar melhorando. >> Segue abaixo o script e em anexo o plano de execução. >> Obrigado. >> select cd_terminal as "cd" >> , cast(data_captura as timestamp) as "dataUltima" >> , cast(data_adm as timestamp) as "dataFechamento" >> , CASE tipoUltima WHEN 4 THEN 'SAIDA' ELSE 'ENTRADA' END as "fluxo" >> , valorSaques as "valorSaques" >> , valorRecargas as "valorRecargas" >> , valorDepositos as "valorDepositos" >> , totalSaques as "totalSaques" >> , totalRecargas as "totalRecargas" >> , totalDepositos as "totalDepositos" >> , denominacoes as "denominacaoUltima" >> , dados_adm as "denominacaoFechamento" from ( >> with r as ( >> select t.cd_terminal >> , c.cd_tipo_transacao as tipo >> , sum(t.valor) as valor >> , count(*) as total >> from transacao_siscap t, codigo_transacao c, ( >> select busca.terminal >> , busca.tipo as tipoUltima >> , busca.data_captura >> , busca.denominacoes >> , busca.data_adm >> , busca.dados_adm >> from ( >> select trn.cd_terminal as terminal, trn.*, adm.* from ( >> select ultima_adm.cd_terminal, >> data_captura as data_adm, >> dados_adm >> from ( >> select adm.cd_terminal, nvl(max(id), 0) as id >> from transacao_adm_siscap adm, >> terminal atm >> where adm.cd_terminal = atm.cd_terminal >> and cd_proc = 310000 >> and atm.ativo = 1 >> group by adm.cd_terminal >> ) ultima_adm, transacao_adm_siscap adm >> where ultima_adm.id = adm.id >> ) adm, ( >> select trn.cd_terminal, cod.cd_tipo_transacao as tipo >> , trn.data_captura as data_captura >> , e.denominacao_movimentada as denominacoes from ( >> select trn.cd_terminal, max(data_captura) as data_captura >> from transacao_siscap trn, transacao_ext_siscap e, terminal atm >> where trn.cd_terminal = atm.cd_terminal >> and trn.id = e.transacao_id >> and e.denominacao_movimentada is not null >> and atm.ativo = 1 >> and cd_status in (03, 04, 05) >> group by trn.cd_terminal >> ) ultima, transacao_siscap trn, transacao_ext_siscap e, >> codigo_transacao cod >> where trn.cd_terminal = ultima.cd_terminal >> and cod.cd_proc = trn.cd_proc >> and ultima.data_captura = trn.data_captura >> and trn.id = e.transacao_id >> ) trn >> where adm.cd_terminal = trn.cd_terminal >> ) busca >> ) busca1 >> where t.cd_proc = c.cd_proc >> and t.cd_terminal = busca1.terminal >> and t.data_captura between busca1.data_adm and busca1.data_captura >> and t.cd_status IN ('03', '04', '05') >> and c.cd_tipo_transacao in (7,4,2,13) >> group by t.cd_terminal >> , c.cd_tipo_transacao >> ) select t.cd_terminal AS cd_terminal >> , nvl((select valor from r where tipo = 4 and r.cd_terminal = >> t.cd_terminal), 0) as valorSaques >> , nvl((select valor from r where tipo = 7 and r.cd_terminal = >> t.cd_terminal), 0) >> +nvl((select valor from r where tipo = 13 and r.cd_terminal = >> t.cd_terminal), 0) as valorRecargas >> , nvl((select valor from r where tipo = 2 and r.cd_terminal = >> t.cd_terminal), 0) as valorDepositos >> , nvl((select total from r where tipo = 4 and r.cd_terminal = >> t.cd_terminal), 0) as totalSaques >> , nvl((select total from r where tipo = 7 and r.cd_terminal = >> t.cd_terminal), 0) >> +nvl((select total from r where tipo = 13 and r.cd_terminal = >> t.cd_terminal), 0) as totalRecargas >> , nvl((select total from r where tipo = 2 and r.cd_terminal = >> t.cd_terminal), 0) as totalDepositos >> from terminal t >> where t.ativo = 1 >> ) busca2, ( >> select busca.terminal >> , busca.tipoUltima >> , busca.data_captura >> , busca.denominacoes >> , busca.data_adm >> , busca.dados_adm >> from ( >> select trn.cd_terminal as terminal, trn.cd_tipo_transacao as >> tipoUltima, trn.*, adm.* from ( >> select ultima_adm.cd_terminal, >> data_captura as data_adm, >> dados_adm >> from ( >> select adm.cd_terminal, nvl(max(id), 0) as id >> from transacao_adm_siscap adm, >> terminal atm >> where adm.cd_terminal = atm.cd_terminal >> and cd_proc = 310000 >> and atm.ativo = 1 >> group by adm.cd_terminal >> ) ultima_adm, transacao_adm_siscap adm >> where ultima_adm.id = adm.id >> ) adm, ( >> select trn.cd_terminal >> , c.cd_tipo_transacao >> , trn.data_captura as data_captura >> , e.denominacao_movimentada as denominacoes from ( >> select trn.cd_terminal, max(data_captura) as data_captura >> from transacao_siscap trn, transacao_ext_siscap e, terminal atm >> where trn.cd_terminal = atm.cd_terminal >> and trn.id = e.transacao_id >> and e.denominacao_movimentada is not null >> and atm.ativo = 1 >> and cd_status in (03, 04, 05) >> group by trn.cd_terminal >> ) ultima, transacao_siscap trn, transacao_ext_siscap e, >> codigo_transacao c >> where trn.cd_terminal = ultima.cd_terminal >> and trn.cd_proc = c.cd_proc >> and ultima.data_captura = trn.data_captura >> and trn.id = e.transacao_id >> ) trn >> where adm.cd_terminal = trn.cd_terminal >> ) busca >> ) busca3 >> where busca2.cd_terminal = busca3.terminal >> >> [As partes desta mensagem que não continham texto foram removidas] >> >> > >
