Paulo, muito obrigado pela ajuda. Verifiquei que existe um nested Loops que possui um custo de 193.748, na ferramenta sql tools que estou ele nao me diz qual é a tabela, irei realizar o plano cartesionao no sql plus e irei mostrar aqui, o APPEND realmente é uma boa, hoje a noite quando colocar pra rodar novamente o insert, eu irei acrescentar o hint sim. Outra coisa, quando mudei para o padrão Oracle normal acontece o seguinte:
from faturamento.conta cnta, cadastro.imovel imo, cadastro.localidade loc, cadastro.localidade elo, cadastro.quadra qua, micromedicao.rota rot, cadastro.setor_comercial stc, cadastro.gerencia_regional ger, cadastro.unidade_negocio uni, ( select coalesce( esf.epod_id, 0 ) as epod_id, cim.imov_id, cli.cltp_id from cadastro.cliente_imovel cim, cadastro.cliente cli, cadastro.cliente_tipo ctp, cadastro.esfera_poder esf, cadastro.cliente_relacao_tipo crt where cli.clie_id = cim.clie_id AND cli.cltp_id = ctp.cltp_id AND ctp.epod_id = esf.epod_id AND cim.crtp_id = crt.crtp_id AND crt.crtp_id = 2 AND( cim.clim_dtrelacaofim is null ) ) esferaPoder, atendimentopublico.ligacao_agua lag, atendimentopublico.ligacao_agua_perfil lap, atendimentopublico.ligacao_esgoto leg, atendimentopublico.ligacao_esgoto_perfil lep, cadastro.SISTEMA_PARAMETROS sp -- essa tabela nao tem join, soh possui uma linha where cnta.imov_id = imo.imov_id and loc.loca_id = imo.loca_id and elo.loca_id = loc.loca_cdelo and qua.qdra_id = imo.qdra_id and rot.rota_id = qua.rota_id and stc.stcm_id = qua.stcm_id and ger.greg_id = loc.greg_id and uni.uneg_id = loc.uneg_id AND esferaPoder.imov_id = imo.imov_id (+) AND lagu_id = imo.imov_id (+) AND lap.lapf_id = lag.lapf_id (+) AND leg.lesg_id = imo.imov_id (+) AND lep.lepf_id = leg.lepf_id (+) ORA-01417: uma tabela só pode ser externamente unida a uma outra tabela no máximo ________________________________ De: Paulo A. Petruzalek <ppetruza...@yahoo.com.br> Para: oracle_br@yahoogrupos.com.br Enviadas: Quinta-feira, 31 de Maio de 2012 12:50 Assunto: Re: [oracle_br] TABLE ACCESS FULL Olha, sem ter acesso ao seu ambiente não ajuda muito ter a query. Um table acess full no seu caso provavelmente é o que você precisa mesmo, isso por si só não quer dizer que é um plano ruim, apenas que em ambos os casos você está acessando mais de 20% das linhas das tabelas, e portanto, é mais barato para o banco fazer o full table scan. Além disso, 3 milhões de um lado e 30 milhões do outro não é considerado um volume de dados muito grande para os bancos atuais e se for só isso deveria resolver em pouco tempo. Eu particularmente estaria procurando no seu plano falhas na condição de join (ex: merge join cartesian) e nested loops (para milhões de linhas é ruim fazer nested loops). Outra coisa, eu vi que você está usando ANSI JOINS, pela minha experiência o Oracle não lida bem com essa sintaxe quando há muitas tabelas envolvidas, então sugiro reescrever usando a sintaxe Oracle. Finalmente, você não mostra o insert, mas sendo uma carga massiva de dados sugiro que você use a hint APPEND para agilizar a carga dos dados e consumir menos recursos do banco. Vocẽ não fala a sua versão, o que limita bastante as opções, mas se estiver num banco enterprise pode usar paralelismo na query para tentar resolvê-la mais rapidamente. []'s Paulo On Thu, 31 May 2012 14:07:16 -0000 "vieira.rafael44" <vieira.rafae...@yahoo.com.br> wrote: > Pessoal, bom dia. Estou com uma query que é usada em um insert que está > demorando em torno de 18 horas. Fiz uma análise e limpei alguns lixos, mas > continua lenta, não irei colar o plano de execução aqui, pois é muito grande. > > Obs: O servidor está livre apenas para rodar esse script, só quem tem acesso > sou eu. > > Tabela faturamento.conta possui 30 milhões de registro e está dando ACCESS > FULL na tabela > > tabela cadastro.imovel possui 3 milhoes registro e tb ocorre ACCESS FULL. > > Segue query para análise: > > SELECT CASE WHEN (substr(sp.parm_amreferenciaarrecadacao,5, 2) = '01') THEN > To_Number(substr(sp.parm_amreferenciaarrecadacao,1, 4) - 1||'12') ELSE > sp.parm_amreferenciaarrecadacao - 1 END as rpen_amreferencia, -- Ano mês de > referência > ger.greg_id, -- Gerência > imo.iper_id, -- Perfil do imovel > imo.last_id, -- Ligacao de Agua Situacao > elo.loca_id as elo, -- Elo > imo.lest_id, -- Ligacao de Esgoto Situacao > imo.imov_idcategoriaprincipal, ---imo.idcatg_pricipal, > imo.imov_idsubcategoriaprincipal, ---imo.idscat_id, > esferaPoder.epod_id, > cltp_id, ---cli.cltp_id, > Nvl(lap.lapf_id,0), > Nvl(lep.lepf_id,0), > stc.stcm_cdsetorcomercial, -- Codigo do Setor Comercial > qua.qdra_nnquadra, -- Numero da quadra > uni.uneg_id, -- Unidade > qua.rota_id, -- Rota > loc.loca_id, -- Localidade > stc.stcm_id, -- Setor Comercial > qua.qdra_id, -- Quadra > case when ( lag.lagu_nnconsumominimoagua > 0 ) then > 1 > else > 2 > end, -- Volume fixado de agua > case when ( leg.lesg_nnconsumominimoesgoto > 0 ) then > 1 > else > 2 > end, -- Volume fixado de Esgoto > > 1, --- dotp_id > cnta.cnta_amreferenciaconta, -- Ano mes de referencia da conta > > > case when > ( imo.last_id in ( 3, 5 ) and lag.hidi_id is not null ) or > -- Medido de Agua > ( imo.lest_id = 3 and imo.hidi_id is not null ) then -- > Medido de Esgoto > 1 > else > 2 > end as indMedicao, -- Indicador de medicao > case when ( to_char(cnta.cnta_dtvencimentoconta, 'YYYYMM') < > sp.parm_amreferenciafaturamento ) then > 1 > else > 2 > end, -- Referencia do vencimento da conta > --count( distinct imo.imov_id ), -- Quantidade de Ligacoes > 0, > count(*), --- qtd_documentos, > sum( coalesce( cnta.cnta_vlagua, 0 ) ) as rpen_vlpendente_agua, > -- Valor de Agua > sum( coalesce( cnta.cnta_vlesgoto, 0 ) ) as > rpen_vlpendente_esgoto, -- Valor de Esgoto > sum( coalesce( cnta.cnta_vldebitos, 0 ) ) as > rpen_vlpendente_debitos, -- Valor de Debitos > sum( coalesce( cnta.cnta_vlcreditos, 0 ) ) as > rpen_vlpendente_creditos, -- Valor de Creditos > sum( coalesce( cnta.cnta_vlimpostos, 0 ) ) as > rpen_vlpendente_impostos, -- Valor de Impostos > imo.cstf_id, -- Consumo Tarifa > rot.rota_cdrota -- Codigo da Rota > > from faturamento.conta cnta > inner join cadastro.imovel imo on cnta.imov_id = imo.imov_id > inner join cadastro.localidade loc on loc.loca_id = imo.loca_id > inner join cadastro.localidade elo on elo.loca_id = loc.loca_cdelo > inner join cadastro.quadra qua on qua.qdra_id = imo.qdra_id > inner join micromedicao.rota rot on rot.rota_id = qua.rota_id > inner join cadastro.setor_comercial stc on stc.stcm_id = qua.stcm_id > inner join cadastro.gerencia_regional ger on ger.greg_id = loc.greg_id > inner join cadastro.unidade_negocio uni on uni.uneg_id = loc.uneg_id > left join ( select coalesce( esf.epod_id, 0 ) as epod_id, > cim.imov_id, > cli.cltp_id > from cadastro.cliente_imovel cim -- on > imo.imov_id=cim.imov_id > inner join cadastro.cliente cli on > cli.clie_id=cim.clie_id > inner join cadastro.cliente_tipo ctp on > cli.cltp_id=ctp.cltp_id > inner join cadastro.esfera_poder esf on > ctp.epod_id=esf.epod_id > inner join cadastro.cliente_relacao_tipo crt on > cim.crtp_id=crt.crtp_id > where crt.crtp_id=2 > and ( cim.clim_dtrelacaofim is null ) ) esferaPoder > on ( esferaPoder.imov_id = imo.imov_id ) > left join atendimentopublico.ligacao_agua lag on lag.lagu_id = imo.imov_id > left join atendimentopublico.ligacao_agua_perfil lap on lap.lapf_id = > lag.lapf_id > left join atendimentopublico.ligacao_esgoto leg on leg.lesg_id = imo.imov_id > left join atendimentopublico.ligacao_esgoto_perfil lep on lep.lepf_id = > leg.lepf_id, > cadastro.SISTEMA_PARAMETROS sp > where ( ( cnta.cnta_amreferenciacontabil <= (CASE WHEN > (substr(sp.parm_amreferenciafaturamento,5, 2) = '01') THEN > to_number((substr(sp.parm_amreferenciafaturamento, 1, 4) - 1) || '12') ELSE > sp.parm_amreferenciafaturamento - 1 END) > and cnta.dcst_idatual in ( 0,1,2) ) > or ( cnta.cnta_amreferenciacontabil > (CASE WHEN > (substr(sp.parm_amreferenciafaturamento, 5, 2) = '01') THEN > to_number((substr(sp.parm_amreferenciafaturamento, 1, 4) - 1) || '12') ELSE > sp.parm_amreferenciafaturamento - 1 END) and cnta.dcst_idatual in (3,4,5,8) ) > and ( cnta.dcst_idanterior is null > or ( cnta.cnta_amreferenciaconta <= (CASE WHEN > (substr(sp.parm_amreferenciafaturamento,5, 2) = '01') THEN > to_number((substr(sp.parm_amreferenciafaturamento, 1, 4) - 1) || '12') ELSE > sp.parm_amreferenciafaturamento - 1 END) and cnta.dcst_idanterior not in (2)) > )) > group by CASE WHEN (substr(sp.parm_amreferenciaarrecadacao,5, 2) = '01') > THEN To_Number(substr(sp.parm_amreferenciaarrecadacao,1, 4) - 1||'12') ELSE > sp.parm_amreferenciaarrecadacao - 1 END, > ger.greg_id, -- Gerência > imo.iper_id, -- Perfil do imovel > imo.last_id, -- Ligacao de Agua Situacao > elo.loca_id , -- Elo > imo.lest_id, -- Ligacao de Esgoto Situacao > imo.imov_idcategoriaprincipal, ---imo.idcatg_pricipal, > imo.imov_idsubcategoriaprincipal, ---imo.idscat_id, > esferaPoder.epod_id, > cltp_id, ---cli.cltp_id, > Nvl(lap.lapf_id,0), > Nvl(lep.lepf_id,0), > stc.stcm_cdsetorcomercial, -- Codigo do Setor Comercial > qua.qdra_nnquadra, -- Numero da quadra > uni.uneg_id, -- Unidade > qua.rota_id, -- Rota > loc.loca_id, -- Localidade > stc.stcm_id, -- Setor Comercial > qua.qdra_id, -- Quadra > case when ( lag.lagu_nnconsumominimoagua > 0 ) then > 1 > else > 2 > end, -- Volume fixado de agua > case when ( leg.lesg_nnconsumominimoesgoto > 0 ) then > 1 > else > 2 > end, -- Volume fixado de Esgoto > > 1, --- dotp_id > cnta.cnta_amreferenciaconta, -- Ano mes de referencia da conta > > > case when > ( imo.last_id in ( 3, 5 ) and lag.hidi_id is not null ) or > -- Medido de Agua > ( imo.lest_id = 3 and imo.hidi_id is not null ) then -- > Medido de Esgoto > 1 > else > 2 > end , -- Indicador de medicao > case when ( to_char(cnta.cnta_dtvencimentoconta, 'YYYYMM') < > sp.parm_amreferenciafaturamento ) then > 1 > else > 2 > end, -- Referencia do vencimento da conta > --count( distinct imo.imov_id ), -- Quantidade de Ligacoes > 0, > imo.cstf_id, -- Consumo Tarifa > rot.rota_cdrota > > > > ------------------------------------ > > ---------------------------------------------------------- > >Atenção! As mensagens do grupo ORACLE_BR são de acesso público e de inteira > >responsabilidade de seus remetentes. > Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/ > ---------------------------------------------------------- > >Apostilas » Dicas e Exemplos » Função » Mundo Oracle » Package » Procedure » > >Scripts » Tutoriais - O GRUPO ORACLE_BR TEM SEU PROPRIO ESPAÇO! VISITE: > >http://www.oraclebr.com.br/ > ---------------------------------------------------------- Links do Yahoo! > Grupos > > -- Paulo A. Petruzalek Oracle DBA OCA 11g - OCP PL/SQL ppetruza...@yahoo.com.br [As partes desta mensagem que não continham texto foram removidas]
