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