desculpem, não sabia. Vitor, posso estar te encaminhando para vc dar uma olhada??
2015-07-08 10:19 GMT-03:00 Vitor Junior vitorj...@gmail.com [oracle_br] < oracle_br@yahoogrupos.com.br>: > > > Orfeu, o grupo não aceita anexos! :) > > > > 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 10:18 GMT-03:00 Orfeu Lima orfeu.l...@gmail.com [oracle_br] < > oracle_br@yahoogrupos.com.br>: > >> >> >> 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] >>>> >>>> >>> >> > >
