Ederson, muito obrigado pela ajuda amigo, vai servir de mais, não só para essa, mas para todas as outras query's. Obrigado amigo.
________________________________ De: ederson2001br <ederson200...@yahoo.com.br> Para: oracle_br@yahoogrupos.com.br Enviadas: Quinta-feira, 31 de Maio de 2012 16:04 Assunto: [oracle_br] Re: TABLE ACCESS FULL Alô Rafael, Minhas sugestões: - Usando o dbms_sqltune.create_tuning_task, você poderá usar o analisador do Oracle para te dar "aquela mão". Para quem não conhece este segredinho, lá vai: - Abra o SQLPLUSW, edite um arquivo chamado P1.SQL e coloque nele: --P1.sql DECLARE my_task_name VARCHAR2 (30); my_sqltext CLOB; BEGIN my_sqltext := ' coloque o seu SQL aqui sem ponto-e-virgula-final '; my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext, --bind_list => sql_binds (anydata.convertnumber (9)), -- se ORA-01008, comente esta linha user_name => 'NM', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'TASK_TEST', description => 'Tuning Task' ); END; / Dica: coloque somente um atributo no select, o importante é a clausula WHERE. Não será retornado nenhuma linha, somente a análise do plano de execução. Não pode usar ASPAS-SIMPLES e nem ASPAS-DUPLAS, pois a atribuição de MY_SQLTEXT é feita com o sql no formato de uma string. Pode usar variável BIND, não precisa atribuir valores. - Outros arquivos: --P2.sql BEGIN dbms_sqltune.execute_tuning_task (task_name => 'TASK_TEST'); END; / --P3.sql column task_name format a20; SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'TASK_TEST'; --P4.sql SET LONG 20000 SET LONGCHUNKSIZE 1000 SET LINESIZE 150 set pagesize 1000 column status format a15 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'TASK_TEST') FROM DUAL; --P5.sql SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('TASK_TEST') RECOMMENTATION FROM DUAL; --P6.sql BEGIN DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'TASK_TEST'); END; / --> Como usar: -Coloque o seu sql na variável my_sqltext em P1.SQL -No Sqlplus, rode @p1, @p2, @p3, @P4, @p5 na sequencia -O P4.sql vai te dar o resumo da melhoria, como criação de índices, verificação de tabelas sem join, etc -Despreze recomendação de aplicar o sql_profile -O P4.sql também simula a criação de indices e mostra como "ficaria o plano", se eles existissem. -O P5.sql dá os comandos de criação dos indices sugeridos -O P6.sql apaga a transação criada em P1, para você rodar novamente ou com outro SQL. Faça bom uso, o recurso é um show! Ederson Elias DBA Oracle http://br.linkedin.com/pub/ederson-elias/24/8b/8b0 --- Em oracle_br@yahoogrupos.com.br, "vieira.rafael44" <vieira.rafael44@...> escreveu > > 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 > [As partes desta mensagem que não continham texto foram removidas]