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]
